Prepared by: Malleswara Rao
DB2/400 (Database )
1.SIMPLE
PHYSICAL FILE
*************** Beginning of data
************
0001.00 A
R STUDET1
0002.00 A STUNO 5S 0
0003.00 A STUNAME 10A
0004.00 A STUDEPT 5A
0005.00 A
K STUNO
****************** End of data
***************
OUTPUT:
STUNO
STUNAME STUDEPT
000001
111 aaa cse
000002
222 bbb it
000003
333 ccc ece
000004
444 ddd eee
000005
555 eee mca
000006 1,248
mahesh mca
****** ******** End of report ********
2.PHYSICAL FILE WITH UNIQUE, COLHDG, DATFMT KEY WORD
*************** Beginning of data
******************************************
0001.00 A
UNIQUE
0002.00 A
R FORMAT
0003.00 A EMPNO 5S 0
0004.00 A EMPNAME 5A COLHDG('EMPNAME1')
0005.00 A EMPSAL 5S 0
0006.00 A EMPD L DATFMT(*MDY)
0007.00 A
K EMPD
****************** End of data
*********************************************
OUTPUT:
EMPNO
EMPNAME1 EMPSAL EMPD
000001
101 gopi 1
01/01/40
000002
102 naga 2
09/09/09
000003
2 moa 23
02/01/40
****** ******** End of report ********
LOGICAL FILE:
3. SINGLE RECORD FORMATS NON JOIN LOGICAL FILE:
*************** Beginning of data
***********************************
0002.00 A
R FRMT
PFILE(EXMPLE1)
0003.00 A ENO
0004.00 A ENAME
0005.00 A
K ENO
****************** End of data
**************************************
OUTPUT:
EMP NUMBER EMP NAME
000001 111
aaa
000002 222
bbb
000003 333
ccc
000004 444
ddd
000005 555
eee
000006 123
abc
LOGICAL FILE WITH SELECT / OMIT KEY WORDS:
*************** Beginning of data
*************************************************
0000.01
DYNSLT
0001.00 A
R STUDET
PFILE(STUDET)
0002.00 A
STUNO
0003.00 A STUNAME
0004.00 A
K STUNO
0005.00 A*
S STUNO
COMP(GT 300)
0006.00 A
O STUNO COMP (GT
400)
****************** End of data
****************************************************
OUTPUT:
STUNO
STUNAME
000001 111
AAA
000002 222
BBB
000003 333
CCC
****** ******** End of report
********
MULTIPLE RECORD FORMATS NON JOIN LOGICAL FILES:
*************** Beginning of data
**********************************
0001.00 A
R FORMAT
PFILE(EMP)
0002.00 A EMPNO
0003.00 A EMPNAME
0004.00 A EMPSAL
0005.00 A
K EMPNO
0006.00 A
R FORMAT1
PFILE(BOSS)
0007.00 A EMPNO
0008.00 A EMPNAME
0009.00 A EMPSAL
0010.00 A
K EMPSAL
****************** End of data
*************************************
OUTPUT:
EMPNO
EMPNAME EMPSAL
000001
101 gopi 1
000002
102 naga 2
000003
103 moss 3
000004
104 boss 4
000005
105 vishn 5
****** ******** End of report
********
JOIN LOGICAL FILES:
- Inner Join / Natural
Logical File
*************** 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(STUDENT)
0005.00 A
CNM
0006.00 A FEES
0007.00 A ROLL
0008.00 A SNAME
****************** End of data
********************************************
OUTPUT
CCD
CNM FEES ROLL
SNAME
000001 100
COMP 10000 1 AAA
000002 200
AS400 30000
2 BBB
000003 300
JAVA 10000
3 CCCC
000004 400
VB 20000
4 DDD
****** ******** End of report
********
- Left Outer Join Logical File
*************** Beginning of data
*******************************************
0000.01 A
JDFTVAL
0001.00 A
R RINFO
JFILE(STUDENT CORS)
0002.00 A
J
JOIN(STUDENT CORS)
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
**********************************************
OUTPUT
CCD CNM
FEES ROLL SNAME
000001 100
COMP 10000 1 AAA
000002 200
AS400 30000
2 BBB
000003 300
JAVA 10000 3 CCCC
000004 400
VB 20000 4 DDD
000005
0 5 EEEE
000006
0 6 FFF
****** ******** End of report
********
- Self Join Logical File:
PF:
*************** Beginning of data *****************************
0001.00 A R RCD
0002.00 A EMPNO 5S 0
0003.00 A EMPNAME 5A
0004.00 A EMPSAL 5S 0
****************** End of data ********************************
OUTPUT
EMPNO EMPNAME
EMPSAL
000001 101
AAA 111
000002 102
BBB 222
000003 103
CCC 333
000004 104
DDD 444
000005 105
EEE 555
000006 106
fff 106
000007 107
ggg 107
******
******** End of report ********
Self Join Logical File:
*************** Beginning of data
**********************************************
0001.00
A*
JDFTVAL
0002.00 A R JOINREC JFILE(PF48 PF48)
0003.00 A J JOIN(1 2)
0004.00 A
JFLD(EMPNO EMPSAL)
0005.00 A EMPNO JREF(1)
0006.00 A EMPSAL JREF(1)
****************** End of data
*************************************************
OUTPUT
EMPNO EMPSAL
000001 106 106
000002 107 107
****** ********
End of report ********
No comments:
Post a Comment