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.
I do same thing. I like having a source member to create/re-create my SQL objects. What I've added to this, at the beginning, I'll use
ReplyDeleteSET SCHEMA mylib;
...
then at the end, I'll use CL statements to change the owner, revoke/grant authorities;
CL:CHGOBJOWN mylibf/mytable OBJTYPE(*FILE) NEWOWN(theowner);
CL:GRTOBJAUT mylibf/mytable OBJTYPE(*FILE) USER(*PUBLIC) AUT(*EXCLUDE);
CL:GRTOBJAUT mylibf/mytable OBJTYPE(*FILE) USER(thegroup) AUT(*USE);
you should also explain how to write a comment /* .... */
ReplyDeleteDoesn't work in a single member when you are using constraints. tables with Primary keys need to all be created first then the alter table to add foreign key constraints can be run...separate member.
ReplyDeleteThank you for posting this, Simon. We're about to upgrade our CRM package and part of that package's upgrade includes the move to DDL. We'll be writing more than a few of these to support the simultaneous migration of our customizations, and we might as well use DDL in anything new that we produce.
ReplyDeleteHi Simon,
ReplyDeleteI ran your example on V6.1 and V7.1
On V6.1 and earlier releases the reference information isn't stored in the new table if it is created with SQL/DDL
Hi Simon
Deleteanother little detail, neither edit codes nor edit words are transferred from the ref file on any release.
Thank you for testing with release 6.1.
DeleteIt is not necessary to hard code the library name in the SQL source. You can specify the library dynamically by using the RUNSQLSTM parameter Default collection . . . . . . . DFTRDBCOL . This comes handy when the source is promoted and SQL table gets created in different environments (TEST, QA, PROD).
ReplyDeleteHow to create a DDL union table?Is it possible to create a DDL union table from two different physical file with different columns and attributes?
ReplyDeleteCan I split fields text into two lines
ReplyDeleteThe column's text can be up to 50 characters maximum.
Delete