DDS Lab Exercises – Creation of Non join

Logical File, Multi Format Logical File

1. Create a LF EMPLF1 based on single PF - EMPMST and having all

the fields in EMPMST. Key is Employee is Unique.

Exercise instructions
_____1. In this exercise you are going to write a LF EMPLF1 in your
library that includes all the fields of EMPMST and define empid
as the key.

_____2. Create a DDS for the EMPLF1 with the following record formats
specifications . Define employee id as the key [Link]
UNIQUE keyword for getting employee records with unique id .

_____3. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 1

 2. Create a LF EMPLF2 based on EMPMST with the selected fields:
Employee code, Name, DOB, Dept, Key is Dept.

Exercise instructions
_____1. In this exercise you are going to write a LF EMPLF2 in your library that
includes the following fields of EMPMST – empcode, name, DOB,
dept define dept as the key.

_____2. Create a DDS for the EMPLF2 with the following record formats
specifications .

_____3. Define dept id as the key field.

Employee id
Employee name
Employee date of birth
Employee department

_____4. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 2

3. Create a LF EMPLF3 with the similar format of EMPLF2 and key is
Empcode and Name.

Exercise instructions
_____1. In this exercise you are going to write a LF EMPLF3 in your
library that has same format of EMPLF2 using FORMAT
keyword - define empcode and name as the key.

_____2. Create a DDS for the EMPLF3 with similar format of EMPLF2.

_____3. Define emp code and name as the key field.

_____4. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 3

4. Create a LF EMPLF4 with similar format of EMPLF2 and have the keys
are in EMPLF3.

Exercise instructions
_____1. In this exercise you are going to write a LF EMPLF4 in your
library with FORMAT keyword and REFACCPTH keyword.

_____2. Create a DDS for the EMPLF4 with the following record formats
specifications with similar format of EMPLF2 .

_____3. Define keys that are in EMPLF3.

_____4. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 4

5. Create a LF EMPLF5 based on EMPMST and EMPMST1 with all the
fields in both fields, key is Employee code.

Exercise instructions
_____1. In this exercise you are going to write a LF EMPLF5 in your library
based on EMPMST & EMPMST1 with all fields in both fields & have
keys is employee code.

_____2. Create a DDS for the EMPLF5 with the following record formats
specifications of EMPMST & EMPMST1 .

_____3. Define emp code as the key field.

_____4. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 5

6. Create a LF EMPLF6 based on EMPMST and EMPMST1 with similar
format of EMPLF2, key are as similar as EMPLF3.

Exercise instructions
_____1. In this exercise you are going to write a LF EMPLF6 in your library
based on EMPMST & EMPMST1 with FORMAT keyword.

_____2. Create a DDS for the EMPLF6 with the following record formats
specifications with similar format of EMPLF2.

_____3. Define key similar as EMPLF3.

_____4. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 6

7. Create a multiple format logical file EMPLF7 First format based on
EMPMST key is Employee code and Second format based on CUSMST
key is customer code.

Exercise instructions

_____1. Create CUSMST file in your library with the following fields.

_____2. In this exercise you are going to write a multi format LF EMPLF7 in
your library based on EMPMST with empcode as key and CUSMST
with customer code as key.

_____3. Create a DDS for the EMPLF7 with the following record formats
specifications .

_____4. Define emp code and customer code as the key field in appropriate
places.

Field name Length Data type

EMPCDE 5 Zoned

EMPNAM 30 Alphanumeric

EMPDOB L Date

EMPDEP 2 Alphanumeric

EMPSAL 9,0 Packed

EMPAD1 25 Alphanumeric

CUSID 5 Zoned

CUSNM 30 Alphanumeric

CUSAD 25 Alphanumeric

CUSPHN 14 Zoned

CUSDOB L Date

CUCSCM 10 Zoned

_____5. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 7

 Exercise – Non-join – File level keywords ,
record level keywords,Field level
Keywords, Key Field Level Keywords,
Select, Omit Keywords
1. Create a LF with file level keywords UNIQUE,FIFO,LIFO,FCFO.

Exercise instructions
_____1. In this exercise you are going to include file level keywords
UNIQUE,FIFO,LIFO,FCFO

FIFO – See whether records are arranged in first in first out order when duplicate
key values are given.

LIFO – See whether records are arranged in last in first out order when duplicate
key values are given.

FCFO – See whether records are arranged in first changed first out order when
duplicate key values are given.

_____2. Create a DDS for the file EMPLF41 with file level keywords UNIQUE
FIFO,LIFO,FCFO in 4 different logical files same as you code for
physical file EMPMST.

_____3. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 8

 2. Create a logical file EMPLF10 with key field employee id and include
the key field keywords ABSVAL, DESCEND

Exercise instructions
_____1. In this exercise you are going to include key field keywords ABSVAL,
DESCEND.

_____2. Create a DDS for the file EMPLF10 which is based on EMPMST3 with
the following record formats specifications . In EMPMST3, define
employee id as the key field.

_____3. In EMPLF10,give descend for empid key field. When you give
DESCEND keyword check whether employee records are listed in
descending order.

_____4. Give ABSVAL for the empid field in logical file. When you give
ABSVAL, check whether absolute value is [Link] descend and
absval and see whether records are arranged with absval and
descending order.

_____5. Compile EMPLF10. Check for no compilation errors.

© PMS Information Systems 2008 9

 3. Create a PF EMPLF11 with field level keywords ALIAS, ALWNULL,
CHECK(MF), CHECK(ME), COLHDG, EDTCDE, EDTWRD, RANGE,
VALUES,DFT,DATFMT,DATSEP,TIMFMT,TIMSEP

Exercise instructions
_____1. In this exercise you are going to include field level keywords ALIAS,
ALWNULL, CHECK(AB),CHECK(MF) , CHECK(ME), COLHDG,
EDTCDE, EDTWRD, RANGE, REFFLD,COMP,RANGE, VALUES,
VARLEN,DATFMT,DATSEP,TIMFMT,TIMSEP .

_____2. Create a DDS for the file LF EMPLF11 based on EMPMST4 with the
following record formats specifications . Specify the following
keywords.

_____3. Give ALIAS name ie alternate name to field EMPNAM. Set ALWNULL
for AMOUNT [Link] range of values ( 2000 5000) to EMPSAL
and select EMPSAL for range of [Link] values keyword for
DEPID field as ’IT’,’PR’,’SA’,’TR’.

_____4. When you give ALWNULL,allows null value in the field.

_____5. When you give COLHDG ,specfies column heading for fields.

_____6. Give DFT values for DEPID as ‘IT’ & EMPSAL fields as 1234.

_____7. Give DATFMT as *DMY and DATSEP as ‘/’ and TIMFMT as *HMS and
TIMSEP as ‘:’.

_____8. Check(AB) – Allow blanks.

_____9. Check for salary field for Check ME – Field must be entered
mandatorily ,CHECK MF – Mandatory fill of that value.

_____10. Give EDTCDE for transaction date field (or) DOB [Link] the
EDTCDE keyword ‘ Y’ for date edit for DOB field .

_____11. Give EDTWRD for amount field (‘ - - ‘). Give edtwrd for phone
number field (Country_code-Area_code-Phone_number).

_____12. Give CHKMSGID for VALUES & RANGE keyword and see whether
the error message is coming.

_____13. When you give REFFLD for a particular field ,refers the field from field
reference file and source of the existing file.

© PMS Information Systems 2008 10

_____14. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 11

 4. Create a EMPLF12 with field level keywords SST,CONCAT, RENAME
.

Exercise instructions
_____1. In this exercise you are going to include field level keywords SST,
CONCAT, ENAME in EMPLF12.

_____2. Create a DDS for the file EMPLF12 with the following record formats
specifications . Specify the following keywords.

_____3. Include a field Charge in EMPMST4/EMPMST3 and Rename

CHARGE to PRICE in EMPLF12.

_____4. Concatenate EMPYR EMPMO EMPDA to SEQDAT field.

Concatenates 2 or more physical file fields into a single field in logical
file.

_____5. Substring address SST(ADDRESS 1 18) and move it to [Link]

is the field in EMPMST3/EMPMST4. Defines a field in a logical file that
is a subset of character or zoned in physical file.

_____6. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 12

 5. Create a LF EMPLF13 with Select/Omit Keywords.

Exercise instructions
_____1. In this exercise you are going to include select/omit keywords in the
logical file.

_____2. Create a DDS for the file EMPLF13 with the following record formats
specifications . Specify the following keywords.

_____3. Create fields EMPSTS,EMPABS,EMPGRD,EMPDUR,EMPPRC in

EMPMST3/EMPMST4 ie status,absolute,grade,duration,percentage
values.

_____4. Select EMPABS comp GT 0 & EMPGRD values(‘UNS’ , ‘INC’).

_____5. Select EMPDUR within RANGE(1 4) OR EMPPRC comp(LT 300).

_____6. Omit EMPSTS values(‘P’ ‘C’).

_____7. Compile [Link] for no compilation errors.

© PMS Information Systems 2008 13

 Exercise –Join Logical File , Inner join,
Outer join, Self join,Joining 3 files,Joining 2
files when join field not there in join logical
File

1. Create a JLF EMPLF14 – join logical file

1) Create EMPMST7 physical file with following fields (key fields –

empid,depid,mgrid,empsal)
2) Create DEPTMST7 physical file with following fields (key fields
– depid ,mgrid)
3) Create SALARY physical file with following fields (key field –
empsal)
EMPMST7
Field name Length Data type

EMPID 5 zero decimal Zoned

EMPNAM 30 Alphanumeric

EMPDOB D format Date

DEPID 6 zero decimal Packed

DEPNM 12 Alphanumeric

EMPSAL 5 zero decimal Zoned

MGRID 5 zero decimal Zoned

EMPAD1 25 Alphanumeric

EMPAD2 25 Alphanumeric

EMPAD3 25 Alphanumeric

EMPHN 14 zero decimal Zoned

EMTRDT D format Date

© PMS Information Systems 2008 14

EMTRTM T format Zoned

EMTUSR 10 Alphanumeric

DEPTMST7
Field name Length Data type

DEPID 6 zero decimal Packed

DEPNM 12 Alphanumeric

MGRID 5 zero decimal Zoned

MGRNM 12 Alphanumeric

SALARY
Field name Length Data type

EMPSAL 5 zero decimal Zoned

EBASIC 5 zero decimal Zoned

EHRA 5 zero decimal Zoned

EDA 5 zero decimal Zoned

Exercise instructions
_____1. In this exercise you are going to create join logical file EMPLF14 of
department and employee table.

_____2. Create a DDS for the join logical file with the following record formats
specifications . Include JFILE,JOIN,JFLD,JREF keywords.

_____3. Join file(JFILE) department and employee file and join(JOIN)

department and employee with join field(JFLD) department [Link]
JREF with department table.

_____4. Compile join logical file [Link] for no compilation errors.

© PMS Information Systems 2008 15

2. Create a JLF EMPLF15 – inner join

Exercise instructions
_____1. In this exercise you are going to create join logical file EMPLF15 for
inner join of department and employee file. Join field is department id.

_____2. Create a DDS for the join logical file with the following record formats
specifications . Include JFILE,JOIN,JFLD,JREF keywords.

_____3. For inner join, join dept & emp file with depid as key – only rows
which are matched are joined .Use DYNSLT keyword.

_____4. Compile join logical file [Link] for no compilation errors.

© PMS Information Systems 2008 16

3. Create a JLF – EMPLF16 -left outer join

Exercise instructions
_____1. In this exercise you are going to create join logical file EMPLF16 for
left outer join of DEPARTMENT and EMPLOYEE file. Join field is
department id.

_____2. Create a DDS for the join logical file with the following record formats
specifications . Include JDFTVAL [Link] the following
keywords.

_____3. Include JFILE,JOIN,JFLD,JREF keywords.

_____4. For left outer join of dept & emp table – contains all rows from
department table ie left table .Use JDFTVAL keyword.

_____5. JDFTVAL – Indicates that the system will provide default values
for fields when a join operation to the secondary file produces no
records.

_____6. Include JDFTVAL for natural join – missing secondaries when records
are there in DEPARTMENT file and no corresponding record in
EMPLOYEE file,JDUPSEQ keywords – more than 1 match in
secondary file ie in employee file. Specifies the field use to arrange
duplicate records on a join. Specify the following keywords.

_____7. Compile join logical file [Link] for no compilation errors.

© PMS Information Systems 2008 17

4. Create a JLF – EMPLF17 Self join.

Exercise instructions
_____1. In this exercise you are going to create join logical file EMPLF17 for
self join. For self join, join field is manager id which is the employee id
in the employee file.

_____2. Create a DDS for the join logical file with the following record formats
specifications .Specify the following keywords.

_____3. Include JFILE,JOIN,JFLD,JREF keywords.

_____4. For SELF join ,join employee table to employee table with key
manager id and employee id.

_____5. Compile join logical file [Link] for no compilation errors.

© PMS Information Systems 2008 18

5. Joining 3 files – join 3 files department,employee and salary.

Exercise instructions
_____1. In this exercise you are going to create join logical file EMPLF18 for
joining 3 files – department,employee,salary.

_____2. Create a DDS for the join logical file with the following record formats
specifications .Specify the following keywords.

_____3. Include JFILE,JOIN,JFLD,JREF keywords.

_____4. For joining 3 files joining field is depid,mgrid,empsal

_____5. Compile join logical file [Link] for no compilation errors.

© PMS Information Systems 2008 19

6. Joining 2 files with Join field not in the logical file

Exercise instructions
_____1. In this exercise you are going to create join logical file EMPLF19 for
joining 2 files department ,employee with join field not in logical file.

_____2. Create a DDS for the join logical file with the following record formats
specifications .Specify the following keywords.

_____3. Include JFILE,JOIN,JFLD,JREF keywords.

_____4. For joining 2 files joining field is not there in the logical
[Link] a ‘N’ in 38th column ie in usage of field is neither
input/output.

_____5. Compile join logical file [Link] for no compilation errors.

© PMS Information Systems 2008 20