JCL-COBOL-VSAM-DB2: July 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.