AS400 traditional method to create table

[TWillard]

I am new to db2/400. I am trying to understand everything about creating physical files. I am looking at table creation for now. Can someone provide some experience to the following questions. For now, I am learning via the traditional methods.

1) I can create a table within STRSQL as such: "create table xxx (name varchar(20))". The message returns states that the table was created but could not be journalized. Why can it not be journalized and should I care if it is not?

2) I attempt to create a table TEST3 as such: "CRTPF FILE(TEST3)". It does not create the file and the following message is returned. "Member TEST3 in QDDSSRC in library *LIBL not found". What does this mean? I thought I was using the crtpf command to create files?

3) Finally, I tried creating a file on my PC and FTPing it to the 400 machine. The contents of the file state the following: "create table xxx (name varchar(20))". I FTPed using ASCII. I then tried to use RUNSQLSTM against the file, as such RUNSQLSTM SRCFILE(*CURLIB/TEST2) SRCMBR(TEST2). This errored out as well. The message stated RUNSQLSTM command failed.

Additionally, I noticed when I FTPed my file to the 400 machine, it did not receive a size or date stamp from the system. It also created a second file TEST2.TEST2 for the one file I FTPed over, TEST2. Why is that? TEST2.TEST2 does not show up in the WORK WITH FILES list?

 

[flapeyre]

Here is a link to the DDS manual:

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/dds/rbafpmstddsover.htm

Specifically, (1) don't worry about journaling for now.

(2) When you create a file using CRTPF, you either specify the record length (in which case it's a flat file with one field), or you specify a source member which has the DDS (Data Description Specifications) for the file. See the link above to the DDS manual.

(3) Check to see that the source member you specified in RUNSQLSTM is the correct one and has valid SQL statements in it.

On the FTP (AS/400side), did you use the (REPLACE parameter on the GET subcommand? (Note that there is no closing parenthesis after the keyword.)



"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[TWillard]

(2) When you create a file using CRTPF, you either specify the record length?

What commands would you use to do this?



(3) Check to see that the source member you specified in RUNSQLSTM is the correct one and has valid SQL statements in it.

What is the source member? Is it the same as the DDS?

 

[flapeyre]

The record length is the RCDLEN parameter on the CRTPF command. You would only use that if you are creating a flat file (i/e., no fileds defined). Otherwise, you define the fields with DDS in a source member and specify the source file and member on the CRTPF command's SRCFILE and SRCMBR parameters:

[tt]CRTPF FILE(your_library/your_file) SRCFILE(your_library/QDDSSRC) SRCMBR(*FILE)[/tt]

Notice I omitted the RCDLEN parameter. This command, assuming you have a source member called your_file in file QDDSSRC in your_library, will create the file your_file in your_library.

The link I gave you earlier has examples on how to write a DDS source member.

Creating a file via SQL: You can do it, but I prefer to do it via DDS. DDS-created files and SQL tables are, for all intents and purposes, the same thing.

Your RUNSQLSTM command had *CURLIB as the source library - I bet the source could not be found in *CURLIB (which is QGPL, ususally, unless you have it set to something else with the CHGCURLIB command).


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[ks01]

Hello ...

I hope I am posting this question to the correct forum. My organization uses DB2 V7 and BMC Catalog Manager V6.

I would like to create a trigger so when a user runs an insert query on table A, the [tt]ADD_USER_ID[/tt] and [tt]ADD_TIMSTAMP[/tt] fields are updated with the user/current timestamp information. I would also like to create triggers for update and delete queries to update the [tt]LAST_CHG_USER_ID[/tt] and [tt]LAST_CHG_TIMESTAMP[/tt] fields of the same record.

Does anyone know how I can create this or where I can read more about it? I’ve tried reading up on IBM’s DB2 UDB for OS/390 Administration Guide, but I get lost as I’m a novice.

Thanks in advance ...

Kent