Ü   Logical file

 

·         Logical file does not occupy any memory.

·         One or more logical file can be derived from a single physical file.

·         It simply contains the record selection definition and when accessed retrieves the records from the physical file to which it refers.

·         A logical file can contain up to 32 record formats.

·         It selects records dynamically. It cannot exist without a physical file.

·         We can filter the data with criteria by using select and omit command.

·          A logical file does not contain any data but provides the ‘VIEWS’ of the data to satisfy end-user’s needs.

 

Ü  Types of Logical file

 There are two types:

1)      Non-join logical file

2)      Join logical file

 

 

1)     Non-join logical file

Non-join logical files can either be a simple logical file, which contains only one record format or a multiple record format logical file, which contains more than one Record format.

Logical files can be derived from 1 to 32 physical files and so a logical file can have a maximum of 32 record formats.

 

ü  Single record format Non-join logical file:

If a logical file is derived from single physical file it is called simple logical file.

 

Columns . . . :    1  71           Browse                        AMINEM/DDSSRC  
SEU==>                                                               MULTIFRMT  
FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++         
       *************** Beginning of data *************************************
0002.00      A          R ACCOUNT                   PFILE(AMINEM/ACCOUNT)       
0003.00      A          K ACCOUNTNUM                                                
0005.00      A          K ACCURRENCY 

 

 

ü  Multiple record format Non-join logical file:

·         Multiple record non-join logical files will select records from 2 or more physical files by referring to only one logical file.

·         Each record format is always associated with one or more physical file.

·         The same PF can be used in more than one record format.

 

Columns . . . :    1  71           Browse                         AMINEM/DDSSRC
SEU==>                                                               MULTIFRMT
FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++
*************** Beginning of data *************************************
0002.00      A          R ACCOUNT                   PFILE(AMINEM/ACCOUNT)
0003.00      A          K ACCOUNTNUM
0005.00      A          K ACCURRENCY
0006.00      A          R TRANS                     PFILE(AMINEM/TRANS)
0007.00      A          K TACCOUNT
0008.00      A          K TCURRENCY
****************** End of data ****************************************

 

 


Ü Levels of entries in Logical file

 


Columns . . . :    1  71           Browse                      AMINEM/DDSSRC
SEU==>                                                                FILTER
FMT LF .....A..........T.Name++++++.Len++TDpB....Functions++++++++++++++++++
        *************** Beginning of data *************************************
0001.0       A                                 DYNSLT      >>>>>>>>>>>>>> File Level
0002.00      A          R LOG1                FILE(AMINEM/ACCOUNT) >>> Record Level
0005.00      A            ACCOUNTNUM
0006.00      A            ACCURRENCY                  >>>>>>>>>>>>>>>>>>> Field Level
0009.00      A          K ACCOUNTNUM
0010.00      A          K ACCURRENCY
0011.00      A          S ACCOUNTNUM          CMP(GT 100000000001)  >> Selection/
0017.00      A          O ACCOUNTNUM          CMP(GT 100000000003)  Omission Level
       ****************** End of data ******************************************

 

 

1. File-level entries (optional): (REFACCPTH, DYNSLT)

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

Format of the keyword is:

·         REFACCPTH (LIB name / DATABASE name)

·         DYNSLT: Use this file-level keyword to indicate that the selection and omission tests specified in the file (using select/omit specifications) are done at processing time. This keyword specifies dynamic select/omit rather than access path select/omit.

Only a Physical file contains data. Logical files just contain pointers for processing the data in a specific sequence and/or subset of data.
When a record is added/changed/deleted in a physical file, the system has to put a lock on it and then go out to check and update any logical files before it can release the lock. While the amount of time to do this is hardly measurable, it can be significant if massive batch updates are done to a file.           
If you are using the Select/Omit option in your DDS and using DYNSLT, the above process does not need to be done at record update time.
Where this is beneficial is when your selection process includes almost all the records in the file. Now when you process using DYNSLT, it will read all the records in the file, but do the selection of which records to process at that time rather than having done all the maintenance ahead of time.

 

Difference between access path and Dynamic select

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

 

2. Record – level entries (PFILE)

PFILE: The physical files containing the data to be accessed through the

 Record formats being defined.

Format of the keyword is:  PFILE (LIB name / PF name)

 

3. Field-level entries (optional)

4. Key field –level entries (optional)

5. Selection/Omission level entries (optional)

 


Ü  SELECT / OMIT Keyword

 

We use SELECT/OMIT keyword to filter the record of physical file according to our need.

 

Lets create one physical file SELOMIT.

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
 SEU==> SELOMIT
 FMT PF.
        *************** Beginning of data *******************
0001.00                 R RECSEL
0002.00                   EMPNO          5P 0
0003.00                   EMPNAME       20A
0004.00                 K EMPNO
        ****************** End of data ************************


Populate the file SELOMIT with data below:

       EMPNO   EMPNAME
 000001 1001   SHRI
 000002 1002   ABHI
 000003 1003   SHRI
 000004 1004   UPI
 000005 1005   ABHI
 000006 1006   SHRI
 000007 1007   ABHI
 000008 1008   UPI
 000009 1009   SHRI
 000010 1010   UPI
 ****** ******** End of report  ********
 


 

Example-1: Now create one logical file which refers the physical file SELOMIT. Put the SELECT/OMIT criteria as shown below to filter the record.

 

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
 SEU==> SELOMILF
 FMT LF...
        *************** Beginning of data *********************
0001.00                 R RECSEL                    PFILE (SELOMIT)
0002.00                 K EMPNO
0003.00                 S EMPNO                     CMP (GT 10003)
0004.00                 O EMPNAME                   CMP (EQ 'SHRI')
        ****************** End of data **************************


Output: SELECT & OMIT

        EMPNO   EMPNAME
 000001 1002   ABHI
 000002 1004   UPI
 000003 1005   ABHI
 000004 1006   SHRI
 000005 1007   ABHI
 000006 1008   UPI
 000007 1009   SHRI
 000008 1010   UPI
 ****** ******** End of report  ********

 

 

 

Example-2: Now clear data of the physical file SELOMIT and fill it with the data as shown below.

         EMPNO   EMPNAME
 000001     20   SHRI
 000002     30   RAM
 000003     40   JYOTI
 000004     50   AMI
 000005     60   SHRI
 000006     70   SHRI
 000007     80   JYOTI
 000008     90   JYOTI
 000009    100   SHAMNT
 ****** ******** End of report  ********
 

 

 

Lets apply multiple SELECT keyword in the Logical File as shown below.

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
 SEU==> SELOMILF1
 FMT LF 
        *************** Beginning of data ************************
0001.00                 R RECSEL                    PFILE (SELOMIT1)
0002.00                 K EMPNO
0003.00                 S EMPNO                     CMP (GT 50)
0004.00                 S EMPNAME                   VALUES ('SHRI')
        ****************** End of data **************************


Output: Multiple SELECT

         EMPNO   EMPNAME
 000001     20   SHRI
 000002     60   SHRI
 000003     70   SHRI
 000004     80   JYOTI
 000005     90   JYOTI
 000006    100   SHAMNT
 ****** ******** End of report  ********
 

 

 


2)     Join logical file

 

·         A join-logical file is a logical file that combines two or more PF. i.e. master + transactional record.

·         A PF cannot be changed through a JLF.

·         DFU cannot be used to display a JLF.

·         Only one record format can be specified in a JLF.

·         Commitment control cannot be used with a JLF.

·         Key fields must be fields defined in the join record format and must be fields from the PRIMARY FILE.

 

 

Entries in join logical file:

1. File-level entries (optional): (JDFTVAL)

2. Record-level entries: (JFILE)

3. join-level entries (JOIN, JFLD, JDUPSEQ)

4. field–level entries (optional): (JREF, ALL, CONCAT, DYNSLT, RENAME, SST, TRNTBL)

5. Key field –level entries (optional)

6. Select and Omit field level entries. (Optional)

      

Ü   JFILE: It is similar to indicate that this is a join logical field and it must have more than 2 physical files.

 

Ü   JOIN: It is similar that this file level entries to be represent  the position of the files .There must  one primary file and can have  more than I secondary files.

 

Ü   JFLD: Which feels we are going to join.

 

Ü   JREF: Represents the primary file reference field

 

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

The format for this keyword is:

JDUPSEQ (Sequencing field-name [*DESCEND])

                  

ü  This keyword has no effect on the ordering of records with unique keys.       

ü  If *DESCEND is not specified then the default is sequencing in ascending order.

 

Example:  JDUPSEQ (Sequencing Duplicate Objects)

 

Lets create a new phyical file SEQ1.


Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
SEU==> 							         SEQ1
FMT PF.
*************** Beginning of data ***********************
0001.00                 R SEQREC1
0002.00                   EMPNO          5P 0
0003.00                   EMPNAME1      20A
0004.00                   ADDRESS       20A
0005.00                 K EMPNO
****************** End of data **************************


Fill data in phyical file SEQ1.

         EMPNO   EMPNAME1              ADDRESS
 000001 10,001   BOB                   23,OLD MADIWALA
 000002 10,002   DANNY                 50,LONG ISLAND
 000003 10,003   PRINC                 90,ATTUR
 ****** ******** End of report  ********

 

 

Lets create another new phyical file SEQ2.

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
SEU==> 						              SEQ2
FMT PF
*************** Beginning of data****************
0001.00                 R SEQREC2
0002.00                   EMPNO          5P 0
0003.00                   EMPNAME       20A
0004.00                   TEL           10P 0


Fill the data in phyical file SEQ2.

   EMPNO   EMPNAME                         TEL
 000001 10,001   BOB                         825,777
 000002 10,001   BOB                         825,999
 000003 10,001   BOB                         825,888
 000004 10,002   DANNY                     4,222,600
 ****** ******** End of report  ********
 

 

 

Join SEQ1 and SEQ2.

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
SEU==> 						           JDFTSEQ
FMT LF
*************** Beginning of data ******************
0001.00                 R RECSEQ                    JFILE (SEQ1 SEQ2)
0002.00                 J                           JOIN (1 2)
0003.00                                             JFLD (EMPNAME1 EMPNAME)
0004.00                                             JDUPSEQ (TEL)
0005.00                   EMPNO                     JREF (2)
0006.00                   EMPNAME1
0007.00                   ADDRESS
0008.00                   TEL
****************** End of data************************


Ouput: Join of SEQ1 & SEQ2

        EMPNO   EMPNAME1              ADDRESS                         TEL
 000001 10,001   BOB                   23,OLD MADIWALA             825,777
 000002 10,001   BOB                   23,OLD MADIWALA             825,888
 000003 10,001   BOB                   23,OLD MADIWALA             825,999
 000004 10,002   DANNY                 50,LONG ISLAND            4,222,600
 ****** ******** End of report  ********
 


 


Ü  Join DESCEND

Below is how we use DESCEND keyword.

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
 SEU==>                                                       JDFTSEQ
 FMT LF 
        *************** Beginning of data ***********************
0001.00                 R RECSEQ                    JFILE (SEQ1 SEQ2)
0002.00                 J                           JOIN (1 2)
0003.00                                             JFLD (EMPNAME1 EMPNAME)
0004.00                                             JDUPSEQ (TEL *DESCEND)
0005.00                   EMPNO                     JREF (2)
0006.00                   EMPNAME1
0007.00                   ADDRESS
0008.00                   TEL
        ****************** End of data *************************


   EMPNO   EMPNAME1              ADDRESS                         TEL
 000001 10,001   BOB                   23,OLD MADIWALA             825,999
 000002 10,001   BOB                   23,OLD MADIWALA             825,888
 000003 10,001   BOB                   23,OLD MADIWALA             825,777
 000004 10,002   DANNY                 50,LONG ISLAND            4,222,600
 ****** ******** End of report  ********
 

 

 

 

Ü   JDFTVAL

·         When this file-level keyword is used the system provides default values for all for fields when a join to a secondary file does not produce any records.

·         If this keyword is not specified a record in the primary file for which there is no corresponding record in the secondary file is skipped.

 

 

 

 
Columns . . . :    1  71            Browse                     AMINEM/QDDSSRC  
SEU==>                                                                LOGICAL 
FMT 
*************** Beginning of data ************************************* 
0003.00      A*                                                                  
0004.00      A                                      DYNSLT                      
0005.00      A                                      JDFTVAL                     
0006.00      A                                                                  
0007.00      A          R LOG1                   JFILE(FILE1 FILE2)    
0008.00      A          J                        JOIN(FILE1 FILE2)     
0009.00      A                                   JFLD(LVLIDN1 LVLIDN2)   
0010.00      A                                   JFLD(ORGCOD1 ORGCOD2)   
0011.00      A                                   JFLD(ACNTNO1 ACNTNO2)   
0012.00      A                                   JFLD(RECSTS1 RECSTS2)   
0013.00      A            LVLIDN                 JREF(1)                     
0014.00      A            ORGCOD                 JREF(1)                     
0015.00      A            ACNTNO                 JREF(1)                     
0016.00      A            SUBCOD                 JREF(1)                     
0017.00      A            MKTCOD                 JREF(1)                     
0018.00      A            RECSTS                 JREF(1)                     
0019.00      A            ORGCODP                                            
0020.00      A            PRTYNO                                             
0021.00      A          K LVLIDN1                                             
0022.00      A          K ORGCOD1                                             
0023.00      A          K ACNTNO1                                             
0024.00      A          K SUBCOD1     

 

 


 

 

Ü  JOIN OPEREATION FOR MORE THAN 2 FILE 

Columns . . . :    1  71            Edit                           AMINEM/PRJ1 
 SEU==>                                                                  LOGIC1 
 FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++ 
        *************** Beginning of data ************************************* 
0001.00                                                                         
0002.00      A          R LOG1                      JFILE( MASTER PARTYD TRANSA 
0003.00      A          J                           JOIN( MASTER PARTYD)        
0004.00      A                                      JFLD( PARTY  PNUM)          
0005.00      A          J                           JOIN( MASTER TRANSACT)      
0006.00      A                                      JFLD( ACC ACCOUNT_NO)       
0007.00      A            ORG                                                   
0008.00      A            ACC                                                   
0009.00      A            CCY                                                   
0010.00      A            PNUM                                                  
0011.00      A            PNAME                                                 
0012.00      A            PADDR                                                 
0013.00      A            TRAN_NO                                               
0014.00      A            TRAN_TYPE                                             
0015.00      A            TRAN_AMT                                              
0016.00      A            TRAN_YEAR                                             
0017.00      A            TRAN_MONTH                                            
0018.00      A            TRAN_DAY                                              
0019.00      A          K ORG                                                   
0020.00      A          K ACC                                                   
0021.00      A          K CCY                                                   
        ****************** End of data **************************************** 

 

 

Ü  Inner join or natural join

 

Inner join means the matching records in between the joining file will be selected.

Lets create two physical files JPF1 and JPF2. We will join these 2 files.

Columns . . . 1 71            Edit                        AMINEM/EXAMPLE
SEU==>     				                               JPF01
FMT PF
*************** Beginning of data ****************************
0001.00                 R JP1REC
0002.00                   EMPNO          5S 0
0003.00                   EMPNAME       20A
0004.00                 K EMPNO
****************** End of data *****************************


        EMPNO   EMPNAME
 000001 1001   SHRI
 000002 1002   UPI
 000003 1003   AMI
 ****** ******** End of report  ********

 

Columns . . . 1 71               Edit                     AMINEM/EXAMPLE
SEU==>					                        JPF02
FMT PF.
*************** Beginning of data ***************************
0001.00                 R JP2REC
0002.00                   EMPNO          5S 0
0003.00                   EMPSAL        10P 2
0004.00                 K EMPNO
****************** End of data ******************************


        EMPNO        EMPSAL
000001 1001          100.00
000002 1002           20.00
000003 1004          300.00
****** ******** End of report  ********

 

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
SEU==> 							    JOFILE
FMT LF.
*************** Beginning of data ************************
0001.00                 R JREC1                     JFILE (JPF01 JPF02)
0002.00                 J                           JOIN (1 2)
0003.00                                             JFLD (EMPNO EMPNO)
0004.00                   EMPNO                     JREF (JPF01)
0005.00                   EMPNAME
0006.00                   EMPSAL
0007.00                 K EMPNO
****************** End of data***************************


Outout: Inner Join

         EMPNO   EMPNAME                        EMPSAL
 000001 1001   SHRI                           100.00
 000002 1002   UPI                             20.00
 ****** ******** End of report  ********

 


 

Ü  Left outer join

Left outer join all the records from primary file and matching records from the secondary file will be selected.

Columns . . . 1 71               Edit                     AMINEM/EXAMPLE
SEU==> 					                        JOOUT
FMT LF.
*************** Beginning of data*********************
0001.00                                             JDFTVAL
0002.00                 R JREC1                     JFILE (JPF01 JPF02)
0003.00                 J                           JOIN (1 2)
0004.00                                             JFLD (EMPNO EMPNO)
0005.00                   EMPNO                     JREF (JPF01)
0006.00                   EMPNAME
0007.00                   EMPSAL
0008.00                 K EMPNO
****************** End of data***********************


         EMPNO   EMPNAME                        EMPSAL
 000001 1001   SHRI                           100.00
 000002 1002   UPI                             20.00
 000003 1003   AMI                               .00
 ****** ******** End of report  *******************
 

 

 

Ü  Self-join

       A physical file can be joined to itself to read records that are formed by combining two or more records from the PF itself.

 

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
SEU==> 					                   SEJOIN
FMT PF
*************** Beginning of data ********************
0001.00                 R EMP
0002.00                   EMPID          5P 0
0003.00                   EMPNAME       20A
0004.00                   MGRID          5P 0
0005.00                 K EMPID
****************** End of data ***************************


         EMPID   EMPNAME                            MGRID
 000001 10,001   SEBI JOSEPH C.                     50,001
 000002 10,002   PURUSHOTTAM                        50,002
 000003 10,003   SAMEER DIGHE                       50,003
 000004 10,004   SHARATA                            50,004
 000005 10,005   PAUL                               50,005
 000006 50,001   SHIVARAM                           90,001
 000007 50,002   GAURAV                             90,002
 000008 50,003   KING                               90,003
 000009 50,004   SAM                                90,004
 000010 50,005   ANIL                               90,005
 ****** ******** End of report  ********
 

 

 

 

Columns . . . 1 71            Edit                     AMINEM/EXAMPLE
 SEU==> SELJOIN
 FMT LF 
        *************** Beginning of data **************************
0001.00                 R EMP                       JFILE (SEJOIN SEJOIN)
0002.00                 J                           JOIN (1 2)
0003.00                                             JFLD (MGRID EMPID)
0004.00                   EMPID               JREF (1)
0005.00                   EMPNAME             JREF (1)
0006.00                   MANAGER             RENAME (EMPNAME) JREF (2)
0007.00                                       COLHDG ('MANAGER')
        ****************** End of data *****************************


         EMPID   EMPNAME               MANAGER
 000001 10,001   SEBI JOSEPH C.        SHIVARAM
 000002 10,002   PURUSHOTTAM           GAURAV
 000003 10,003   SAMEER DIGHE          KING
 000004 10,004   SHARATA               SAM
 000005 10,005   PAUL                  ANIL
 ****** ******** End of report  ********

 

 


 

Ü  Difference between non-join logical files and join logical files 

 

Non join logical file

Join logical file

We can able to insert or delete or update records using non-logical file.

Insertion, updating or deletion of records is not possible in join logical files.

DFU can be used to display non-join logical file.

DFU is not available

1-32 record format is specified

Only one record format can be specified

Commitment control is used

Commitment control cannot be used.

 

 

Ü  Related Command 

 

       I.            Add Logical File Member (ADDLFM)

 

    II.            Change Logical File (CHGLF)

 

 III.            Create Duplicate Object (CRTDUPOBJ)

 

 IV.            Display Database Relation (DSPDBR)

 

 

 

Ü  Difference between physical file and logical file

 

Physical file

Logical file

1. Occupies the portion of memory. It’s containing data. 

Does not occupy any memory space. Does not contain any data.

2. A physical file contains one record format

 A logical file can contain up to 32 record formats.

3.Can be exist even without LF

Can’t exist without PF

4. The PF can’t be deleted, if dependent logical file exists in the system.

The LF can be deleted without deleting the PF.

5.CRTPF command is used to create such object

CRTLF command is used to create such type object

 

 











User Comments:



Copyright © www.go4as400.com, 2013-2023. Copyright notice   Terms of services   Privacy policy