ODBC Connection to DB2 AS400

[Christina Lim]

Dear all,

Our DB2 v7.1 database reside in a AS400 machine while our Data Stage Server resides on a Sun Solaris machine.

Connection to DB2 AS400 through Db2 Plugin works fine.
However, we could not establish a db2 connection through ODBC.

Code: Select all

[MDIDRDA2]
Driver=/datastage/Ascential/DataStage/branded_odbc/lib/VMdb218.so
Description=DataDirect DB2 Wire Protocol Driver
LogonID=test
Password=test
AppCodePage=1252
[b]ServerCharSet=1252[/b]
IpAddress=128.230.01.222
Location=MDIDRDA
Collection=PRSDTA
TcpPort=446
Package=DDODBC
Action=REPLACE
QueryBlockSize=8
CharSubTypeType=SYSTEM_DEFAULT
ConversationType=SINGLE_BYTE
CloseConversation=DEALLOC
UserBufferSize=32
MaximumClients=35
GrantExecute=1
GrantAuthid=PUBLIC
OEMANSI=1
DecimalDelimiter=PERIOD
DecimalPrecision=15
StringDelimiter=SINGLE_QUOTE
IsolationLevel=CURSOR_STABILITY
ResourceRelease=DEALLOCATION
DynamicSections=32
Trace=0
WithHold=0

From the above setting, we got an error message prompting us to change the ServerCharSet from default, 1252 to 37 when trying to import tables from the DataStage Manager.

Data source 'MDIDRDA2' appears to have no Tables

After we've changed to ServerCharSet, 37, we got the above error when importing tables from DataStage Manager.

JobCClientExtractClientDetailsodbc..CLNTPF_N_CLRRPF.read_ClientRecords: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT CHDRNUM FROM PRSDTA.COVTPF
SQLSTATE=S1000, DBMS.CODE=-805
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][DB2]L805 PROGRAM NAME 'PRSDTA .DDODBCA' NOT FOUND IN PLAN ''

The above error is the one we get from the DataStage Director when connecting through ODBC.

Code: Select all

[MDIDRDA2]
Driver=/datastage/Ascential/DataStage/branded_odbc/lib/VMdb218.so
Description=DataDirect DB2 Wire Protocol Driver
LogonID=test
Password=test
AppCodePage=1252
[b]ServerCharSet=37[/b]
IpAddress=128.230.01.222
Location=MDIDRDA
Collection=PRSDTA
TcpPort=446
Package=DDODBC
Action=REPLACE
QueryBlockSize=8
CharSubTypeType=SYSTEM_DEFAULT
ConversationType=SINGLE_BYTE
CloseConversation=DEALLOC
UserBufferSize=32
MaximumClients=35
GrantExecute=1
GrantAuthid=PUBLIC
OEMANSI=1
DecimalDelimiter=PERIOD
DecimalPrecision=15
StringDelimiter=SINGLE_QUOTE
IsolationLevel=CURSOR_STABILITY
ResourceRelease=DEALLOCATION
DynamicSections=32
Trace=0
WithHold=0

The above is the odbc.ini setting.

Has anyone came across these errors?

[denzilsyb]

I havent come across those errors, but this may help..

In DS 7.0.1 you need to bind DB2 packages to the DSN you want to connect to. For info on this, look in the install and upgrade guide (I am not sure this was the case for vers 6)

Before connecting to a DB2 DSN you need to bind DB2 packages to the DSN. The method differs according to whether you are using a wire
protocol driver, or a non-wire protocol driver:

Also - why do you want to use ODBC if the API works? My experience tells me that the API is much faster than the ODBC connections.