DB2 Load Error

antonyraj.deva · Post by **antonyraj.deva** » Wed Jun 23, 2010 4:51 am

Hi All,

I'm getting the following error messages when I try to load into a DB2 Table.

Fatal main_program: PATH search failure: [sil/step_il.C:1310]
Fatal main_program: Error loading "orchdb2op": Could not load "orchdb2op": Not a directory. [api/opreg.C:287]
Fatal main_program: Could not locate operator definition, wrapper, or Unix command for "db2write"; please check that all needed libraries are preloaded, and check the PATH for the wrappers [sil/step_il.C:603]
Fatal main_program: PATH search failure: [sil/step_il.C:1310]
Fatal main_program: Error loading "orchdb2op": Could not load "orchdb2op": Not a directory. [api/opreg.C:287]
Fatal main_program: Could not locate operator definition, wrapper, or Unix command for "db2write"; please check that all needed libraries are preloaded, and check the PATH for the wrappers [sil/step_il.C:603]

My job design is
Sequential File ---> Transformer ----> DB2 Enterprise

The transformer is used for mapping the Source with Target.

Kindly requesting assistance with this issue.

Thanks in advance....

ray.wurlod · Post by **ray.wurlod** » Wed Jun 23, 2010 5:01 am

Check the "job starting" event in the job log, particularly the PATH and LIBPATH environment variables*. Have the DB2 components been removed from these?

* or LD_LIBRARY_PATH or SHLIB_PATH, as appropriate.

mk_ds09 · Post by **mk_ds09** » Wed Jun 23, 2010 5:17 am

Hi

Is this first job you have designed which is writing to the database.

If not, can you check the database related parameters from the previously designed jobs and check if you are passing any incorrect value..

If this is first job, then it seems that the there is issue with the database connection. Can you check the database related parameters DB2_INSTANCE_HOME...Can you write a simple job to read from the given database, if that is successful then u can use those parameters.

And final option will be to raise with your DBA team , if you are not able to read and write from the database..

Hope this helps..

antonyraj.deva · Post by **antonyraj.deva** » Wed Jun 23, 2010 5:41 am

Hi Ray,

I've set the LIBPATH variable with the lib64 libraries for DB2 and also the APT_DBINSTANCE_HOME & APT_DBNAME.

The job starting messages are as follows

Starting Job DBSWRT_LOAD_BKP.
Environment variable settings:

_=/usr/bin/nohup
A__z=! LOGNAME
APT_COMPILEOPT=-O -q64 -c
APT_COMPILER=/usr/vacpp/bin/xlC_r
APT_CONFIG_FILE=/data/ds/Projects/IBM/InformationServer/Server/Configurations/default.apt
APT_DB2INSTANCE_HOME=/home/hmi01pu1
APT_DBNAME=DSTAGE
APT_DISABLE_COMBINATION=1
APT_DUMP_SCORE=1
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_LINKER=/usr/vacpp/bin/xlC_r
APT_LINKOPT=-G -q64
APT_MONITOR_MINTIME=10
APT_MSG_FILELINE=1
APT_NO_ONE_NODE_COMBINING_OPTIMIZATION=1
APT_OPERATOR_REGISTRY_PATH=/data/ds/Projects/IBM/InformationServer/Server/Projects/sabb_hor/buildop
APT_ORCHHOME=/data/ds/Projects/IBM/InformationServer/Server/PXEngine
APT_PM_SHOW_PIDS=1
APT_PM_SHOWRSH=1
APT_RECORD_COUNTS=1
APT_STARTUP_STATUS=1
ASBHOME=/data/ds/Projects/IBM/InformationServer/ASBNode
AUTHSTATE=compat
BELL=^G
CLASSPATH=/home/db2inst2/sqllib/java/db2java.zip:/home/db2inst2/sqllib/java/db2jcc.jar:/home/db2inst2/sqllib/java/sqlj.zip:/home/db2inst2/sqllib/function:/home/db2inst2/sqllib/java/db2jcc_license_cu.jar:.
DB2INSTANCE=db2inst2
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DS_USERNO=-20546
DSHOME=/data/ds/Projects/IBM/InformationServer/Server/DSEngine
DSIPC_OPEN_TIMEOUT=30
FLAVOR=-1
HOME=/home/dsadm
ISFSHMID=454033432
LANG=en_US
LC__FASTMSG=true
LDR_CNTRL=MAXDATA=0x60000000@USERREGS
LIBPATH=/data/ds/Projects/IBM/InformationServer/Server/Projects/sabb_hor/RT_BP64.O:/data/ds/Projects/IBM/InformationServer/Server/DSComponents/lib:/data/ds/Projects/IBM/InformationServer/Server/DSComponents/bin:/data/ds/Projects/IBM/InformationServer/Server/DSParallel:/data/ds/Projects/IBM/InformationServer/Server/PXEngine/user_lib:/data/ds/Projects/IBM/InformationServer/Server/PXEngine/lib:/data/ds/Projects/IBM/InformationServer/Server/Projects/sabb_hor/buildop:/data/ds/Projects/IBM/InformationServer/Server/branded_odbc/lib:/data/ds/Projects/IBM/InformationServer/Server/DSEngine/lib:/data/ds/Projects/IBM/InformationServer/Server/DSEngine/uvdlls:/data/ds/Projects/IBM/InformationServer/ASBNode/apps/jre/bin:/data/ds/Projects/IBM/InformationServer/ASBNode/apps/jre/bin/classic:/data/ds/Projects/IBM/InformationServer/ASBNode/lib/cpp:/data/ds/Projects/IBM/InformationServer/ASBNode/apps/proxy/cpp/aix-all-ppc_64:/sysp/DataStage/IBM/db2/V9/lib64/libdb2.a:/usr/lib:/lib
LOCPATH=/usr/lib/nls/loc
LOGIN=dsadm
LOGNAME=dsadm
MAIL=/usr/spool/mail/dsadm
MAILMSG=[YOU HAVE NEW MAIL]
ODBCINI=/data/ds/Projects/IBM/InformationServer/Server/DSEngine/.odbc.ini
ODMDIR=/etc/objrepos
OSH_DUMP=1
OSH_ECHO=1
OSH_EXPLAIN=1
OSH_PRINT_SCHEMAS=1
OSH_STDOUT_MSG=1
PATH=/data/ds/Projects/IBM/InformationServer/Server/Projects/sabb_hor/wrapped:/data/ds/Projects/IBM/InformationServer/Server/Projects/sabb_hor/buildop:/data/ds/Projects/IBM/InformationServer/Server/Projects/sabb_hor/RT_BP64.O:/data/ds/Projects/IBM/InformationServer/Server/DSComponents/lib:/data/ds/Projects/IBM/InformationServer/Server/DSComponents/bin:/data/ds/Projects/IBM/InformationServer/Server/DSParallel:/data/ds/Projects/IBM/InformationServer/Server/PXEngine/user_osh_wrappers:/data/ds/Projects/IBM/InformationServer/Server/PXEngine/osh_wrappers:/data/ds/Projects/IBM/InformationServer/Server/PXEngine/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/dsadm/bin:/usr/bin/X11:/sbin:.:/home/db2inst2/sqllib/bin:/home/db2inst2/sqllib/adm:/home/db2inst2/sqllib/misc:/usr:/usr/vacpp:/usr/vacpp/bin
PIDTOK=909658
PWD=/data/ds/Projects/IBM/InformationServer/Server/DSEngine
PX_DBCONNECTHOME=/data/ds/Projects/IBM/InformationServer/Server/DSComponents
SHELL=/usr/bin/ksh
TERM=
TZ=SAUST-3
UDTBIN=/data/ds/Projects/IBM/InformationServer/Server/DSEngine/ud41/bin
UDTHOME=/data/ds/Projects/IBM/InformationServer/Server/DSEngine/ud41
USER=dsadm
WHO=sabb_hor

Parallel job initiated

Server:ORADEV01
Project:sabb_hor
Job No:64
Job name:DBSWRT_LOAD_BKP
Invocation:
Event Number:23
Event type:Info
User:dsadm
Timestamp:6/23/2010 11:54:23 AM
Message Id:DSTAGE_RUN_I_0470
Message:
OSH script
# OSH / orchestrate script for Job DBSWRT_LOAD_BKP compiled at 11:51:33 23 JUN 2010
#################################################################
#### STAGE: Sequential_File_0
## Operator
import
## Operator options
-schema record
{final_delim=end, delim=',', quote=double}
(
DBIQID:uint32;
DBSORW:uint32;
I002:nullable ustring[max=255];
I003:nullable ustring[max=255];
I004:nullable ustring[max=255];
I005:nullable ustring[max=255];
I006:nullable ustring[max=255];
I007:nullable ustring[max=255];
)
-file '/data/ds/Projects/Sample/Other/DBSWRT_SPLIT.txt'
-rejects continue
-reportProgress yes
## General options
[ident('Sequential_File_0'); jobmon_ident('Sequential_File_0')]
## Outputs
0> [] 'Sequential_File_0:DSLink2.v'
;
#################################################################
#### STAGE: Transformer_5
## Operator
transform
## Operator options
-flag run
-name 'V0S5_DBSWRT_LOAD_BKP_Transformer_5'
## General options
[ident('Transformer_5'); jobmon_ident('Transformer_5')]
## Inputs
0< [] 'Sequential_File_0:DSLink2.v'
## Outputs
0> [] 'Transformer_5:DSLink6.v'
;
#################################################################
#### STAGE: DB2_UDB_Enterprise_10
## Operator
db2write
## Operator options
-db_cs [&DSProjectMapName]
-table 'DSADM.APPLICATION_MASTER'
-drop
-mode replace
## General options
[ident('DB2_UDB_Enterprise_10'); jobmon_ident('DB2_UDB_Enterprise_10')]
## Inputs
0< [] 'Transformer_5:DSLink6.v'
;
# End of OSH code

Parallel job default NLS map ISO-8859-1, default locale OFF
Advanced runtime options used: -default_date_format "%mm/%dd/%yyyy"
main_program: IBM WebSphere DataStage Enterprise Edition 8.1.0.5040
Copyright (c) 2001, 2005-2008 IBM Corporation. All rights reserved
main_program: conductor uname: -s=AIX; -r=3; -v=5; -n=oradev01; -m=00CBED0A4C00
main_program: orchgeneral: loaded
orchsort: loaded
orchstats: loaded
Server:ORADEV01
Project:sabb_hor
Job No:64
Job name:DBSWRT_LOAD_BKP
Invocation:
Event Number:29
Event type:Info
User:dsadm
Timestamp:6/23/2010 11:54:25 AM
Message Id:IIS-DSEE-TCOS-00021
Message:
main_program: Echo:
import
-schema record
{final_delim=end, delim=',', quote=double}
(
DBIQID:uint32;
DBSORW:uint32;
I002:nullable ustring[max=255];
I003:nullable ustring[max=255];
I004:nullable ustring[max=255];
I005:nullable ustring[max=255];
I006:nullable ustring[max=255];
I007:nullable ustring[max=255];
)
-file '/data/ds/Projects/Sample/Other/DBSWRT_SPLIT.txt'
-rejects continue
-reportProgress yes
[ident('Sequential_File_0'); jobmon_ident('Sequential_File_0')]
0> [] 'Sequential_File_0:DSLink2.v'
;
transform
-flag run
-name 'V0S5_DBSWRT_LOAD_BKP_Transformer_5'
[ident('Transformer_5'); jobmon_ident('Transformer_5')]
0< [] 'Sequential_File_0:DSLink2.v'
0> [] 'Transformer_5:DSLink6.v'
;
db2write
-db_cs ISO-8859-1
-table 'DSADM.APPLICATION_MASTER'
-drop
-mode replace
[ident('DB2_UDB_Enterprise_10'); jobmon_ident('DB2_UDB_Enterprise_10')]
0< [] 'Transformer_5:DSLink6.v'
;
. main_program: PATH search failure: [sil/step_il.C:1310]

Thanks in advance.

antonyraj.deva · Post by **antonyraj.deva** » Wed Jun 23, 2010 5:52 am

Hi MK,

This is the first job to load the database and as of today there is no data to read from this database.

I'm able to connect to database from "DSSH" shell and also I've imported the metadata from the database from the designer client.

The DB2_INSTANCE_HOME was set after getting the values from the DBA.

I'm really unsure where I'm missing out.

Thanks

chulett · Post by **chulett** » Wed Jun 23, 2010 7:00 am

There are several DB2 specific configuration steps you need to take before you can access DB2 for the first time... and they should all be documented.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 23, 2010 5:05 pm

There is a DB2 Configuration guide in the documentation set.

Note, though, that it does not contain any information about the DB2 Connector stage. Patch JR36509 (applied after 8.1 FP1) includes missing documentation on this and the Oracle Connector and Distributed Transaction stages.

antonyraj.deva · Post by **antonyraj.deva** » Thu Jun 24, 2010 7:40 am

Thanks Craig & Ray,

I tried to find the patch JR36509 in http://www-01.ibm.com/support/docview.w ... wg21406224 but in vain.

I've asked the Server Administrator to download the fix pack and install it first.

In the mean time I had a look into the Version.xml file in the server and got a feel that some DB2 related values are not set right.

I herewith attach the entries related to DB2

Code: Select all

<Property name="DB2AdminGroupName" value="dasadm1" />
  <Property name="DB2AdminUserHomeDirectory" value="/home/dasusr2" />
  <Property name="DB2AdminUserName" value="dasusr2" />
  <Property name="DB2InstallLocation" value="/sysp/DataStage/IBM/db2/V9" />
  <Property name="DB2FencedUserGroupName" value="db2fadm1" />
  <Property name="DB2FencedUserHomeDirectory" value="/home/db2fenc2" />
  <Property name="DB2FencedUserName" value="db2fenc2" />
  <Property name="DB2InstanceGroupName" value="db2fadm1" />
  <Property name="DB2InstanceHomeDirectory"
value="/home/db2inst2" />
  <Property name="DB2InstanceName" value="db2inst2" />
  <Property name="DB2InstancePortNumber" value="50000" />
  <Property name="DB2InstanceUserName" value="db2inst2" />
  <Property name="DB2V9Found" value="false" />

There are few things which I felt might be causing the issue.

1. The APT_DB2INSTANCE_HOME value is set to "/home/hmi01pu1" but the "echo $DB2INSTANCEHOME" returns "/home/db2inst2"

2. The default database is set to be "DSTAGE" but it resides in a different server

3. The port number set by the Server Administrator is "50000" and the port number specified by the Database Administrator is "60060".

4. The DB2AdminUserName is mentioned as "dasusr2" whereas the userid I was asked to connect with is "dsadm"

5. The DB2InstanceUserName is "db2inst2" which again should be "dsadm" I guess.

Kindly requesting to let me know of your opinion on these.

Thanks again....

antonyraj.deva · Post by **antonyraj.deva** » Mon Jun 28, 2010 5:04 am

Hi All,

I'm unable to find the correct fix pack from the IBM website.

The version is 8.1.0.0. Please provide me the link if anyone has it.

Thanks in advance.

chulett · Post by **chulett** » Mon Jun 28, 2010 5:39 am

A direct download link doesn't exist for everything. As far as I know, there's still some that you need to request of your official support provider so that they can make it available for you.

antonyraj.deva · Post by **antonyraj.deva** » Mon Jun 28, 2010 6:10 am

Thanks Craig. I'll speak to the support provider and once the fix pack is installed, I'll test the DB2 again.

Bongo51 · Post by **Bongo51** » Mon Jun 28, 2010 8:22 am

Hi,

To downoad a specific patch you can try Fixcentral. However I beleive you may have to have a n account to download the patch. here is the link:

http://www-933.ibm.com/support/fixcentral/