Retrieving and Storing SQL Source for DB2 Database Objects

December 2, 2009 Skip Marchesani

One issue that System i professionals are constantly raising when I’m preaching the gospel of DB2 for i SQL is that it’s difficult, or in some cases, not possible, to capture and store the SQL source statements or DDL (Data Definition Language) that were used to create DB2 database objects on a System i.

This is actually quite easy to do in Navigator using either of two almost identical functions. One is Generate SQL found in Database, and the other will be the focus of this article: Insert Generated SQL found in Run SQL Scripts. For reference, I am running V6R1 of Navigator with service pack SI34289 against a System i running V5R4 of i5/OS.

To show how easy it is to accomplish this, let’s do a simple case study.

Let’s assume you find a production library on your System i called SQLWRK, which contains three database objects shown in the green-screen display in Figure 1A. Joe Java Consultant, who is long since gone, created SQLWRK, and now the database objects need some modification. But, after looking high and low, there is no DDS or SQL DDL to be found.

Figure 1A.

The associated text shown in Figure 1A provides a hint that these three objects were created using SQL, and further investigation using the DSPFD command confirms that this is the fact. So without the SQL DDL source, what are your options? If you are not comfortable with SQL statements and syntax, creating the required SQL statements manually may be difficult or impossible. If that’s the case, do you start from scratch and create the DDS for these objects? To answer that question, let’s do some additional investigation.

Let’s look at the three database objects from the perspective of Navigator by clicking on the + sign found to the left of Database, then Ccsnj, then Schemas (schema name=library name), and then SQLWRK, which is displayed in the list of schemas/libraries. Next click on the All Objects heading under SQLWRK and a list of the database objects stored in SQLWRK is displayed in the right pane of the Navigator panel as shown in Figure 1B below.

Figure 1B.

I am assuming you are experienced using Navigator and know how to add SQLWRK (or another schema/library name) to the schema list if it is not already included in that list.

The first thing you should notice is that there are six objects–two check constraints, two indexes, a primary key constraint, and a table/physical file–in the Navigator display, and only three objects–two logical files and a physical file–in the green-screen display.

A check constraint (a.k.a., column level constraint) provides function that is similar to, but much broader in scope than, DDS validity checking keywords. An SQL index is a special type of SQL object that appears as a logical file. A primary key constraint is SQL’s method for creating a uniquely keyed physical file. And, a table is a physical file created using SQL.

This difference between the two displays–Figure 1A and 1B–is the result of the objects in SQLWRK being created using SQL as opposed to DDS. The Figure 1B Navigator display shows all the database objects including those unique to SQL. The green screen display in Figure 1A does not directly display the same SQL database objects. However, there are additional commands that can provide similar information via the green screen.

The point to be made here is that these objects created using SQL are not what they initially appear to be when viewed using a green screen. Therefore creating new DDS to modify and recreate these objects is not an option since in this case that approach will not provide the identical function required by the applications using the objects. The SQL source DDL statements that originally were used to create the objects are required. You can use the Insert Generated SQL option, found in Run SQL Scripts, to create these SQL statements.

To access Insert Generated SQL, open Run SQL Scripts by clicking on Run an SQL Script, which is found in the bottom middle of the Navigator display, as shown in Figure 1B. In the resulting Run SQL Scripts panel, click on Edit in the toolbar, and then Insert Generated SQL as shown in Figure 2.

Figure 2.

The Generate SQL panel will be displayed. Click on the Format tab and select the desired naming convention–either System (library/object) or SQL (library.object). For this example we will use System naming convention. Click on the Options tab and make sure DB2 Family and Extensions is selected under Standards option, and that the remaining six options are also selected as shown in Figure 3.

Selecting all these options tells Generate SQL to create a maximum or complete set of full function DB2 for i SQL DDL statements (as opposed to a set of SQL DDL with lesser function that can be ported to and executed on another ANSI/ISO platform).

Figure 3.

Next you need to select those objects for which SQL will be generated. Click on the Add button toward the upper right of the Generate SQL panel in Figure 3 to display the Select Objects panel shown in Figure 4. As Navigator sometimes does, it may think about this for a while before displaying this panel.

Figure 4.

If the requisite schema/library is not in the left or Supported Objects portion of the Select Objects panel schema list, click on the Schemas button on the bottom left. This brings up the Schemas to Display dialogue, which allows you to browse for, select, and display additional schema names.

Expand SQLWRK by clicking on the + sign to the left (already done in Figure 4) and you see the various types of SQL database objects that could be found in SQLWRK. You can click on each type individually followed by the Add button to have objects of the specific type displayed in the right or Objects to Process portion of the pane. Or, just click on SQLWRK followed by the Add button to see all objects in SQLWRK displayed, as has already been done in Figure 4.

Note that when you click on SQLWRK followed by the Add button, SQLWRK is included in the list of objects to be processed. This means that an appropriate SQL DROP SCHEMA and CREATE SCHEMA statement will be generated for SQLWRK. If you do not wish to have these SQL statements generated, click on SQLWRK in the Objects to Process pane and then the Remove button to remove SQLWRK from the Objects to Process list. Any of the names in the Object to Process list can also be removed in this manner.

Next click on the OK button at the bottom right and you will be returned to the Generate SQL panel, propagating the object names from the Select Objects panel to the Generate SQL panel as shown in Figure 5.

Figure 5.

Now click on the Generate button at the lower right of Generate SQL panel Figure 5. The SQL DDL statements required to create each of the objects listed in the Generate SQL panel in Figure 5 will be generated as an SQL script with multiple SQL statements, which will be displayed in Run SQL Scripts as shown in Figure 6.

Figure 6.

Some things you should note about the SQL script that was generated.

When you clicked on SQLWRK and then the Add button in the Select Objects panel shown in Figure 4, SQLWORK was included in the list of objects to be processed. As mentioned previously, this means that an SQL DROP SCHEMA and CREATE SCHEMA statement was generated for SQLWRK.

If you would like these SQL statements to be ignored when the script is executed, just preface each line of each statement with a double hyphen and space (–), and the statement will be treated as a comment and ignored when the script is executed. In other words:

DROP SCHEMA SQLWRK RESTRICT;
CREATE SCHEMA SQLWRK;

becomes:

-- DROP SCHEMA SQLWRK RESTRICT;
-- CREATE SCHEMA SQLWRK;

Also note that in each SQL statement in the script, the object name is qualified with the schema/library name of SQLWRK. This if OK as long as you want the objects to be created in SQLWRK. If you want the flexibility to create these objects in the schema/library of your choice, then the qualification needs to be removed, which can easily be done.

If not already open, open the SQL script just generated in Run SQL Scripts. Click on Edit in the left of the toolbar and then Replace, which will take you to the Replace dialog. Key SQLWRK/ in the Find box, leave the Replace box empty, and click on the Replace All button.

All occurrences of SQLWRK/ will then be removed from the SQL script, and you will have the flexibility to create these objects in the schema/library of your choice when the script is executed.

This script can then be saved as a PC file with a .sql extension or in a System i source file. How about QDDSSRC? Saving to or executing an SQL script from a System i source file is a new function available in V6R1 of Run SQL Script in Navigator. Just click on File in the toolbar, then Save As. The select source physical file in the Save To box of the resulting Save panel.

The explanation of the process to generate the SQL source DDL to create one or more database objects takes orders of magnitude more time that the actual process itself. Using the example discussed in this article, the total execution time of SQLWRK from the beginning in Figure 2 to the completion in Figure 6 was less than 25 seconds. As I said in the beginning of this article, generating the SQL source DDL statements required to create database objects is really easy to do.

Oh, and while I’m thinking of it. . .

You can use the same process to generate the SQL source DDL for physical or logical file created using DDS.

Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book DB2/400: The New AS/400 Database. He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.