Physical and Logical Files

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)