Generate SQL for Database Objects - IBM i

 

Generate SQL

Data Definition Language (DDL) is used to create Database objects. 

If we have a database object and no corresponding DDL statements or If we have files created using DDS and needs to modify the files using DDL instead of DDS. 

SQL Procedure 'GENERATE_SQL' generates the DDL statements required to recreate the database object. 

This procedure offers flexible return methods and returns DDL statements either as a result set or to the specified source file member. 

Let's have a look at an example to see how this works. 

In the above example, we are generating the DDL source for the Physical File created using DDS (see below for DDS source). 

Before we see what this procedure returns, Let's have a look at the parameters passed. 

DATABASE_OBJECT_NAME - Name of the object name for which DDL source to be generated for. '%' can be specified if there are multiple objects starting with same name. 

DATABASE_OBJECT_LIBRARY_NAME - Name of the library name in which data base object is present in. '%' can be specified if the object is to be considered from multiple libraries starting with same name. 

DATABASE_OBJECT_TYPE - Type of the database object passed. Please note that this is SQL object type (E.g.: 'TABLE' is to be used if generating the DDL for Physical File). 

Below are some of the object types allowed. 

• TABLE - Object is an SQL table or Physical file. 
• VIEW - Object is an SQL view or Logical file.
• PROCEDURE - Object is an SQL procedure. 
• FUNCTION - Object is an SQL function. 

Full list of allowed object types can be found here. 

The above three parameters are mandatory and procedure would fail if not passed. 

Let's now look at the result set returned. 

The result set contains,

• Source Sequence Number.
• Source Date. 
• Source Data.

Same information is stored in table Q_GENSQL (member - Q_GENSQL) in QTEMP library. Table QTEMP doesn't necessarily present by the time procedure is run. If the table is already present, data in the member would be replaced. 

Let's now have a look at the DDS source used to create this Physical File originally. 

In the above example, we aren't passing the source file details to where the DDL statements should be restored. 

If we pass the source library, file and member details generated DDL statements would be updated to the corresponding source member. One thing to remember is if we are passing the Source file and member, this should be present by the time procedure is called, otherwise GENERATE_SQL would throw error.

We will have a look at generating DDL statements for the procedure 'GENERATE_SQL' itself and store the data into source member passed. 

In the above example, we are generating the source for 'GENERATE_SQL' procedure into the Source file passed. 

Let's have a look at the additional parameters we are passing. 

DATABASE_SOURCE_FILE_NAME - Name of the source file to which generated SQL statements to be added. 

• Source file passed should be present on the system. 
• Length of the source file should be greater than or equal to 92. 
• Name of the source file is case sensitive (E.g.: If I pass 'qsqlsrc' instead of 'QSQLSRC' an error would be thrown.

DATABASE_SOURCE_FILE_LIBRARY_NAME - Name of the library in which source file is present in. Library name is case sensitive and should be present on the system. 

Apart from the library name, This parameter accepts two special values mentioned below.

• *CURLIB - Current Library
• *LIBL - Library List

DATABASE_SOURCE_FILE_MEMBER - Name of the source file member to which generated SQL statements to be added. This should be a valid member name, case sensitive and should be present on the system. 

This parameter accepts two special values mentioned below. 

• *FIRST - First member of the source file passed.
• *LAST - Last member of the source file passed. 

REPLACE_OPTION - Replace option specifies if the source file member should be replaced or if the generated statements to be added to the existing member. 

Valid values are,

• 0 - Resulting SQL statements are added to the existing member. 
• 1 (default) - Existing member would be cleared before adding the generated SQL statements. One thing to note here is member may be cleared even if there is an error while processing. 

If SQL statements are being generated for more than one object at a time, only the source for last member would be remained in the member (If '1' is specified).

NAMING_OPTION - Naming convention to be used in the generated SQL statements. Below are the valid values. 

• SQL - SQL naming convention like schema.table
• SYS - System naming convention like library/file

While looking at the above examples, we found that '%' can be used for identifying a database object that starts with specific name or database object library that starts with a specific name.

What happens if there are more than one object matching with this criteria? 

This would depend upon the replace option. If the replace option is not specified or specified as '1', then SQL statements generated for the last object would be returned in the result set or updated in the source member passed. 

All the previous members would be replaced by the subsequence occurrence. 

In the above example, 

• Line - 1: We are using '%' to find the data base objects that start with 'TEST'. 
• Line - 2: We are using '%' to find the data base object specified in the libraries that start with 'REDDYP'. 
• Line - 4: Parameter 'REPLACE_OPTION' plays important role here. 
• If '0' is specified, SQL statements generated for each object found would be appended to the result set or source member (QTEMP/Q_GENSQL (Q_GENSQL) as no source file/member is passed). 
• If '1' is specified, SQL statements generated for each object would replace the existing data and will result in retaining the data for last object.

Apart from these, we are passing two additional parameters here. 

DROP_OPTION - Drop option specifies if the DROP statement should be generated before the CREATE statement. 

Valid values are,

• '0' (default) - DROP statement should not be generated. 
• '1' - DROP statement should be generated. 

CREATE_OR_REPLACE_OPTION - Create or Replace option specifies if the CREATE OR REPLACE statement should be generated or just CREATE statement. 

Valid values are, 

• '0' (default) - CREATE OR REPLACE should not be generated. 
• '1' - CREATE OR REPLACE should be generated. 

Result to the above query would only contain the source for the last object found and would contain both DROP statement and CREATE OR REPLACE statement. 

Apart from the parameters mentioned above there are many more useful parameters allowed by this procedure. Full list of parameters can be found here.