How to make your DB2 program work in a DRDA setup ?

DRDA - Distributed Relational Database Architecture.

Couple of you will have this question - Why do you need the DRDA set up ? The answer will reside on the early pages of your account when it was set up. We cannot go back to those days, but let me try to explain the architecture from a DBA's perspective.

You might have two separate production LPAR's ( APAR & BPAR ) each with a single subsystem ( APAR - ASUB, BPAR - BSUB ). Business decided that from ASUB I should be able to talk to BSUB. If ASUB has the DB2 objects ( database, tablespace, tables ) then BSUB will have a reference using an ALIAS to the object in ASUB.

How to make your DB2 program work in a DRDA setup ?

All I have to do is link the program to the remote object using the below BIND structure on the DB2 PLAN. The program in the form of a DB2 package has to exist in ASUB and BSUB, but the PLAN need not if you are using the below BIND syntax :

 

The above syntax is from APAR, ASUB - PROG001 is mapped on to PLAN001 and the syntax of PLAN001 is tweaked so that it is able to talk to BSUB using DRDA protocol

Note : This PLAN should exist or should be created in the region where you want to execute the program which in our case is ASUB. This plan need not exist in BSUB, however it is mandatory that PROG001 should exist in ASUB as well as BSUB.

Is there any other way ? Yes -

1) The below syntax is from APAR, ASUB - PROG001 is mapped on to PLAN001 - PROG001 accesses the ALIAS which in turn access the TABLE using the DRDA protocol on BSUB

2) The below syntax is from BPAR, BSUB - PROG001 is mapped on to PLAN001 - PROG001 accesses the TABLE and waits for the connection in case PROG001 present in ASUB is executed from APAR

Note : The PLAN names in both the LPAR's are the same, Unlike the previous example in which we only had one PLAN which would establish a connection, In the above example we saw two plans being created that would suffice the DRDA establishment

The BIND note :

Both these programs should have a contoken match else will end up with a -805. In other words, the programmer should not miss any BIND's in the remote/physical region when he is working with DRDA protocol.

5 keywords every developer and DBA should know

I am listing some of the most important terminology that every developer and DBA in the z/OS Mainframe world should know while interacting with IBM DB2. Most of the times a miss communication between a developer and a DBA can cause delays and account for each others precious time. I have tried to explain the key terms in the simplest possible way so that the vast majority of readers get used to the fact that, yes - indeed its necessary to know them

1) PRECOMPILE
Processes an application program source code ( ex : COBOL ) which contains SQL statements within EXEC and END-EXEC statements. Once you precompile you get a modified source code which will be your LOAD MODULE present in the LOADLIB PDS. This load module in the form of a member within your load library will contain host language calls for SQL statements.

2) DBRM
A member within your DBRM library containing all the SQL's extracted from source code by the precompiler. DBRM MODULE is present in the DBRMLIB PDS. The library name is very important as one might miss performing a BIND and will have no clue which DBRM library the BIND member was placed. As a developer, you should be able to provide the DBRM library used while performing PRECOMPILE

3) BIND
You need to perform the BIND operation to compile all your SQL statements (DBRM) into an executable format. A DBA may ask you which DBRMLIB you used while your precompiled and bound your program ? You should be able to provide the necessary details.

4) PACKAGE / PROGRAM
A DB2 Package is an object that contains executable forms of SQL statements. Basically if you are in DB2 10 for z/OS - All your application programs should be in the form of a package. It's a good practice to keep all your programs as a DB2 package

5) PLAN
An application PLAN can contain one or more packages or it can contain bound form of SQL statements. You need a PLAN to run/execute a package in DB2.

Summary
There are many other factors which every developer and DBA must know, but clear knowledge on these keywords can help reduce the time spent in conversations during job abends  !