Dynamic access to DB2 tables via DRDA from IDEAL

Document ID : KB000054830
Last Modified Date : 14/02/2018
Show Technical Document Details


Ideal applications can access DB2 remote tables via DRDA (Distributed Relational Database Architecture). This access requires some additional customization steps that are outlined in this document.


DB2 uses a three-part name for remote objects, with the first part indicating the node at which they reside. Ideal does not use three-part names.

Being so, Ideal needs to use a two-part name to access the remote content and this can be achieved defining, on the "local" DB2 subsystem, aliases for the remotely-defined tables.

Moreover, IDEAL CATALOG DVW command must work on the "local" DB2 catalog and needs local DB2 definitions. IDEAL is accessing SYSIBM.SYSTABLES via SQL.

Once you get past the CATALOG DVW process, the run-time access is transparent. It also allows the remote objects to be relocated without
changing the applications as the alias is all that get changed.

To run CA-Ideal For DB2 programs in dynamic mode (from VLS) via DRDA, the following steps must be performed:

  1. on the local subsystem, BIND PACKAGEs for each IDEAL development DBRM giving them a COLLECTION name "IDEAL" and the "location" should be the remote LOCATION NAME in the DB2 catalog. You need to bind them "locally" also, i.e. without a "location" specified:
    DSN    SYSTEM(DSJ)                                           BIND   PACKAGE(location.IDEAL) +                                       MEM(IDDB22RR) +                                              VALIDATE(BIND) +                                             ISOLATION (CS)                                     BIND   PACKAGE(location.IDEAL) +                                                       MEM(IDDB22DY) +                                                              VALIDATE(BIND) +                                                             ISOLATION (CS)                                                     BIND      PACKAGE(location.IDEAL) +                                                    MEM(IDDB22PL) +                                                              VALIDATE(BIND) +                                                             ISOLATION (CS)                                                     BIND      PACKAGE(location.IDEAL) +                                                    MEM(IDDB22CT) +                                                              VALIDATE(BIND) +                                                             ISOLATION (CS)                                                     BIND   PACKAGE(IDEAL) +                                       MEM(IDDB22RR) +                                              VALIDATE(BIND) +                                             ISOLATION (CS)                                     BIND   PACKAGE(IDEAL) +                                                       MEM(IDDB22DY) +                                                              VALIDATE(BIND) +                                                             ISOLATION (CS)                                                     BIND      PACKAGE(IDEAL) +                                                    MEM(IDDB22PL) +                                                              VALIDATE(BIND) +                                                             ISOLATION (CS)                                                     BIND      PACKAGE(IDEAL) +                                                    MEM(IDDB22CT) +                                                              VALIDATE(BIND) +                                                             ISOLATION (CS)
  2. bind the IDEAL development PLAN specifying the PACKAGEs with asterisk (*) to indicate to DB2 it is for all locations.
    BIND      PLAN(IDP220DV) +                                                             MEM(IDDB22CT,IDDB22DY,IDDB22PL,IDDB22RR) +                                   PKLIST (*.IDEAL.IDDB22CT,*.IDEAL.IDDB22DY, +                                  *.IDEAL.IDDB22PL,*.IDEAL.IDDB22RR)     +                                     ACTION(REPLACE) +                                                            RETAIN +                                                                     VALIDATE(BIND) +                                                             ISOLATION (CS)                                                     END 
  3. in DB2 local subsystem you need to create a copy of the tables that the Ideal for DB2 applications need to access on the remote location (same name and structure); this step is required as CATALOG DVW works on the "local" DB2 catalog: e.g. CREATE ALIAS IDEALDS.DETAIL ....

  4. at this point you can successfully catalog the required DB2 dataviews, as now the DB2 tables are defined on the "local" DB2 catalog: CAT DVW IDEALDS.DETAIL

  5. drop all the "local" copies of the DB2 tables: DROP TABLE IDEALDS.DETAIL

  6. in DB2 local subsystem, create a DB2 ALIAS pointing to TABLE on "remote" DB2 subsystem:

At this point, all is correctly set.