MainframeMaster

JCL Tutorial

JCL Database Practical Examples

Progress0 of 0 lessons

Introduction to Database JCL

JCL (Job Control Language) plays a crucial role in mainframe database operations. Whether working with DB2, IMS, VSAM, or other database systems, well-crafted JCL is essential for successful database maintenance, backup, recovery, and data processing tasks.

This tutorial provides practical examples of JCL for common database operations, with explanations of key components and best practices. These examples can be adapted to meet specific requirements in your environment.

DB2 Database Operations

DB2 is IBM's relational database management system widely used in mainframe environments. Here are practical JCL examples for common DB2 operations.

DB2 Utility: RUNSTATS

The RUNSTATS utility collects statistics about tables and indexes to optimize query performance. Here's a JCL example:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//DB2STATS JOB (ACCT),'DB2 RUNSTATS',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//JOBLIB   DD  DSN=DB2.V12.SDSNLOAD,DISP=SHR
//*
//RUNSTATS EXEC PGM=DSNUTILB,
//         PARM='DB2T,RUNSTATS'
//*
//STEPLIB  DD  DSN=DB2.V12.SDSNLOAD,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//UTPRINT  DD  SYSOUT=*
//*
//SYSIN    DD  *
  RUNSTATS TABLESPACE EMPDB.EMPTAB
           TABLE(ALL) 
           INDEX(ALL)
           SHRLEVEL REFERENCE
           REPORT YES
/*

This job runs the RUNSTATS utility against the EMPDB.EMPTAB tablespace, collecting statistics for all tables and indexes. The SHRLEVEL REFERENCE parameter specifies that the utility requires a read-only access, and REPORT YES generates a report of the collected statistics.

DB2 Utility: UNLOAD

The UNLOAD utility extracts data from DB2 tables into sequential datasets. Here's a JCL example:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//DB2UNLD JOB (ACCT),'DB2 UNLOAD',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//JOBLIB   DD  DSN=DB2.V12.SDSNLOAD,DISP=SHR
//*
//UNLOAD   EXEC PGM=DSNUTILB,
//         PARM='DB2T,UNLOAD'
//*
//STEPLIB  DD  DSN=DB2.V12.SDSNLOAD,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//UTPRINT  DD  SYSOUT=*
//*
//SYSREC   DD  DSN=USERID.EMPLOYEE.DATA,
//         DISP=(NEW,CATLG,DELETE),
//         UNIT=SYSDA,
//         SPACE=(CYL,(10,5),RLSE),
//         DCB=(RECFM=FB,LRECL=250,BLKSIZE=0)
//*
//SYSIN    DD  *
  UNLOAD TABLESPACE EMPDB.EMPTAB
         FROM TABLE EMP.EMPLOYEE
         UNLDDN SYSREC
         SHRLEVEL REFERENCE
/*

This job unloads data from the EMP.EMPLOYEE table (in the EMPDB.EMPTAB tablespace) to the USERID.EMPLOYEE.DATA dataset. The SHRLEVEL REFERENCE parameter specifies read-only access to ensure data consistency during the unload process.

DB2 Utility: LOAD

The LOAD utility populates DB2 tables with data from external sources. Here's a JCL example:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
//DB2LOAD JOB (ACCT),'DB2 LOAD',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//JOBLIB   DD  DSN=DB2.V12.SDSNLOAD,DISP=SHR
//*
//LOAD     EXEC PGM=DSNUTILB,
//         PARM='DB2T,LOAD'
//*
//STEPLIB  DD  DSN=DB2.V12.SDSNLOAD,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//UTPRINT  DD  SYSOUT=*
//*
//SYSREC   DD  DSN=USERID.EMPLOYEE.DATA,DISP=SHR
//SYSDISC  DD  DSN=USERID.EMPLOYEE.DISCARDS,
//         DISP=(NEW,CATLG,DELETE),
//         UNIT=SYSDA,
//         SPACE=(CYL,(5,1),RLSE)
//SYSERR   DD  DSN=USERID.EMPLOYEE.ERRORS,
//         DISP=(NEW,CATLG,DELETE),
//         UNIT=SYSDA,
//         SPACE=(CYL,(5,1),RLSE)
//SYSMAP   DD  DSN=USERID.EMPLOYEE.MAP,
//         DISP=(NEW,CATLG,DELETE),
//         UNIT=SYSDA,
//         SPACE=(CYL,(5,1),RLSE)
//*
//SYSIN    DD  *
  LOAD DATA INDDN SYSREC
       INTO TABLE EMP.EMPLOYEE
       REPLACE
       DISCARDDN SYSDISC
       ERRDDN SYSERR
       MAPDDN SYSMAP
       (EMPID      POSITION(001:006) CHAR,
        FIRSTNAME  POSITION(007:026) CHAR,
        LASTNAME   POSITION(027:046) CHAR,
        DEPARTMENT POSITION(047:050) CHAR,
        SALARY     POSITION(051:058) DECIMAL EXTERNAL,
        HIREDATE   POSITION(059:068) DATE EXTERNAL)
/*

This job loads data from the USERID.EMPLOYEE.DATA dataset into the EMP.EMPLOYEE table. The REPLACE option replaces existing data in the table. The job includes definitions for discarded records, error reports, and mapping information. Field positions and data types are specified for each column.

IMS Database Operations

Information Management System (IMS) is IBM's hierarchical database and transaction processing system. Here are practical JCL examples for common IMS operations.

IMS Database Backup

Regular database backups are essential for disaster recovery. Here's a JCL example for an IMS database backup:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//IMSBKUP JOB (ACCT),'IMS BACKUP',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//BACKUP   EXEC PGM=DFSRRC00,
//         PARM='ULU,DFSURGU0,,0,,,,,,,,,,&IMSID'
//*
//STEPLIB  DD DSN=IMS.RESLIB,DISP=SHR
//IMS      DD DSN=IMS.PSBLIB,DISP=SHR
//         DD DSN=IMS.DBDLIB,DISP=SHR
//DFSRESLB DD DSN=IMS.RESLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//CUSTDB   DD DSN=IMS.CUSTDB,DISP=SHR
//BKUPDB   DD DSN=IMS.CUSTDB.BACKUP,
//         DISP=(NEW,CATLG,DELETE),
//         UNIT=TAPE,
//         VOL=SER=CUSTBK,
//         DCB=(RECFM=VB,LRECL=4096,BLKSIZE=4100)
//*
//SYSIN    DD *
BACKUP DBD(CUSTDBD) DATASET(CUSTDB) OUTDD(BKUPDB)
/*

This job backs up an IMS customer database (CUSTDB) to a tape dataset. The Database Descriptor (DBD) name CUSTDBD identifies the specific database structure, and the output is directed to the BKUPDB DD statement.

IMS Database Recovery

Recovery is needed when a database becomes corrupted or after hardware failures. Here's a JCL example for IMS database recovery:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//IMSRECV JOB (ACCT),'IMS RECOVER',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//RECOVER  EXEC PGM=DFSRRC00,
//         PARM='ULU,DFSURDB0,,0,,,,,,,,,,&IMSID'
//*
//STEPLIB  DD DSN=IMS.RESLIB,DISP=SHR
//IMS      DD DSN=IMS.PSBLIB,DISP=SHR
//         DD DSN=IMS.DBDLIB,DISP=SHR
//DFSRESLB DD DSN=IMS.RESLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//CUSTDB   DD DSN=IMS.CUSTDB,DISP=OLD
//BKUPDB   DD DSN=IMS.CUSTDB.BACKUP,DISP=OLD,
//         UNIT=TAPE,VOL=SER=CUSTBK
//LOGDS    DD DSN=IMS.CUSTDB.LOGFILE,DISP=SHR
//*
//SYSIN    DD *
RECOVER DBD(CUSTDBD) DATASET(CUSTDB) INDD(BKUPDB) LOGDS(LOGDS)
/*

This job recovers an IMS customer database (CUSTDB) using a backup (BKUPDB) and log file (LOGDS). The recovery process applies changes from the log to bring the database to a consistent state.

VSAM Database Operations

Virtual Storage Access Method (VSAM) is a file management system that supports various data organizations. Here are practical JCL examples for common VSAM operations.

VSAM Define Cluster

Creating a VSAM cluster using IDCAMS. Here's a JCL example:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//VSAMDEF JOB (ACCT),'DEFINE VSAM',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//DEFINE   EXEC PGM=IDCAMS
//*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD /*
  DEFINE CLUSTER (NAME(USERID.CUSTOMER.KSDS) -
                 CYLINDERS(10 5) -
                 KEYS(10 0) -
                 RECORDSIZE(200 200) -
                 SHAREOPTIONS(2 3) -
                 INDEXED -
                 SPEED) -
         DATA (NAME(USERID.CUSTOMER.KSDS.DATA)) -
         INDEX (NAME(USERID.CUSTOMER.KSDS.INDEX))
/*

This job defines a Key-Sequenced Data Set (KSDS) VSAM cluster for customer data. It specifies the primary and secondary allocation in cylinders, the key length and offset, record size, and share options. The DATA and INDEX components are named separately.

VSAM Backup and Restore

Backing up and restoring VSAM files using IDCAMS REPRO. Here's a JCL example:

jcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//VSAMBKRS JOB (ACCT),'VSAM BKP/RES',CLASS=A,MSGCLASS=X,
//         NOTIFY=&SYSUID
//*
//*-- Step 1: Backup VSAM file to sequential file --*
//BACKUP   EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//VSAMIN   DD DSN=USERID.CUSTOMER.KSDS,DISP=SHR
//SEQOUT   DD DSN=USERID.CUSTOMER.BACKUP,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(5,2),RLSE),
//            DCB=(RECFM=VB,LRECL=204,BLKSIZE=0)
//SYSIN    DD /*
  REPRO INFILE(VSAMIN) OUTFILE(SEQOUT)
/*
//*
//*-- Step 2: Restore VSAM file from sequential backup --*
//RESTORE  EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SEQIN    DD DSN=USERID.CUSTOMER.BACKUP,DISP=SHR
//VSAMOUT  DD DSN=USERID.CUSTOMER.KSDS,DISP=OLD
//SYSIN    DD /*
  REPRO INFILE(SEQIN) OUTFILE(VSAMOUT)
/*

This job has two steps: The first step backs up a VSAM KSDS file to a sequential dataset, and the second step restores the VSAM file from the backup. The REPRO command copies records from one dataset to another.

Best Practices for Database JCL

Follow these best practices to ensure reliable and efficient database operations:

Key Recommendations

  • Include appropriate condition codes to avoid processing bad data after a failed step
  • Use standard naming conventions for datasets to simplify administration
  • Document JCL with comments to explain the purpose of each step
  • Allocate sufficient space to avoid out-of-space conditions during processing
  • Implement proper restart capabilities for long-running jobs
  • Use DISP=(NEW,CATLG,DELETE) to ensure temporary datasets are cleaned up
  • Include NOTIFY parameters to inform users of job completion
  • Use generation data groups (GDGs) for maintaining multiple backup versions

Common Database JCL Exercises

Practice your skills with these hands-on exercises:

Exercise 1: DB2 Table Export/Import

Write JCL to export a DB2 table to a sequential file and then import it to a new table with a different name.

Required tasks:

  1. Create an UNLOAD step to extract data from a DB2 table
  2. Create a second step using LOAD to populate a new table
  3. Include appropriate DD statements for all input/output files
  4. Add condition codes to skip the LOAD step if the UNLOAD step fails

Exercise 2: IMS Database Reorganization

Write JCL to unload an IMS database, delete and redefine it, then reload it.

Required tasks:

  1. Create an unload step using DFSURGU0
  2. Add a step to delete and redefine the database using IDCAMS
  3. Create a reload step using DFSURGL0
  4. Include appropriate condition code handling

Exercise 3: VSAM Backup Strategy

Develop a JCL procedure for backing up multiple VSAM files to GDG datasets.

Required tasks:

  1. Create a JCL procedure with symbolic parameters for VSAM file names
  2. Use IDCAMS REPRO to back up each file to a GDG
  3. Include reporting to document the backup results
  4. Develop a calling job that executes the procedure multiple times

Frequently Asked Questions

What is the difference between SHRLEVEL REFERENCE and SHRLEVEL CHANGE in DB2 utilities?

SHRLEVEL REFERENCE means the utility requires read-only access to the data, preventing updates during execution. SHRLEVEL CHANGE allows concurrent updates to the data while the utility runs, making it less disruptive but potentially less consistent.

How can I improve performance of DB2 LOAD jobs?

To improve LOAD performance: 1) Sort input data by the clustering index, 2) Use SORTKEYS option for large loads, 3) Use LOG NO option (but be cautious with recovery implications), 4) Increase buffer pool sizes, 5) Consider disabling index updates during load with INDEXDEFER option, and 6) Use parallel index builds with SORTDEVT and SORTNUM parameters.

What are the key differences between DB2, IMS, and VSAM in terms of JCL?

DB2 JCL typically uses IBM utilities like DSNUTILB with SQL-like control statements. IMS JCL uses IMS-specific utilities like DFSRRC00 with database descriptors (DBDs) and program specification blocks (PSBs). VSAM JCL primarily uses IDCAMS utility with AMS commands like DEFINE, DELETE, REPRO, etc.

How do I handle restart procedures for failed database jobs?

For restart procedures: 1) Use RESTART parameter on the JOB statement to restart from a specific step, 2) Implement COND parameters to skip completed steps, 3) For DB2 utilities, use RESTART option in the utility control cards, 4) Consider using checkpoint/restart capabilities for long-running jobs, and 5) Always review job logs to understand the failure point before restarting.

What security considerations should I keep in mind for database JCL?

Security considerations for database JCL include: 1) Use proper dataset protections through RACF or similar security tools, 2) Avoid hardcoding passwords in JCL, use external security like RACF instead, 3) Implement appropriate authority checks for utilities, 4) Consider using batch job submission profiles to control who can run specific jobs, and 5) Implement audit trails for sensitive operations.

Test Your Knowledge

1. Which DB2 utility collects statistics about tables and indexes for query optimization?

  • LOAD
  • UNLOAD
  • RUNSTATS
  • REORG

2. What parameter in a DB2 LOAD job specifies that existing data should be replaced?

  • RESUME NO
  • REPLACE
  • OVERWRITE
  • DISCARD

3. Which program is typically used for IMS database operations in JCL?

  • DSNUTILB
  • DFSRRC00
  • IDCAMS
  • IEBGENER

4. What IDCAMS command is used to back up VSAM files?

  • BACKUP
  • COPY
  • REPRO
  • EXPORT

5. What is the purpose of the DISCARDDN parameter in a DB2 LOAD utility?

  • To specify where to write discarded records that fail validation
  • To discard the entire load job if errors occur
  • To discard existing data in the table
  • To specify which columns to discard during load

Related Pages

JCL VSAM Concepts

Learn about VSAM file organization and access methods

JCL IDCAMS Utility

Master the IDCAMS utility for VSAM file manipulation

JCL Space Allocation

Learn about dataset space allocation strategies

JCL DCB Parameters

Understand Data Control Block parameters for datasets

JCL Job Statement Parameters

Explore job statement parameters for effective job control

Related Concepts

DB2 Database Administration

Learn about DB2 database administration concepts

IMS Database Concepts

Understand IMS hierarchical database structures

VSAM File Organization

Explore VSAM file types and organization methods

Mainframe Backup and Recovery

Learn strategies for mainframe data backup and recovery

Database Performance Tuning

Master techniques for optimizing database performance

JCL BasicsJCL MGMTCLAS Reference