Using the ODBC Operator on IBM z/OS - Parallel Transporter

Using the ODBC Operator on IBM z/OS

Ensuring that the HOST ODBC Driver is Setup

This task 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).

For more information the DB2 z/OS ODBC Guide and Reference.

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.

Ensuring that UNIX User IDs are Setup

This task is performed by the system security staff or system programmers.

Teradata PT requires that User OMVS Segments are defined.

The userId must be correctly set up for USS (UNIX) access. In MVS terms, this is called adding an OMVS segment with a valid (UID/GID) and (home dir).

Note: If the OMVS segment is not defined, the results are undetermined.

Teradata PT hangs when the default OMVS Segment is used because IBM (when running the default OMVS segment) does not support some of the callable services used by Teradata PT, among them kill(), pidaffinity(), trace(), and sigqueue().

The userId must have correct RACF/ACF2/Top Secret security authorizations to the DBMS and the database in question.

Obtaining Database System Identity Information

Note: The following information, used by the Teradata PT ODBC operator, must be provided by a DB2 DBA or z/OS system programmer:

Because the IBM DB2 database is a subsystem, knowing the subsystem ID (SSID) is required.

Knowing the data source name of the database, referred to as the “connection name” or the “location name,” is in most cases required. The connection or location name is defined in SYSIBM.LOCATIONS when DDF is configured in the DB2 subsystem.

In many applications, when DDF is not being used, a local database is accessed. 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.

When the data source name is not provided, the default local database will be used. The local database is identified by the SSID.

Setting Up the Teradata PT Job JCL for ODBC

To define a subsystem to DB2 ODBC:

Specify the MVSDEFAULTSSID keyword in the common section of the initialization file identified by the DDNAME “DSNOAINI”.

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 DSNHDECP load module is usually link-edited into the *.SDSNEXIT data set. See the example below:

  //STEPLIB DD DSN=DSN910.SDSNEXIT,DISP=SHR

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

To set up a Teradata PT Job JCL for ODBC:

Add the DB2 library to the JOBLIB/STEPLIB, as in the following example:

//JOBLIB DD ( …  )

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

(Optional) Add the DB2 Initialization Dataset with the Teradata PT jobstep/procstep override, as in the following example:

//TPT.DSNAOINI DD DSN=DB2910.INIT(DB2INIT)

DB2INIT:

[COMMON] 

MVSDEFAULTSSID=SSID

Updating the Teradata PT Job Variables File

Set the following variables in the job variables file:

Data_Source  = 'connection-name' 

DB2UserName  = 'johndoe'

DB2Password  = 'abcd1234'

Updating the Teradata PT Job Script

Define the following attributes in the ODBC job script:

VARCHAR DSNName       = @Data_Source,

VARCHAR UserName      = @DB2UserName,

VARCHAR UserPassword  = @DB2Password,

Note: The DSNName is the data source name. When this is not provided, the default local database identified by the SSID is used. When the user name and password are not provided the RACF credentials of the job and/or the name of the user who submitted the job are used.

Debugging Connection Errors

To debug connection errors, set the ODBC Operator script "tracelevel" to "special" in the ODBC job script, as follows:

VARCHAR TraceLevel = 'Special' 

The following are common connection errors:

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}

  SQLSTATE=58004  ERRLOC

  CAF "OPEN" failed using DB2 system:DSN9 and PLAN:DSNACLI

  RC=0c and REASON=00f30040

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

When you have not obtained DB2 and RACF (ACF2) authorization, the following error occurs:

Fatal error received from ODBC driver:

              STATE=42505, CODE=-922,

              MSG='{DB2 for OS/390}{ODBC Driver}

 DSNT408I SQLCODE = -922, ERROR:  AUTHORIZATION FAILURE: 00D31024   ERROR.

          CONNECT

 DSNT418I SQLSTATE   = 42505 SQLSTATE RETURN CODE 

If ODBC does not connect, the following error occurs:

Fatal error received from ODBC driver:

STATE=58004, CODE=-99999,

MSG='{DB2 FOR OS/390}{ODBC DRIVER}  SQLSTATE=58004  ERRLOC=2:170:9

CAF "CONNECT" failed using DB2 system:DSN9

RC=08 and REASON=00f30002

This can occur for two reasons:

The Data Source Name is incorrect or does not exist.

The Data Base System is not running.