Build SQL to Define Your Existing Files, Take 2

Converting physical files to tables is a good thing to do, but if you work in the sorts of shops where I’ve worked, you already have more than you can do and you don’t have time to fix what isn’t broken. However, if I could make it easy for you, maybe you’d find the time to convert physical files where doing so is to most advantageous. Let me show you how easy it can be.

First, you need a place to put the SQL DDL (data definition language) source.

crtpf  mylib/sqlsrc

Second, you need an SQL client. I have used both green screen Start SQL (STRSQL) and the Run SQL Scripts utility that is part of Access Client Solutions.

Run two measly commands, replacing appropriately.

call qcmdexc ('addpfm mylib/sqlsrc somepf');

If the member in which you will store the SQL does not exist, you must create it.

call qsys2.generate_sql
        (database_object_name => 'SOMEPF',
         database_object_library_name => 'SOMELIB',
         database_object_type => 'TABLE',
         database_source_file_name=> 'SQLSRC',
         database_source_file_library_name => 'MYLIB',
         database_source_file_member => 'SOMEPF',
         replace_option => '1',
         statement_formatting_option => '0');

Voilà! You have SQL.

--  Generate SQL
--  Version:                   V7R2M0 140418
--  Generated on:              05/17/16 08:00:00
--  Relational Database:       BR549000
--  Standards Option:          DB2 for i

CREATE TABLE SMITH.ACUST (
--  SQL150B   10   REUSEDLT(*NO) in table ACUST in SMITH ignored.
  ACCOUNT_NUMBER FOR COLUMN ACCOUNT    DECIMAL(7, 0) NOT NULL DEFAULT 0 ,
  NAME CHAR(20) CCSID 37 NOT NULL DEFAULT '' ,
  CITY CHAR(16) CCSID 37 NOT NULL DEFAULT '' ,
  STATE CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
  ZIP CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
  PRIMARY KEY( ACCOUNT_NUMBER ) )

  RCDFMT CUSTOMER   ;

LABEL ON COLUMN SMITH.ACUST
( ACCOUNT_NUMBER IS '                    Customer            number' ,
  NAME IS '                    Customer            name' ,
  CITY IS '                                        City' ,
  STATE IS '                                        State' ,
  ZIP IS '                    ZIP                 Code' ) ;

LABEL ON COLUMN SMITH.ACUST
( ACCOUNT_NUMBER TEXT IS 'Customer account number' ,
  NAME TEXT IS 'Customer name' ,
  CITY TEXT IS 'City' ,
  STATE TEXT IS 'State' ,
  ZIP TEXT IS 'Postal codee' ) ;

GRANT DELETE , INSERT , SELECT , UPDATE
ON SMITH.ACUST TO PUBLIC ;

GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE
ON SMITH.ACUST TO SMITH WITH GRANT OPTION ;

Notice the last parameter, statement_formatting_option. Be sure to pass a zero to this parameter, otherwise you’ll get a bunch of garbage on the end of each line.

There are more parameters, but the ones I’ve given you have been enough for my purposes so far. You can read about the parameters on the IBM developerWorks site.

I realize that there are other good ways to build SQL DDL. See the links below for more on this topic.

I also realize that generating the SQL DDL is only the beginning of the process, but sometimes getting started is the hardest part.

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

RELATED STORIES

TR8 DB2 For i Enhancements, Part 1

Retrieving and Storing SQL Source for DB2 Database Objects

Build SQL to Define Your Existing Files, Revisited

Build SQL to Define Your Existing Files

generate_sql documentation at IBM developerWorks

Winnie-the-Pooh