22
Wed, May
2 New Articles

Programming in ILE RPG - DDS Database Concepts

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

While most recent database objects are created by using SQL, many files are still defined by using DDS. The procedure for creating database file definitions with DDS is similar to that of creating an RPG program.

Editor's Note: This article is excerpted from chapter 3 of Programming in ILE RPG, Fifth Edition.

The first step is to use an editor, such as the RDi LPEX editor, to create a source member of definition statements. Most installations use a file named QDDSSRC to store members representing externally described files. The source member type of a physical file member is PF, and the type of a logical file member is LF. The editor automatically provides prompts appropriate to the member type you specify.

Data Description Specifications (DDS) comprise a fixed-format language that you can use to code the source definitions for physical and logical database files as well as for display and printer files. All DDS lines include an A in position 6. An asterisk (*) in position 7 of a DDS source line signals a comment line. You can use comment lines throughout the file definition. At a minimum, you should include a few comment lines at the beginning of each file definition to identify the nature of the file.

In addition to comment lines, DDS includes record format descriptions, which name each record layout (format) within the file; field definition lines, which describe fields within records; and perhaps key specifications to designate which fields are to serve as keys to the file. The particular nature of these specifications depends upon whether you are defining a physical file or a logical file.

DDS extensively uses a variety of keywords, each with a special meaning. Keywords that apply to the file as a whole are called file-level keywords, those that apply to a specific record format within the file are known as record-level keywords, and those that associate only with a specific field are termed field-level keywords.

Although all externally defined files share those general features previously mentioned, the details of a DDS definition depend upon the type of file you are defining. Accordingly, let’s first look at using DDS to define physical files.

DDS for Physical Files

The concept of a physical file object parallels that of an SQL table. In fact, SQL tables are implemented as physical files. When you use DDS to describe the file, the physical file’s source statements define the record format of the file. Physical files can contain only one record format. That means that every record within a physical file must have an identical record layout. The record format is signaled by an R in position 17, and you enter a name for the record format in positions 19–28 (Name++++++).

Following the record format specification, you must enter lines to define each field the record contains. After the field definitions, you optionally can designate a key for the file. A K in position 17 denotes a key field. When you list a key field, its contents determine a sequence in which you can retrieve records from the file. Figure 3.6 shows the DDS code for the Customers file discussed earlier.

Programming in ILE RPG - DDS Database Concepts - Figure 1

Figure 1: Physical file DDS

Let’s look at the details of this definition. First, UNIQUE is a file-level keyword. (All file-level keywords appear at the beginning of the DDS, before any record format specification line.)

UNIQUE stipulates that the file cannot contain records with duplicate key values. When you include this keyword, attempts to write a record to the file with a key value identical to a record already in the file causes the system to generate an error message. Use of UNIQUE is optional—without its use, the system permits records with duplicate key values.

The record format line is next. Note the R in position 17 and the format name, CUSTSREC, left-aligned in positions 19–28. DDS allows record format names (and field names, for that matter) up to 10 characters. DDS source code cannot contain lowercase alphabetic characters, except in a string enclosed in double quotation marks, so you must enter all record format and field names in DDS source as uppercase.

You define the fields of a record on successive lines below the record format line. The field name begins in position 19 (Name++++++). Next, you specify the length of the field, right-adjusted in positions 30–34 (Len++). Numeric field definitions must include a decimal entry (Dc, positions 36–37) to indicate how many decimal places the field includes. You use position 35 to specify the data type. DDS supports the following commonly used data types, discussed earlier:

  • A = Character
  • S = Zoned (signed) numeric
  • P = Packed numeric
  • L = Date
  • T = Time
  • Z = Timestamp

DDS recognizes other data types as well, but these character, numeric, and date data types satisfy most common requirements. When you do not specify a data type in position 35, DDS defaults to A for character fields or P for numeric fields (i.e., those with a decimal positions entry in positions 36–37).

Following the definition of all the fields to appear within the record, you can designate one or more fields as the record key by coding a K in position 17 and specifying the name of the key field in positions 19–28. In the example, CUSTNO is named as the file’s key field. Notice that you must define the key field as part of the record before naming it in the K specification. If you list more than one key line, you are specifying a composite (concatenated) key. For a composite key, list the key fields in order from major to minor. Note that fields need not be adjacent to each other within the record to be key components.

The TEXT keyword entries are optional ways to provide documentation. In the example, TEXT is used with each field to explain what the field represents. You must enclose text comments with apostrophes (') and surround them with parentheses. Although text comments are not required, it makes good sense to include them, especially if your field names are somewhat cryptic. TEXT also can appear as a record-level keyword to document the record format.

Programmers new to DDS are sometimes confused by the fact that the filename is not included within the DDS (except perhaps within a comment line). The filename is determined when you actually compile the DDS. By default, the name of the source member becomes the name of the compiled object, or database file.

DDS for Logical Files

Although you can, in theory, get by using only physical files to define your data, you are just scratching the surface of the IBM i database capabilities until you begin to use logical files. The concepts behind logical files correspond to those of SQL views and indexes.

Recall from the introduction to this chapter that logical files define access paths to data stored in physical files. You can use a logical file to change the retrieval order of records from a file (by changing the designated key field), to restrict user views to a subset of fields or records contained in a physical file, or to combine data stored in two or more separate physical files into one logical file. Although the data is stored in physical files, after you have defined logical files to the system, you can refer to these logical files in RPG programs as though the logical files themselves actually contained records. The advantage of using logical files is that they can provide alternative ways to look at data, including different orders of record access, without redundantly storing the actual data on the system.

A logical file based on a single physical file is called a simple logical file. The method of defining simple logical files is similar to that of defining physical files. You first specify a record format, then stipulate a list of fields (optional), and follow that with one or more (optional) key fields. Because logical files provide views of physical files, you must include the PFILE keyword beginning in position 45 of the Keywords area on the record format line, followed, in parentheses, by the name of the physical file upon which the logical record format is based.

The easiest way to code a simple logical file is to use the same record format name within the logical file as the record format name in the physical file on which the logical file is based. With this method, the system assumes the record layouts of the files are identical. As a result, you do not need to include fields within your logical record description. However, you can still designate one or more fields as a key, and this key does not have to match the key of the physical file. The example in Figure 3.7 shows a logical file based on customer file Customers.

Notice that this DDS contains no field-level entries. With this definition, all the fields defined within the physical file are implicitly included within the logical file. Because the logical file is keyed on last name and then first name, keyed sequential access of this logical file retrieves the customer records in alphabetic order by last name then by first name. Programmers widely use this kind of logical file definition to change the retrieval order of records in a file. Its effects are identical to that of physically sorting file records into a different order, but without the system overhead that a physical sort requires. This logical file is similar to an SQL index, and indeed, SQL can use this logical file as an index to improve the performance of some SQL statements.

Programming in ILE RPG - DDS Database Concepts - Figure 2

Figure 2: Logical file DDS based on Customers file

To restrict the logical file so that it includes only some of the fields from the physical file, give the logical file a record format name different from that of the record format name in the physical file, and then list just those fields to include in the logical file. Only the fields listed are accessible through the logical file. Again, you can designate one or more of these fields to serve as the key to the file. Figure 3.8 illustrates the DDS for such a logical file.

Programming in ILE RPG - DDS Database Concepts - Figure 3

Figure 3: Logical file DDS displaying accessible fields

Notice that you do not need to specify length, type, and decimal positions for the fields in a logical file. These field attributes are already given in the physical file on which the logical file is based.

You can define logical files to include only a subset of the records contained in the physical file by using Select and Omit specifications. You use this feature only if the logical file first contains a key specification. Your specifications base the record inclusion or exclusion on actual data values present in selected fields of the physical file records.

For example, assume you want to include only the male customers from the Customers file. You’d simply designate Cgender as a select field (S in position 17) and then, in position 45, provide the basis for the selection. One way to do this is with the COMP keyword. COMP specifies a comparison between a field’s value and a single given value to serve as the basis of selection or omission. You specify the nature of the comparison by using one of eight relational operators:

  • EQ (equal to)
  • GT (greater than)
  • GE (greater than or equal to)
  • LT (less than)
  • LE (less than or equal to)
  • NE (not equal to)
  • NG (not greater than)
  • NL (not less than)

Figure 3.9 shows DDS using Select/Omit to create a logical file consisting of a subset of the records in the Customers file. The S in position 17 indicates a Select specification, and the O in position 17 of the next line indicates an optional Omit specification. The DDS simply indicates that the file should select all records with Cgender equal to 'M' and omit all other records.

Programming in ILE RPG - DDS Database Concepts - Figure 4

Figure 4: Logical file DDS using Select and Omit specifications

In addition to COMP, DDS often uses two other keywords to identify selection criteria. The VALUES keyword is similar to COMP, but it allows comparison with multiple values. The RANGE keyword lets you set a range of values to select or omit.

DDS offers many other database capabilities beyond the physical and logical file constructs that you’ve examined here. Variants on logical files, for instance, can be much more complex than the simple examples we’ve illustrated. For example, you can create logical files based on two or more physical files with multiple record formats—with each format based upon a different physical file. The logical file then gives the appearance that the physical files have been merged together.

Another feature, join logical files, joins fields from different physical files into a single record, using a matching field common to the physical files upon which to base the join. The logical file then appears as if the data exists in one file, with one format, when in reality, it has been brought together from several different physical files. You can, however, accomplish many of these same results with an SQL statement, or within an RPG program, without using complex logical files. Most programmers prefer to stick with simple logical files and avoid these more complex constructs. In addition to the keywords in this chapter, the DDS can use several dozen other keywords used with data file definitions. For additional details about these capabilities, see IBM’s online Knowledge Center.

Creating Database Files with DDS

The first step in creating a physical or logical file is to enter the DDS statements into a source member by using an editor. As previously mentioned, it is standard practice to use source file QDDSSRC to store database source members. Also recall that the source type is PF for physical file and LF for logical file.

After you’ve entered your DDS code, you must compile it to create the file as an object on the system. If you are working from a development platform, such as RDi, follow the same procedure to compile a database object that you use to create a program module object. To compile by directly entering a command at a command line, rather than by working through menus or other tools, use commands CRTPF (Create Physical File) and CRTLF (Create Logical File).

If the system encounters syntax errors while trying to create your file, it sends a message indicating that the creation was unsuccessful. Otherwise, the system informs you that the job completed normally and that the database object now exists.

Once the file object exists, you can use it to store data. You can enter data into physical files by using system utilities, by writing values to the file through a program, by copying records to the file from another file, or by using SQL statements.

You must create a physical file before you can create logical files based on that physical file, as failure to do so results in error messages. Also, you must delete all the logical files associated with a physical file before you can delete the physical file.

Be aware of one additional caveat: if you want to change a physical file’s definition after you have stored data in the file, deleting the file deletes the data in the file as well. You can avoid such data loss by using the CHGPF (Change Physical File) command. This command also can accomplish changes to a physical file without deleting the data or dependent logical files.

When you compile an RPG program that uses an externally described file, the file must exist before your program can be compiled successfully. If you change the definition of a physical or logical file after you have compiled a program that uses that file, you must recompile the program before the system will let the program run. This feature, called level checking, prevents you from running a program based on an obsolete or inaccurate definition of a database file.

Next time: Externally Described Printer Files. Can't wait? Want to learn more about Programming in ILE RPG?  Pick up the book in the MC Bookstore today!

 

James Buck
Jim Buck's career in IT has spanned more than 35 years, primarily in the college education, manufacturing, and healthcare industries. Past president (13 years) of the Wisconsin Midrange Computer Professional Association, he has served on several teams developing IBM and COMMON certification tests. Jim has co-authored several IBM i textbooks with Bryan Meyers that are used by many companies and in colleges worldwide. Other accomplishments include: recipient of the 2007 IBM System i Innovation - Education Excellence Award, 2014 COMMON President's Award, and 2013/2016/2017 IBM Champion - Power Systems.

Jim is the president and founder of imPower Technologies, where he provides professional IBM i training and consulting services. He is active in the IBM i community, working to help companies train their employees in the latest IBM technologies and develop the next generation of IBM i professionals.

MC Press books written by Jim Buck available now on the MC Press Bookstore.

Control Language Programming for IBM i Control Language Programming for IBM i
Master the A-Z of CL, including features such as structured programming, file processing enhancements, and ILE.
List Price $79.95

Now On Sale

Mastering IBM i Mastering IBM i
Get the must-have guide to the tools and concepts needed to work with today's IBM i.
List Price $85.95

Now On Sale

Programming in ILE RPG Programming in ILE RPG
Get the definitive guide to the RPG programming language.
List Price $95.95

Now On Sale

Programming in RPG IV Programming in RPG IV
Understand the essentials of business programming using RPG IV.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

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: