Retrieving the source of SQL objects

GENERATE_SQL_OBJECTS is a SQL procedure I have used which I have found very useful, but have not written about. It allows me to recreate the SQL statement that was used to create a SQL object or objects into a source file member. I could change many of SQL objects using the ALTER statement. But, in my opinion, it is easier to change the statement in a source member. I make sure that the create statement is CREATE OR REPLACE, create the changed object and old one is replaced.

There are two things I consider minor inconveniences to using this SQL procedure:

1. I cannot pass the name of the object to the procedure. All of the objects I wish to retrieve the create statement for must be placed in a DDL Table, that procedure then "reads".
2. The source member that is generated contains the create statements for all of the objects listed in the above Table.

I can use this with most SQL object types. In this example I am only using it with a few I have mentioned in previous posts.

The Table that is used for input must be defined with the following columns:

01 CREATE TABLE QTEMP.OBJECTS ( 02 OBJECT_SCHEMA VARCHAR(258), 03 OBJECT_NAME VARCHAR(258), 04 SQL_OBJECT_TYPE CHAR(10) 05 ) ;

The names and definitions of the columns must be as I have given. The Table's name and the library can be whatever I want.

I did include an multiple row Insert statement after the Table's definition.

06 INSERT INTO QTEMP.OBJECTS 07 VALUES('MYTEST','OTRIGGER','TRIGGER'), 08 ('MYTEST','OFUNCTION','FUNCTION'), 09 ('MYTEST','OVARIABLE','VARIABLE'), 10 ('MYTEST','OINDEX','INDEX'), 11 ('MYTEST','OTABLE','TABLE'), 12 ('MYTEST','OVIEW','VIEW'), 13 ('MYTEST','OTYPE','TYPE'), 14 ('QSYS','MYTEST2','SCHEMA') ;

I execute the statements in the source member using the Run SQL Statements command, RUNSQLSTM, the file is created and the data is inserted immediately afterwards.

Having inserted the rows into the input Table I now need to create those objects. As this is only an example I put all of the create statements into one source member. As you can appreciate this is a large source member, rather than include its contents here I am going to show the contents of the member at the bottom on this post. You can see it by clicking on the link here.

I am not going to describe how to create each of these SQL object types. I am going to provide you with a link to a post where I go into detail how to create of these SQL object types.

• Trigger
• Function
• Variable
• Index
• Table
• View
• Type
• Schema

Note:  There are other types of SQL objects you can use GENERATE_SQL_OBJECTS with, these are just the ones I chose.

The GENERATE_SQL_OBJECTS SQL procedure has 36 parameters. I am only going to use 15:

1. SYSTEM_TABLE_NAME:  Name of the Table containing the list of SQL objects
2. SYSTEM_TABLE_SCHEMA:  Library the Table is in
3. DATABASE_SOURCE_FILE_NAME:  Name of the source file the output member is in
4. DATABASE_SOURCE_FILE_LIBRARY_NAME:  Library the source file is in
5. DATABASE_SOURCE_FILE_MEMBER:  Source member name
6. STATEMENT_FORMATTING_OPTION:  Formatting options used when generating the statements
7. REPLACE_OPTION:  Is the source member cleared or appended to
8. NAMING_OPTION:  Whether to use SQL or system naming convention
9. COMMENT_OPTION:  Add comment for object
10. LABEL_OPTION:  Add labels to the objects
11. HEADER_OPTION:  Should a header be generated in the source member
12. TRIGGER_OPTION:  Should a trigger be added
13. CONSTRAINT_OPTION:  Should constraints be added
14. CREATE_OR_REPLACE_OPTION:  Should CREATE OR REPLACE be added
15. QUALIFIED_NAME_OPTION:  Should object name be qualified by library name

My statement using the Procedure looks like:

01 CALL QSYS2.GENERATE_SQL_OBJECTS( 02 SYSTEM_TABLE_NAME => 'OBJECTS', 03 SYSTEM_TABLE_SCHEMA => 'QTEMP', 04 DATABASE_SOURCE_FILE_NAME => 'OTHERSRC', 05 DATABASE_SOURCE_FILE_LIBRARY_NAME => 'MYTEST', 06 DATABASE_SOURCE_FILE_MEMBER => 'ALLSRC', 07 STATEMENT_FORMATTING_OPTION => '1', 08 REPLACE_OPTION => '1', 09 NAMING_OPTION => 'SQL', 10 COMMENT_OPTION => '1', 11 LABEL_OPTION => '1', 12 HEADER_OPTION => '1', 13 TRIGGER_OPTION => '0', 14 CONSTRAINT_OPTION => '0', 15 CREATE_OR_REPLACE_OPTION => '1', 16 QUALIFIED_NAME_OPTION => '0' 17 ) ;

Lines 2 and 3: Tell the procedure where the input file is.

Lines 4 – 6: The source file, library, and member names.

Line 7: I want to have the statement formatted with end of line and tab characters.

Line 8: Clear the source member before adding the SQL statements.

Line 9: I prefer the SQL naming convention.

Line 10: Add comments if present.

Line 11: Add LABEL statements, where appropriate and if defined in the SQL object.

Line 12: Add a header to the source member.

Line 13: Do not add triggers.

Line 14: Do not add constraints.

Line 15: Make the statements CREATE OR REPLACE.

Line 16: If the create statement does not qualify the object, do not do qualify.

The source file and member must be created before executing this procedure:

CRTSRCPF FILE(MYTEST2/OTHERSRC) MBR(ALLSRC)

I created the call to the Procedure in a source member, therefore, I execute it using the RUNSQLSTM command.

What I found interesting is that the create statements for the objects are not processed in the order of the rows in the Table. They are processed in this order:

1. Schemas
2. Types
3. Sequences
4. Aliases
5. Tables and Indexes
6. Functions
7. Procedures
8. Variables
9. Views
10. Triggers
11. Masks
12. Permissions
13. XSR objects

You can see this in the source member that was generated. This is a large source member, therefore, I am going to add the code to the bottom of this post and give you a link to it link here.

This is a very useful tool to be able to recover the create statement from a SQL objects. If you need to do this I recommend you try some of the other options and values in the parameters to see which give you the statements formatted the way you want.

 

You can learn more about the GENERATE_SQL_OBJECTS SQL procedure from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

 

---

 

Source that was used to create the SQL objects

-- Functions CREATE OR REPLACE FUNCTION MYTEST.OFUNCTION (T2COL2 VARCHAR(20),T2COL3 VARCHAR(20)) RETURNS VARCHAR(40) LANGUAGE SQL DETERMINISTIC BEGIN DECLARE FULLNAME VARCHAR(40) ; SET FULLNAME = RTRIM(T2COL2) CONCAT ' ' CONCAT RTRIM(T2COL3) ; RETURN FULLNAME ; END ; -- Index CREATE INDEX MYTEST.OINDEX ON MYLIB.TESTFILE (FLD001) WHERE FLD001 > 100 ; -- Table CREATE OR REPLACE TABLE MYTEST.OTABLE (FIRST_COLUMN FOR COLUMN "FIRST" CHAR(10), SECOND_COLUMN FOR COLUMN "SECOND" TIMESTAMP) ; -- Trigger CREATE OR REPLACE TRIGGER MYTEST.OTRIGGER AFTER INSERT OR DELETE OR UPDATE ON MYLIB.TESTFILE REFERENCING NEW ROW AS N OLD ROW AS O FOR EACH ROW MODE DB2ROW BEGIN DECLARE TSTAMP TIMESTAMP ; IF INSERTING THEN INSERT INTO MYLIB.TESTFILTRG VALUES(N.FLD001, N.FLD002, CURRENT TIMESTAMP, JOB_NAME, 'I') ; END IF ; IF DELETING THEN INSERT INTO MYLIB.TESTFILTRG VALUES(O.FLD001, O.FLD002, CURRENT TIMESTAMP, JOB_NAME, 'D') ; END IF ; IF UPDATING THEN SET TSTAMP = CURRENT TIMESTAMP ; INSERT INTO MYLIB.TESTFILTRG VALUES(O.FLD001, O.FLD002, TSTAMP, JOB_NAME, 'U0') ; INSERT INTO MYLIB.TESTFILTRG VALUES(N.FLD001, N.FLD002, TSTAMP, JOB_NAME, 'U1') ; END IF ; END ; -- Type DROP TYPE MYTEST.OTYPE ; CREATE TYPE MYTEST.OTYPE AS VARCHAR(30) ; -- Variable CREATE OR REPLACE VARIABLE MYTEST.OVARIABLE VARCHAR(30) DEFAULT 'Nothing here yet' ; -- View CREATE OR REPLACE VIEW MYTEST.OVIEW (PARTITION,JOBNAME,SUBSYSTEM,JOBTYPE,JOBSTS,FUNCTION) AS SELECT CAST(CURRENT_SERVER AS CHAR(10)), JOB_NAME,SUBSYSTEM,JOB_TYPE,JOB_STATUS,FUNCTION FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL')) WHERE JOB_STATUS = 'MSGW' ;

Return

 

Source that was generated by GENERATE_SQL_OBJECTS.

-- Generate SQL -- Version: V7R4M0 190621 -- Generated on: DD/DD/DD HH:HH:HH -- Relational Database: DEV740 -- Standards Option: Db2 for i CREATE SCHEMA SECOND_TEST_LIB FOR SCHEMA MYTEST2 ; -- SQL150C 10 CRTAUT for schema MYTEST2 ignored. GRANT CREATEIN , USAGE ON SCHEMA SECOND_TEST_LIB TO SIMONH WITH GRANT OPTION ; CREATE TYPE MYTEST.OTYPE AS VARCHAR(30) CCSID 37 ; GRANT ALTER , USAGE ON TYPE MYTEST.OTYPE TO SIMONH WITH GRANT OPTION ; CREATE TYPE MYTEST.OTYPE AS VARCHAR(30) CCSID 37 ; GRANT ALTER , USAGE ON TYPE MYTEST.OTYPE TO SIMONH WITH GRANT OPTION ; CREATE INDEX MYTEST.OINDEX ON MYLIB.TESTFILE ( FLD001 ASC ) WHERE FLD001 > 100 RCDFMT OINDEX ; CREATE OR REPLACE TABLE MYTEST.OTABLE ( FIRST_COLUMN FOR COLUMN FIRST CHAR(10) CCSID 37 DEFAULT NULL , SECOND_COLUMN FOR COLUMN "SECOND" TIMESTAMP DEFAULT NULL ) RCDFMT OTABLE ; GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE ON MYTEST.OTABLE TO SIMONH WITH GRANT OPTION ; SET PATH *LIBL ; CREATE OR REPLACE FUNCTION MYTEST.OFUNCTION ( T2COL2 VARCHAR(20) , T2COL3 VARCHAR(20) ) RETURNS VARCHAR(40) LANGUAGE SQL SPECIFIC MYTEST.OFUNCTION DETERMINISTIC READS SQL DATA CALLED ON NULL INPUT SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DLYPRP = *NO , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN DECLARE FULLNAME VARCHAR ( 40 ) ; SET FULLNAME = RTRIM ( T2COL2 ) CONCAT ' ' CONCAT RTRIM ( T2COL3 ) ; RETURN FULLNAME ; END ; GRANT ALTER , EXECUTE ON SPECIFIC FUNCTION MYTEST.OFUNCTION TO SIMONH WITH GRANT OPTION ; SET PATH *LIBL ; CREATE OR REPLACE VARIABLE MYTEST.OVARIABLE VARCHAR( 30) DEFAULT 'Nothing here yet' ; GRANT ALTER , READ , WRITE ON VARIABLE MYTEST.OVARIABLE TO SIMONH WITH GRANT OPTION ; CREATE OR REPLACE VIEW MYTEST.OVIEW ( "PARTITION" , JOBNAME , SUBSYSTEM , JOBTYPE , JOBSTS , "FUNCTION" ) AS SELECT CAST(CURRENT_SERVER AS CHAR(10)), JOB_NAME,SUBSYSTEM,JOB_TYPE,JOB_STATUS,FUNCTION FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL')) WHERE JOB_STATUS = 'MSGW' RCDFMT OVIEW ; GRANT ALTER , REFERENCES , SELECT ON MYTEST.OVIEW TO SIMONH WITH GRANT OPTION ; CREATE OR REPLACE TRIGGER MYTEST.OTRIGGER AFTER INSERT OR DELETE OR UPDATE ON MYLIB.TESTFILE REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2ROW SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE , COMMIT = *NONE , DECRESULT = (31, 31, 00) , DLYPRP = *NO , DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX BEGIN DECLARE TSTAMP TIMESTAMP ; IF INSERTING THEN INSERT INTO MYLIB . TESTFILTRG ( FLD001 , FLD002 , FTIMSTAMP , FJOBNAME , FTYP E ) VALUES ( N . FLD001 , N . FLD002 , CURRENT_TIMESTAMP , QSYS2 . JOB_NAME , 'I' ) ; END IF ; IF DELETING THEN INSERT INTO MYLIB . TESTFILTRG ( FLD001 , FLD002 , FTIMSTAMP , FJOBNAME , FTYP E ) VALUES ( O . FLD001 , O . FLD002 , CURRENT_TIMESTAMP , QSYS2 . JOB_NAME , 'D' ) ; END IF ; IF UPDATING THEN SET SQLP_L3 . TSTAMP = CURRENT_TIMESTAMP ; INSERT INTO MYLIB . TESTFILTRG ( FLD001 , FLD002 , FTIMSTAMP , FJOBNAME , FTYP E ) VALUES ( O . FLD001 , O . FLD002 , SQLP_L3 . TSTAMP , QSYS2 . JOB_NAME , 'U0' ) ; INSERT INTO MYLIB . TESTFILTRG ( FLD001 , FLD002 , FTIMSTAMP , FJOBNAME , FTYP E ) VALUES ( N . FLD001 , N . FLD002 , SQLP_L3 . TSTAMP , QSYS2 . JOB_NAME , 'U1' ) ; END IF ; END ;

Return