Tuesday, December 3, 2013

Day-5.

Join Logical File:

Overview:

This module enables the student to understand the concept of Join Logical File.

Outline:

Topics covered,

· Join Logical file creation

Types of Join Logical File

Keywords related to files

Objective:

By the end of this module, the student should be able to:

Create a Join Logical file

Understand the types of Join Logical files.

Keywords related to files.

JOIN LOGICAL FILES

Join logical files concatenate the fields from the records in two or more physical file and process them as one record. The advantages of join logical files include the following

Increased productivity: Because multiple READ operations area not required with join logical files the coding in RPG/400 programs is simplified.

Improved performance: Because a join logical file builds only one record the processing, program performance is improved. Only one READ (or CHAIN) operation has to be specified instead of multiple READ’s (or CHAINs) to open for nonjoin logical file processing. Furthermore, if a program has fewer open data paths, the job’s PAG (Process Access Group) size is reduced. This saves data storage and facilitates faster program loading.

More flexible database: As compared to nonjoin logical files, join logical files supported by IBM’s AS/400 computer are the following:

Features of Join Logical Files.

The features unique to join logical files supported by IBM’s AS/400 computer are the following:

Join logical files are READ only files and may not be used in update processing.

Join logical files support only inner and left outer joins. Outer join processing is not supported.

They may reference from 2 to 32 physical files. The physical file specified may be in key or arrival sequence. A common key (or Keys) is not required to link the files. In addition, because the same physical file may be specified as link the files. In addition, because the same physical file may be specified as the base file more than once, it may be joined to itself.

Any key field specified must be included in the primary file.

Select/ Omit criteria may be specified for any field in a join logical file.

Join Logical File Keywords. The steps in building a join logical file include the following.

Name all the physical files that will be accessed by the join logical file.

Specify the fields from each physical files to each other.

Define all the fields from each physical file that will be included in the join logical file’s record format.

The creation of a join logical file depends on knowledge of the seven keywords:

JFILE, JOIN, JFLD, JREE, JDUPDEQ, JDFTVAL, and DYNSLT. The function and syntax of each of these keywords are explained in the following paragraphs.

JFILE Keyword (Record Level): This record level (which requires the letter R in column17 of the Data Description statement) keyword is used to identify the physical files to be accessed in a join logical file. At least 2 physical files and no more than 32 may be specified in one JFILE keyword.

The general format of the LFIE keyword follows:

JFILE((library name/physical file name [..32])

The first file included in a JFILE keyword is called the primary file, and it is this file from which the join processing starts.

When a user formats a JFILE keyword, the physical file has the smallest number of data records should be specified first (as the primary file) The sequence in which the physical files are specified in the JFILE keyword can affect both performance and the results of join logical file processing.

JOIN Keyword (Join Level): The JOIN keyword is keyword is required in the ending for a join logical file to join two physical files for processing. If three physical files are accessed by the join logical file, two JOIN keywords must be included , and so forth. The general format of a JOIN keyword is as follows:

JOIN(from-file to-file)

The from-file and to-file entries may be the names or relative numbers of two physical files that were included in the JFILE keyword. In the first example that follows, relative numbers 1 and 2 are used in the alternative coding in the JOIN keyword. To join the third file to the master file, the second example uses 1 and 3.

JFILE(CUMAST CUSTRAN CUSPAID)

The JOIN keyword may be formatted as:

JOIN(CUSMAST CUSTRAN) –or-JOIN(1 2)

And

JOIN(CUSTMAST CUSPAID) –or-JOIN (1 3)

When duplicate physical file names are specified in a JFILE keyword, the JOIN keyword must use the relative number format. Definition of the JOIN keyword requires that the letter J be included in column 17 of the DDS statement.

JFLD Keyword (Join Level): A JFILE keyword identifies the from field and the to field that will join two physical files. The related fields must have the same attributes (type, size and decimal positions ) but they do not need to have the same name. Any from field size, and to field that does not have the same attributes may be redefined in the join logical file. Any fields specified in a JFLD keyword must have been defined in the related physical file. Consequently, join fields do not have to be defined in the join logical file. The general format of the JFLD keyword is

JFLD (from –field name to-field name)

Notice that two fields may be specified in a JFLD keyword. If the physical files are to be joined by other fields, then additional JFLD keywords must be defined.

JREF Keyword(Field Level):The JREF keywords is used when the physical files accessed by the join logical file have some or all of the same field names. JREF is used to identify the physical file in which the field is related. The general format of the JREF keyword is

JREF(file –name relative-file-number)

A file name or the relative positions of the file’s name in the JFILE keyword in the JFILE keyword may be included in the JREF statement. The related field name must be entered in the Name field (columns 19-28) of the DDS statement.

JDUPSEQ Keyword (Join Level): A JDUPSEQ keyword specifies the order in which the records from physical files that have duplicate join fields will be processed> The general format format the JDUPSEQ (sequencing file-name [DESCEND])

If DESCEND is included in the keyword, the duplicate records (same field value will be retrieved in a descending order instead of a ascending default order.

JDFTVAL Keyword (File Level): The JDFTVAL keyword enables primary file records that do not have matching secondary file records to be included in the join, Without the JDFTVAL keywords, any primary file record that did not have a matching secondary file record would be skipped. The general format of the JDFTVAL keyword is secondary file record would be skipped. The general format of the JDFTVAL keyword is secondary file record would be skipped. The general format of the JDFTVAL keyword is secondary file record would be skipped. The general format of the JDFTVAL keyword is

JDFTVAL(no parameter)

DYNSLT Keyword (File Level) : The DYNSLT keyword is required when the JDFTVAL keyword is specified in a join logical file. When specified, it causes record selection to occur when a record is read instead of after it is stored. The general format of the DYNSLT keyword is

DYNSLT(no parameter)

Types of Joins in Join Logical Files

SELF JOIN

INNER JOIN

OUTER JOIN

Join logical files combine different fields from more than one physical file into a single record. You must specify the JFILE keyword at the record level for join logical files.

Specify the entries in the following order to define a join logical file:

·         File-level entries (optional).

·         Record-level entries.

·         Join-level entries.

·         Field-level entries.

·         Key field-level entries (optional).

·         Select/omit-field level entries (optional).

·         Only one record format is allowed in a join logical file.

e.g .   We are using two Physical Files to create a Join Logical File



1. CORS

Columns . . . :    1 71            Edit                            PB01U01D/MYDDS

SEU==>                                                             CORS

FMT PF .....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++

        * Beginning of data *

0001.00      A          R RCORS

0002.00      A            CCD            3S 0       COLHDG('COURSE CODE')

0003.00      A            CNM            5A         COLHDG('COURSE NAME')

0004.00     A            FEES           5A         COLHDG('COURSE FEES')

0005.00      A          K CCD

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



















F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F10=Cursor   F11=Toggle

F16=Repeat find       F17=Repeat change          F24=More keys

                                          COPYRIGHT IBM CORP. 1981, 2002.

2. STUDENT

Columns . . . :    1 71            Edit                            PB01U01D/MYDDS

SEU==>                                                             STUDENT

FMT PF .....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++

        * Beginning of data *

0001.00                                             UNIQUE

0002.00      A          R RSTUD

0003.00      A            ROLL           3S 0       COLHDG('ROLL NUMBER')

0004.00      A            SNAME          5A         COLHDG('STUDENT NAME')

0005.00      A            CCD            3S 0       COLHDG('COURSE CODE')

0006.00      A            CORS           5A         COLHDG('COURSE NAME')

0007.00      A            ADD            5A         COLHDG('ADDRESS')

0008.00      A          K ROLL

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













F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F10=Cursor   F11=Toggle

F16=Repeat find       F17=Repeat change          F24=More keys

                                         (C) COPYRIGHT IBM CORP. 1981, 2002.

Join Logical File.

Keywords:

JFILE (Joined Files) keyword

Use this record-level keyword to identify the physical files containing the data to be accessed through the join logical file you are defining.

The format of the keyword is:

JFILE( [library-name/] physical-file-name [..32] )

JOIN (Join) keyword

Use this join-level keyword to identify which pair of files are joined by the join specification in which you specify this keyword.

The format of the keyword is:

JOIN( from-file to-file )

JFLD (Joined Fields) keyword

Use this join-level keyword to identify the from and to fields whose values are used to join physical files in a join logical file. These fields are both referred to as join fields.

The format of the keyword is:

JFLD( from-field-name to-field-name )

JREF (Join Reference) keyword—join logical files only

Use this field-level keyword in join logical files for fields whose names are specified in more than one physical file. This keyword identifies which physical file contains the field.

The format of the keyword is:

JREF( file-name | relative-file-number )

Example For Inner Joint

Columns . . . :    1 71            Edit                            PB01U01D/MYDDS

SEU==>                                                             CRSSTD

FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++

        * Beginning of data *

0001.00      A          R RINFO                     JFILE(CORS STUDENT)

0002.00      A          J                           JOIN(CORS STUDENT)

0003.00      A                                      JFLD(CCD CCD)

0004.00      A            CCD                       JREF(CORS)

0005.00      A            CNM

0006.00      A            FEES

0007.00      A            ROLL

0008.00      A            SNAME

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















F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F10=Cursor   F11=Toggle

F16=Repeat find       F17=Repeat change          F24=More keys

                                         (C) COPYRIGHT IBM CORP. 1981, 2002.

OUTPUT

                                 Display Report

                                              Report width . . . . . :      64

Position to line . . . . .              Shift to column . . . . . .

Line   ....+....1....+....2....+....3....+....4....+....5....+....6....

        COURSE CODE COURSE NAME COURSE FEES ROLL NUMBER STUDENT NAME

000001     100         comp         10000           1         aaa

000002     200         as400        30000           2         bbbbb

000003     300         java         10000           3         cccc

000004     400         vb           2000            4         dddd

End of report **







                                                                       Bottom

F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

JDFTVAL (Join Default Values) keyword (OUTER JOIN)

This keyword has no parameters.

Columns . . . :    1 71            Edit                            PB01U01D/MYDDS

SEU==>                                                             CRSSTD

FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++

        * Beginning of data *

0001.00      A                                      JDFTVAL

0002.00      A          R RINFO                     JFILE(CORS STUDENT1)

0003.00      A          J                           JOIN(CORS STUDENT1)

0004.00      A                                      JFLD(CCD CCD)

0005.00      A            CCD                       JREF(CORS)

0006.00      A            CNM

0007.00      A            FEES

0008.00      A            ROLL

0009.00      A            SNAME

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







F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F10=Cursor   F11=Toggle

F16=Repeat find       F17=Repeat change          F24=More keys

                                         (C) COPYRIGHT IBM CORP. 1981, 2002.

OUTPUT

                                 Display Report

                                              Report width . . . . . :      64

Position to line . . . . .              Shift to column . . . . . .

Line   ....+....1....+....2....+....3....+....4....+....5....+....6....

        COURSE CODE COURSE NAME COURSE FEES ROLL NUMBER STUDENT NAME

000001     100         comp         10000           1         aaa

000002     200         as400        30000           2         bbbbb

000003     300         java         10000           3         cccc

000004     400         vb           2000            4         dddd

000005     500         vc           5000            0

000006     600         QA           15000           0

000007     123         AS400        10000           0

End of report





                                                                       Bottom

F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

Example for Self Join

Columns . . . :    1 71            Edit                            PB01U01D/MYDDS

SEU==>                                                             CRSSTD

FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++

        * Beginning of data *

0001.00      A          R RINFO                      JFILE(STUDENT STUDENT)

0002.00      A          J                           JOIN(1 2)

0003.00      A                                      JFLD(CCD CCD)

0004.00      A            CCD                       JREF(1)

0005.00      A            ROLL                      JREF(1)

0006.00      A            SNAME                     JREF(2)

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





















F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F10=Cursor   F11=Toggle

F16=Repeat find       F17=Repeat change          F24=More keys

                                         (C) COPYRIGHT IBM CORP. 1981, 2002.

OUTPUT

                                 Display Report

                                              Report width . . . . . :      38

Position to line . . . . .              Shift to column . . . . . .

Line   ....+....1....+....2....+....3....+...

        COURSE CODE ROLL NUMBER STUDENT NAME

000001     100            1         aaa

000002     200            2         bbbbb

000003     300            3         cccc

000004     400            4         dddd

End of report **

DYNSLT (Dynamic Select) keyword

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 has no parameters.

Columns . . . :    1 71            Edit                            PB01U01D/MYDDS

SEU==>                                                             CRSSTD

FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++++++++++++++

        * Beginning of data *

0001.00      A                                      DYNSLT

0002.00      A          R RINFO                     JFILE (CORS STUDENT1)

0003.00      A          J                           JOIN (CORS STUDENT1)

0004.00      A                                      JFLD (CCD CCD)

0005.00      A                                      JDUPSEQ (ROLL)

0006.00      A            CCD                       JREF (CORS)

0007.00      A            CNM

0008.00      A            FEES

0009.00      A            ROLL

0010.00      A            SNAME

0011.00      A          O CCD                       COMP (LT 100)

0012.00      A          S ROLL                      RANGE (2 5)

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









F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F10=Cursor   F11=Toggle

F16=Repeat find       F17=Repeat change          F24=More keys

                                         (C) COPYRIGHT IBM CORP. 1981, 2002.

OUTPUT

                                 Display Report

                                              Report width . . . . . :      64

Position to line . . . . .              Shift to column . . . . . .

Line   ....+....1....+....2....+....3....+....4....+....5....+....6....

        COURSE CODE COURSE NAME COURSE FEES ROLL NUMBER STUDENT NAME

000001     200         as400        30000           2         bbbbb

000002     300         java         10000           3         cccc

000003     400         vb           2000            4        dddd

End of report **





                                                                       Bottom

F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

Assignment on above session:

Q1. Explain the function of the following join logical file keywords:

JFILE      JFLD JDUPSEQ

JOIN       JREF DYNSLT

Q2 What is the function of the JREF (1) keyword?

Q3. How would a JDFTVAL keyword control change the processing of the physical files?

Q4 What is the function of select/omit control for logical files?

Q5 Name the keywords supported with logical file select/omit control.

Q6 How is an OR relationship between two or more selection/omit statements specified? How is an AND relationship indicated?

Q7 Create two PF’s EMP and SAL.

EMP will have Employee Details.

Sal   will have Salary Details.

EMPSAL will be the common field between two PF’s.

Use JDUPSEQ Join level keyword for a field.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)