Server Job to connect to DB2 on mainframe

[RamonaReed]

Can someone point me to the best tool (or maybe a free tool ) to use to connect a server job to a DB2 table on a mainframe. We actually have DS390, but we are looking at using server jobs to directly connect to the database. (We heard that using DB2 connect is faster than using the DS390 piece). Also can anyone clarifiy this for us, which is better to use?

[peterbaun]

Hi Ramona,

If you are moving data inside the MF environment then using DS390 will be much faster than using the server version of DataStage - at least it was when I tested this a while ago. There is a rather large overhead of first moving data across the network for the server version to process the data and then moving it back again to the mainframe. So if your source and target database both reside on the MF I definately will suggest using the MF version.

If you want to access data from the server version of DS you should be able to use ODBC/DB2 Connect. Normally the problem is to get the apropriate access rights in order to do so.

If you want a "free" solution then unload the data on the mainframe to files, ftp these to the server and process them here (and possibly ftp them back again to be loaded on the MF) - not sure that I will suggest to do so though !

There are also some gateway solutions from iway software that can be used - I don't have any experience with these though (these solutions are not free...).

Regards
Peter

[Carol S Johnson]

If you want to access data from the server version of DS you should be able to use ODBC/DB2 Connect. Normally the problem is to get the apropriate access rights in order to do so.

How is ODBC/DB2 Connect configured ?

One would assume that if DB2Connect has a proven connection from UNIX to DB2 Z/OS, then the only necessary change would be for the .odbc.ini to change to the TCP/IP of the DB2Connect server ( & DB2 port ) where DB2Connect is set for passthru to mainframe DB2. That doesn't seem to work with DataDirect V4.2 ( Ascential build 17) ODBC DB2 wire driver, DB2Connect 8.1, DB2 V7.1 Z/OS, DataStage 5.2 on UNIX, and UNIX AIX 5.1

Connection and data retrieval works without DB2Connect in the picture. We set the .odbc.ini to go directly to the mainframe using the mainframe TCP/IP & DB2 port. Applications are coded with the ODBC stage. Unfortunately, the V4.2.17 DB2 wire driver causes a DSNL511I warning message within the MVS syslog and the DB2 log. DataDirect is aware but fixing this issue will be far in the future.

Because of mainframe log sizes from frequently run DataStage apps, we're looking for an alternative.

[inter5566]

Carol,

We are using the UDB/DB2 stage on a HP-UX server to connect with DB2 on the mainframe using DB2Connect. We are running DS UNIX server version 5.2r5 and 7.1r1 and both are working correctly. As I recall, the connection setup was troublesome but not impossible. The DB2 stage does not require a DSN entry in either the .odbc.ini or uvodbc.config file, all connectivity is handled by DB2Connect. You must have DB2Connect running on your DataStage server, then the DB2Connect catalog is queried by the stage to establish the connection. The DSN you list in the DB2 stage must match the case sensitive value put into the DB2Connect catalog.

HTH and YMMV,
Steve

[Carol S Johnson]

If there is no entry in .odbc.ini nor in uvodbc.config, then the DataDirect ODBC driver would not be used and a client configuration would be called for. There wouldn't seem to be a way for DS to find DB2Connect unless there is something in dsenv to point to DB2Connect. I'll add those 2 entries to dsenv and try again.

BTW, what version of DB2Connect are you using with DataStage 5.2 ?

[inter5566]

Carol,

Here's the entries from our dsenv file:

Code: Select all

# DB2 7.1 PLATFORM SPECIFIC SECTION
DB2DIR=/opt/IBMdb2/V7.1
DB2INSTANCE=db2inst1
INSTHOME=/db2_con/db2inst1
THREADS_FLAG=native

export DB2DIR
export DB2INSTANCE
export INSTHOME
export THREADS_FLAG

SHLIB_PATH=$SHLIB_PATH:$INSTHOME/sqllib/lib:$DB2DIR/lib
PATH=$PATH:$INSTHOME/sqllib/bin:$INSTHOME/sqllib/adm:$INSTHOME/sqllib/misc

As far as version goes, we have version 7.1 installed with service packs added to represent version 7.2

HTH,
Steve

[santosh]

I have a requirement to read DB2 (sitting on Mainframe) table from Datastage Server 7.5.1A ( sitting on UNIX AIX). I can see from existing post that its feasable. As existing post is from 2004 -05 and so just wanted to check if any new feature is available in datastage.

[ray.wurlod]

Nothing new in the server job world.