How to find the plan and package name in DB2 from program name

Suppose we have a cobol-db2 module named 'PGM-ABC'

We want to bind it and run the program. But we are not sure about the package and plan details.So where do we find it from? SYSIBM tables will give us the details we need.Lets check it out.

With the program name we can query SYSIBM.SYSPACKAGE table giving the name of the program(PGM-ABC) against the column 'NAME'.  We can use DB2 file-aid tool or use the below  query in SPUFI.

Select location, collid,name ,contoken,timestamp,bindtime from SYSIBM.SYSPACKAGE where NAME = 'PGM-NAME' ;

Once we get the COLLID,we need to query the SYSIBM.SYSPACKLIST table to get the plan name

SELECT PLANNAME,LOCATION,COLLID FROM SYSIBM.SYSPACKLIST WHERE COLLID='colld-id-name-we-got-above';

From experience, we should try to select the latest rows from the tables checking the TIMESTAMP fields; or check what other ppl have used in recent times.

SAS in Mainframes(z/Os) - Connecting to DB2 from SAS in mainframes

This post is all about connecting DB2 from SAS in mainframe environment and using the result set for further processing. To call and connect to DB2 session we need the SQL proc to be called first using the statement PROC SQL. When used in a program we need to begin  the code with PROC SQL and end it with QUIT; In Between these two we can write our SQL codes to fetch the required data.
Without wasting much time lets check the first JCL used to connect to db2.

//TTYYTST  JOB(TEST),CLASS=I,MSGCLASS=X,MSGLEVEL=(1,1)

//                NOTIFY=&SYSUID
//STEP01  EXEC PROC=SAS,OPTIONS='  '
//WORK    DD SPACE=(CYL,(,500)),VOL=(,,,59)
//OUTFILE DD DSN=TEST.OUTPUT.SAS,
//                   DISP=(,CATLG,DELETE),
//                  DCB=(RECFM=FB,LRECL=122,BLKSIZE=000),
//                  UNIT=SYSDA,SPACE=(TRK,(500,500),RLSE)
//SYSIN     DD *
OPTION NOCENTER;
OPTION SORTLIB;
PROC SQL;                                     <== Starts here
CONNECT TO DB2 (SSID=DQAT);    <== DQAT is the db2 subsystem here  
   CREATE TABLE test AS                  <== test is the sas datset here.
        SELECT * 
         FROM CONNECTION TO DB2 (
select fname,
         lastname,
         dept_name,        
from  test01.dept
where student_id = 1 fetch first 5 rows only) ;

%PUT SQLXRC=&SQLXRC;

%PUT SQLMSG=&SQLMSG;

QUIT;

DATA STEP1;

SET TEST;

FILE OUTFILE DLM=',' ;

IF _N_=1 THEN DO;

PUT   'FIRST NAME,'

          'LAST NAME,'

          'DEPT NAME,'

;

END;

PUT  FNAME

          LASTNAME

         DEPT_NAME

;

//SYSPRINT DD SYSOUT=*

//*

JCL is self explanatory. %PUT SQLXRC and SQLMSG are the macros used to capture the DB2 return codes and error messages if any.

Invoke CA7 commands from Batch terminal - use of SASBSTR

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 are astronomical, it is difficult to manage the jobs manually. So we need a tool like CA7 to manage the same. Manipulation of jobs, predecessor and triggers are done through CA7 panels. But sometimes we need to define hundreds of jobs to CA7, it is advisable to use batch terminal to do the same. Also batch terminals are less prone to errors. Batch terminals are a set of CA7 commands in a member of a partitioned dataset which is input to a CA7 batch program and submitted using a JCL

The program which executes the CA7 commands in batch is called SASSBSTR. The list of commands are put into a member of a partitioned dataset and given as an input to the program through SYSIN DD statement in the JCL. When executing the SASSBSTR program, the SYSIN input statements are copied into the input dataset called CA7. Communication dataset informs CA7 that there are commands waiting in the input dataset. These are read and processed by CA7. Any output is written to the output dataset. When all the commands have been processed, SASSBSTR copies the contents of the output dataset to SYSPRINT. Have a look into the below diagram

CA7 commands from batch terminal

Now, lets have the JCL to execute the CA7 from batch terminal.

//@SASBSTR EXEC PGM=SASBSTR

//UCC7CMDS DD DSN=TTOS.NCA7A01.COMMDS,DISP=SHR

//SYSDUMP DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSOUT DD SYSOUT=*

//SYSIN DD *

LJOB,JOB=SJABCD7,LIST=ALL     <==  CA7 command goes here

/*

//*

All relevant information for the particular job SJABCD7 will be listed in SYSOUT since we gave SYSPRINT DD SYSOUT=*.

Check the commonly used CA7 commands in this page. We can use these commands in the SYSIN DD* statements.

Explanation of JCL:

SASSBSTR is the program which executes the batch terminal

UCC7CMDS DD statement is a communication dataset which is used to read the CA7 commands from SYSIN DD Statements into the input dataset.

The CA7 commands are embedded within the SYSIN DD statement.All valid commands can be used here. Usually the programs are written in a cataloged procedure and the batch terminal is given as an input to the program. The output can be stored in a dataset.