Skip to main content

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. 
Labels:

Comments

Post a Comment

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel
4 comments
Read more

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke
Post a Comment
Read more

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i
2 comments
Read more