develUP Your Skills: Generate SQL for Database Objects

Generate SQL Objects

In the previous post we have seen how to generate DDL statements for a specific database object passed using GENERATE_SQL procedure.

In this post we will see how to generate DDL statements required to create group of objects at once using GENERATE_SQL_OBJECTS procedure.

This procedure accepts a table (with list of database objects) as an input and generates the DDL statements for the objects listed in the table.

Before we see how this procedure works, let's see how should we create the input table.

Input table should be created with the column names mentioned below.

OBJECT_SCHEMA
OBJECT_NAME
SQL_OBJECT_TYPE

OBJECT_SCHEMA (Object Schema/Library Name)

Refers to the schema name of the object for which DDL is to be generated.
No special values (like *LIBL, *CURLIB) are allowed.
This would be ignored if the object type is schema.

OBJECT_NAME (Object Name)

Refers to name of object for which DDL is to be generated.
Specific Name of Procedure or Function to be used for Procedures and Functions.
For Table or View, Object must not refer to the alias.
Extract object name to be mentioned and no '%' is allowed to indicate the list of objects starting with specific name.

SQL_OBJECT_TYPE (SQL Object Type)

Refers to type of the object for which DDL is to be generated.
Below are the valid SQL Object Types.

ALIAS - Object is an SQL alias.
CONSTRAINT - Object attribute is a constraint.
FUNCTION - Object is an SQL function.
INDEX - Object is an SQL Index.
MASK - Object is an SQL column mask.
PERMISSION - Object is an SQL row permission.
PROCEDURE - Object is an SQL procedure.
SCHEMA - Object is an SQL schema (or library).
SEQUENCE - Object is an SQL sequence.
TABLE - Object is an SQL table (or physical file).
TRIGGER - Object attribute is a trigger.
TYPE - Object is an SQL type.
VARIABLE - Object is an SQL global variable.
VIEW - Object is an SQL view.
XSR - Object is an XML schema repository object.

Let's create the input table and add few database objects into this table.

We are using the same names as described above to create the input table.

We are inserting objects of different types into the input table created.

Line - 7: Inserting the logical file 'TESTLF', library 'REDDYP1' and SQL object type 'INDEX'.
Line - 8: Inserting the specific name 'QSQGENSQLO' of SQL procedure 'GENERATE_SQL_OBJECTS', library 'QSYS2' and SQL object type 'PROCEDURE'.
Line - 9: Inserting the physical file 'TESTPF', library 'REDDYP1' and SQL Object type 'TABLE'.
Line - 10: Inserting the schema (library) 'REDDYP1', library 'QSYS' and SQL Object type 'SCHEMA'. For schema, library name would be ignored.

One thing to note here is duplicate entries are not allowed.

We aren't adding objects in any specific order. However, GENERATE_SQL_OBJECTS procedure would generate the DDL statements for each object in the below order. So that if any dependent objects are present depended object would be created first (E.g.: Schema and Table or Table and View etc.)

Schemas
Tables
Sequences
Aliases
Non-MQT (Materialized Query Tables) tables, any constraints and indexes on these tables
Functions
Procedures
Variables
Views, Logical files, MQTs. and any constraints or indexes on these tables
Triggers
Masks
Permissions
XSR Objects

Let's now call the procedure GENERATE_SQL_OBJECTS by passing the input table with the list of database objects and see how the DDL statements would be generated.

Below are the parameters we are using in the above query.

SYSTEM_TABLE_NAME

Name of the table with the list of database objects for which DDL statements are to be generated. See above for more details on how the table should be created.

This is the only mandatory parameter. Default values would be considered for all other parameters if not passed.

SYSTEM_TABLE_SCHEMA

Name of the schema in which the table (passed in the previous parameter) is present in.

Default value 'QTEMP' would be considered if not passed.

DATABASE_SOURCE_FILE_NAME

Source file name in which DDL statements are to be generated.

Default value 'Q_GENSQOBJ' would be considered if not passed.

DATABASE_SOURCE_FILE_LIBRARY_NAME

Library name of the source file (passed in the previous parameter) in which DDL statements are to be generated.

Default value 'QTEMP' would be considered if not passed.

DATABASE_SOURCE_FILE_MEMBER

Member name of the source file (passed in the previous parameter) in which DDL statements are to be generated.

Default value 'Q_GENSQOBJ' would be considered if not passed.

If Database source file name, library and member aren't passed, statements would be generated in the default source member in QTEMP and the same would be returned as result set.

If these values are passed, member should exist on the system.

There are many other parameters we can consider passing based on the requirement and most of them are similar to GENERATE_SQL procedure.

Let's have a look at the DDL statements generated and the order they are generated.

DDL for Schema is generated first.