24
Tue, Dec
1 New Articles
  1. You are here:  
  2. Home
  3. Programming
  4. Programming - Other
  5. CL
  6. Retrieve DDS Source for a Physical or Logical File

Retrieve DDS Source for a Physical or Logical File

CL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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:

RTVDDSSRC FILE(DATALIB/ORDERS) SRCFILE(QGPL/QDDSSRC) REPLACE(*YES)

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.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: