Wednesday, October 15, 2014

How I code source for SQL DDL tables

create sql table ddl using runsqlstm

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.

9 comments:

  1. 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
    SET 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);

    ReplyDelete
  2. you should also explain how to write a comment /* .... */

    ReplyDelete
  3. Doesn'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.

    ReplyDelete
  4. Thank 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.

    ReplyDelete
  5. Hi Simon,

    I 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

    ReplyDelete
    Replies
    1. Hi Simon

      another little detail, neither edit codes nor edit words are transferred from the ref file on any release.

      Delete
    2. Thank you for testing with release 6.1.

      Delete
  6. It 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).

    ReplyDelete
  7. How 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?

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.