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