Skip to main content

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. 

Generate SQL in IBM i

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. 

Generated SQL source - IBM i

Generated SQL Source - IBM i

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. 

Physical File - IBM i

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. 

Generate SQL - IBM i

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. 

Generate SQL - IBM i

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. 

Generated SQL source - IBM i

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


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

Comments

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

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

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