Have you ever dreamed of making a DB2 program that doesn't run under the TSO DSN command and the DSN subcommand RUN. This tip will show you how. When DB2 was invented (as far as I know), IBM invented an interface called CAF which enables programs to connect directly to DB2. In the beginning the CAF interface was only intended to be used from assembler programs, but now it is easy to use from different higher leveled languages like COBOL, PL/1 and C.

Before I tell you how to use CAF instead of DSN, I must issue a warning. When you use CAF instead of DSN then you have to define a way to tell your program what DB2 subsystem you want to connect to and what DB2-plan you want to execute under. These choices are externalized when you use DSN. Using CAF, your program becomes dependent on named ressources. Of course you can solve this problem, but you have to define how you want to solve it. This typically leads to new problems like standards and so on.

Let's skip this boring subject and look at an example written in PL/1:

cafeks: PROC(parm) OPTIONS(MAIN) REORDER;

DCL parm CHAR(100) VAR;
DCL caf_ssid CHAR(4);
DCL caf_function CHAR(12);
DCL caf_plan CHAR(8);
DCL caf_termop CHAR(4);
DCL caf_retcode FIXED BIN(31);
DCL caf_reascode CHAR(4);
DCL dsnali EXT ENTRY OPTIONS(ASM INTER);

caf_ssid = parm;
caf_plan = 'CAFEKS';
caf_retcode = 0;
caf_reascode = 0;

caf_function = 'OPEN';
CALL dsnali( caf_function
           , caf_ssid, caf_plan
           , caf_retcode, caf_reascode
           );
IF caf_retcode = 0
THEN DO;
  /* Write the rest of your program here as */
  /* if it had to be run with DSN */
  caf_termop = 'SYNC';
  caf_function = 'CLOSE';
  CALL dsnali(caf_function, caf_termop);
END;
ELSE
  SELECT(caf_reascode);
  WHEN('00F30034'X)
    /* The DB2 plan doesn't exist or auth error */
    ;
  WHEN('00F30006'X)
    /* The DB2 subsystem doesn't exist on this MVS */
    ;
  OTHERWISE
    /* Other error encountered during OPEN */
    ;
  END;

END cafeks;

Instead of the comments you must substitute the appropiate code for your installation. I have removed the caf_retcode and caf_reascode from the caf_function = 'CLOSE' call, because it doesn't affect the program execution of this program whether the CLOSE was OK or not. The program terminates anyway right after the call. You might say, that this call is obsolete, but I am showing it to you, because after the CLOSE you are able to connect to another DB2 subsystem and/or another DB2 plan without terminating the program. This is not possible with DSN.

When you link your CAF program you must include DSNALI instead of DSNELI otherwise your SQL calls expect to be executed using the DSN command. Imagine you have compiled my CAFEKS program into a LOAD module called CAFEKS. Now you can execute it in batch simply by writing //CAFEKS EXEC PGM=CAFEKS,PARM='/DB2' if your DB2 subsystem is called DB2.

You might be interested in reading more about the CAF interface, when you have seen this tip running. Look into the Application Programming and SQL guide for the DB2 version currently running at your installation. In the manual for version 5 it is described in chapter 6.6.