Db2 Attachment Facilities

You want to write a program that connects to Db2 for z/OS and runs queries, updates the tables, and all the stuff you would expect from a database application. Let's assume your application needs to run on z/OS. What options do you have to connect to Db2 from a local z/OS application? That is what we will discuss in this blog.

First, you need to understand the environment your program will be running in. If it is REXX, you will use DSNREXX. If it is Java, your choice would be JDBC or even DDF. If it is C or C++ you may want to use ODBC. For other modern ways you may want to refer to the prior blog from Marcus.

However, what if you are writing a traditional z/OS application in COBOL, PL/I, or C, or even in High Level Assembler? That is where we come to Db2 attachment facilities.

Db2 attachment facilities are interfaces between your application and Db2 and they provide a communication channel. Applications must invoke an attachment facility implicitly or explicitly. In order to use any attachment facility, your code must use a specific attachment facility module (language interface) or a universal interface module (DSNULI).

There are couple of attachment facilities available. Some are specific to certain environments, some are not.

If your application runs in CICS, there is a CICS Attachment Facility. If you application runs in IMS, there is an IMS Attachment Facility.

However, what if you application is none of CICS nor IMS? Then you have three options - TSO Attachment Facility, Call Attachment Facility (CAF), RRS Attachment Facility (RRSAF). Which one to use? Let's explore the differences of these three a bit.

This one is the easiest to use. Your application does not need to do any explicit connections, this is all handled for you by the Db2 DSN command processor. DSN processor will create a connection for your application, will open a plan for you, and you can focus purely on the SQL in your code.

The benefit of this attachment facility is that it is the easiest to you use. You do not need to manage the connection or the plan. On the other hand, if the Db2 is down, your application will not be even executed. You cannot connect to another Db2 and you are limited to a single task connection. If you need more flexibility, then you need a different attachment facility.

Example of an invocation under DSN is:

//GO EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=prefix.SDSNEXIT
//        DD DISP=SHR,DSN=prefix.SDSNLOAD
⋮
//SYSTSPRT DD SYSOUT=A
//SYSTSIN DD *
 DSN SYSTEM (ssid)
 RUN PROG (SAMPPGM) -
     PLAN (SAMPLAN) -
     LIB (SAMPPROJ.SAMPLIB) -
     PARMS ('/D01 D02 D03')
 END
 /*

This facility gives you more flexibility compared to the prior one, but it also means more work. Particularly, you need to connect - either explicitly or implicitly (at the time of first SQL). You also have a choice of plan you want to open, again either explicitly or implicitly. However, you can do even more. For instance, you have flexibility of having multiple connections in multiple tasks, while TSO Attachment Facility allows only a single connection.

With CAF, you can disconnect and connect to multiple Db2 subsystems, you may have multiple plans opened in different tasks, etc. There are some restrictions though also, such as that you are limited to a single connection per thread, you cannot mix different attachment facilities, and some more.

Your CAF application can run even if the Db2 is down. Obviously, in such case your application will not have access to Db2 data, but can inform the user or do some other business.

This all comes with some price of managing the connections and plans in your own application.

Now it seems CAF has enough functionality even for advanced users, but wait a minute there is one more attachment facility available.

Imagine your application has similar requirements as in the prior case, but needs even more. What if you need different tasks running under different userids? Imagine a service provider type of application. What if you want to reuse the threads? Or what if you need to use the two phase commit protocol not supported by CAF? This and more is where RRSAF may help.

Again, there is a price. RRSAF is the most complex. It requires RRS (Resource Recovery Services) a component of z/OS to run, you need to manage connections, plans, you also need to sign on. If you are using two phase commit protocol you need to use the RRS services to commit and not the standard COMMIT/ROLLBACK.

RRSAF provides really a great flexibility, which can be handy in very specific cases, but you need to know why and what do you want to achieve.

Let's briefly compare some key attributes of the three aforementioned attachment facilities.

Understanding attachment facilities gives you the insight of what Db2 uses under the hood to communicate with applications. It is worth mentioning that higher level interfaces (such as JDBC, ODBC) use these attachment facilities for connecting local applications.

• IBM documentation
• John Maenpaa’s presentation: DB2 Attachment Facilities, IDUG NA 2010, IDUG EMEA 2009
• Emil Kotrc's presentation: Db2 Attachment Facilities - Advanced Topics, IDUG NA 2023