I received a message from KaBrito about the post Defining SQL tables using a reference file.
Question: How would you do that in some type of a Source Control Management application?
Promote the table to production and then run the Alter table command?
With the move from DDS files to SQL DDL tables this is going to become a frequent asked question.
I put all of the SQL statements for defining the DDL table into one source member. I give it the source type of DDL. Those of you who have been readers of this blog for a while know I put all source members into one source file, but when I have to break the source types into their own source files I put the DDL source into its own source file, QDDLSRC.
An example of one of these source members could be:
CREATE TABLE MYLIB/TESTTABLE AS (SELECT INVOICE AS INVOICE, CUSTOMER AS CUSTOMER, REFDATE AS INVDATE, AMOUNT AS INVAMT FROM MYLIB/REFFILE) DEFINITION ONLY INCLUDING COLUMN DEFAULTS RCDFMT TESTTABLER ; ALTER TABLE MYLIB/TESTTABLE ADD PRIMARY KEY (INVOICE) ; LABEL ON TABLE MYLIB/TESTTABLE IS 'Test SQL table' ; LABEL ON COLUMN MYLIB/TESTTABLE ( INVDATE IS 'Inv date', INVAMT IS 'Inv amt' ) ; LABEL ON COLUMN MYLIB/TESTTABLE ( INVDATE TEXT IS 'Invoice date', INVAMT TEXT IS 'Invoice amount' ) ;
The CREATE TABLE, ALTER TABLE to add a key, and LABEL ON COLUMN SQL commands are all present in the one member. Each SQL command ends with a semi colon ( ; ).
I do not compile the source member. To create the table I use the 'Run SQL Statement', RUNSQLSTM, command.
RUNSQLSTM SRCFILE(MYLIB/DEVSRC) SRCMBR(TESTTABLE) + COMMIT(*NONE)
The table is created with all of the keys, column headings, and column text specified in the source member.
KaBrito, I hope this has answered your question.
You can learn more about the RUNSQLSTM command on the IBM website here.
This article was written for IBM i 7.2, and it should work with earlier releases too.