Tip of the week is on summer holiday. The next tip will be published on the 4th of august 2008.

Ugens tip holder sommerferie. Næste tip udkommer den 4. august 2008.

Way back in year 2002 I wrote a tip about CAF. In that tip I mention the possibility to change DB2 subsystem from within a program. In order to change DB2 subsystem using CAF you have to do a lot more coding than revealed then, because it is not without challenges to do so.

When you have activated the CAF interface using OPEN it is easy to beleive that after a CLOSE you may perform a new OPEN against another DB2 subsystem. The programmers behind the CAF interface felt that such a solution was too straightforward. Instead you will receive an OPEN failure telling you that you are already connected to another DB2 subsystem. With OPEN and CLOSE you may switch DB2 plan, but not DB2 subsystem.

In order to switch DB2 subsystem you have to use two extra CAF interface calls named CONNECT and DISCONNECT. Here is an example in PL/I which you have to combine with the tip from 2002 in order to make it fully operational:

dcl caf_function char(12);
dcl caf_ssid char(4);
dcl caf_termecb pointer;
dcl caf_startecb pointer;
dcl caf_ribptr pointer;

caf_ssid = 'MYDB';
caf_termecb = null;
caf_startecb = null;
caf_ribptr = null;
caf_function = 'CONNECT';
call dsnali( caf_function, caf_ssid, caf_termecb, caf_startecb
           , caf_ribptr, caf_retcode, caf_reascode
           );
if caf_retcode = 0
then do;
  /* Put your CAF OPEN here */
  /* Do program processing */
  /* Put your CAF CLOSE here */
  caf_function = 'DISCONNECT';
  call dsnali(caf_function);
end;
else do;
  /* Do some kind of error processing */
  /* Remember to display value of caf_reascode */
end;

It is most important to start with the CONNECT call. When you issue the OPEN call in order to specify the DB2 plan you must remember to use the same DB2 subsystem name as in the CONNECT call. After the CLOSE call you must issue a DISCONNECT call. It is not important to check the return codes after CLOSE and DISCONNECT because the following CONNECT call will tell you if anything went wrong during CLOSE or DISCONNECT. Furthermore DISCONNECT may issue a warning even though it was successful and then the error processing suddenly gets complicated.

In the description of DISCONNECT it is mentioned that DISCONNECT performs an implicit CLOSE with option SYNC if the CLOSE has not been performed. Unfortunately you cannot rely on this feature because a following CONNECT/OPEN will fail if you let DISCONNECT perform the CLOSE. Therefore you must make an explicit CLOSE call. If you are more interested in all CAF details please read about them in Application programming and SQL guide. The link is for DB2 version 7, but is still OK because the CAF interface is very seldom subject for change.

Do not be confused about the three pointers to the CONNECT call. It works perfectly when you use the null value for these three parameters. The pointers are there to make people coding started tasks happy so they are able to find out whether DB2 are up or down. If you have similar considerations a way around such a problem is to perform a DISCONNECT before your task starts to wait for the next assignment. Then perform a new CONNECT when the assignment arrives.