What is the best way to stop a DB2 Load job running?

[Daddy Doma]

Hi Guys,

We are trying to use the DB2 Load function for our tables and often get problems with the jobs hanging. Looking at the db2 log:

Code: Select all

Type                             = LOAD
Database Name                    = DEDW
Partition Number                 = 0
Description                      = OFFLINE LOAD ASC AUTOMATIC INDEXING INSERT COPY NO EDW_IDS_DVP.PARTY
Start Time                       = 16-03-2007 12:53:44.246125
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
   Phase Number                  = 1
      Description                = SETUP
      Total Work                 = 0 bytes
      Completed Work             = 0 bytes
      Start Time                 = 16-03-2007 12:53:44.246141
   Phase Number [Current]        = 2
      Description                = LOAD
      Total Work                 = 0 rows
      Completed Work             = 0 rows
      Start Time                 = 16-03-2007 12:55:20.725991
   Phase Number                  = 3
      Description                = BUILD
      Total Work                 = 2 indexes
      Completed Work             = 0 indexes
      Start Time                 = Not Started

Our DBA helped us identify that this problem occurs when our load jobs are trying to process a dataset that has no records in it. When this happens the job either hangs or returns the following:

Code: Select all

DB_IDS_Table_ins,0: DB2 Load Binary Error.
DB_IDS_Table_ins,0: SQLCODE = -911; SQLSTATE=40001/data/edw/Ascential/DataStage/Scratch
DB_IDS_Table_ins,0: SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001

Again we have noticed a pattern where this occurs due to no data in the dataset.

My question is, what is the best way to check if a dataset has data in it, and then use that to control whether a load job runs?

Pre-warning, we are already trying to use with limited success the dsrecords command to evaluate datasets. All this returns is a warning that the dataset does not exist, when we can clearly find it and browse it using Dataset Management...

[ray.wurlod]

Please post the exact warning.

Check that you are providing the correct pathname for dsrecords and the correct pathname for the xyz.ds file - that is, the Data Set control file.

Code: Select all

$APT_ORCHHOME/bin/drecords -n /opt/etl/.../xyz.ds

[Daddy Doma]

The exact message we get is:

Code: Select all

testDS_PrepLoad_Batch..JobControl (@countEpisode): Executed: dsrecords /data/edw/dev_01a/processing/EDW_IDS_DVP.EPISODE.ref.ds
Reply=137
Output from command ====>
ld.so.1: dsrecords: fatal: liborchsun4.so: open failed: No such file or directory
Killed 

All good when run from command line:

Code: Select all

-bash-3.00$ dsrecords /data/edw/dev_01a/processing/EDW_IDS_DVP.EPISODE.ref.ds
0 records
-bash-3.00$

We've been working with IBM support and checking all our environment library paths, etc... Still nothing resolved...

[ray.wurlod]

Thta suggests that the problem might be with LD_LIBRARY_PATH (or whatever your library search path environment variable might be called) not including the parent directory of liborchsun4.so for DataStage processes but including it for yours.

Check the second event in the job log, where the environment variables are listed. Check whether the library search path includes the parent directory of liborchsun4.so.

If it's not there, edit dsenv so that it will be there in future.

[Daddy Doma]

We thought we had looked at this. liborchsun.so is stored in our $PXHome/lib path, which is /data/edw/Ascential/DataStage/PXEngine.752.1./lib

Our LD_LIBRARY_PATH in the Director log shows

Code: Select all

/data/edw/Ascential/DataStage/DSEngine/java/jre/lib/sparc/client:/data/edw/Ascential/DataStage/DSEngine/java/jre/lib/sparc:/data/edw/Ascential/DataStage/branded_odbc/lib:/data/edw/Ascential/DataStage/DSEngine/lib:/data/edw/Ascential/DataStage/DSEngine/uvdlls::/opt/IBM/db2/V9.1/lib32:/usr/lib:/lib: /data/edw/Ascential/DataStage/PXEngine.752.1/lib

Spot the problem? The space between the : and our pathname. Total n00b mistake! Important lesson for all developers; formatting counts!