Ü   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

 

 


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 ************************

       

  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  ********

 

 

 

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 **************************

 

        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  ********

 

 

 


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 ************************

 

         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  ********

 

 

 

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 **************************

 

         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)

 


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 **************************

 

         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  ********

 

 

 

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

                                                                               

   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  ********

 

 

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************************

 

        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

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.

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 ********************************

                                 Display Report

        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*********************

     

                                       

         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. If you delete a LF, the PF can’t be deleted

If you delete a LF, the PF can’t be deleted

5.CRTPF command is used to create such object

CRTLF command is used to create such type object

 

 





User Comments:





Copyright © Go4as400.com, 2013-2017. Copyright notice