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.
The procedure showed here is located in the Utility Services section.
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.
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.
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.
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.
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