Where can I find the redistributable version of the IBM DB2 Type 4 driver? There is no need to download the JDBC driver. How to install IBM db2 ODBC driver.

Where can I find the redistributable version of the IBM DB2 Type 4 driver?I suppose this is the driver I would use to connect from a Java app (on windows) to DB2 on the mainframe?

6 Answers

You will not be able to connect to the mainframe with any redistributable JDBC driver. The driver pack consists of the actual type 4 driver (db2jcc.jar) and any number of license files of the form:

where the cisuz bit is variable, indicating the platforms that you're allowed to run on (iSeries, pSeries, System z, LUW and so on).

You're only likely to get cu with any freely distributable pack. You need the z to access DB2 on the mainframe and that's jealously guarded so you'll need to purchase a specific edition of DB2 Connect to get it. I think both PE and EE, the personal and enterprise editions, have this licence file.

Without that license file, the type 4 driver won't even try to talk to the server, you'll get an exception.

paxdiablopaxdiablo

IBM's Fix pack site has the 'IBM Data Server Driver for JDBC and SQLJ' which is nothing but the JDBC type 4 driver. Though the page I pointed to above happens to be the windows page, it's the same type 4 driver for all platforms, as should be expected.

I don't think any user/password is required.

TanktalusTanktalus

There is no need to download the JDBC driver separately it is already shipped with your DB2 product.You can easily find it at this location : C:Program FilesIBMSQLLIBjavadb2jcc.jar

db2jcc.jar is the driver name

You can get the drivers from the IBM site. You will need to have IBM ID and password to login (which you can obtain here). Zip file is about 7 MBs, in contains DB2 9.5 JDBC (type 2/4) and SQLJ drivers. Type 4 drivers are in db2jcc4.jar.However, you won't be able to connect to mainframes with this driver if mainframe is running DB2 for z/OS. To do so, you need at least to purchase DB2 Connect product, which will cost you about $500 minimum.

DasDas

If you're running on an AS/400 (or iSeries, or whatever the heck IBM is calling it these days), you'll probably want to get it from JTOpen.

Their toolbox replaces the old Java Toolbox and includes the JDBC drivers.

J.T. GrimesJ.T. Grimes

If I need any IBM JARs for DB2 or MQ, I usually just add it to the instructions that DB2 or MQ needs to be installed as a prerequisite along with a URL to download it.

The same goes for Java and many other not easily redistributable products as well.

This eliminates the need to worry about licensing issues as it would be on the onus of the user rather than the vendor to obtain the proper licenses.

Archimedes TrajanoArchimedes Trajano

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.

2. 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:

//STEPLIB DD DSN=DSN910.SDSNEXIT,DISP=SHR
// 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,

2. 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

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

DB2INIT:

[COMMON]
MVSDEFAULTSSID=SSID

NOTES:

Ibm Db2 Odbc

• 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}
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 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}
DSNT408I SQLCODE = -922, ERROR: AUTHORIZATION FAILURE: 00D31024 ERROR.
CONNECT
DSNT418I SQLSTATE = 42505 SQLSTATE RETURN CODE

• 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

Ibm Db2 Odbc Driver

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