DDS Physical Files to DDL Table Conversion – Why?
DDS Physical Files to DDL Table Conversion – Why?
Have you ever thought about modernizing your database? A lot of IBM i companies have replaced the green screen with a graphical interface and converted their legacy monolithic programs into procedures and functions written in modern programming languages, but what about the database?
Data is often still stored in DDS (Data Description Specification) described physical files which grew over time and include a lot of redundancy, which requires additional programming effort. Clearly, some modernization is required. However, it is just as clear that the database cannot be converted and redesigned in a single step – everyday business must go on. Nevertheless, you have to start somewhere, and what you can do first is convert the DDS described physical files into DDL (Data Definition Language) defined tables and also convert DDS described keyed logical files into SQL indexes.
When we talk to customers or programmers about going from DDS to DDL, they quite often do not see any need. They reason that DDS described physical files and SQL defined tables can be accessed and used with SQL and native I/O in exactly the same way.
So why convert DDS described physical files into SQL tables?
• DDS for defining database objects is only used on IBM i, while SQL is standard and all database manufacturers are committed to the standard. Even though there might be some SQL dialects, the differences between DDL commands for different databases are marginal.
• Any programmer or database administrator coming out of school or from a different operating system or a different database first has to learn the DDS syntax, while SQL is widely taught and used.
• DDS is stabilized – i.e., the last enhancement was in release V5R3M0 to allow packed and zoned fields to be defined with up to 63 digits.
• All current enhancements are done in SQL, and there are even enhancements in older releases (for example Identity Columns introduced with release V5R1M0) that are not available in DDS. The following list is only a brief selection of important features that can only be used in SQL:
- Additional Datatypes: CLOB, DBCLOB, BLOB (Large Objects), DECFLOAT, XML, ROWID
- Identity Columns: automatically generates a unique key value
- Auditing Columns: for example, ROW CHANGE TIMESTAMP automatically stores the latest update timestamp
• A lot of companies still use cryptic 6-to-10-character column names, because RPGIII could only handle 6 characters and RPGIV (prior to release 6.1) only 10-character column names. Even though longer names could be generated in DDS (by using the keyword ALIAS), long names were rarely defined because of the RPG restrictions.
Quite often, these column names are cryptic or consist only of a few characters followed by a running number, which makes it difficult for new programmers to determine what the field’s content is.
With SQL, long column and database object names can be created with up to 128 characters, and they can coexist with short (maximum 10-character) system names.
Working with longer descriptive names makes your life a lot easier, but if you want to add long column names, you should plan the long names based on predefined naming conventions.
Otherwise, you may end up with different names such as CustNo, CustomerNumber, Customer_No, or Customer#, all describing a customer number column.
Some differences between DDS physical files and SQL tables that you need to know about
1. When writing, updating or deleting records with native I/O, a format name that is different from the table name is needed. According to the SQL standard, a SQL table does not include a record format. But due to a non-standard enhancement in Db2 for i, it is possible to add a record format to a SQL table by specifying the RCDFMT keyword in the CREATE TABLE statement.
2. A DDS described physical file can include a key definition. SQL tables are always unkeyed. But unique keys can be converted into a primary key, and there is no way to include non-unique keys in a SQL table.
When converting from DDS to DDL, you should first check the programs with a keyed access on the physical file. Then, create a new logical file or (even better) a SQL index with the appropriate key fields and change your RPG programs to access the new index or logical file instead of the physical file. You can then convert your DDS described physical file into a SQL table, ignoring your key information …without wasting your primary key, which may be important for implementing referential integrities in the future based on identity columns.
By the way, if you have a unique key on your physical file and you do not create an additional index, your RPG program can handle the primary key constraint exactly in the same way as it handled the unique key in the physical file.
3. Minor architectural differences in database objects deliver significant advantages.
When inserting data into a DDS described physical file, the data is not checked when it is written into the file, but it is checked when it is read.
In this way, it is possible to enter invalid numeric values (e.g. *BLANKS) into numeric fields. This situation can occur by executing the CPYF CL command with the Format Option (FMTOPT) *NOCHK. In old programs, we quite often run into this situation, specifically when the record data structure within the RPG program was not properly initialized. Even if your programs work correctly today, you still may have records with invalid numeric values in your DDS described physical files. This invalid data must be detected and rectified before converting the physical file to a SQL Table.
Conversely, when inserting data into a DDL defined table, the data is checked when it is written, but not checked when it is read, because the table can only include valid data. In this way, it is not possible to write invalid numeric data into numeric fields, not even with a CPYF with Format Option *NOCHK. So a SQL defined table includes only valid data, one big advantage.
But wait—there is also another advantage to converting DDS described physical files into SQL tables:
What happens if the data is checked when it is written, but not checked when it is read compared with the opposite? How many read operations and how many write/update operations will be performed? Maybe 80% read and 20% write/update?
Even though we talk about nano seconds, checking data takes time. If 80% of the database operations are no longer checked (because data is read from a SQL table) and only 20% of the database operations (write/update) are checked, file access becomes faster—regardless of whether the access is performed with SQL or native I/O.
In short, converting DDS described physical files to SQL tables is already highly beneficial, even if you only go that far.
After the conversion, we can start to include the new features that were added in the last releases, such as Identity Columns, Auditing Columns, Temporal Tables etc. We can also start/continue to include program logic into the database, for example by adding check constraints, referential integrities and triggers. And with that, we have already laid the first cornerstone for a future redesign of the database.
If you are looking for answers in regards to your database, feel free to reach out to me. I am here to consult on all your IBM i related database concerns![/vc_column_text][/vc_column][/vc_row]
About Birgitta Hauser
View all posts by Birgitta Hauser | Website
Physical files in AS400
A physical file is a database file that contains data records. The data records are grouped into physical file members and each member has its own access path to the data. Usually, database files have only one member which, by default, is added to the file when the file is created.
A physical file can contain only one record format. Based on the requirement, we can create two types of physical files.
- Flat file
- DDS file
What is meant by Flat file?
Flat files are nothing but a physical file with only one column. The length of this column is usually larger than a typical column of any other physical file. This is because each record of a flat file actually contains several columns of an ordinary physical file. We can write, read, update, delete the Flat file.
The object name, the record format name and field name of this flat physical file are the same as specified when creating the physical file.
Create Flat file
We need to use the CRTPF command as below in the command line to create a physical file.
RCDLEN must be specified, SRCFILE and SRCMBR cannot be specified; RCDLEN is used to specify a fixed record length for the record format. A value ranging from 1 through 32766 bytes can be specified for the record length.
AS/400 actually creates a file named HISTORY in library TEST, a member named HISTORY, and a field of length 100 that is named HISTORY.
What is meant by DDS file?
File-level, record-level, field-level, and key-field-level entries are used to define a physical file with data description specifications.
Entries needs to be in the following order to define a physical file:
- File-level entries
- Record-level entries
- Field-level entries
- Key field-level entries
File level entries
File level entries are optional and they give the system information of the entire file. UNIQUE, LIFO, FIFO, FCFO, REF are the keywords used at the file level.
UNIQUE: File level keyword used to specify that records with duplicate key values are not allowed within a member of this physical or logical file.
You can specify whether null key values are to be considered as duplicates using the parameter. Any insertions or additions of new records, or updates to existing records, which might result in a duplicate key, are rejected. The application program issuing the write, or the update operation receives an error message. When a workstation user is using data file utility (DFU), a message is displayed at the workstation. A copy file command that copies records with duplicate keys in this file is not completed.
FIFO: File level keyword used to specify that if records with duplicate key values are retrieved from the same physical or logical file member, they are to be retrieved in a first-in first-out (FIFO) order.
At least one key field must be specified in a file containing the FIFO keyword.
LIFO: File level keyword used to specify that records with duplicate key values from the same physical file member are retrieved in a last-in first-out (LIFO) order.
At least one key field must be specified in a file containing the LIFO keyword.
FCFO: File level keyword used to specify that if records with duplicate key values are retrieved from the same physical or logical file member, the record with the key value that was changed first is the first record retrieved. This is in a first-changed first-out (FCFO) order.
At least one key field must be specified in the file containing the FCFO keyword.
REF: File level keyword to specify the name of the file from which field descriptions are retrieved. REF supplies the field attributes from a previously defined record format. Specify the file name once in the REF keyword instead of on several REFFLD keywords if each field description refers to the same file. To refer to more than one file, use the REFFLD keyword. You can specify the REF keyword only once.
Record format level entries
A “record format” is a named structure that is used for device file I/O. For a PF the record format name is specified along with an optional text description. The record level entries can be FORMAT, TEXT.
Specify the record format in either of the following two ways:
- Define a new record format.
Specify field and key-field specifications as required for the new record format.
- Share an existing record format.
Use the FORMAT keyword to specify that the operating system is to use a previously defined record format from another physical file. When the FORMAT keyword is used, you must specify key-field level specifications again (if a keyed access path is required) even if they were specified on the existing record format.
Record-level keyword used to specify that this record format is to share the field specifications for a previously defined record format. The name of the record format you are defining must be the name of the previously defined record format.
The format of the keyword is:
FORMAT (LIB-NAME / FILE-NAME)
The database-file-name parameter is required. It is the name of the physical or logical file from which the previously defined record format is taken.
The library-name is optional. If you do not specify the library-name, the library list (*LIBL) in effect at file creation time is used.
If you specify the FORMAT keyword, you cannot specify field specifications for this record format. Specify key specifications and, if necessary, select/omit specifications if you want them to be in effect for this file. (They can be the same as or different from the previously defined record format.)
The FORMAT keyword is not valid in join logical files and you cannot specify a join logical file as the parameter value on the FORMAT keyword.
If the database file from which you are using the record format is deleted, the record format remains in existence as long as some file is using the record format. For example, RECORD in FILE2 uses the FORMAT keyword to share the specifications of RECORD in FILE1. Both files have been created. If FILE1 is deleted and then re-created with different DDS, RECORD still exists in FILE2. It can be referred to for the original record format by other files using the FORMAT keyword.
The record-level keyword is used to supply a text description of the record format and it is used for documentation purposes only.
The format of this keyword is:
The text must be enclosed in single quotation marks. If the length of the text is greater than 50 positions, only the first 50 characters are used by the high-level language compiler.
Field level entries
The field names and field lengths are specified along with an optional text description for each field. (ALIAS, ALWNULL, CCSID, CHECK, CHKMSGID, CMP, COLHDG, COMP, DATFMT, DATSEP, DFT, EDTCDE, EDTWRD, REFFLD, REFSHIFT, TEXT, TIMEFMT, TIMESEP, VALUES, VARLEN)
ALIAS: Field-level keyword to specify an alternative name for a field. When the program is compiled, the alternative name is brought into the program instead of the DDS field name.
ALWNULL: Field-level keyword to define this field to allow the null value.
CCSID: Field-level keyword to specify a coded character set identifier (CCSID) for character fields. The CCSID keyword is a file- or field-level keyword on physical files, and a field-level keyword on logical files.
CHECK: Field-level keyword to specify validity checking for data.
CHKMSGID: Field-level keyword to identify an error message that is associated with validity checking keywords.
CMP/COMP: Field-level keyword to specify validity checking for the field you are defining when the field is referred to later during display file creation. The COMP keyword is equivalent to the CMP keyword.
COLHDG: Field-level keyword to specify column headings used as a label for this field by text management, the query utility, the data file utility (DFU), and the screen design aid (SDA).
DATFMT: Field-level keyword to specify the format of a date field. This keyword is valid only for date fields (data type L) or for logical-file zoned fields (data type S), packed fields (data type P), or character fields (data type A) whose corresponding physical file fields are date fields (data type L).
DATSEP: Field-level keyword to specify the separator character for a date field. This keyword is valid only for date fields (data type L).
DFT: Field-level keyword to specify a default value for a field.
EDTCDE/EDTWRD: Field-level keyword to specify editing for the field you are defining when the field is referenced later during display or printer file creation. The EDTCDE and EDTWRD keywords do not affect the physical or logical file.
REFFLD: Field-level keyword to refer to a field.
REFSHIFT: Field-level keyword to specify a keyboard shift for a field when the field is referred to in a display file or data file utility (DFU) operation.
TEXT: Field-level keyword to supply a text description (or comment) for the record format or field used for program documentation.
TIMEFMT: Field-level keyword to specify the format of a time field.
This keyword is valid for either time fields (data type T) or zoned fields (data type S) whose corresponding physical file fields are time fields (data type T).
TIMESEP: Field-level keyword to specify the separator character used for a time field. This keyword is valid only for time fields (data type T).
VALUES: You can specify this keyword at the field level, the select/omit-field level, or both.
VARLEN: You can use this field-level keyword to define this field as a variable-length field.
Variable-length fields are useful for improving storage when the data for the field typically fit within a certain length, but can occasionally be longer. Specify the maximum length of the field in positions 30 to 34. You can specify the allocated length (or typical length) in the parameter.
Key field level entries
The field names used as key fields are specified. (DESCEND, SIGNED, ABSVAL, UNSIGNED, ZONE, NOALTSEQ, DIGIT)
DESCEND: Key field-level keyword to specify that the values of this character, hexadecimal, or numeric key field are retrieved in descending sequence.
SIGNED: Key field-level keyword is in effect when sequencing the values associated with this numeric key field, the operating system considers the signs of the values (negative versus positive values).
ABSVAL: You can use this key-field level keyword to direct the operating system to ignore the sign of the field when the system sequences the values associated with this numeric field.
UNSIGNED: Key field-level keyword to specify that numeric fields are sequenced as a string of unsigned binary data. The default values of character, date, time, timestamp, and hexadecimal fields are set to unsigned values.
ZONE: You can use this key field-level keyword to specify that only the zone portion (farthest left 4 bits) of each byte of the key field is used when the operating system is constructing a value associated with this key field. The digit portion is filled with zeros.
NOALTSEQ: Key field-level keyword to specify that the ALTSEQ keyword specified at the file level does not apply to this key field.
If you specify ABSVAL or SIGNED for a key field, NOALTSEQ is automatically in effect, whether the NOALTSEQ keyword is specified for that key field.
DIGIT: Key field-level keyword is in effect, only the digit portion (farthest right 4 bits) of each byte of the key field is used when the system constructs a value associated with this key field. The zone portion is zero-filled.
Chapter 22: Data Description Specifications
The abbreviation DDS stands for Data Description Specifications. DDS is a programming language that originated with the System/38. DDS is used to describe records in files by giving a name to the record and listing all the fields that will be considered part of the record. DDS enables the eServer i5 to have a relational database that is fully integrated with the system. The i5 also supports SQL for database definition and manipulation, but that interface is not discussed in this chapter.
No matter what language you use to write your programs, the system always knows the record layout of your data files if they were defined using DDS. And, as you will see, many tools are available to you if your data files are externally defined using DDS.
There is an alternative, however. You can also describe the file inside each program that uses it. Older systems often describe files this way because no other choice exists. The problem with program-described files is that they have a tendency toward lack of standardization. For example, the first field of the file (which holds a vendor number) may be called VNDNBR in one program, VENDOR in another, and VENDNO in a third.
Because each program must contain the entire definition for the record, the effort is multiplied by whatever number of programs use the same file. Although using compiler directives such as /COPY in RPG alleviates the problem, files created with DDS are fully defined to the system. The system knows what fields make up the record, so it can make use of this definition everywhere a file is used. For example:
CL programs have no compiler directive to copy source code (like /COPY in RPG). If you use a file in CL, you must declare each field separately or at least know its absolute position within the record. If the file were created with DDS, however, the CL program already knows the definition of the file.
Products and features such as Query/400, SQL/400, OPNQRYF, and Query Management also can make use of the definition of the file provided by DDS. Without this file definition, it would be impractical (to say the least) to use any of these products (except Query/400, which can use IDDU file definitions).
This all started as a question from a member of another programming team at work. They had been asked to add a new field to an existing file. Most of the time this field would be empty, but it could contain up to 2,000 characters of data in some cases.
"It would be easy with a SQL table," they said "I could just make a new column VARCHAR (variable length character field) and make the default value null. But I don't know how to do that with a DDS field."
By using a variable length character field will mean that the field will not always be 2,000 characters. It will be as long as the data within it. And when there is no data in the field it will take up zero space. What a disk space savings.
Fortunately doing this with a DDS file is as easy as it is with a SQL table using the right keywords when defining the file. And it is just as easy to handle the variable length and null value in a RPG program. I decide to create my example file and the program on an IBM i partition that is running 7.2 just to show that there is nothing from a newer release need to do this.
It will come as no surprise to my regular readers that the file I will be using in this post is called . It contains just two fields:
|01 A UNIQUE 02 A R TESTFILER 03 A KEY01 5A 04 A BIGCOL 2000A VARLEN 05 A ALWNULL 06 A ALIAS(BIG_COLUMN) 07 A K KEY01|
Line 1: When I define a DDS file or DDL (SQL) table I always use a unique key to ensure that it will never contain duplicate keyed records.
Line 3: This is the key field:
Line 4 – 6: This is the definition for the field my colleague was asking for. It is a maximum of 2,000 characters long. The means that it is of varying length.
Line 5: The field is allowed to be null. Notice I did not say "contains null" I will explain later why that is so.
Line 6: I decided to give this field an alias name, that is longer than a ten long system name, that I can use in my RPG program.
After compiling the file if I use the Display Field File Description command, , I can see that the field has not been allocated the space of 2,000 characters:
|Field Type Length Length Position Usage Heading KEY01 CHAR 5 5 1 Both KEY01 BIGCOL CHAR 2000 2002 6 Both BIGCOL Alternative name . . . . . . . . . . . . : BIG_COLUMN Variable length field -- Allocated length : None|
contains three records. Two where the is null, and one with text in it.
|KEY01 BIGCOL ----- -------------------------------------------------------- 1 - 2 VERY, VERY LONG DESCRIPTION FOR THIS COLUMN HERE TO SHOW 3 -|
Before I show any programs using this file I want to explain how null works in the IBM i operating system. As I mentioned above the fields themselves never contain null. Within the file is a "null byte map". The only place I have ever had to accommodate "null byte map" is in a RPG program is in a trigger program. The "null byte map" area in RPG is broken up into a series on "null indicators", one for each field in the file. If the "null indicator" contains a value of "on" the corresponding field is considered null. The built in function allows me to handle this "null indicators" for the individual fields, as you will see below.
And now a simple program showing how to read this file.
|01 **free 02 ctl-opt option(*nodebugio:*srcstmt) 03 alwnull(*usrctl) ; 04 dcl-f TESTFILE keyed alias ; 05 dcl-s Work50 char(50) ; 06 dcl-s Length int(5) ; 07 dow (1 = 1) ; 08 read TESTFILER ; 09 if (%eof) ; 10 leave ; 11 endif ; 12 dsply ('Key field = ' + KEY01) ; 13 Length = %len(BIG_COLUMN) ; 14 dsply ('Length of BIG_COLUMN = ' + %char(Length)) ; 19 enddo ; 20 *inlr = *on ;|
Line 1: It is 2020 so all of our program should be totally free RPG.
Lines 2 and 3: These are the program's control options. The important one for today is on line 3, . This allows me handle nulls in the file using the null indicators.
Line 4: I have defined the file as input only, keyed, and I can use the alias names of the fields.
Lines 5 and 6: I will be using these variables in various parts of this example program.
Lines 7 – 11: I will be reading all of the records in the file.
Line 12: Display the value of the key field.
Line 13: As variable in length I can use the built in function to see how long the field is in each record.
Line 14: Display the length of .
When I run this program I see the following:
|DSPLY Key field = 1 DSPLY Length of BIG_COLUMN = 0 DSPLY Key field = 2 DSPLY Length of BIG_COLUMN = 69 DSPLY Key field = 3 DSPLY Length of BIG_COLUMN = 0|
As expected the field in the first and third records are empty.
If I wanted to see the contents of the field I might change the following lines in the program
|13 Work50 = %subst(BIG_COLUMN:1:50) ; 14 dsply Work50 ;|
Line 13: I have to use the variable as the command can only be used with a variable of up to 52 characters.
Alas when I run the program with these lines in it I get the following:
|DSPLY Key field = 1 Length or start position is out of range for the string operation (C G D F).|
As I cannot access the data within the field in the records where it is null I need to do something like this to avoid the error:
|13 if %nullind(BIG_COLUMN) ; 14 dsply ('Big column = *N') ; 15 else ; 16 Work50 = %subst(BIG_COLUMN:1:50) ; 17 dsply Work50 ; 18 endif ;|
Line 13: If the field's null indicator is on I execute line 14. If not then I will display the contents, well the first 50 character of, .
When I call the program I do not get an error.
|DSPLY Key field = 1 DSPLY Big column = *N DSPLY Key field = 2 DSPLY VERY, VERY LONG DESCRIPTION FOR THIS COLUMN HERE T DSPLY Key field = 3 DSPLY Big column = *N|
In the next program I am going to be updating and writing to .
|01 **free 02 ctl-opt option(*nodebugio:*srcstmt) 03 alwnull(*usrctl) ; 04 dcl-f TESTFILE keyed usage(*update:*output) alias ; 05 setll *hival TESTFILER ; 06 readp TESTFILER ; 07 %nullind(BIG_COLUMN) = *off ; 08 BIG_COLUMN = 'UPDATED IN PROGRAM' ; 09 update TESTFILER %fields(BIG_COLUMN) ; 10 KEY01 = '4' ; 11 %nullind(BIG_COLUMN) = *on ; 12 write TESTFILER ; 13 chain '2 ' TESTFILER ; 14 if (%found) ; 15 %nullind(BIG_COLUMN) = *on ; 16 update TESTFILER %fields(BIG_COLUMN) ; 17 endif ; 18 *inlr = *on ;|
Line 4: This time I have defined the file for update and output.
Lines 5 – 9: Here I am getting the last record in the file, setting off the null indicator, and moving a character value to the field. I always like using the built in function as it will only update the fields I list.
Lines 10 – 12: Here I am writing a new record to the file, and as I have set on the null indicator the will be considered null.
Lines 13 – 17: I always use a key field list with , , etc. I prefer this method to the fixed format key list or key data structure as you can see the fields, or in this case value, I am using as the key to chain to the file on the line the operation happens, rather than having to hunt elsewhere for the key's definition. When the record is retrieved from the file I am setting on 's null indicator to on to wipe the value from the field and set it to null.
When the program has finished the contents of now looks like:
|KEY01 BIGCOL ----- ------------------ 1 - 2 - 3 UPDATED IN PROGRAM 4 -|
And by using the variable length field it is not always 2,000 characters long, it is just the length of the field's contents thereby saving the disk space that would have been utilized by the rest of the 2,000 unused characters.
This article was written for IBM i 7.2, and should work for some earlier releases too.
We finish our first three days in the same place and in the same way as we started - in the bathroom, with cancer. Finally, in the corridor, when I'm already dressed, Masha gives me a great farewell blowjob. God, this is beautiful, he moaned, choking on his words, devouring her pussy.AS/400 - Introduction to DDS - Data Description Specifications
Open your mouth, you stupid bitch. - He commanded. The old woman, realizing what they wanted from her, opened her toothless mouth wide and closed her eyes. A stream of warm urine splashed her face and poured into her mouth, an elderly woman, choking, began to try to swallow. A moment later, unable to bear this sight, the teenager grabbed the old woman by the hair, planted her open mouth on his excited penis.
You will also be interested:
- Simple baseball tattoos
- Trout tattoo
- Simple chalkboard quotes
- 1 direction memes
- Gearwrench 1/4
- Kamen rider tumblr
- Vip72 download
- Orlando chess tournament
- Kawaii logo ideas
- Avg size crossword
- S10 prism blue
- Harbor freight membership
- Thunder god
On the eve of departure, our darling announced to us that she wanted to spend a farewell night with the whole group. This proposal was accepted simply with a bang !!. We laid a gorgeous table and sent the hero of our time, Sanka (the discoverer of Olya) to her room.