PART IV - DB2 AND TSO - IBM Mainframe

TSO or Time-Sharing Option is one of the environments from which DB2 data can be accessed. TSO enables the users to interact with MVS using an on-line interface that is either screen or panel driven. The Interactive System Productivity Facility (ISPF) provides the mechanism for communicating by panels, which is the common method for interaction between TSO and application users. The TSO attachment facility provides access to DB2 in two ways:

  • On-line, in TSO foreground, driven by application programs, CLISTs or REXX EXECs coded to communicate with DB2 and TSO, possibly using ISPF panels.
  • In batch mode using TSO terminal monitor program, IKJEFT01, to invoke the DSN command and run a DB2 application program.

DB2 and TSO

DB2 and TSO

The TSO attachment facility operates by means of a communication channel that uses a single thread to direct DB2 calls. Each user can be logged on, in the foreground, to a single TSO address space at any time. IBM provides two on-line TSO applications that can be used to access DB2 data.'DB2l and Catalog Visibility. The DSN command processor establishes the thread that enables TSO to communicate with DB2. An alternative method will be to use Call Attachment Facility (CAF) in TSO to communicate with DB2.

TS0/DB2 Parameters

DB2 is a parameter-driven subsystem. A series of parameters known as DSNZPARMs or ZPARMs, is passed to DB2 when it is started. Of those parameters, two are directly related to TSO and are discussed here. They are IDFORE and IDBACK.

IDFORE controls the number of users that can access DB2 simultaneously from the TSO foreground. The types of DB2 users include the following: DB2I, QMF, users running DSN command, users running TSO/DB2 programs and user running any DB2 tool on-line in foreground TSO. DB2 limits the number of user to the number specified in the IDFORE parameter. When the limit is reached the additional requests are rejected.

IDBACK controls the number of concurrent DB2 batch connections. These connections, are not limited to TSO batch only, they include batch DB2 jobs using DSN command, batch DB2 jobs using CAF, QMF batch jobs, DB2"utilities, etc.

DB2 Access in TSO

DB2 batch programs are executed in the background under the control of the TSO Terminal Monitor Program, IKJEFT01. A TSO session is thereby created in batch. The DSN command is invoked by this session through input specified in the SYSTSIN data set of the JCL. For example the following JCL can be used to run a TSO/DB2 batch job.

DB2 Access in TSO

This JCL invokes the TSO in batch, reads the SYSTSIN input, and invokes the DSN command processor for the DB2P subsystem. Then it runs the program PRG001 using PLAN PLAN001. When the program is complete the DSN session ends.

DB2 Access using Foreground TSO

Another way to access DB2 data is through on-line TSO using DSN command processor. You can issue the following command at the TSO ready prompt or ISPF option 6: DSN SYSTEM (xxxx), where 'xxxx' represent the DB2 subsystem name. This places you under the control of DSN. A prompt labelled DSN is displayed.Tndicating that you are in the middle of the DSN session. You can now issue a DSN sub-command, including the RUN command. Suppose you want to run the program PRGOOl using the plan PLAN001 in the foreground TSO. You issue the following commands:

DB2 Access using Foreground TSO

But a more commonly used method is to use CLIST or REXX EXEC to run the DB2 program than use the DSN commands directly. The CLIST or REXX EXEC can be invoked by the TSO user either directly by entering the name from the ISPF option 6 or the TSO READY prompt. The following figure shows the configuration for an on-line TSO, ISPF driven DB2 application.

tSPF driven DB2 Application Program

tSPF driven DB2 Application Program


DB2I is a series of ISPF panels and CLISTs that can increase the TSO DB2 developer's productivity. DB21 provides many features that can be exploited by the TSO user to query and administer DB2 data. To access the DB2I at the TSO 'READY' prompt type ISPF and get into ISPF. In the ISPF main menu choose DB2I. In the ISPF menu, where DB2I will be depends ontour installation. Contact the system personnel for the information. Once you are in DB21 the main panel is displayed which gives 10 options SPUFI, DCLGEN, PROGRAM PREPARATION, PRECOMP1LE, B1ND/REB1ND/FREE, RUN, DB2 COMMANDS, UTILITIES, DB21 DEFAULTS and EXIT.

The first option of DB21 is SPUFI or 'SQL Processor Using File Input". It reads the SQL statements contained as a text in a sequential file, processes those statements and places the output in the output dataset specified in the browse mode. The screen is self explanatory and the only inputs the user has to give is the input file name where the SQL statements are placed and the output file name where the results have to be placed.


IBM's 'Query Management Facility (QMF)' is an interactive query tool used to produce formatted query output. QMF enables its users to dynamically submit SQL queries, much like SPUFI. QMF can produce formatted reports from your query results using the 'QMF form'. You can type in the SQL query after choosing the 'Query' option (option 6) form the QMF main panel and can type in the SQL statements you want. At the end press PF2 to run the query, the results will be displayed on the screen. This is an excellent way of pre-testing your SQL statements, because QMF checks for the syntax of the SQL statements and give the cost-estimate for running each query. Once the query is displayed you can print it by pressing PF4 or format it using PF9. When PF9 is pressed a default form is generated which edited to suit your needs. Another useful option of QMF is the table editor. You can choose this from the QMF main panel by pressing PF8, which will take you to a screen where you must enter the table name you want to edit, and also whether you wan to add rows or change rows. You can also search for rows based on specified search criteria using this option.

Another object QMF Proc is yet another important feature of QMF. A QMF query can contain only one SQL statement. To execute multiple SQL statements at a time, you can use a QMF Proc. QMF Procs contain QMF commands that are tied together and executed serially.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Mainframe Topics