Blog
DB2utor

Categories

July 14, 2009

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