Several issues ago, I suggested that you consider moving to SQL to define your database files. In this issue, I want to continue to support that notion by providing RTVSQLSRC, a CL command that will allow you to re-create the SQL statements you used to create your files.
Typically, when you create a file using SQL, the SQL statements used to create the file are lost to cyberspace because you don't save them in a source member as you do with DDS. SQL statements are usually entered through a PC-based tool, the OS/400 STRSQL command, or a third-party SQL product, such as ASC's SEQUEL.
What if you have created a file using SQL and later need to change the file or create a similar file on another system? With SQL, you would use the ALTER TABLE statement and change the field attributes or insert a new field. You rarely need to re-create the file when SQL is available.
For example, suppose you need to create a simple file named MYFILE and include two fields, CUSTNO and CUSTNAME. You could run the following SQL statement to create the file:
CustName Char(30) Not Null)
Later, if you need to change the length of one of the fields, you use the ALTER TABLE statement. In this example, if you need to change the length of the CUSTNO field to nine digits, you'd use this statement:
Alter Column CustNo Set Data Type Dec(9,0) Not Null
The CUSTNO field is now a nine-digit decimal field. Pretty simple, isn't it?
Now, suppose you want to add a field to the file. That's nearly as easy. Use the ALTER TABLE statement again, but this time, indicate that you're adding a field, as follows:
Add Column ShipTo Char(25) Not Null
You now have a third field in your file: SHIPTO, which is a 25-position character field.
To accomplish a similar function with DDS, you would go into the DDS source, change the field attributes or add the new field, and then run the CHGPF command to "re-create" the file and preserve the existing data.
The cool thing about DDS is that (a) it is very easy to comprehend and (b) you preserve the original source code used to create the file. This allows easy changes or, if necessary, re-creation of the file, a relatively easy task.
But what happens to the SQL statements once they've evaporated from your job?
The good news is that IBM has provided the QSQGNDDL API to retrieve SQL source needed to create/re-create a file. The API, however, is not limited to just files created with SQL; it can also generate SQL source needed to create a file that was originally created with DDS.
If you use the QSQGNDDL API to retrieve the SQL source needed to create the above file, you end up with the following SQL source:
CustNo Decimal(9,0) Not Null,
CompName Char(30) Not Null,
Address Char(25) not Null);
The API generates the above SQL CREATE statement and places it into a source member of your choosing.
QSQGNDDL: Generate Data Definition Language
DDS is a data definition language (DDL). SQL is also a DDL. Although SQL can be a DDL, it can also be a data manipulation language.
The QSQGNDDL API extracts the necessary SQL DDL statements to create the file. It does not extract the data manipulation language statements, such as INSERT and UPDATE, which may or may not have been used to insert data into the file. When a file is re-created, it is empty, so be aware of that. If you need to preserve your data in an existing file, you need to use the ALTER TABLE statement.
The purpose of this article is simply to illustrate that you can retrieve the SQL used to create an existing file. That file need not necessarily be created with SQL, as the API will also produce SQL CREATE statements for non-SQL physical and logical files.
To use QSQGNDDL, you have to prepare a data structure, identify the file and the source file where the SQL statements are coming from and being stored, and specify any other special flags.
One of the flags that may be specified is to generate a DROP statement. The DROP statement in SQL is the same as a DLTF command. The DROP statement deletes the object, just like the DLTF CL command does. So use caution when the DROP statement is generated.
As mentioned, the API uses a data structure to identify the various flags and attributes you've specified to control the API. The data structure name is SQSR0100, and it's located in the QSQGNDDL source member in QRPGLESRC in the QSYSINC library. You can include it in your RPG IV source code by including the following statement:
This data structure is illustrated in the table below.
|
Subfield
|
Attributes
|
Description
|
|
Object Name
|
Char(258)
|
The name of the database file whose SQL is to be retrieved
|
|
Object Lib
|
Char(258)
|
The library containing the file
|
|
Object Type
|
Char(10)
|
The SQL object type (such as VIEW or TABLE)
|
|
Source File
|
Char(10)
|
The source file that will receive the generated SQL statements
|
|
Source Lib
|
Char(10)
|
The source file's library
|
|
Source Member
|
Char(10)
|
The source member that will receive the generated SQL statements
|
|
Severity Level
|
Int4 (10i0)
|
The error severity at which to cause the retrieve SQL command to fail
|
|
Replace Member
|
Char(1)
|
'1' = Replace source member
'0' = Do not replace source member
|
|
Stmt Formatting
|
Char(1)
|
'1' = Insert hex X'00' at the end of each SQL statement
'0' = Generate usable SQL statements
|
|
Date Format
|
Char(3)
|
Format for date fields
|
|
Date Separator
|
Char(1)
|
Date separator symbol
|
|
Time Format
|
Char(3)
|
Format for time fields
|
|
Time Separator
|
Char(1)
|
Time separator symbol
|
|
Naming Syntax
|
Char(3)
|
'SYS' = use OS/400 library/file naming
'SQL' = use SQL library.file naming
|
|
Decimal
|
Char(1)
|
Symbol used for decimal notation
|
|
Standards
|
Char(1)
|
'0' = Generate SQL statements with OS/400 extensions
'1' = Generate SQL statements to DB2 standards
'2' = Generate SQL statements to ISO/ANSI standards
|
|
Drop
|
Char(1)
|
'0' = Do not generate a DROP instruction
'1' = Generate a DROP instruction
|
|
Msg Level
|
Int4 (10i0)
|
A number indicating the level at which error messages are generated. For
example, if 20 is specified, messages for errors of severity 20 or higher are
generated, but 19 and lower are not.
|
|
COMMENT
|
Char(1)
|
'0' = Do not generate COMMENT ON statements
'1' = Generate COMMENT ON statements
|
|
LABEL
|
Char(1)
|
'0' = Do not generate LABEL ON statements
'1' = Generate LABEL ON statements
|
|
Comment Header
|
Char(1)
|
'0' = Do not generate a comment block in the header area (top) of the
source member
'1' = Generate date/time stamp, version, and other information as a comment
at the top of the source member
|
|
Reserved
|
Char(*)
|
Reserved for future use
|
Of course, populating this data structure isn't too difficult, but wouldn't it be easier if you had a RTVSQLSRC CL command? I agree. So I wrote one.
Listed in Figure 1 is the command definition source code for the RTVSQLSRC command. It is a typical "user-defined" OS/400 CL command.
|
Figure 1: Here's the command definition source for RTVSQLSRC.
The RTVSQLSRC command allows you to specify a subset of the settings of the data structure used by the QSQGNDDL API. If I did not expose a setting that you want to be able to control, you can simply add it to the command, provided you know something about user-written CL commands.
Re-creating Your File with SQL
The RTVSQLSRC command can retrieve the SQL statements needed to create any file on the system, regardless of whether or not the file was originally created with SQL or DDS. Once the SQL source is retrieved into the source member, you can use the OS/400 RUNSQLSTM command.
The SQL source generated by RTVSQLSRC is formatted to be compatible with the OS/400 RUNSQLSTM CL command. Rebuilding the file using the generated SQL statements is simple. For example:
This RUNSQLSTM command will process the SQL statements stored in the CUSTMAST source member of the QSQLSRC source file. RUNSQLSTM is provided on all AS/400 and iSeries systems to run SQL statements stored in a source file member.
The Command Processing Program
To make this command easy to use required wrapping the QSQGNDDL API in an RPG IV program. All the parameters from the command are passed to the program using a procedure interface instead of the conventional *ENTRY/PLIST. Unfortunately, RPG IV requires a prototype whenever a procedure interface is used, so you have duplicate code in the beginning of the source member.
To compile the command's RPG IV command processing program (CPP) RTVSQLSRC, use option 14 in PDM (the CRTBNDRPG command). Optionally, the "Compile with no prompting" from within CODE/400 can be used as well.
The only requirement for the program is that it must run in a "regular" activation group, so I've included the DFTACTGRP(*NO) keyword in the Header specification on line 3 of the source member. See Figure 2.
|
Figure 2: This is the RTVSQLSRC program source.
Bob Cozzi has been programming in RPG since 1978. Since then, he has written many articles and several books, including The Modern RPG Language --the most widely used RPG reference manual in the world. Bob is also a very popular speaker at industry events such as COMMON and RPG World and is the author of his own Web site, www.rpgiv.com, and of the RPG ToolKit, an add-on library for RPG IV programmers.
LATEST COMMENTS
MC Press Online