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.
|
Field Name
|
Description
|
|
APRCEN
|
Retrieval century: 0=19xx, 1=20xx
|
|
APRDAT
|
Retrieval date: year/month/day
|
|
APRTIM
|
Retrieval time: hour/minute/second
|
|
APFILE
|
File
|
|
APLIB
|
Library
|
|
APFTYP
|
P=PF, L=LF, R=DDM PF, S=DDM LF
|
|
APFILA
|
File attribute: *PHY or *LGL
|
|
APMXD
|
Reserved
|
|
APFATR
|
File attribute: PF, LF, PF38, or LF38
|
|
APSYSN
|
System name (source system, if file is DDM)
|
|
APASP
|
Auxiliary storage pool ID: 1=System ASP
|
|
APRES
|
Reserved
|
|
APMANT
|
Maintenance: I=*IMMED, R=*REBLD, D=*DLY
|
|
APUNIQ
|
Keys must be unique: N=No, Y=Yes
|
|
APKEYO
|
L=LIFO, F=FIFO, C=FCFO, N=No specific key order
|
|
APSELO
|
Select/omit file: N=No, Y=Yes
|
|
APACCP
|
Access path: A=Arrival, K=Keyed, E=EVI, S=Shared
|
|
APNSCO
|
Number of files accessed by logical file
|
|
APBOF
|
Physical file
|
|
APBOL
|
Library
|
|
APBOLF
|
Logical file format through which data is accessed
|
|
APNKYF
|
Number of key fields per format
|
|
APKEYF
|
Key field name
|
|
APKSEQ
|
Key sequence: D=Descending, A=Ascending
|
|
APKSIN
|
Key sign specified: N=UNSIGNED, S=SIGNED, A=ABSVAL
|
|
APKZD
|
Zone/digit specified: N=None, Z=ZONE, D=DIGIT
|
|
APKASQ
|
Alternative collating sequence: N=No, Y=Yes
|
|
APKEYN
|
Key field number: 1=First key in format
|
|
APJOIN
|
Join logical file: N=No, Y=Yes
|
|
APACPJ
|
Access path journaled: N=No, Y=Yes
|
|
APRIKY
|
Constraint type: P=PRIMARY, U=UNIQUE, N=NONE
|
|
APUUIV
|
Number of unique key values given at file creation
|
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.
|
Start
Position |
Data Element
|
|
6
|
'A' constant
|
|
7
|
Comment (*)
|
|
17
|
Name Type
|
|
19
|
Name (Field, record, key, etc.)
|
|
29
|
Reference Indicator
|
|
30
|
Field Length
|
|
35
|
Data Type
|
|
36
|
Number of Decimal Places
|
|
38
|
Use (I=Input)
|
|
45
|
Keyword
|
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.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online