Pages

Tuesday, February 23, 2021

Generate SQL for Database Objects - Part 2

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.

Create Table - IBM i

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

Insert data into a table - IBM i

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. 

Generate SQL Objects - IBM i

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.

Generate SQL - IBM i

DDL for Table is generated after Schema. 

Generate SQL - IBM i

DDL for Procedure is generated after Table.

Generate SQL - IBM i

DDL for Index is generated after Procedure.

Generate SQL - IBM i

All of these are generated in the single source member passed in the parameters. 

Full list of parameters can be found here


If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

No comments:

Post a Comment

Popular Posts