Go4AS400-Image

The Web Ask Go4AS400
Share  
Homepage
See Sitemap
as400_FAQ
See Videos here
Contact me



Ü   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

        ****************** End of data ******************

                                                                               

   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.

 

 

 

Ü  JOIN OPEREATION FOR COMPOSITE KEY FIELD

 

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

 

 



Comments:

Copyright © Go4as400.com, 2014. Copyright notice