Wednesday, October 27, 2021

Retrieving the source of SQL objects

sql procedure to recreate sql source

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.

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

3 comments:

  1. If you just want to generate a single object, use the GENERATE_SQL procedure instead. This would also allow you to generate each object into a separate source file or IFS file, if that is your preference.

    ReplyDelete
  2. Simon, this is a new function for me. Great read and examples.. thanks for sharing..

    ReplyDelete
  3. Why you have to run the store procedure when you can just go to the Schema in ACS or Navigator for i to locate the Database object, then right click the object to generate the sources? The UI is very user friendly and many options you can choose such as output to Physical Source file that you mentioned. Also, you can highlight as many DB objects as you want to generate all the sources at once.

    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.