Physical file in AS400

 

Physical file in AS400

Introduction

A physical file is a database file that contains data records. The records are then grouped into physical file members and each member has its own access path to the data. Ideally, the physical files have one member by default, which automatically gets added when the physical file gets created.

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.

Entries levels in Physical file

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

1. File Level
2. Record Format Level
3. Field Level
4. Key Field Level

Level of Entries in PF

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.

• CCSID

• It is used to provide the CCSID(coded character set identifier) for the character, graphic fields, etc.

• CHECK 

• It is used to provide validity checking.

• CHKMSGID

• It is used to specify a message to be displayed when a validity check error occurs.

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

• EDTCDE

• It is used to specify an edit code for the numeric fields. It does not affect the PF instead it is referenced later during the DSPF or PRTF creation. 

• EDTWRD

• It is used to specify an edit word. It does not affect the PF instead it is referenced later during the DSPF or PRTF creation. 

• REFFLD 

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

• REFSHIFT

• It is used to define the keyboard shift of a field when the field is referred to in DSPF (Display/Device file).

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

• NOALTSEQ 

• It is used to ignore any alternative collating sequence define t file level using ALTSEQ.

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

• ZONE 

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

Limits of a database file

Limits of a database file

The number of fields in a record format can be up to 8000 fields.

The number of key fields in a file can be up to 120 fields.

The maximum number of constraints per physical file can be up to 5000 constraints.

The maximum number of triggers per physical file can be up to 300 triggers.

The maximum number of members in a physical file can be up to 32767.

The size of a Zoned or Packed decimal field can be up to 63 digits.

The size of a character or DBCS field can be up to 32766 bytes.

Did you find the information listed in this article helpful? If you enjoyed this article, share it with your friends and colleagues!

Thanks!

Related Post

Example DDS PF with all the Data types in DDS AS400