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.

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.

DDL for Table is generated after Schema. 

DDL for Procedure is generated after Table.

DDL for Index is generated after Procedure.

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

Full list of parameters can be found here. 

Original blog => https://www.ibmiupdates.com/2021/02/generate-sql-objects.html

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. 

Delete Old Spooled files from SQL - IBM i

 

Spooled Files

Spooled files are generated when when system writes (prints) the data to an Output Queue. These could be Reports generated by the programs using Printer File (PRTF), System dumps or Job logs. 

Spool files usually doesn't occupy much system space and often ignored. But, they could occupy the considerable amount of time if the spool files hasn't been cleared for long time. 

Spool files can be deleted by using DLTSPLF (Delete Spooled File) command. 

One other way of deleting spool files is from SQL by using DELETE_OLD_SPOOLED_FILES procedure. This procedure is present in SYSTOOLS library.

One good thing about this procedure is there is an option to preview. 

Let's have a look at how this works. 

In the above procedure call, we are only passing one parameter. 

• 'PREVIEW' - By passing 'YES', this procedure won't delete any spool file and returns the list of spooled files matching with the selection. 

We have not passed any selection to delete the spooled files. So, procedure would consider the default parameters to delete or preview the spool files to be deleted. 

Below are the other parameters for this procedure. 

DELETE_OLDER_THAN -  Timestamp needs to be passed as a parameter. This would be considered as the starting point for deleting the spool files. Any spool file older than this timestamp would be considered for deletion. 

Default value for this parameter is 'CURRENT TIMESTAMP - 3 MONTHS'. If we don't pass this parameter all the spool files older than 3 months would be considered for deletion. 

P_OUTPUT_QUEUE_NAME - Name of the Output Queue. Spool files from this OUTQ would be considered for deletion. 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from all the OUTQs would be considered for deletion. 

P_OUTPUT_QUEUE_LIBRARY_NAME - Name of the library Output Queue is present in. Spooled files from all the output queues from this library would be considered for deletion (If OUTQ name has been passed only that specific OUTQ would be considered). 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from the OUTQs in all the libraries would be considered for deletion. 

P_USER_NAME - Name of the user whose spooled files are to be deleted. All the spooled files for this user would be considered for deletion.

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files for all the users would be considered for deletion. 

Let's have a look at an example by passing these parameters. 

In the above example, we are passing all the parameters and procedure would return the list of spooled files matching with the parameters passed. We can just pass the parameters that are required and leave the remaining to default. 

This would return the list of spooled files that are considered for deletion (PREVIEW => YES). 

Passing 'NO' to PREVIEW or removing PREVIEW parameter would delete these spool files. 

One thing to note is that, User should have sufficient authority to the spooled files to be deleted. 

• User should have authority to the DLTSPLF command. 
• User should have authority to the QSYS2/OUTPUT_QUEUE_ENTRIES_BASIC view.

Built-in Functions %MAX and %MIN in RPGLE - IBM i

 

%MAX and %MIN in RPGLE

While working with the data, we often need to identify the minimum or maximum value from the list of values provided. 

Built-In functions %MAX (maximum value) and %MIN (minimum value) provides easier way to return the minimum or maximum value from the list of values provided. 

Syntax:

%MAX(value1 : value2 : value3 ...)

%MIN(value1 : value2 : value3 ...)

There are few important points to note while working with these BIFs.

• Minimum of two operands (or values) to be passed. 

• Data types of the values passed must be compatible with other values.
• If one value is packed numeric, other values can be packed numeric, zoned numeric, integer, unsigned integer, binary decimal or float.

• There is no maximum limit on the number of values to be passed (except when used in the declaration statement, we will see more on this later). 

• Values can either be passed directly or by using the declared variables.

Let's have a look at an example to understand easily. 

In the above example, 

• Line - 11: %MIN is used with the list of values 10.1 (float), 89 (decimal) and a packed decimal field (wNinetyNine) with no decimal points, All these three are compatible for comparison. 
• Lowest value here is '10.1' and %MIN would return the same. 
• %MIN is returning to a variable which doesn't have decimal points defined, so '10' would be the return value. 

• Line - 14: %MAX is used with the list of values 10.9 (float), 89 (decimal) and a packed decimal field (wNinetyNine) with no decimal points.
• Highest value here is '99' and %MAX would return '99'.

• Line - 19: %MIN is used with the two strings 'RETURN', 'MINIMUM' along with a character (VARCHAR) field declared (wConstant). 
• Lowest value here is 'CONSTANT' and the same would be returned by %MIN.

• Line - 20: %MAX is used with the two strings 'RETURN', 'MAXIMUM' along with a character (VARCHAR) field declared (wConstant). 
• Highest value here is 'RETURN' and the same would be returned by %MAX.

This was a straight forward example with moving the minimum value moving directly to the declared variable. 

%MIN and %MAX can be directly used to in conditions to check if the return value is part of a specific range (using %RANGE), list (using %LIST), array or equal to the value passed (or a declared variable). 

Let's have a look a small example with IF conditions. 

In the above example,

• Line - 3: Checking if the minimum value returned by %MIN is present in a specific range provided using %RANGE. 

• Line - 7: Checking if the maximum value returned by %MAX is present in the list of values provided using %LIST.

• An array can be used as well instead of %RANGE or %LIST.

• Line - 11: Checking if the maximum value returned by %MAX is same as the value specified.
• Declared variable can be used as well instead of specifying the value directly.

Apart from these, we can use %MIN and %MAX in declaration statements i.e., to specify the length of a variable or to specify the dimension of array etc.

Below are the couple of points to remember when using these BIFs in declaration statement. 

• There must only be two values specified. 
• Both the values must be numeric (not float or hexadecimal).

Below is a quick example to show how to use these BIFs in declaration statements.

In the above example, 

• Line - 3: %MIN is used to assign the dimension of 'wDecArray2' based on the number of elements in the array 'wDecArray1' and the value passed.
• Since wDecArray1 is a varying-dimension array '*MAX' to be used in %ELEM and maximum dimension of the array would be returned. 
• %MIN would return the minimum of both 10 and 15. 

• Line - 4: %MAX is used to specify the length of the character variable being declared. 
• %MAX would return the maximum of the two values passed.

Hope the above info was a bit of help to understand %MAX and %MIN better.

Character to Numeric conversion in RPG - Allow Blanks & Thousands Separator - IBM i

 

Character to Numeric Conversion

Converting a numeric value from a character field to decimal field is one thing that we use almost every other day. 

There are many BIFs to do this (like %DEC, %INT, %FLOAT...). 

What if the character field doesn't contain any data? Or, If the character field contains comma (,) as thousands separator? 

Before we see how we deal with these, let's see what happens if a blank character field is used to convert a numeric value or if character field contains thousands separator. 

In the above example, We are doing character to numeric conversion twice with a blank value and a number stored in character with thousands separator. We are enclosing both these operations in a Monitor block, so that program doesn't fail.

• Line - 9: Converting blank character to a decimal value. %DEC doesn't allow blank value by default (with no control options specified) and this statement would fail causing the program to run On-Error block. 
• Line - 19: Converting character (number with thousands separator and two decimal positions) to a decimal value. %DEC would only allow one separator ('.' or ',') and considers them as a separator for decimal positions. In this case, multiple separators are used and would cause the statement to fail and program would execute On-Error block. 

Both the scenarios mentioned above would result in failure. It is not always possible to setup On-Error block with the appropriate default value to be specified as we will be working with wide range of data (as the On-Error block mostly used to deal with invalid values and the above two scenarios are valid). 

We can use 'EXPROPTS' (Expression Options) to deal with these two scenarios. 

• Using '*ALWBLANKNUM' in control options with EXPROPTS would convert the Blank character to zero with out any error. 
• Using '*USEDECEDIT' in control options with EXPROPTS would consider the thousands separators and decimal separator as specified in control specifications using DECEDIT.

If we don't specify DECEDIT in the control specification, By default ',' (comma) would be considered as separator and '.' (period) would be considered to identify decimal positions. 

We can use the same example by just adding EXPROPTS to control options would execute the program successfully.

EXPROPTS(*ALWBLANKNUM : *USEDECEDIT)

This makes the error handling easy while doing Character to Numeric conversions. 

Hope the above info was a bit of help.