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:
Create Table MyFile (CustNo Dec(7,0) Not Null,
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 Table MyFile
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:
Alter Table MyFile
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:
Create Table Myfile (
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:
/COPY QSYSINC/QRPGLESRC,QSQGNDDL
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.
RTVSQLSRC: CMD PROMPT('Retrieve SQL Source')
/* Command processing program is: RTVSQLSRC */
PARM KWD(FILE) TYPE(QUAL) MIN(1) PROMPT('File name')
QUAL: QUAL TYPE(*NAME) MIN(1) EXPR(*YES)
QUAL TYPE(*NAME) DFT(*LIBL) SPCVAL((*CURLIB) +
(*LIBL)) MIN(0) EXPR(*YES) PROMPT('Library')
PARM KWD(SRCFILE) TYPE(SRCFILE) PROMPT('Source +
file name')
SRCFILE: QUAL TYPE(*NAME) DFT(QSQLSRC) EXPR(*YES)
QUAL TYPE(*NAME) DFT(*LIBL) SPCVAL((*CURLIB) +
(*LIBL)) MIN(0) EXPR(*YES) PROMPT('Library')
PARM KWD(SRCMBR) TYPE(*NAME) DFT(*FILE) +
SPCVAL((*FILE)) EXPR(*YES) PROMPT('Source +
member')
PARM KWD(REPLACE) TYPE(*LGL) DFT(*YES) +
SPCVAL((*YES '1') (*NO '0') (*ON '1') +
(*OFF '0') (*REPLACE '1') (*NOREPLACE +
'0')) EXPR(*YES) PROMPT('Replace source +
member')
PARM KWD(NAMING) TYPE(*CHAR) LEN(3) RSTD(*YES) +
DFT(*SYS) SPCVAL((*SYS 'SYS') (*SQL +
'SQL')) EXPR(*YES) PROMPT('Qualified name +
syntax')
PARM KWD(SQLLVL) TYPE(*INT2) RSTD(*YES) DFT(*SYS) +
SPCVAL((*SYS 0) (*DB2SQL 1) (*ANSISQL 2)) +
PROMPT('SQL Standards syntax')
PARM KWD(GENDROP) TYPE(*LGL) RSTD(*YES) DFT(*NO) +
SPCVAL((*NO '0') (*YES '1')) EXPR(*YES) +
PROMPT('Generate DROP file statement')
PARM KWD(COMMENT) TYPE(*LGL) RSTD(*YES) DFT(*YES) +
SPCVAL((*YES '1') (*NO '0')) EXPR(*YES) +
PROMPT('Generate header comments')
PARM KWD(MSGLVL) TYPE(*INT2) DFT(30) RANGE(0 39) +
PROMPT('Message generation level')
PARM KWD(SEVLVL) TYPE(*INT2) RSTD(*YES) DFT(30) +
VALUES(0 10 20 30 40) PROMPT('Error +
severity level to fail') |
|
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:
RUNSQLSTM SRCFILE(QSQLSRC) SRCMBR(CUSTMAST) COMMIT(*NONE)
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.
** Program name: RTVSQLSRC ** Download the latest version at: ** http://www.rpgiv.com/newsletterH BNDDIR('QC2LE') OPTION(*SRCSTMT:*NODEBUGIO)H DFTACTGRP(*NO)D RtvSQLSrc PRD FileLib 20AD SrcFileLib 20AD Srcmbr 10AD bReplace 1N OPTIONS(*NOPASS)D szNaming 3A OPTIONS(*NOPASS)D nStandard 5I 0 OPTIONS(*NOPASS)D bDrop 1N OPTIONS(*NOPASS)D bHeader 1N OPTIONS(*NOPASS)
D nMsgLvl
5I 0 OPTIONS(*NOPASS)
D nSevLvl 5I 0 OPTIONS(*NOPASS)D RtvSQLSrc PI
D FileLib 20A
D SrcFileLib 20A
D Srcmbr 10A
D bReplace 1N OPTIONS(*NOPASS)
D szNaming 3A OPTIONS(*NOPASS)
D nStandard 5I 0 OPTIONS(*NOPASS)
D bDrop 1N OPTIONS(*NOPASS)
D bHeader 1N OPTIONS(*NOPASS)
D nMsgLvl
5I 0 OPTIONS(*NOPASS)
D nSevLvl 5I 0 OPTIONS(*NOPASS)
D SafeAddMbr PR
D SrcFileLib 20A Value
D SrcMbr 10A Value
D system PR extproc('system')
D szCMDString * OPTIONS(*STRING) VALUE
D apiError DS Inz
D errDSLen 10I 0 Inz(%size(apiError))
D errRtnLen 10I 0 Inz
D errMsgID 7A Inz(*ALLX'00')
D errReserved 1A Inz(X'00')
D errMsgData 64A Inz(*ALLX'00')
/COPY QSYSINC/QRPGLESRC,QSQGNDDL
/COPY QSYSINC/QRPGLESRC,QUSROBJD
D pFileLib S *
D InFileLib DS BASED(pFileLib)
D InFile 10A
D InLib 10A
D pSrcFileLib S *
D InSrcFileLib DS BASED(pSrcFileLib)
D SrcFile 10A
D SrcLib 10A
D QRtvOBJD PR Extpgm('QUSROBJD')
D rtnData LIKE(QUSD0200)
D rtnLen 10I 0 Const
D odFormat 8A Const
D ObjLib 20A Const
D ObjType 10A Const
D apiErrorDS Like(ApiError)
D InReplace S 1N Inz('1')
D InNaming S 3A Inz('SYS')
D inMsgLvl S 5I 0 Inz(30)
D inSevLvl S 5I 0 Inz(30)
D inStandard S 5I 0 Inz(0)
D inDrop S 1N Inz('0')
D inHeader S 1N Inz('1')
D FileType S Like(QUSEoA05)
D SQLTempl S Like(QSQR0100)
D QRtvSQLSrc PR Extpgm('QSQGNDDL')
D sqTempl Like(QSQR0100)
D sqtLen 10I 0 Const
D sqFormat 9A Const
D apiError 16A
C eval *INLR = *ON
C eval pFileLib = %addr(FileLib)
C eval pSrcFileLIb = %addr(SrcFileLib)
C if srcmbr = '*FILE'
C eval srcmbr = InFile
C endif
** Copy the input parms to their variables, if specified.
C if %parms >= 4
C eval InReplace = bReplace
C if %parms >= 5
C eval InNaming = szNaming
C if %parms >= 6
C eval InStandard= nStandard
C if %parms >= 7
C eval InDrop = bDrop
C if %parms >= 8
C eval InHeader = bHeader
C if %parms >= 9
C eval inMsgLvl = nMsgLvl
C if %parms >= 10
C eval InSevLvl = nSevLvl
C endif
C endif
C endif
C endif
C endif
C endif
C endif
** Retrieve the file attribute (LF or PF)
C callp qrtvobjd(QUSD0200 : %Len(QUSD0200) :
C 'OBJD0200' : FileLIb :'*FILE':apiError)
C if %subst(errMSGID:1:5) = 'CPF98'
C** Failed - Source file not found. :(
C return
C endif
** Does the member exist? It has to or this stupid API won't work!
C callp SafeAddMbr(InSrcFileLib : SrcMbr)
C
C eval FileType = QUSEoA05
C CLEAR QSQR0100
C eval qsqOBJN = InFile
C eval qsqOBJL = inLib
C select
C when FileType = 'PF'
C eval qsqObjT = 'TABLE'
C when FileType = 'LF'
C eval qsqObjT = 'VIEW'
C endsl
C eval qsqSFilN = SrcFile
C eval qsqSFilL = SrcLib
C eval qsqSFilM = SrcMbr
C
C eval qsqSL02 = InSevLvl
C eval qsqRo = InReplace
C eval qsqNo02 = InNaming
C eval qsqML02 = InMsgLvl
C eval qsqSFo = '0'
C eval qsqDF02 = 'ISO'
C eval qsqTF02 = 'ISO'
C eval qsqDS02 = ' '
C eval qsqTS02 = ' '
C eval qsqDP02 = '.'
C eval qsqSo01 = %char(InStandard)
C eval qsqDo = InDrop
C eval qsqCo = '1'
C eval qsqLo = '1'
C eval qsqHo = InHeader
** Copy the data structure to our local data structure
** Note: When everyone is on V5R1 and later, we can use
** a qualified data structure instead.
C eval sqltempl = Qsqr0100
C CallP qrtvsqlsrc(sqltempl : %Len(QSQR0100) :
C 'SQLR0100' : apiError)
C
C return
P SafeAddMbr B Export
D SafeAddMbr PI
D SrcFileLib 20A Value
D SrcMbr 10A Value
D InSrcFileLib DS
D SrcFile 10A
D SrcLib 10A
** The structure returned by the QusRMBRD API.
D szMbrd0100 DS INZ
D nBytesRtn 10I 0
D nBytesAval 10I 0
D szFileName 10A
D szLibName 10A
D szMbrName 10A
D szFileAttr 10A
D szSrcType 10A
D dtCrtDate 13A
D dtLstChg 13A
D szMbrText 50A
D bIsSource 1A
D CPF_MbrNotFound...
D C CONST('CPF9815')
**----------------------------------------------------------------
** Input Parameters to the QUSRMBRD API
**----------------------------------------------------------------
** Tells the APIs how long the buffers are that are being used.
D nBufLen S 10I 0
** Format to be returned
D Format S 8A Inz('MBRD0100')
** Whether or not to ignore overrides (0=Ignore, 1 = Apply)
D bOvr S 1A Inz('0')
C eval InSrcFileLib = SrcFileLib
**----------------------------------------------------------------
C Reset apiError
C Eval nBufLen = %size(szMbrD0100)
**----------------------------------------------------------------
C Call 'QUSRMBRD'
C Parm szMbrD0100
C Parm nBufLen
C Parm Format
C Parm SrcFileLIb
C Parm SrcMbr
C Parm bOvr
C Parm apiError
** If the member doesn't exist, add it; otherwise, just return.
C if errRtnLen > 0
C if errMsgID = CPF_MbrNotFound or
C errMsgID = 'CPF3019' or
C errMsgID = 'CPF32DE' or
C errMsgID = 'CPF3C27' or
C errMsgID = 'CPF3C26'
C callp system('ADDPFM FILE(' + %TrimR(srcLib) + '/'
C + srcfile + ') MBR(' + srcmbr + ')')
C endif
C endif
C return
P SafeAddMbr E |
|
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.
| 
