Physical file in AS400

• A physical file is a database file that contains data records.
• A physical file is an AS/400 object with the object type *FILE and the attribute PF.
• A physical file is composed of three components, namely,
  1. Record Format
  2. Access Path
  3. Data Member

Physical file has default no. of members as 1 and the maximum no. of records as 10000 and an extended capacity of 1000 incremented thrice (that is, 13000 records).

Entries levels in Physical file

There are four levels of entries that the physical file contains in a specific order as described below:

• File Level
• Record Format Level
• Field Level
• Key Field Level

File Level entries (Optional)

These entries provide system information of the file.

The keyword used at the file level are as follows:

UNIQUE

It is used to specify that duplicate records cannot be inserted into the physical file.

FIFO

It is used to specify that records with duplicate key values will be retrieved in FIFO order (First In First Out).

LIFO

It is used to specify that records with duplicate key values will be retrieved in LIFO order (Last In First Out).

FCFO

It is used to specify that records with the key-value which is changed first will be retrieved first.

REF

• It is used to specify the file from which the field description is retrieved.
• You can specify the REF keyword only once.
• If all the fields are referred from the same file you can use the REF keyword and if they refer from multiple files then use the REFFLD keyword with each field which is a FIELD level entry.

REF(LibName/FileName)

Record Format Level Entries

Record format name is specified along with the optional text description.

The keyword used at the record format level are as follows:

FORMAT

• It is used to share the field description with an existing record format.
• If this keyword is used then we need to again define the key field level specification even if they are already defined on the existing record format being shared.

FORMAT(LibName/FileName)

• The library name is optional in the FORMAT keyword.

TEXT

• It is used to provide a text description of the record format of the file.

TEXT('description')

Field Level Entries

Field name, length, and data type along with option Text description and Column heading are specified for each field.

Keywords used at the field level are as follows.

ALIAS

It is used to provide an alternative name for a field.

ALWNULL

It is used to allow the NULL value in the field.

CMP/COMP

It is used to provide a comparison value.

COLHDG

It is used to provide column headings for the fields.

DATFMT

It is used to specify the format of the DATE fields.

DATESEP

It is used to specify the separator used in the formatted DATE field. DFT It is used to provide a default value for the field.

REFFLD

It is used to copy the field description from the referenced field.

TEXT

It is used to provide a description of the field.

TIMFMT

It is used to specify the format of the TIME field.

TIMSEP

It is used to specify the separator used in the formatted TIME field.

VALUES

It is used to provide a list of valid values.

VARLEN

It is used to define a field as a variable-length field.

Key Field Level Entries

The field names used as a key field are specified at this level in PF.

Keywords used at this level are as follows:

ABSVAL

It is used to arrange the record using the absolute value of the key field value.

DESCEND

It is used to arrange the records from the highest to the lowest key field value.

DIGIT

It is used to arrange the records using only the digit portion of the key field value.

SIGNED

It is used to arrange the records using the sign portion of the key field value.

UNSIGNED

It is used to arrange the records without using the sign portion of the key field value.

Create command

To create a physical file we use the CL command CRTPF and use the DDS (Data Description Specifications) contained in a source file member.

You need to specify PF name and Library where you want to create it, then you need to provide SRCFILE (source file name and library) and SRCMBR (source member used to create the PF object)

A physical file can have only one record format.

DDS PF with all the Data types in DDS AS400

In DDS supported physical file we do specify the data type of the field on fixed position 35.

Valid Data Types Entries

Points to Remember

• For Character data type (A) decimal positions 36 and 37 would be blank.
• For Packed decimal(P) and Zoned Decimal(S) data type (P) decimal positions 36 and 37 would contain value from 0 up to 63 max.
• For Numeric field data type such as packed, zoned and binary positions 37 would contain value 0 to tell its a numeric field.
• For Binary(B) data type maximum length allowed is 18.
• For Float(F) data type maximum length allowed is 9 for single precision and FTPCN(*SINGLE) keyword used.
• For Float(F) data type maximum length allowed is 17 for double precision and FTPCN(*DOUBLE) keyword used.

Example DDS source definitions for basic DDS data types with its maximum allowed limits

Field Reference file in AS400

• A Field Reference file or simply a Reference file in AS400 is a physical file that does not contain any data and keeps only field descriptions i.e its data type, length, and CCSID information, etc.

• DDS keywords REF and REFFLD allow us to refer to a field description in an exiting file.

Using REFFLD(Referenced Field) keyword in Physical File

REFFLD keyword is a field-level keyword in DDS Physical files and this can be used to refer to field descriptions either from one file or multiple files.

Physical File DDS Example

Logical File

• The Logical file (LF) is a database file object that does not contain actual data.

• It denotes how the data is actually retrieved to the RPG or CL program.

• It is simply an access path to the data over the physical file.

• Any logical file (LF) cannot exist without the physical file (PF) existence i.e. Logical files (LF) are dependent files of the physical files (PF).

  • A Logical file (LF) with Keys are Access path (INDEX) over the physical file (PF)
  • A Logical file (LF) with no key are VIEWS over the physical file (PF).

A logical file (LF) can have a maximum of up to 32 record formats defined in it and has a permanent object on the IBM i system where we can specify filter criteria as well using SELECT or OMIT criteria.

If there is/are Logical files (LF) present for a physical file (PF) then we cannot delete Physical file (PF) until and unless we delete all the dependent Logical files (LF) over that physical file (PF). But Logical file (LF) can be deleted without deleting the Physical file (PF) first.

Types of Logical Files

There are 2 types of Logical files (LF) that exist. Please follow the below link to read about them.

• Non-Join Logical File
  • Single Record Format Logical File
  • Multiple Record Format Logical File
• Join Logical File (LF)

Non-Join Logical file in AS400

Non-Join Logical files are created on single or multiple record formats from the same or multiple physical files. So basically, It's a simple logical file.

Join Logical file in AS400

Join two physical files to create Join logical file in AS400

Use JDUPSEQ keyword to arrange duplicate records in secondary file in join logical file in AS400

Join fields with different attributes to create a Join logical file in AS400

Specify the key fields in the Join Logical File in AS400

Multimember physical file

In case if we used the default provided value as 1 for parameter MAXMBRS on the CRTPF command still we can change the MAXMBRS value using CHGPF(Change physical file) command.

Ways to insert records in multiple members of a multimember physical file in AS400

Ways to display data of multiple members of multimember physical file in AS400

Copy, Clear and Remove member in multimember physical file in AS400

Flat File in AS400

Reading Flat File in RPGLE

Writing in flat file in RPGLE

Chain and Update in Flat file in RPGLE

Chain and Delete in Flat File in RPGLE

Difference b/w PF and LF

Access Path and Types of Access Paths in AS400

What is LEVEL CHECK in AS400

Whenever PF is compiled, the system generates unique code for identifying the file for future reference.

When we compile the Program that uses the PF, will use that unique code of the PF.

If we now call the program, then its run successfully. But if we change the PF and recompile the PF, the system regenerates a new unique code for that PF. So, our program Don't have this unique code and hence terminates abnormally with a Level Check error.

The solution of Level Check error is whenever it happens, we have to either compile the PF with Level Check parameter value *NO or we have to compile the program again.