Wednesday, November 25, 2015

COBOL DB2 Program from scratch - step by step guide

This post is just to help/guide to a new programmer in writing a cobol db2 program from scratch.
Let us write  a cobol program  to read the EMPLOYEE table and get  the details of the employee with name 'RYAN'.
I am assuming the table is already created in the user database like below:.

EMPLOYEE
EMPID EMPNAME     DEPARTMENT          SALARY              DESIGNATION
1000     XXXXXXX       XX                     10000                  SE
1001     YYYYYYY          YY                      9000                    SE
1002     ZZZZZZZ       ZZ                     20000                   TL


STEP1:  We need to declare the  Table structure in the Working Storage section. Ideally we should DCLGEN Tool to generate the structure for our DB2 table. The option to go to our DCLGEN tool depends on the ISPF settings. Generally it can be invoked using 'D' option on the ispf menu to display DB2I Default pannel.
DCLGEN Screen 
On pressing ENTER, the DCLGEN will be generated and will look like below.

*****************************************************************
EXEC SQL DECLARE DEV.EMPLOYEE TABLE
 ( EMPID CHAR(10) NOT NULL,
   EMPNAME CHAR(30) NOT NULL,
   DEPARTMENT CHAR(2) NOT NULL,
  SALARY DECIMAL(10,2) NOT NULL,
  DESIGNATION CHAR(4) NOT NULL )
)
END-EXEC.
*************** COBOL DECLARATION FOR TABLE DEV.EMPLOYEE *********
01 EMPOYEE-RECORD.
    05 HV-EMPID PIC X(10).
    05 HV-EMPNAME PIC X(30).
    05 HV-DEPARTMENT PIC X(2).
    05 HV-SALARY PIC S9(8)V99 COMP-3.
    05 HV-DESIGNATION PIC CHAR(4).
*********** THE NUMBER OF COLUMNS IN THIS DECLARATION IS 5 *****
This DCLGEN  needs to be included into the Working Storage Section of our cobol program in the following way:
EXEC SQL
INCLUDE  EMPLOYEE
END-EXEC.
Also, the most important copybook SQLCA needs to be included . Apart from this we wont be able to capture the SQL Return codes

EXEC SQL
INCLUDE  SQLCA
END-EXEC.
Also since our query in the program might return more than single row, we need cursors in our program. Read About cursor programming here
I am not going into the details of cursor programming here, since those are there in other posts.
Once the program is ready and compiled , we need to bind it to a plan in the test region. Once the bind is successful, we can run the program using  the IKJEFT01 utility as below.

cobol db2 run jcl

Wednesday, November 18, 2015

Frequently used CA7 Commands

To Define Jobs in CA7:
Each Job in CA7 must first be defined in its database; the easiest way to do this is through the command 'JOB'. Just type the command JOB or 'DB' in the CA7 Scheduler and it will show the list of available options we have to add the jobs.

Lets have look into some of the day-to-day commands which we use frequently to check the job schedules and other details.

1. LJOB,JOB=jobname,LIST=ALL
It will show you all the details of the job once you press enter.

We can use the below values with the LIST parameter
 LIST=NODD.  It will show the TRIGGERED BY JOS and SUCCESSOR jobs
 LIST=RQMT   to get the Requirement and Network connections
 LIST=SCHD    to know many sch id are assigned to a particular job using the below command
 LIST=TRIG     to know only the triggering jobs

2. Get the list of jobs in tree structure. Use the command
   FRJOB,JOB=jobname

3. Get the tree structure of the downstream job. Use the command
    FSTRUC,JOB=jobname

4.  Get the JCL in the pannel using the below command
 LJCL,JOB=jobname

5.List the active jobs using the command
LACT

6. Want to see the member names in any PDS. Use the below command
LISTDIR,DSN=pds-name

CA7 Guide for beginner's

CA7 is a product of Computer Associates and is a scheduling tool for batch jobs in mainframe.When the number of jobs to be executed is huge in number and jobs need to be executed depending on several parameters (like time dependency,  availability of data sets from other jobs, successful completion of other jobs), a tool like CA7 comes into play. 
Once a job is scheduled /triggered or manually added in CA7, it will start a process of moving through various QUEUES in CA7. CA7 will manage it until its completion.
Below is the diagram which shows in brief the lifecyle of the job inside a CA7 Scheduler.


Lets have a quick look into the Queues:
Request Queue:  When a job enters CA7, it will reside in the request queue until all its requirements have been satisfied. Examples of requirements  can be cited like :
Predecessor job
User holds
Submit time
Data set requirements

Ready Queue:  When a job's requirement have been satisfied, it will move to the ready queue. It will wait until initiators are available and any virtual resource or workload manager requirement is satisfied to enable it to execute.

Active Queue: When job can run it is moved to the active Q where it will remain whilst the job is executing within JES2. Once the job  has completed successfully on JES2 the job will be marked completed within CA7 and is removed from the active Q.

Failure list: If the job fails the it will be moved back to the requirement queue but will need correction prior to rerun. JCL errors are returned to ready Q and other failures are moved to request Q.

How to login to CA7.
Differnet systems use different methods to get into CA7. Some system allow the users to access the CA7 from ISPF menu while others use varied methods. Once we go inside the interface all remains the same.

Refer to This link for list of CA7 Commands.