Migrating from DB2 Private Protocol to DRDA

For years IBM has been warning people that DB2 private protocol would go away. With DB2 V9, it became official -- DB2 private protocol has been deprecated. As a result, you need to plan a migration path to convert all your existing plans and packages from DB2 private protocol to DRDA.

 

DB2 private protocol, also known as system-directed access, was introduced in DB2 V2.2 to support Distributed Unit of Work (DUW). DB2 private protocol is used by your application when you reference a table name using a  three-part name or an alias defined with a three-part name.

 

With DB2 V3, however, IBM unveiled the DRDA protocol, also known as application-directed access. Initially, DB2 private protocol trumped DRDA in terms of security and performance. However, IBM put its resources into DRDA (DB2 private protocol hadn't been enhanced since DB2 V5). Clearly, DRDA is now the far superior protocol.

 

To help you convert programs, IBM is providing a REXX tool. DSNTP2DP. When executed, DSNTP2DP searches DB2 catalog tables SYSIBM.SYSPLANS and SYSIBM.SYSPACKAGES for DBPROTCOL ='Y' and any object dependency, either through a three-part name or alias using a three-part name. Even if DBPROTCOL="Y" is set, the application doesn't automatically access a table through a three-part name. The code to rebind the plan/package isn't generated unless it does.

 

With DRDA, the PLAN and PACKAGE must be bound at both the local and remote locations. Because of this the alias must be defined on the local executing subsystem with the three-part name (location.owner.name) and on the remote subsystem as a two-part name (owner.name). DSNTP2DP generates three separate scripts to build the alias and bind the plans and packages.

 

The first step is to define the alias at the remote location. The script does this by first connecting to the remote location and then creating the alias without the location name. 

For example, on the local subsystem I've created this alias with a three-part name:

 

CREATE ALIAS HRDATA.DEPARTMENT FOR

           DBP1LOC.PROD01.DEPARTMENT;

 

The generate script builds this alias on the remote system without the location name:

 

CONNECT TO DBP1LOC;

CREATE ALIAS HRDATA.DEPARTMENT for PROD01.DEPARTMENT;

RELEASE DBP1LOC;

COMMIT;

 

The second step is to convert the packages on the local system and add a copy of the package on the remote system. Let's say I have package PY0010 in collection PAYROLL_C using private protocol. The BIND PACKAGE script performs these steps:

 

REBIND PACKAGE(PAYROLL_C.PY0010) DBPROTOCOL(DRDA)

 

BIND PACKAGE(DBP1LOC.PAYROLL_C) COPY(PAYROLL_C.PY0010) –

          OPTIONS(COMPOSITE) OWNER(PROD01) QUALIFIER(PROD01) –

          DBPROTOCOL(DRDA) SQLERROR(CONTINUE)

 

The final step is to rebuild any PLAN that is using DB2 private protocol. All DBRMs associated to the PLAN are converted to packages and stored in a default collection of DSNCOLLID. For this example, I have PLAN "PYBATCH," which has DBRM PY0020.

 

BIND PACKAGE (DSNCOLLID) MEMBER(PY0020) –

   LIBRARY(‘PROD01.TEMP.PY0020’) –

  OWNER(PROD01) QUALIFIER(PROD01)

  . . .

  DBPROTOCOL(DRDA) SQLERROR(CONTINUE)

 

BIND PACKAGE (DBP1LOC.DSNCOLLID)  COPY(DSNCOOLID.PY0020) –

     OPTIONS(COMPOSITE) OWNER(PROD01) QUALIFIER(PROD01) –

      DBPROTOCOL(DRDA) SQLERROR(CONTINUE)

 

 

 BIND PLAN(PYBATCH) ACTION(REPLACE) RETAIN –

     PKLIST(DSNCOLLID.*, DBP1LOC.DSNCOLLID.*) -

     …

     DBPROTOCOL(DRDA

 

This information is available in the DB2 Installation Guide. Remember: To quickly find information regardless of the particular manual it's in, go to the DB2 Version 9.1 for z/OS Information Web site.

 

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc/db2prodhome.htm

Comments

[Rob Jones]

Troy,
Thanks for the reminder that we need to do this; but it reminds me of the big question:

How are people handling these 2 part binds when run through an automated solution like Endevor? When to run the 2 part bind? Where to run the second part? How to handle the allowed errors vs unexpected? These were issues that Private Protocaol binds never had to handle.

[Troy Coleman]

Hi Rob,
I don't have an environment I can test this out but from what I can remember you will get the following error: 1) On bind at the remote location you will get a -204 object not found if you do not define the alias at that location. The other problem you could see is -805 if you do not bind the package at the remote location. As for CA Endevor I would think you would change the bind option as I listed above and ensure you create the alias at both the local and remote systems.

[Robert Plata]

Troy,
I am heading this project for my company, in a DBA role. My management has taken the approach that the DBAs will handle the binds for all DB2 programs/plans from private protocol to DRDA. We use Endevor as well. However, we will be bypassing that process as a courtesy to our programmers.

The issue am running into is making sure that the bind cards specify DBPROTOCOL(DRDA) before the rebinds take place, so as not to revert back to private protocol. This became an issue because it was broadcast to the programmers that they would not have to do any work in this effort. It turns out this is not entirely true, if we do this under DB2 V8 and keep our zparms as is.

[Robert Plata]

An application programmer just verified that a -805 is received from the execution of the application program with the DB2 program specifying DRDA, and no supporting alias, nor DB2 program, residing on the remote DB2.

[tonmoy dasgupta]

Quote
"When executed, DSNTP2DP searches DB2 catalog tables SYSIBM.SYSPLANS and SYSIBM." for DBPROTCOL ='Y' "
End-Quote

You probably meant DBPROTCOL ='D' for 'Y' is not a valid value.

Thank you for the article though.

[Troy Coleman]

Hi Tonmoy,
Yes, The correct value would be "D" for DRDA or "P" for private. It must have been a late night. I'm not sure why I put "Y". Cheers!
Troy

[Tina Bayer]

A remote subsystem question: does only the PACKAGE get bound on the remote subsystem, or, does the entire PLAN also need to be bound on the remote subsystem ?