Anyone who has been around the iSeries for a while knows what a lifesaver the Retrieve CL Source (RTVCLSRC) command can be. If a CL source member is accidentally deleted or if you'd like to examine a CL program for which you never had source, the RTVCLSRC command will re-create a program source member for you from a CL program object (provided that the original programmer lets his CL source be retrieved).

Unfortunately, IBM hasn't provided a similar command to retrieve the data description specifications (DDS) source for a file. For shops that rely on DDS, such a command would be useful to help do the following:

• Recover lost source members or application files for which source isn't available
• Build source for tables created by an SQL-driven application such as Microsoft Access or Operations Navigator
• Build a source member for a work file that's similar to an existing file
• Build a source member for an *OUTFILE created by a Query/400 or Query Manager query
• Synthesize source members when the FORMAT parameter is required for a join OPNQRYF and you don't have source for the base files

Most iSeries programmers know that they can retrieve a wealth of information about a file using the IBM-supplied Display File Description (DSPFD) and Display File Field Description (DSPFFD) commands. For example, DSPFD file-name TYPE(*ACCPTH) reveals key level information about a file. DSPFD file-name TYPE(*SELECT) displays the select/omit criteria information for a logical file. Finally, DSPFD file-name TYPE(*JOIN) shows join information for a join logical file. In most cases, these commands provide enough information to rebuild a source member from a file object in a way that is similar to the RTVCLSRC command.

The RTVDDSSRC Utility

The Retrieve DDS Source (RTVDDSSRC) command rebuilds the source member from a physical file or logical file. Below, I've listed the file, record, join, field, key field, and select/omit level keywords that are supported by the RTVDDSSRC command.

File Level Keywords
DYNSLT
FCFO
FIFO
LIFO
UNIQUE

Record Level Keywords
JFILE
PFILE
TEXT

Join Level Keywords
JDUPSEQ(*DESCEND not supported)
JFLD
JOIN

Field Level Keywords
ALIAS
ALWNULL
CCSID
COLHDG
DATFMT
DATSEP
DFT
EDTCDE
EDTWRD
JREF
FLTPCN
REFFLD
RENAME
SST
TEXT
TIMFMT
TIMSEP
VARLEN

Key Field Level Keywords
ABSVAL
DESCEND
DIGIT
SIGNED
UNSIGNED
ZONE

Select/Omit Level Keywords
ALL
COMP (CMP)
RANGE (COMP GE LE)
VALUES

To find the values for these keywords and determine when to use them, RTVDDSSRC uses a CL program to run the DSPFD and DSPFFD commands and direct their results to OUTFILEs. To get an idea of the type of information these OUTFILEs contain, Figure 1 shows the field list for an outfile created by the DSPFFD command on a V4R4 system.

Figure 1: Field list for an outfile created by the DSPFFD command

As you can see, the file contains data type, size, and miscellaneous attribute and keyword information for every field in a given file. Therefore, to determine that a keyword such as ALWNULL should be specified in the source member for a given field, all you have to do is check the WHNULL field for a Y. The outfiles created by the DSPFD command are processed in a similar way to produce all of the file, select/omit, join, and key level keywords.

With the information gathered to build the DDS member, how do you know how to format the member? That's easy. As with RPG source, DDS source is defined by a rigid set of column positions wherein specific entries must be made. Figure 2 shows a list of positions and content for a DDS source member.

Figure 2: DDS specification definition

An RPG program is used to rearrange the information garnered in the OUTFILEs into this predefined DDS format.

Using the Command

The RTVDDSSRC command is easy to use because it has only four parameters:

• The qualified PF or LF data file name to be retrieved
• The qualified source file
• The source member name (defaults to the data file name)
• An option as to whether the source member should be replaced if it already exists

To retrieve the source member for file DATALIB/ORDERS into source file QGPL/QDDSSRC, specify:

Special Uses

Users sometimes export new tables from Microsoft Access to the iSeries via ODBC, but the iSeries columns aren't always created with the optimum data type. For example, when the CURRENCY data type is exported from Access to the iSeries, it's translated into a double floating point type. Likewise, Access's TEXT data type is translated to a variable character type. This may be undesirable if you're using a High Level Language (HLL) such as RPG/400 to process the exported table because RPG/400 doesn't directly support FLOAT or VARCHAR. This problem can be easily rectified by doing the following:

• Retrieve the DDS source for the exported table
• Change the data type in the source from variable character to character or from float to packed decimal
• Issue the Change Physical File (CHGPF) command (specifying the source file) to update the field(s) in the file to the new data type(s)

Limitations

Be aware that RTVDDSSRC falls short in a couple of areas. First, the JDFTVAL and CONCAT keywords aren't supported because the DSPFFD and DSPFD commands don't record this information.

Keep in mind when working with SQL tables that SQL allows alias names that DDS does not. For example, if an Access table has a field name called Order Number, when the table is exported to the iSeries, this column will be assigned a system-generated system field name and will be given an alias of "Order Number" (yes, double quotes and a space are included!). SQL will accept this as a valid ALIAS name, but DDS won't, so you must change these aliases in the DDS spec.

Some file level keywords (e.g., CCSID, REF) are supported only at the field level. For example, if CCSID(65535) is specified at the file level, the RTVDDSSRC command will place CCSID(65535) on every character field. The SQL-specific data types--including large objects (LOBs), data links, and user-distinct types (UDT)--are not supported in DDS. These fields will be marked with an asterisk in the rebuilt member. SQL views containing formulas for calculated fields will not be correctly retrieved either.

Library names are placed in the source. If a REFFLD, JFILE, or PFILE keyword contains a library that doesn't exist on the system, then the missing library name will be replaced with *LIBL.

Fields with the REFFLD attribute are particularly enigmatic because the DSPFFD doesn't relate if a particular attribute of a REFFLD, such as EDTCDE, comes from the base file or is overridden in the PF or LF source. Additionally, DSPFFD doesn't even mark REFFLD fields that have overridden lengths as REFFLDs. Because of these inheritance problems, fields with the REFFLD keyword specified are only placed on PFs. REFFLDs on LFs will have all attributes and keywords spelled out, whether inherited or not.

Finally, compile time keywords such as SRTSEQ, RECOVER, and SHARE must be gleaned from the existing file by using the DSPFD command.

Though easy to use, the DSPFD and DSPFFD commands aren't necessarily the best way to retrieve file information:

• They're relatively slow.
• In some cases, they can't furnish complete information about the file.
• The *OUTFILE file format created by these commands is subject to change with each new release. For example, the outfile for DSPFFD created on a V3R2 system is not identical to one on a V4R3 system. Therefore, the RPG program may need to be recompiled after an OS upgrade.

The QUSLFLD (List Fields) and QDBRTVFD (Retrieve Database File Description) system APIs provide a better way to retrieve the same information (but without the limitations) provided by these IBM commands. However, in contrast to the DSPFD and DSPFFD commands, these API calls can be difficult to work with.

Download the source code for the utility, which consists of a command, a CL program, and an ILE RPG program: 031003SansoterrartvddssrcV2.zip

A Helpful Option

The RTVDDSSRC command is useful for retrieving DDS source in any number of situations. It can be used to document files created by Query/400, to save time in constructing work files that are similar to existing files, to retrieve lost source members, and to easily make changes to a file that was imprecisely defined after being exported from a PC.

It also shows the wealth of information that is available with the DSPFD and DSPFFD commands and how a HLL program can easily manipulate this data.