On z/OS, the ODBC operator can be used to extract data from DB2.  The purpose of this article is to demystify the use of the ODBC operator in conjunction with DB2 on IBM’s z/OS operating system.  

The ODBC operator is a producer operator in TPT that enables universal open data access to retrieve data from many ODBC-compliant data sources. Its intended use is to connect to a non-Teradata data source and extract data from it. When combined with  consumer operators such as the Stream and Update operators, the ODBC operator can extract data from an ODBC data source and send it directly to the consumer operator (i.e. without landing data into intermediate storage before loading it into Teradata).

There are several steps that should be taken to establish a connection to DB2 via the ODBC operator.  The first few steps are operational, the next steps after that are fact gathering, and the final steps are the implementation. 

Creating UNIX User ID’s

First of all, TPT itself has some special UNIX requirements.  Although this is outside the scope of the article, this often causes problems and therefore it will be covered here.  I use the term UNIX here because it has a widely accepted and well understood meaning.  The proper term for UNIX on z/OS is USS (Unix System Services).  

TPT requires User OMVS Segments to be defined.  This step is performed by the system security staff or system programmers. 

  1. The User ID must be correctly set up for (UNIX/USS) access.

In z/OS terms this is called adding a OMVS segment with a valid (UID/GID) and (home dir).

  • If the OMVS segment is not defined the results are undetermined
  • TPT will “hang” when the default OMVS Segment is used because some of the callable services used by TPT such as kill(), pidaffinity(), trace(), and sigqueue() are not supported by IBM when running with the default OMVS segment.
  1. The User ID must have correct "RACF/ACF2/Top Secret" security authorizations to the DBMS and the database in question.

Setup the HOST IBM ODBC Driver

Because ODBC is an optional component it might not have been setup. Therefore the next step is to ensure that the DB2 ODBC driver has been installed and initialized correctly.  This is usually performed by a z/OS System Programmer or DB2 DBA.

The Mainframe HOST DB2 ODBC interface uses a plan named DSNACLI.  Part of the DB2 installation process involves binding this plan and its associated packages (see member DSNTIJCL in SDSNSAMP).

If you want to know more, see:

DB2 V9.1 for z/OS ODBC Guide and Reference (http://publibfp.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNODK12/CCONTENTS?SHELF=dsnshka3&DN=SC18-9850-02&DT=20081113063437

NOTE: The above interface IS NOT the JDBC interface.  Some windows DESKTOP software, (including many IBM applications use JDBC).  Just because your windows software is functional does NOT MEAN the above task has been performed correctly.       

Obtaining Database Identity Information

After the operational requirements have been satisfied we move on to the next step of getting the connection to take place.  We need to obtain the connection information that will identify the database to our TPT application.

This information is used by the TPT ODBC operator and can be provided by a DB2 DBA or z/OS System Programmer:

  1. The IBM DB2 Data Base is a Sub-System.  The Data Base Sub-System ID (SSID) is required.
  2. The Data Source Name of the Data Base is optional.  Some times referred to as the “connection name” or the “location name” (see the following):

The target database (also known as a datasource) for IBM relational database management systems is the “location name“.  This is defined in SYSIBM.LOCATIONS when DDF is configured in the DB2 subsystem.

In many applications, a local database is accessed (DDF is not being used).  In these cases, the local database name is the name that was set during DB2 installation as “DB2 LOCATION NAME” on the DSNTIPR installation panel for the DB2 subsystem.  Your local DB2 administration staff can provide you with this name, or you can use a NULL CONNECT.

With a NULL CONNECT, you connect to the default local database without supplying a database name.  The local data base is identified by the (SSID).

How to define a subsystem to DB2 ODBC

The DB2 subsystem can be identified by specifying the MVSDEFAULTSSID keyword in the common section of the initialization file indicated by the DD-NAME (DSNAOINI). If the MVSDEFAULTSSID keyword does not exist in the initialization file, DB2 ODBC uses the default subsystem name specified in the DSNHDECP load module that was created when DB2 was installed.

The initialization file is identified by adding the DD-NAME (DSNAOINI) to the JCL job STEP where the access is required. 

The DSNHDECP load module is usually link-edited into the *.SDSNEXIT data set.  In this case, the JOBLIB or STEPLIB DD card should include:

//                 DD DSN=DSN910.SDSNLOAD,DISP=SHR

Creating the TPT Job JCL for ODBC   

After obtaining the connection information we can now move on to the final implementation step, creating our TPT Job JCL to execute our ODBC SQL query request.

NOTE: The actual dataset names and the SSID in the following examples are site dependent 

  • Create the TPT Attribute File
  1. Optional TPT Script Attributes

VARCHAR DSNName         = @Data_Source,
VARCHAR UserName        = @DB2UserName,
VARCHAR UserPassword  = @DB2Password,

  1. Corresponding optional TPT Script Variable Assignments

Data_Source  = 'connection-name'
DB2UserName  = 'joeblow‘
DB2Password  = ‘abcd1234‘

  • Add the DB2 libraries to the JOBLIB/STEPLIB

//JOBLIB  DD    ( … )
//               DD   DSN=DSN910.SDSNEXIT,DISP=SHR       <---(SSID) Method-1 <<<
//               DD   DSN=DSN910.SDSNLOAD,DISP=SHR 

  • (SSID Method-2)-->> DB2 Initialization Dataset with the TPT jobstep/procstep override





  • The DSNName attribute is the Data Source Name.  When this is not provided the default local Database identified by the Subsystem ID (SSID) is used (as mentioned above this is called a NULL CONNECT)
  • When the user name and password are not provided the racf credentials of the job and/or the user who submitted the job are used.

Debugging Connection Errors

Invariably when something can go wrong, it will go wrong.  Here are some steps that can be taken and some examples of typical errors that can occur:

  • Set the ODBC Operator script “tracelevel” attribute to “special”

 VARCHAR TraceLevel = 'Special'  

  • DSNACLI Plan Authorization

When the DSNACLI plan is not bound at DB2 installation time, the following error occurs: 

Fatal error received from ODBC driver:
 STATE=58004, CODE=-99999,
 MSG='{DB2 for OS/390}{ODBC Driver}
  CAF "OPEN" failed using DB2 system:DSN9 and PLAN:DSNACLI
  RC=0c and REASON=00f30040


After maintenance is applied to DB2, a connection failure of (-803) indicates the plan needs to be rebound.

  • User needs DB2 and RACF (ACF2) authorization

 Fatal error received from ODBC driver:
 STATE=42505, CODE=-922,
 MSG='{DB2 for OS/390}{ODBC Driver}

  • ODBC Connection Errors

This can occur for two reasons:

  1. The Data Source Name is incorrect or does not exist
  2. The Data Base System is not running or the (SSID) is incorrect

Fatal error received from ODBC driver:
STATE=58004, CODE=-99999,
CAF "CONNECT" failed using DB2 system:DSN9
RC=08 and REASON=00f30002