Deep Dive into DDS and DDL

Externally described files offer the following advantages:

• Code efficiency in RPG/400 programs. If the same file is used by many programs, the fields can be defined once to the OS/400 system and used by all the programs. This practice eliminates the need to code input and output specifications for RPG/400 programs that use externally described files.
• Requires less maintenance when the file’s record format is changed. You can often update programs by changing the file’s record format and then recompiling the programs that use the files without changing any coding in the program.
• Improved documentation because programs using the same files use consistent record-format and field names
• Improved reliability. If level checking is specified, the RPG program will notify the user if there are changes in the external description.

Defining Externally Described Files

When defining an externally described file in fix format you have to provide file format as E whereas in free format you can use DISK(*EXT).

The information in this external description includes:

• File information like file type, attributes, access method (by key or relative record number)
• Record-format description, which includes the record format name and field descriptions (names, locations, and attributes).

Types of files that can be described externally in a RPGLE program

• Physical files (PF): A Physical file or PF is a data file in IBMi which contains actual data that is stored on the system and its description.
• Logical files (LF): A logical file contains a description of records of a Physical File. They do not contain actual data. It is a view or representation of one or more PFs. A LF cannot exists without a PF.
• Display files (DSPF): Display files used in IBMi to define the layout of the screens for interactive programs. It allows developers to design user interfaces, specifying screen formats, Input fields and output areas.
• Printer file (PRTF): Printer file is used to define the format of the output intended for printing or to show reports to user. PRTF specify the arrangement of text and data on printed documents and ensures proper format, presentation, and alignment.

Flat Files

A flat file is a special type of physical file that do not have any hierarchical structure or multiple records formats. It consists of a single field with long length (RCDLEN) which is defined at the time of creation. The maximum length of RCDLEN can be 32766 bytes.

Flat files have no field definitions and no indexes can be built off of them. In a flat file the file name, record format & field name are same.

We can write, read, update, delete the Flat file. Reading and deleting can be done normally whereas update and write operations required the use of Data Structures.

Usage

• Flat files are mostly used as Output file to copy the data to Stream file on IFS.
• Flat files are used to store data for Pre-Runtime Arrays.

Creating a flat file.

• Creating a flat file is same as creating Physical File.
• It is very easy to declare either in command line or even in DDS in STRSEU editor.

CRTPF (Library/FlatFileName) RCDLEN(50)

Figure 1 : Creating A Flat file

Figure 2 : Creating A Flat file

When we do DSPFFD (Display File Field Description) on a flat file. We can see that the file
name, record format name & field name is same for flat file. The record length provided at the time of creating flat file becomes its field length

Figure 3 : DSPFFD On Flat files

Operations on a flat file:

Reading a Flat File Using RPGLE PGM

Figure 4 : Reading A Flat file

• Line 2 & 3 : Included a flat file “FLATFILE” in program by declaring it with usage as input and PREFIX W_ to make field name as w_flatfile to the field and renamed the record Format from FLATFILE to Ffile with RENAME.(Note: We need to RENAME the record format to ignore compile-time severity 40 error
  *RNF2109 And we need to add PREFIX to the field, otherwise, we will again get compile-time severity 30 error *RNF7503)

  

• Line 4 and 5: are comment.
• Line 6: We have defined a variable FFvar with the same length as flat file RCDLEN.
• Line 9: We have set the pointer on the start RRN value on the flat file.
• Line 10: Read the record of a flat-file from start.
• Line 11 to 15: we have started a do-while loop till end of file is reached.
• Line 12: The records from flat file fields is assigned to the variable FFVar.
• Line 13: Displaying the data in FFvar.
• Line 14: Read the next data from flat file.
• Line 17: Set the last record indicator = *ON.

Writing data in a Flat File Using RPGLE PGM

Figure 5: Writing data into a flat file

• Line 2 & 3 : Included a flat file “FLATFILE” in program by declaring it with usage as input, output, update & delete and PREFIX W_ to make field name as w_flatfile to the field and renamed the record Format from FLATFILE to Ffile with RENAME.
• Line 6 : Declare a variable FFvar with same length as RCDLEN.
• Line 12 : Initialize the FFvar with the data.
• Line 13: Assigned the value of FFVar to the flat file field w_Flatfile.
• Line 14: Write the data on record format FFile using WRITE Opcode.
• Line 17: Set the last record indicator = *ON

Chain and Update on Flat File Using RPGLE PGM

Figure 6 : Chain and Update on a Flat file using RPGLE Program

Figure 7 : Data in Flat file before chain and Update

Figure 8 :Data in Flat file after chain and Update

• Line 2 & 3 : Included a flat file “FLATFILE” in program by declaring it with usage as
  input, output, update & delete and PREFIX W_ to make field name as w_flatfile to the field and renamed the record Format from FLATFILE to Ffile with RENAME.
• Line 5 : Declare a variable UpdVar with same length as RCDLEN.
• Line 8 : Initialize the UpdVar with the data.
• Line 9: Set the data pointer to 1 RRN with Chain operation on flat file.
• Line 10 – 13: this is an If-Elseif block which get executed when the chain operation has found data in flat
  file.
• Line 11 : Assigns the value in UpdVar to w_Flatfile field in flat file.
• Line 12: Update the flat file using UPDATE opcode and record format Ffile.
• Refer Figure 7 and Figure 8 for data in Flat file before and after Chain-Update operation.
• Line 15: Set the last record indicator = *ON

Chain and Delete on Flat File Using RPGLE PGM

Figure 9: Chain-Delete on a flat file using RPGLE program

• Line 2 & 3 : Included a flat file “FLATFILE” in program by declaring it with usage as input, output, update & delete and PREFIX W_ to make field name as
  w_flatfile to the field and renamed the record Format from FLATFILE to Ffile with RENAME.
• Line 5 : Declare a variable DelVar with same length as RCDLEN.
• Line 8 : Initialize the DelVar with the data.
• Line 9: Set the data pointer to 1 RRN with Chain operation on flat file.
• Line 10 – 13: this is an If-Elseif block which get executed when the chain operation has found data in flat
  file.
• Line 11 : Assigns the value in DelVar to w_Flatfile field in flat file.
• Line 12: Delete the flat file record using DELETE opcode and record format Ffile.
• Refer Figure 10 and Figure 11 for data in Flat file before and after Chain-Delete operation.
• Line 15: Set the last record indicator = *ON

Figure 10: Data in flat file before chain delete

Figure 11: Data in flat file after chain-delete

Introduction

• In AS400, a physical file is a fundamental database object used for storing structured data.
• It serves as a container for records, like a table in a relational database.
• Physical files are a key component that is widely used in IBM’s AS400 platform for data storage and retrieval.

Usage

• Physical files are used to store several types of data, including customer information, inventory records, financial data and more.
• They play a significant role in application development on the AS400 platform, enabling programs to read, write, update, and delete records efficiently.
• Physical files are also crucial for generating reports and performing data analysis.

Common Commands for Physical File in AS400

• Create Physical File: CRTPFThis command is used to create a new physical file.
• Reorganize Files: RGZPFMThe RGZPFM command is used to reorganize a physical file, optimizing its storage and performance.
• CHGPF: The CHGPF command is used to change the attributes of a Physical file. This command allows us to modify various properties of PF such as record format, record length, field definition etc.

Restrictions and Compatibility

• Record Length Each physical file can have a maximum record length, which may vary based on the AS400 model or OS version.
• Compatibility AS400 physical files are primarily designed for use within the AS400 environment. While there are methods to access AS400 data from other platforms, it may require additional integration work.

Examples:

To Create PF, press F6 and fill following details

Press Enter.

Press F4 on first line

For Record format, Type ‘R’ in Name type
For Field name, Name type will be blank.
For Key field, Type ‘K’ in Name Type.

After this provide the lengths and data type.

In function we will use the keywords like COLHDG for Column heading and TEXT for Text description.

Now the PF looks like this.

Now to Compile this PF we can either use opt 14 or can use ‘CRTPF’ command as shown below. . . . . . . . . . . .

Press Enter and PF will be compiled in the library.

UPDDTA

To add record in physical file we can use below command

 UPDDTA PIOLIB/TESTPF

Or we can use SQL statements as well.

Strsql . . .. press enter . . .. write below query

 Insert into piolib/testpf values (1,’name’,’city’)

Type of Entries

• Record level entries For a PF, the record format name is specified along with an optional text description. The record level entries can be FORMAT, TEXT.

FORMAT

This record-level keyword specifies that the record format being defined is to share the field specifications of a previously defined record format. The name of the record format being defined must be the name of the previously defined record format.

The format of this keyword is:

FORMAT (LIB-NAME / FILE-NAME)

TEXT

This 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:

TEXT (‘description’)

• File Level Entries (Optional): UNIQUE: A record cannot be entered or copied into a file if its key value is same as the key value of a record already existing in the file.FIFO: The duplicate key records will be retrieved first in first out order.

  LIFO: The duplicate key records will be retrieved in last in first out order.

  FCFO: The duplicate key records will be retrieved in first changed first out order.

  REF: This keyword is used to specify the name of the file from which the fields are taking definition.

  

  Same syntax for FIFO (First In, First Out), LIFO and FCFO.

  Syntax and how to use REF keyword:

  

  Now we can refer to the file ‘TESTPF’ to take the field definition in the new file.

  

  In Ref, will enter ‘R’ and in Function will use REFFLD (Referenced Field) with filed name and file name which we are referring.

  

• Field Level Entries:The Filed name and Field lengths are defined in this along with optional text description.For that we use ALIAS, COLHDG, DATFMT, EDTCDEEDTCDE, EDTWRD, REFFLD etc.
• Key Field Level Entries: The field names used as key fields are specified.

CHGPF

If we do not want to lose our data but want to compile source member, we can do it by using CHGPF command.

It is mostly used when we change the attribute of a field.

CHGPF FILE(PIOLIB/TESTPF) SRCFILE(PIOLIB/QDDSSRC) SRCMBR(TESTPF) MAXMBRS (2)

So here I have added maximum member to 2 using CHGPF command so now we can add one more member to same PF ‘TESTPF’.

ADDPFM

By using ADDPFM command we can add members to the physical file.

ADDPFM + F4 . . ..

ADDPFM FILE(PIOLIB/TESTPF) MBR(MEMBER2) TEXT (‘Test Member’)

Enter . . ..

Now one member is added in the physical file TESTPF.

To do RUNQRY on that particular member, write runqry on command line and press F4 and give that member name shown as below screenshot.

Logical File

Introduction

• Logical file provides a unique way of accessing data stored in Physical file.
• Logical files are used to define specific access paths or views to the data in a physical file, making it easier and more efficient to retrieve data for specific purposes.
• We can filter the data with criteria by using select and omit command.
• LF (Logical Files) does not contain any type data, it just provides the view of a physical file, hence it does not occupy any memory as well.
• More than one logical file can be derived from one physical file.
• Logical files can contain up to thirty-two record formats.

Common Commands

• CRTLF To create the Logical file, we can either do it by taking option fourteen or can use ‘CRTLF’ command.
• CHGLFIT is used to modify the attributes and definitions of a logical file.
• ADDLFMBy using ADDLFM command we can add members to the Logical file.

Types of Logical Files

• Non-Join Logical File
• Join Logical File

Type of Entries

• File Level Entries (Optional)REFACCPTH:Syntax:

   Lib Name/Database Name

  The access path information for this logical file is to be copied from another PF or LF.

  DYNSLT:

  This File level keyword indicates that the selection and omission specified in the file are done at processing time.

  Dynamic select occurs whenever the program reads a file. But the access path occurs before the file is read (but not necessarily).

• Record Level EntryPFILE: In Record level entry, we defined the physical file whose data is going to be accessed by this Logical file.Format of the keyword is: PFILE (LIB name / PF name)

  Other than there are 3 more Lev entries which are optional. Filed level entry, Key field level entry and selection/omission level entry.

  Below is the screenshot to define the level of entries in Logical file. . ..

  

Examples

• Non-Join Logical FileHow to create a logical file . . . . . .

To Create LF, press F6 and fill following details

Press Enter.

Press F4 on first line

First will define the record format so type “R” in name type and give record format a name as shown in screenshot.

In functions will use the keyword PFILE with physical file library and physical file name.

After this Mention the fields which you want in this Logical file from that physical file.

Below is the screenshot for same:

Now to Compile this LF we can either use opt fourteen or can use ‘CRTLF’ command as shown below. . . . . . . . . . . .

CRTLF FILE(PIOLIB/TESTLF) SRCFILE(PIOLIB/QDDSSRC) SRCMBR(TESTLF)

• Select/Omit KeywordWe use SELECT/OMIT keyword to filter the record of physical file according to our need.We have one physical file ‘TESTPF’ as shown below with some recording. . ..

  

  Now Create one logical file which refers to this physical file “TESTPF” with select/omit criteria shown as below screenshot. . . . . .

  

  Below is the output screenshot.

  

• Join Logical File
  • Join Logical file is a logical file that combines two or more than two physical files.
  • In join logical file only one record format can be specified.

A. Type of Entries in Join Logical File

• i) File Level entries (Optional): ‘JDFTVAL’This Keyword in the LF is used to specify default values for fields that do not exist in one of the joined physical files.
• ii) Record Level entries: ‘JFILE’ This Keyword in the LF is used to specify the files to be joined.
• iii) Join Level entries: ‘JOIN,’ ‘JFLD,’ ‘JDUPSEQ’ JOIN is similar that this file level entries to represent the position of the files. There must be one primary file and can have more than one secondary file.FLD is used to specify join fields in a logical file.

  JDUPSEQ This join–level keyword is used to specify the order in which records with duplicate join fields are presented when the JLF is read.

• iv) Filed Level Entries (Optional): ‘JREF,’ ‘DYNSLT,’ ‘RENAME,’ ‘ALL’ etc.JREF: we can use this field-level keyword in join logical files for fields whose names are specified in more than one physical file. This keyword identifies which physical file contains the field. We can specify either the physical file name or its relative file number.Some other level entries are same as non-join logical file i.e., Key filed level entries, select omit level entries.

Example:

Here we have two Physical files PF01 and PF02 with the following data. . . . . .

PF01

Output:

PF02

Output

LF01 (To join PF01 and PF02)

Output (JOIN of PF01 and PF02)

DDS Keywords:

• REF – This is a file level keyword.
• REFFLD – This is a field level keyword.
• FORMAT – This is a record-level keyword.

The above DDS keywords allow us to refer to a field description in an existing file.

Utilizing these keywords eliminates the necessity to repeatedly specify the field and its description when used in another file.

A) Using REF(Reference) keyword in Physical File (PF)

REF keyword is a file-level keyword in DDS for physical files.

This REF keyword can be used to specify files from where the field descriptions are to be retrieved in the current DDS PF.

Syntax:

• REF keyword with Library name(optional)and Filename and Record format name(optional).REF(LibraryName/FileName RecordFormatName)
• REF keyword with file nameREF(FileName)

• If you do not specify library name, then at the time of compilation *LIBL library list is searched for the file.
• If you do not specify the record format name, then each record format is searched sequentially in the file to find the field description.

Example of using REF keyword in DDS physical file:

• Suppose EMPLOYEE file is a Reference file and DDS is as follows in which all fields are declared with field name, field length, data type.
  
• Let’s create the ACCOUNT file that refers to the field description from Reference file EMPLOYEE using the REF keyword.
  
• The above DDS code can also be written below.
  

So, all the fields in ACCOUNT have the same field attributes as defined in EMPLOYEE file after using the REF keyword.

B) Using REFFLD(Referenced Field) keyword in Physical File

The REFFLD keyword is a field-level keyword in DDS Physical files.

This REFFLD keyword can be used to refer to field descriptions either from one file or multiple files.

Syntax:

• REFFLD with only Referenced Field Name when referring to the same DDS file field.REFFLD(ReferenceFieldName)
• REFFLD with Record format(optional) name and Referenced Field Name when referring to the same DDS file field.REFFLD(RecordFormatName/ReferenceFieldName)
• REFFLD with Referenced Field Name and File Name when referring to the different DDS file field.REFFLD(ReferenceFieldName FileName)
• REFFLD with Referenced Field Name and Library Name(optional) and File Name when referring to the different DDS file field.REFFLD(ReferenceFieldName LibraryName/FileName)
• REFFLD with Record Format Name(optional) and Referenced Field Name and Library Name(optional) and File Name when referring to the different DDS file field.REFFLD(RecordFormatName/ReferenceFieldName LibraryName/FileName)

Example of using REFFLD keyword in DDS physical file:

• • Suppose EMPLOYEE File is a Reference file and DDS is as follows.
    
  • Let’s create a file ACCOUNT2 that refers to the field description from Reference file EMPLOYEE and from the same file ACCOUNT2 using the REFFLD keyword.
    
  • Here in the above example field ADDRESS1 is the field defined in ACCOUNT2 file itself.
  • Field ADDRESS2 is referred from field ADDRESS1 in the same DDS ACCOUNT2.
  • Field ADDRESS3 is again referred from the same field ADDRESS1 in the same DDS ACCOUNT2 only the record format name is used along with field name.
  • Field ACC_ID is referred from field EMP_ID in file EMPLOYEE.
  • Field ACC_NAME is referred from field EMP_NAME in file EMPLOYEE.
  • Field FIELD is referred from field ADDRESS1 in the same DDS file ACCOUNT2.

C) Format keyword in Physical File

This record-level keyword is 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.

FORMAT is required when you want to refer to an existing record format.

Syntax:

FORMAT([library-name/] database-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.

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.

Example:

• If you want to create a file with the same record format as another PF, you can use the FORMAT keyword below.
• Below is the DDS for ACCOUNT2 file (LF) having the same record format name ACCOUNTR as the file ACCOUNT.
  
• This means that the record format ACCOUNTR will have the same field names and attributes as the record format in the physical file ACCOUNT (mentioned in FORMAT keyword).
• You do not need to specify the field names and attributes in this LF.
• If necessary, you can Specify key specifications and 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.)
• Below is the DDS for ACCOUNT having the same record format name ACCOUNTR.
  
• Below is the DDS for ACCOUNT2 having the same record format name ACCOUNTR.
  

• Data definition language (DDL) is described in the form of SQL which creates, alters, and deletes database objects.
• DDL is created by using the option F6. To compile the DDL source RUNSQLSTM command is used
• RUNSQLSTM SRCFILE(*lib/File) SRCMBR(*member) COMMIT(*NONE)

Create a DDL table: By statement ‘Create or replace table *tablename*’ can create a table.

RUNSQLSTM: This is the CL command used to run the SQL statements. To create a table from the DDL source RUNSQLSTM is used. Commit is the commitment control that determines the changes in file outcomes. If *CHG, *ALL is for commit then there will be a record lock that has happened for ALTER, INSERT, DELETE, DROP, etc., on that record of the file. If *None is mentioned then there will be no lock on that file.

View: The table can be viewed in 2 ways.

• STRSQL – by using the command “SELECT * FROM Lib/file”
• By using RUNQRY “RUNQRY *N Lib/file”
• Using WRKQRY

To view the table in WRKQRY, choose option 5 concerning query (file name) and Lib. Before that, the table should be created using option 1, and also select the fields for it. Then copying query also be done using option 3.

Insert: Inserting a row in a table can be done in two ways.

• By using STRSQL – “insert into lib/file values(fld1_val, fld2_val,….)”
• Using UPDDTA library/file.

Update: Update a row can be done in two ways.

• By using STRSQL – “update lib/file set fld1= ‘value’, fld2=’value’… Where fld = ‘value’”
• By using UPDDTA library/file.

Indexes: An index is similar to LF and simply a set of pointers used to point the rows in a table. These are used to speed up the data access. Mainly for partitioning the data as we need. This table can be viewed by the RUNQRY command.

Converting DDS to DDL source:

• DDL is in text format that can be generated as SQL after ‘RUNSQLSTM’.
• SQL statement with the equivalent of DDS source is to be created by ‘Create table’ or ‘Replace table’.
• If DDS has existing data needs to do ‘CHGPF’ to duplicate those data.

Usage:

• DDL is more stabilized compared to the DDS table.
• DDL is capable of doing all types of enhancement as did in SQL.
• The length of the field name is DDL has no limit compared with DDS which has a maximum of length 10. DDS field names or in a short form are not able to be understood by all.
• DDL can store both the Field name and the System name. For example, the field name is ‘EMP_name’ and the system name is ‘Name’.

Restrictions:

• The altering of the structure of the table may lead to errors. If it depends on some other file(constraints/integrity).
• Multi-format files are difficult to handle.
• Select/Omit is not in the DDL source for table. Yet, can be used as where condition for VIEWS/INDEXES.
• There is no exact match for DATFMT/DATSEP.

Examples:

• Here creating a table for ticket booking timestamp data types is used. Timestamp gives output as both date and time.

  Primary Key is used to identify the particular unique key record in the file and it should not allow a null value.

  Record format(RCDFMT) is mandatory for all files and when it is not used system generates a record format(RCDFMT) as a system value which is used for the input/output of the file.
  The Rename keyword for the name of the system file name. If the rename keyword is not used then the same name is considered for the system name.
  The Label keyword is used to describe the file for understanding.

  

• Employee details with the comparison of DDL and DDS.
  

Label on Column:

The label on the command is used to display the field name in the output and for the references. To separate the field name should start after the 20th position. For example ’Employee name’ is represented below to separate ‘Employee’ and ‘Name’.

If we use the TEXT IS command for the own reference for the field.

Index: In DDL, index is used as part of LF from DDS source. The index is used to pinpoint the particular fields from the Table or PF. These tables are not able to be seen