JCL-COBOL-VSAM-DB2: 2013

Tuesday, 23 July 2013

locks

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.perf%2Fsrc%2Ftpc%2Fdb2z_lockmode.htm

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007870.htm

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Ft0004984.htm

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_rules4specialregisters.htm

DB2' or 'STD'.
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_setcurrentrules.htm

DB2 trace

Provides DB2 accounting, statistics, auditing, and performance trace information.

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0020153.html

DELETE NO ACTION

Specifies that the row in the parent table can be deleted if no other row depends on it. If a dependent row exists in the relationship, the DELETE fails. The check for dependent rows is performed at the end of the statement.

DELETE RESTRICT

For example, you cannot delete a department from the department table if it is still responsible for some project that is described by a dependent row in the project table.

DELETE CASCADE

Specifies that first the designated rows in the parent table are deleted. Then, the dependent rows are deleted.

For example, you can delete a department by deleting its row in the department table. Deleting the row from the department table also deletes:

The rows for all departments that report to it
All departments that report to those departments and so forth.

DELETE SET NULL

Specifies that each nullable column of the foreign key in each dependent row is set to its default value. This means that the column is only set to its default value if it is a member of a foreign key that references the row being deleted. Only the dependent rows that are immediate descendents are affected.

DELETE SET DEFAULT

Specifies that each column of the foreign key in each dependent row is set to its default value. This means that the column is only set to its default value if it is a member of a foreign key that references the row being deleted. Only the dependent rows that are immediate descendants are affected.

For example, you can delete an employee from the employee table (EMPLOYEE) even if the employee manages some department. In that case, the value of MGRNO for each employee who reported to the manager is set to blanks in the department table (DEPARTMENT). If some other default value was specified on the create of the table, that value is used.

This is due to the REPORTS_TO_EXISTS constraint defined for the department table.

If a descendent table has a delete rule of RESTRICT or NO ACTION and a row is found such that a descendant row cannot be deleted, the entire DELETE fails.

SYSADM

-Installation of RDBMS
-Creation of system level objects(databases)
-grants other authorized DB2 users the appropriate privileges
-system performance

DBADM

-Creating database objects(Tables,indexes,views,triggers)
-Grant privileges ( INSERT,SELECT,UPDATE,DELETE,LOAD,BIND)
-Binding : Pakages, plans
-Maintenance of database objects(Loading data gathering statistics making backups, recovering data)

programmar

-Creating test environment
-Coding programs,stored procedures,SQL
-prototyping SQL "explaining SQL"
-precompile & bind
-physically embed SQL statements into host programs.

End-user
-run business applications
-change DB2 data
-Write SQL queries
-Produce reports

Operator

-Load
-Backup
-production application
-reorg

First Normal from - Eliminate repeating groups
Second Normal form- Eliminate all attributes(columns) that depend only on part of a multi-valued primary key
third Normal form - Eliminate columns not dependent on the key at all

Date:4bytes
time:3bytes

5. The event that occurs when a transaction reads data that has not yet been committed is called as, Dirty reads.
ii. The Event that occurs when a row of data matchs some search criteria but in the initial read is not seen is called as Phantom

2. Phantoms phenomena occurs in, Cursor Stability, Read Stability, Repeatable Read, Uncommited Read

Wednesday, 3 July 2013

Import - VSAM

//TRNG111A JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
IMPORT INDATASET(TRNG111.TEST.EXPRT) -
OUTDATASET(TRNG111.TEST.KSDSTE)

Export - VSAM

//TRNG111A JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IEFBR14
//DD1 DD DSN=TRNG111.TEST.EXPRT,DISP=(NEW,CATLG),
// SPACE=(TRK,(5,0)),DCB=(LRECL=80,BLKSIZE=8000,RECFM=FB)
//STEP2 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
EXPORT 'TRNG111.TEST.KSDSTE' OUTDATASET(TRNG111.TEST.EXPRT) -
CIMODE PERMANENT
/*

Build Path

//TRNG111P JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEFINE PATH(NAME(TRNG111.JCLVSAM.PATH) -
PATHENTRY(TRNG111.JCLVSAM.KSDSAIX) NOUPDATE)
/*

Build Index

Build Index

//TRNG111B JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//* JCL TO BUILD ALTERNATE INDEX RECORDS
//SYSIN DD *
BLDINDEX IDS(TRNG111.JCLVSAM.KSDS1) -
ODS(TRNG111.JCLVSAM.KSDSAIX) EXTERNALSORT

Data in TRNG111.JCLVSAM.KSDS1 (Seq DS)

c2000ramesh15-10-2000che25000
c3100ganesh25-01-2001bng10000
c3500satish12-02-2000hyd15000
c4000ganesh10-12-2001che30000
c5000ramesh23-06-2008del30000

Alternate Index For Build Index

//TRNG111A JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEFINE ALTERNATEINDEX(NAME(TRNG111.JCLVSAM.KSDSAIX) NOERASE -
TRACKS(5,0) -
RELATE(TRNG111.JCLVSAM.KSDS1) NONUNIQUEKEY -
UPGRADE KEYS(6,5) -
CONTROLINTERVALSIZE(1024) RECORDSIZE(100,100))
/*

Alternate Index

Alternate Index

//TRNG111A JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEFINE ALTERNATEINDEX(NAME(TRNG111.JCLVSAM.KSDSAIX) NOERASE -
TRACKS(5,0) -
RELATE(TRNG111.JCLVSAM.KSDS1) UNIQUEKEY -
UPGRADE KEYS(6,5) -
CONTROLINTERVALSIZE(1024) RECORDSIZE(100,100))
/*

KSDS - REPRO Using IDCAMS to COPY A DATASET INTO NEW DATASET

KSDS - REPRO Using IDCAMS to COPY A DATASET INTO NEW DATASET

//TRNG111A JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
REPRO IDS(TRNG111.JCLVSAM.DATA1) ODS(TRNG111.JCLVSAM.KSDS1)

Data in TRNG111.JCLVSAM.DATA1 (Seq DS)

c2000ramesh15-10-2000che25000
c3100ganesh25-01-2001bng10000
c3500satish12-02-2000hyd15000
c4000ganesh10-12-2001che30000
c5000ramesh23-06-2008del30000

Tuesday, 2 July 2013

ESDS- REPRO to COPY A DATASET INTO NEW DATASET

//TRNG111A JOB NOTIFY=TRNG111
//STEP1 EXEC PGM=IEFBR14
//*JCL PGM TO COPY A DS INTO NEW DATASET
//AAA DD DSN=TRNG111.TEST.ESDS5,DISP=(NEW,CATLG),SPACE=(TRK,(5,3)),
// RECORG=ES,LRECL=100
//STEP2 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
REPRO INDATASET(TRNG111.EMPL.DATA) OUTDATASET(TRNG111.TEST.ESDS5)

RRDS Using IDCAMS

//TRNG111A JOB NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEFINE CLUSTER(NAME(TRNG111.TEST.RRDS2) -
NOERASE NUMBERED NOREUSE RECORDSIZE(70,90) FREESPACE(0,0) -
TRK(10,5) SHAREOPTIONS(1,3) SUBALLOCATION CONTROLINTERVALSIZE(1024) -
RECOVERY

Monday, 1 July 2013

COBOL--EMPLEAVE

IDENTIFICATION DIVISION.
PROGRAM-ID. EMPLEAVE.
DATA DIVSION.
WORKING-STORAGE SECTION.
01 EMP-DTL.
05 EMP-NAME PIC X(05).
05 EMP-LEAVE-TYPE PIC A(02).
88 VALID-TYPE VALUE 'EL','ML','SL','CL'.
05 EMP- LEAVE-DATE.
10 EMP-DD PIC 9(02).
10 EMP-MM PIC 9(02).
10 EMP-YY PIC 9(02).
PROCEDURE DIVISION.
ACCEPT EMP-LEAVE-TYPE.
IF VALID-TYPE
DISPLAY 'LEAVE TYPE VALID ', EMP-LEAVE-TYPE
ELSE
DISPLAY 'LEAVE TYPE INVALID ',EMP-LEAVE-TYPE
END-IF.
STOP RUN.

COBOL--INSPECT

IDENTIFICATION DIVISION.
PROGRAM-ID. INSP.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 WS-USER PIC X(10).
77 WS-COUNT PIC 9(02).
PROCEDURE DIVISION.
ACCEPT WS-USER.
INSPECT WS-USER TALLYING WS-COUNT FOR ALL CHARACETRS.
DISPLAY 'NUMBER OF CHARACTERS IN USER NAME= ', WS-COUNT.
INSPECT WS-USER TALLYING WS-COUNT FOR ALL 'A'.
DISPLAY 'NUMBER OF TIMES CHARACTER A IS FOUND= ',WS-COUNT.
INSPECT WS-USER REPLACING ALL 'C' BY 'G'.
DISPLAY 'USER NAME AFTER REPLACING C BY G = ', WS-USER.
STOP RUN.

COBOL - OCCUR Example program

IDENTIFICATION DIVISION.
PROGRAM-ID. OCCUR1.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 WS-NM PIC x(06).
77 WS-TOT-MRK PIC 9(04) VALUE 0.
77 WS-AVG-MRK PIC 9(04) VALUE 0.
01 STU-TBL.
05 STU-DET OCCURS 5 TIMES INDEXED BY X1.
10 STU-NM PIC X(06).
10 STU-RLNO PIC 9(04).
10 MRKA PIC 9(03).
10 MRKB PIC 9(03).
10 MRKC PIC 9(03).
10 MRKD PIC 9(03).
10 MRKE PIC 9(03).
PROCEDURE DIVISION.
0000-START.
PERFORM VARYING X1 FROM 1 BY 1 UNTIL X1 > 5
ACCEPT STU-NM(X1)
ACCEPT STU-RLNO(X1)
ACCEPT MRKA(X1)
ACCEPT MRKB(X1)
ACCEPT MRKC(X1)
ACCEPT MRKD(X1)
ACCEPT MRKE(X1)
END-PERFORM.
ACCEPT WS-NM.
SET X1 TO 1.
SEARCH STU-DET
AT END DISPLAY 'STUDENT NAME NOT FOUND'
WHEN STU-NM(X1) = WS-NM
COMPUTE WS-TOT-MRK = MRKA(X1) + MRKB(X1) + MRKC(X1) + MRKD(X1) + MRKE(X1)
COMPUTE WS-AVG-MRK = WS-TOT-MRK / 5
DISPLAY 'STUDENT FOUND'
DISPLAY 'TOTAL MARK=', WS-TOT-MRK, ' AVERAGE MARK= ', WS-AVG-MRK
END-SEARCH.
STOP RUN.

Wednesday, 26 June 2013

Merge

//TRNG111M JOB NOTIFY=TRNG111,TIME=10
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN01 DD DSN=TRNG111.FORSORT.UNSORT1,DISP=SHR
//SORTIN02 DD DSN=TRNG111.FORSORT.SORT1,DISP=SHR
//SORTOUT DD DSN=TRNG111.MERGE.DATA,DISP=(NEW,CATLG),SPACE=(TRK,(5,5)),
// DCB=(LRECL=80,BLKSIZE=8000,RECFM=FB)
//SYSIN DD *
MERGE FIELDS=(1,3,CH,A)
/*

Tuesday, 25 June 2013

SORTING

UNSORTED

C3000SUNDAR10-01-2000SB20000HYD
C8000GANESH01-05-2001CA15000CHE
C1000SUHASH23-08-2003SB25000BNG
C7000HARISH05-05-2008FD12000CHE
C5000SUBASH11-11-2011SB20000HYD
C9000GANDHI01-01-2001CA15000CHE
C6000SURESH03-03-2003SB25000BNG
C2000RAMESH05-05-2005FD12000CHE

JCL CODE TO SORT

//TRNG111A JOB NOTIFY=TRNG111,TIME=10
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=TRNG111.FORSORT.UNSORT1,DISP=SHR
//SORTOUT DD DSN=TRNG111.FORSORT.SORT1,DISP=SHR
//SYSIN DD *
SORT FIELDS=(6,6,CH,A) SKIPREC=1,STOPAFT=4
/*

SORTED

C8000GANESH01-05-2001CA15000CHE
C7000HARISH05-05-2008FD12000CHE
C5000SUBASH11-11-2011SB20000HYD
C1000SUHASH23-08-2003SB25000BNG

IEBPTPCH Utility

TRNG111.PT.PCH

C3000SURESH10000CHESB 20-05-2000
C4000GANESH25000HYDCA 15-12-2001
C8000SATISH15000CHEFD 18-10-2002

//TRNG111L JOB NOTIFY=&SYSUID
//STEP1 EXEC PGM=IEBPTPCH
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=TRNG111.PT.PCH,DISP=SHR
//SYSUT2 DD SYSOUT=R
//SYSIN DD *
PRINT TYPORG=PS,MAXFLDS=6
TITLE ITEM=('CUSTOMER DETAILS',20)
TITLE ITEM=('CS ID NAME BALANCE CITY OPEN DATE')
RECORD FIELD=(5,1,,1),
FIELD=(6,6,,8),
FIELD=(5,12,,17),
FIELD=(3,20,,26),
FIELD=(10,23,,32)
/*

Executing the particular steps using IEBEDIT

//TRNG111A JOB CLASS=C,NOTIFY=TRNG111
//STEP1 EXEC PGM=IEFBR14
//AA DD DSN=TRNG111.AA.DATA,DISP=(NEW,CATLG),
// SPACE=(TRK,(1,1))
//STEP2 EXEC PGM=IEFBR14
//BB DD DSN=TRNG111.BB.DATA,DISP=(NEW,CATLG),
// SPACE=(TRK,(1,1))
//STEP3 EXEC PGM=IEFBR14
//CC DD DSN=TRNG111.CC.DATA,DISP=(NEW,CATLG),
// SPACE=(TRK,(1,1))
//STEP4 EXEC PGM=IEFBR14
//AD DD DSN=TRNG111.DD.DATA,DISP=(NEW,CATLG),
// SPACE=(TRK,(1,1))

RUN PGMX

//TRNG111X JOB CLASS=A,NOTIFY=TRNG111
//S1 EXEC PGM=IEBEDIT
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=TRNG111.NEW.JCL(PGMX),DISP=SHR
//SYSUT2 DD SYSOUT=(*,INTRDR)
//SYSIN DD *
EDIT START=TRNG111A,TYPE=INCLUDE,STEPNAME=(STEP1,STEP3)

Copy Datasets Using IEBGENER

//JOBCOPY1 JOB CLASS=A,MSGLEVEL=(1,1),NOTIFY=&SYSUID
//STEP1 EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DISP=SHR,DSN=TRNG111.X.X
//SYSUT2 DD DISP=SHR,DSN=TRNG111.X.Y
//SYSIN DD DUMMY

Print dataset using IDCAMS

//TRNG111Y JOB CLASS=C,NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
PRINT INDATASET(TRNG111.X.X) CHAR

//* CHAR/HEX/DUMP SKIP(nnn) COUNT(mmm) (for all datasets)

//TRNG111C JOB CLASS=C,NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//ABC DD DSN=TRNG111.X.X,DISP=SHR
//XYZ DD DSN=TRNG111.Y.X,DISP=SHR
//SYSIN DD *
REPRO INFILE(ABC) OUTFILE(XYZ) SKIP(1) COUNT(2)

List Catalog using IDCAMS

//TRNG111Z JOB CLASS=C,NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
LISTCAT ENTRIES(TRNG111.X.X) ALL

Delete Dataset Using IDCAMS

//TRNG111Y JOB CLASS=C,NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DELETE 'TRNG111.Y.X'

Copy Using IDCAMS

Copy Using IDCAMS

//TRNG111C JOB CLASS=C,NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
REPRO IDS(TRNG111.X.X) ODS(TRNG111.X.X)

Create GDG

Define GDG

//TRNG111G JOB CLASS=A,MSGCLASS=X,NOTIFY=TRNG111
//S1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//MOD1 DD DSN=TRNG111.GDG1.MOD,DISP=(NEW,CATLG),
// DCB=(LRECL=80,BLKSIZE=8000,RECFM=FB),SPACE=(TRK,0)
//SYSIN DD *
DEFINE GDG(NAME(TRNG111.TEST.GDG) LIMIT(5) NOEMPTY SCRATCH

Create GDG

//TRNG111A JOB NOTIFY=TRNG111
//S1 EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=TRNG111.X.X,DISP=SHR
//SYSUT2 DD DSN=TRNG111.TEST.GDG(+1),DISP=(NEW,CATLG),
// SPACE=(TRK,(10,5)),
// DCB=(TRNG111.GDG1.MOD,LRECL=80,BLKSIZE=8000,RECFM=FB)
//SYSIN DD DUMMY

COPY and Compress Data-Sets

//TRNG111C JOB CLASS=A,MSGLEVEL=(1,1),NOTIFY=TRNG111
//STEP1 EXEC PGM=IEBCOPY
//SYSPRINT DD SYSOUT=*
//ABC DD DSN=TRNG111.NEW.COBOL,DISP=SHR
//XYZ DD DSN=TRNG111.NEW.COBOL,DISP=SHR
//SYSIN DD *
COPY INDD=ABC,OUTDD=XYZ

COPY Data-set Members using Procedure

Procdure Code

//D3PROC1 PROC
//STEP1 EXEC PGM=IEBCOPY
//SYSPRINT DD SYSOUT=&C1
//ABC DD DSN=&XXX,DISP=SHR
//XYZ DD DSN=&YYY,DISP=SHR
//SYSIN DD DUMMY

Job Code

//TRNG111P JOB NOTIFY=TRNG111
// JCLLIB ORDER=TRNG352.NEW.JCL
//S1 EXEC D3PROC1,C1=*,XXX=TRNG111.NEW.JCL,YYY=TRNG111.JCL.TEST
//STEP1.SYSIN DD *
COPY INDD=ABC,OUTDD=XYZ