QSYS2.GENERATE_SQL stored procedure

Welcome to a new article about the best server in the world, yes IBM i. This time I will write about a DB2 for i Service that can be used to obtain SQL Data Definitions statements (DDL) from any database object. This can be very useful in case you want to convert DDS to DDL or because you have lost the DDS of a file, you can recover it in SQL format with this service.

DB2 for i Services

There are a lot of views, tables and procedures available to you so you can get information about database objects in a very easy way. All of these services are located in QSYS2 schema  and you can find a complete list of them in the following IBM Web page: DB2 for i Services. 
 
The website is divided into different categories for a direct access to the possible services. The available categories are:

The procedure showed here is located in the Utility Services section.

 

GENERATE_SQL

This procedure returns the DDL of a database object so you are able to recreate it. Theses sentences are returned in a member of a physical source file, as a source stream file in the IFS or as a result set. If you choose a member of a physical source file, the member must exist or the procedure will fail. 
 
I encourage you to use Run SQL Scripts from Access Client Solutions to view the result set because if you use the SRTSQL command the result is stored in the Q_GENSQL member of the physical source file QTEMP/Q_GENSQL so you will need to open it to view its contents, i.e. the result set instead of view it directly as with Run SQL Scripts.
 
Take care about authorization when you are going to write the sentences in a source physical file or in a stream file or the procedure will fail. In addition, be careful also about the authorization you have on the objects for which you want to get the SQL statements.

Parameters

There are a lot of parameters that you can pass to the procedure. I will show you some of them. A complete list can be found on the IBM Web site: GENERATE_SQL.
 
  • DATABASE_OBJECT_NAME .- Name of the database object to generate sql statements from. The name can be a system name or an SQL name. It is case sensitive and the wildcard ‘%’ can be used to select multiples object of the same type.
  • DATABASE_OBJECT_LIBRARY_NAME.- Library where the database object is located. The name can be a system name or an SQL name. It is case sensitive and the wildcard ‘%’ can be used to select multiples. This name is ignored if the specified object type is SCHEMA.
  • DATABASE_OBJECT_TYPE.- Type of the database object for which DDL is generated. Possibles types are:
    • ALIAS
    • CONSTRAINT
    • FUNCTION
    • INDEX
    • MASK
    • PERMISSION
    • PROCEDURE
    • SCHEMA
    • SEQUENCE
    • TABLE
    • TRIGGER
    • TYPE
    • VARIABLE
    • VIEW
    • XSR
  • DATABASE_SOURCE_FILE_NAME.- Name of the physical source file that will contains the SQL statements returned by the procedure. The record length of the specified physical source file must be greater than or equal to 92. The name is case sensitive. A special value of *STMF can be specified to return the SQL sentences to a stream file in the IFS. If the value is not specified, Q_GENSQL will be used.
  • DATABASE_SOURCE_FILE_LIBRARY_NAME.- Name of the library that holds the previous physical source file. The name is case sensitive. You can use special values *CURLIB and *LIBL. If the value is not specified, QTEMP will be used.
  • DATABASE_SOURCE_FILE_MEMBER.- Name of the member that will contains the SQL statements returned by the procedure. The name is case sensitive. You can use special values *FIRST and *LAST. If the value is not specified, Q_GENSQL will be used.
  • NAMING_OPTION.- Naming convention. Possible values are SQL for a schema.table format or SYS for a library/file format. If the value is not specified, SQL will be used.
  • DECIMAL_POINT.- The decimal point used for numeric constants. Possible values are . (period) or , (comma). If the value is not specified, period will be used.
  • DROP_OPTION.- Specifies if DROP SQL statements should be generated prior to the CREATE statement to drop the specified object. Possible values are 0 that means that sentences will not be generated or 1 that means that sentences will be generated. If the value is not specified, 0 will be used.
  • LABEL_OPTION.- Option that specifies whether LABEL SQL statements should be generated if a label exists on the specified database object.  Possible values are 0 that means LABEL statements will not be generated or 1 that means that LABEL statements will be generated. If the value is not specified, 1 will be used.
  • HEADER_OPTION.- Option that specifies whether a header should be generated prior to the CREATE statement. The header consists of comments that describe the version, date and time, the relational database, and some of the options used to generate the SQL statements. Possible values are 0 that means that a header will not be generated or 1 that means that a header will be generated. If the value is not specified, 1 will be used.

Examples

Let’s go with some examples to illustrate how GENERATE_SQL works. I will use Run SQL Scripts for all of them.

Case 1 – Lost DDS/DDL

There is a file in the system that has a field that must be modified but there are no DDS nor DDL. Let’s see what GENERATE_SQL can do for us.

First of all let’s see what will be generated using Run SQL Scripts:

Now let’s modify the above SQL sentence to recover the source to a physical source file. Recovery will take place on QSQLSRC physical source file in the BOOKS member and the type will be SQL.

File: BOOKS

Library: ASALCEDO1

Type: TABLE

Let’s see what’s happening if the member doesn’t exists in the physical source file:

As you can see as the member BOOKS does not exists an error is raised. Let’s run the same sentence after the member BOOKS has been created.

Now we have obtained a Statement ran successfully message. Let’s open the member in Runs SQL Scripts.

This are the SQL statements as they are in BOOKS member.

Case 2 – Convert DDS to DDL

In this second example we want to convert old DDS file to new DDL. It doesn’t matter if we have the DDS or not because GENERATE_SQL will work with the file object.

First of all let’s see the DDS of the file:

As you can see the file has the most common keywords that can be coded in a physical file so you can see how GENERATE_SQL deals with all of them.

Now let’s code the SQL sentence to recover the source to a physical source file directly (remember that the member must exist). Recovery will take place on QSQLSRC physical source file in the MUSICDB member and the type will be SQL.

File: MUSICDB

Library: ASALCEDO1

Type: TABLE

Statement ran successfully message was received. Let’s open the member in Runs SQL Scripts to see what was generated.

There are several things to explain after the conversion.

Lines 8-12: Look how the ALIAS keyword of the physical file has been converted to the name of the column and how the name of the field of the physical file has been converted to the short name of the column so it can be used anywhere that need that the length of the name of a column won’t be greater than 10 characters.

Line 12: The name of the field in the physical file matches the name of an SQL function so GENERATE_SQL has surrounded it with double quotes.

Line 13: The UNIQUE key of the physical file has been converted to an SQL PRIMARY KEY.

Lines 17-32: LABEL ON sentences has been generated for the table, column headings and text descriptions of the fields.

Case 3 – Convert Logical File to an SQL View

In this third example we want to convert a logical keyed file with select/omit to a view DDL.

First of all let’s see the DDS of the file:

As you can see the file is a logical file with dynamic selection of records and only shows four fields of MUSICDB which is the psychical file that points to. It will only show records whose GENRE is HEAVY METAL or HARD ROCK.

Now let’s code the SQL sentence to recover the source to a physical source file directly (remember that the member must exist). Recovery will take place on QSQLSRC physical source file in the MUSICDBL1 member and the type will be SQL.

File: MUSICDBL1

Library: ASALCEDO1

Type: VIEW

Statement ran successfully message was received. Let’s open the member in Runs SQL Scripts to see what was generated.

There are several things to explain after the conversion.

Lines 7-8: A view was created and the key was ignored. In case number four I will show how to generate an index.

Lines 19-20: The condition of the select/omit of the logical file is now the condition of the WHERE clause.

Lines 23-33: Labels are taken from the physical file to which points the logical file.

Case 4 – Convert Logical File to an SQL Index

In this fourth and last example we want to convert a logical keyed file with select/omit to an index DDL.

As you can see the DDS are the same as the previous case:

The difference is that now we don’t want that GENERATE_SQL takes care about fields. We just want the key and select/omit clauses to generate an index and the sentence will be the same as previous case but we will add INDEX_INSTEAD_OF_VIEW_OPTION => 1 so GENERATE_SQL will create and index and not a view.

Now let’s code the SQL sentence to recover the source to a physical source file directly (remember that the member must exist). Recovery will take place on QSQLSRC physical source file in the MUSICDBIDX member and the type will be SQL.

File: MUSICDBL1

Library: ASALCEDO1

Type: VIEW

Statement ran successfully message was received. Let’s open the member in Runs SQL Scripts to see what was generated.

There are several things to explain after the conversion.

Lines 6-7: An index has been created and the format of the dependent table has been ignored.

Lines 9-10: The condition of the select/omit of the logical file is now the condition of the WHERE clause.

One more thing to consider is that, although I have specified LABEL_OPTION => 1 as I am creating an Index, GENERATE_SQL has ignored it.

Conclusion

I hope you liked the article and if you didn’t know anything about DDS to DDL conversion, now you have an starting point.

Please comment anything you want and stay tuned for next article.

One Response

  1. Pingback: QSYS2.GENERATE_SQL stored procedure Antonio Salcedo - Built on Power

Leave a Reply

Your email address will not be published. Required fields are marked *

CPTServ
Powered by  GDPR Cookie Compliance
Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.