Outline

• Environment which connect to DB2
• Attachment Facility Overview
• TSO
• DSN
• CAF
• RRSAF
• CICS
• IMS
• DDF

Introduction

• Tuesday, we were talking about different z/OS subsytems which are used in DB2.
• We had already looked at storage subsystems, so we began last day talking about IRLM to do locking.
• Then we talked about DB2's use of RACF for access control.
• Finally, we talked about DB2 in a Parallel Sysplex Environment.
• Today, we are going to review and continue a topic we hit at the end of Tuesday. Namely, the DB2 Attachment Facilities...
• Our goal will be to understand how application connect to DB2 from various major environments such as TSO, Batch, and CICS.

Environments Connecting to DB2

• Applications running in many different environments may have a need to access DB2.
• On the z/OS system, these applications may be running in one of the following environments:
  • TSO -- TSO stands for Time Sharing Option. TSO is a z/OS subsystem which enables users to interact with z/OS using the Interactive System Productivity Facility, or ISPF, for interacting between TSO applications and users.
  • z/OS batch
  • CICS -- CICS stands for Customer Information Control System. CICS is an IBM online transaction processing system that is used by major banks in the world.
  • IMS -- IMS stands for Information Management System. IMS is a premier transaction and hierarchical database management system offered by IBM.
  • WebSphere -- a web application server .
  • Remote -- The application can be even running on a remote system, which may not even be a z/OS platform.

Issues that Arise from Different Environment Accessing DB2

• Cross boundary -- TSO, batch, CICS, or IMS run in a different address space from DB2. DB2 needs to determine how to bridge data from a different address space.
• Security -- DB2 needs to decide if it can trust an incoming request
• Resource recovery coordination -- Resources might be changed in both the application environment, for example some data sets, and in DB2 tables. Someone needs to be in charge to preserve data integrity.

Attachment Facility Overview

• An attachment facility is DB2 code that bridges the boundary between the application address space and the DB2.
• It is a DB2 component that runs under the application's address space.
• It enables applications to connect to and use DB2 to process SQL statements, commands, or calls to the Instrumentation Facility Interface (IFI).

DB2 Thread

• A DB2 application needs to connect to DB2 via an attachment facility.
• In addition, a thread must be established for each embedded SQL program for DB2 to process this applications request.
• A DB2 thread is a set of internal control blocks which DB2 uses to track a user's work.
• A thread, if not explicitly created, is created when the first SQL in an application program is executed.
• The thread is used to send SQL requests to DB2 and to send data from DB2 to the application. The latter is sent along with the execution status in the SQLCA.

TSO (Time-Sharing Option)

• TSO is an interactive time-sharing environment for z/OS system.
• TSO enables many users to access z/OS concurrently, with each thinking that he is the only user on the system.
• TSO interacts with users either in command prompt mode or panel driven mode.
• Further, TSO can be run either interactively or in batch mode.
• In batch mode, TSO line commands are executed as a group by running the TSO Terminal Monitor Program (TMP) - the IKJEFT1B program.

TSO Attachment Facility (TSOAF)

• The TSO attachment facility is used by TSO applications to get an implicit connection to DB2
• It is implicit in that the application has no control over this connection: The DB2 Thread is created on the first execution of an SQL statement or a DB2 command and the thread is terminated when the application program ends.
• TSOAF includes DSN and DB2I as its front-end for the users.
• To use the TSOAF, an application needs to be link-editted with DSNELI.
• In an application, all embedded SQL statements are converted to call DSNHLI by the DB2 pre-compiler regardless of the attachment facility used.
• Information passed in this call includes program name, section number which is generated by the pre-compiler, and timestamps.

DSN

• DSN is a TSO command processor that runs with the TSO terminal monitor program (TMP).
• Because the TMP runs in either foreground or background, DSN applications run either interactively or as batch jobs.
• DSN is the front-end for using the TSO attachment services to access DB2.
• You can use the DSN command processor during program development to perform the following functions:
  • Bind -- BIND PACKAGE builds an application package; BIND PLAN builds an application plan.
  • Run -- RUN executes an application program which might contain SQL commands.
  • DCLGEN -- The declarations generator (DCLGEN) produces an SQL DECLARE TABLE statement and a COBOL, PL/I, or C data declaration for a table or a view named in the DB2 catalog.
  • SPUFI -- SPUFI (SQL Processor Using File Input) is used to run dynamic SQL statements. It reads SQL statements contained as text in a sequential file, processes those statements, and places you in an ISPF (Interactive System Productivity Facility) browse session to view the results. SPUFI provides a quick way to run dynamic SQL statements.

DSN -- Sample Usage for Subcommands

• The above is an example JCL program which is written for the batch environment which would run under the TSO Terminal Monitor Program (TMP) in background mode.
• The // above are not comments. They actually say JCL things.
• For instance, the procedure above is called DSNHC. It is used to pre-compile and compile a C source program, and perform a link-edit to generate an application load module. A DBRM (database request module) is also generated in this process.
• The example illustrates that when an application is link-edited, the DSNELI library needs to be included in the load module.
• Notice also that one starts saying DSN commands in a SYSTSIN DD card

DSN -- Sample Usage for z/OS Commands

This is an example of using DSN to issue z/OS console commands:

• STA -- start trace of Performance class 6 and 7; output to SMF (system management facility).
• DIS -- display what class of trace is enabled.

DB2I -- DB2 Interactive

• DB2 Interactive is an online, TSO/ISPF-based interface to DB2. Internally, DB2I invokes DSN to process the commands.
• DB2I has main panel options for the following:
  • SPUFI
  • DCLGEN
  • Program preparation -- prepare DB2 application to run
  • BIND
  • RUN
  • DB2 console commands
  • Utilities

DB2I -- Screenshot

Call Attachment Facility (CAF)

• An online TSO/DB2 program can tie up a thread for a long time if the user spends time thinking about his next input.
• If the program uses the Call Attach Facility (CAF) to create a thread for each user's input action, the thread is terminated before the next panel or prompt appears.
• i.e., if we are using CAF we follow the model: get user input, make connection, do action, get result, close connection, go to next panel.
• CAF saves resources on inactive threads; however, there is an extra thread creation and termination overhead for each user action.
• Programs that run in z/OS batch, TSO foreground, and TSO background can use CAF.
• CAF is invoked by DSNALI call. This call can issue one of the following five commands: CONNECT, DISCONNECT, OPEN (a thread), CLOSE (a thread), TRANSLATE (formats SQL messages in SQLCA)

Resource Recovery Services Attachment Facility (RRSAF)

• The Resource Recovery Services Attach Facility (RRSAF) has a similar functionality to CAF.
• However, if RRSAF is used, an application can reuse threads for different user id using the SIGNON function.
• Further, an application can act as the transaction manager to control the atomicity of the changes made via the application environment and the DB2 subsystem.
• To use RRSAF, an application needs to be link edited with DSNRLI.
• Applications can invoke DSNRLI to use the following major functions: IDENTIFY (caller task), SIGNON (establish primary ID and secondary IDs), CREATE THREAD, TERMINATE THREAD, TERMINATE IDENTIFY, TRANSLATE.

CICS on z/OS

• CICS stands for Customer Information Control System. CICS is a transaction server that primarily runs on z/OS.
• Most of CICS applications are pseudo-transactional and come in two flavors: conversational and non-conversational.
• The former includes a conversation between CICS and its end-user
• A non-conversation transaction, by contrast, processes one input, responds, and ends. It doesn't get input from the terminal.
• CICS applications can be written in numerous programming languages, including COBOL, PL/I, C, C++, IBM assembly language, REXX, and JAVA.
• A CICS/DB2 application needs to be preprocessed twice.First, you preprocess to handle EXEC SQL directives. The the CICS command translator needs to be run to handle EXEC CICS directives. Finally, the regular high-level language program compilation can be done.

CICS Attachment Facility

• A CICS region is a named collection of resources controlled by CICS as a unit.
• Using the CICS Attach facility, one CICS region can only be connected to one DB2 subsystem at a time. But a DB2 subsystem can be accessed by multiple CICS regions.
• A CICS region that is connected to DB2 might make use of several threads from CICS to DB2.
• CICS transactions requiring DB2 resources enter DB2 via the CICS attachment facility, of which the entry module is DSNCLI.
• This attachment facility creates a new thread or it re-uses an existing thread for the CICS transaction to access DB2.

Types of CICS-DB2 Threads

There are three main kinds of CICS-DB2 threads:

• Command threads -- These are threads are used to process DSNC commands. DSNC is a CICS transaction which processes commands targeted for DB2.
• Entry threads -- aka dedicated threads. An entry thread is associated with a DB2 plan. Multiple CICS transactions, which use the same application plan, can be grouped in the CICS Resource Control Table (RCT) to use the same entry thread.
• Pool threads -- Pool threads are used when the defined command threads or entry threads are exhausted.

Two-Phase Commit

• DB2 and CICS often must agree on when a transaction is committed.
• When DB2 is invoked under CICS, DB2 is under the transaction management of CICS.
• In this setting, the "transaction control" SQL statements such as COMMIT and ROLLBACK are not valid statements.
• Instead, a two-phase process is used to agree on when a commit has occurred.

Two-Phase Commit -- continued

• The start of this process is when a CICS SYNCPOINT is requested in a CICS/DB2 program.
• In the first phase, the participants then write all the log records that would be needed to do a commit.
• If it looks like the transaction can commit a participant sends an agreement message; otherwise, an abort message is sent.
• In the second phase, if the CICS coordinator gets an agreement from all participants it sends out a commit message to everyone, if it then gets an acknowledgment back from each participant, it commits.
• If in the first phase somebody aborts, then the coordinator sends out a roll back message and each member rolls back its logs for that transaction.
• If a system fails during this process, on restart the commit status can be uncertain for some transactions. These are called the in-doubt transactions.
• When DB2 is restarted most in-doubt transactions can be resolved automatically. If not, you can use the RECOVER INDOUBT command to commit or roll back the pending changes.

IMS and IMS Attachment Facility

• IMS consists of two main subsystems: (1) a transaction processing system -- IMS/TM and (2) a hierarchical database management system -- IMS/DB
• IMS does not use SQL as its language interface. Instead it uses a language called DL/1
• IMS connections to DB2 are similar to CICS connections to DB2, but are more flexible and complicated.

DSNHLI Resolved in Link-Edit

• As we mentioned before, SQL statements are converted to calls to DSNHLI in embedded applications.
• The DSNHLI entry point is resolved during link-edit to include the appropriate language interface (LI) modules
• For TSOAF, this is resolved to DSNELI. DSNELI is the alias for DSNHLI in the DB2 libraries. Therefore there is no need to include DSNELI specifically in the link-edit.
• For CAF, one must specifically include DSNALI to avoid a default to TSOAF.
• Similarly for RRSAF, one must specifically include DSNRLI to avoid a default to TSOAF.
• CICS AF must specifically include DSNCLI in the link-edit phase.
• The IMS library includes DSFLI000 as an alias to DSNHLO. There is no need to specifically include DFSLI000 in the link-edit commands.

Remote Access to DB2

• As mentioned earlier, an application running in a remote system, which does not have to be a z/OS platform, may need to access DB2 data.
• To do this the DRDA (Distributed Relational Database Architecture) protocols might be used.
• DRDA is a set of protocols, or rules, that enable a user to access distributed data regardless of where it physically resides.
• The distributed data facility (DDF) is IBM's implementation of DRDA.
• It allows client applications that run in an environment that supports DRDA to access data at DB2 servers.
• DDF supports TCP/IP and Systems Network Architecture (SNA) network protocols.
• DDF allows the DB2 server to act as a gateway for remote clients and servers. A DB2 server can forward requests on behalf of remote clients to other remote servers.
• DRDA and DDF will be covered in more detail later in the semester.