DB2UDB Enterprise error

neeraj · Post by **neeraj** » Mon Apr 17, 2006 11:42 pm

This is regarding the DB2 Enterprise stage used within PX. The data getting populated within the table is not in proper format.
A sequential file( from server job ) is read in PX job. the data in the sequential file is loaded into the dataset and Inturn this dataset loads data in to the DB2 table. The Db2 stage used here is Enterprise Stage.

There is a source field coming in with Char (8) in the sequential file. When tried to read the file in PX job, not able to view data, since the char field consists of only one chararacter. Hence used varchar and then populated the data within the Dataset after triming as char.. When this data is loaded from Dataset into the Db2 table thru DB2 Enterprise stage the data gets populated but when tried to retrieve data back from the table it says zero records found or Data source empty.
But if the Db2 API stage is used within the DS job then data gets perfectly loaded as well gets retrieved properly. This is explained with an example and the actual job location below.
Project : UKDatawarehouse_75_DEV
Job Location : Jobs/Users/NitinJ/PLM

For example : The DWH_PRAR_LMT table is getting populated with various fields like Arrg_ID , PRAR_LMT_TYCD etc. Within this the PRAR_LMT_TYCD is a Char(8) field. The tycd value getting populated for a arrg_ID is '1' or '2' or '3'. This data gets inserted within the table while using DB2 Enterprise stage but when tried to retrieve back it gives no result ie. zero records found. While if DB2 API Stage is used then the data is perfect.

Select * from DWHD037.DWH_PRAR_LMT fetch first 10 rows only"

ARRG_ID PRAR_LMT_TYCD REC_ACDT REC_EDT REC_TS PRAR_LMT_CHNG_METH PRAR_LMT_STDT PRAR_LMT_ENDT LMT_VAL CCOD PRAR_LMT_STAT LMT_GRNT_DT
-------------------- ------------- ---------- ---------- -------------------------- ------------------ ------------- ------------- ----------------- ---- ------------- -----------
997000000118846 3 07/03/2006 31/12/9999 2006-04-17-14.47.16.544239 - - - 0.00 GBP - -
997000000118836 3 07/03/2006 31/12/9999 2006-04-17-14.47.18.640091 - - - 0.00 GBP - -
997000000118838 3 07/03/2006 31/12/9999 2006-04-17-14.47.19.417904 - - - 0.00 GBP - -

Above we are able to see the value in PRAR_LMT_TYCD as '3' within the table.
Now when retrieved data for the same tycd the result is zero records.

db2 "Select ARRG_ID, PRAR_LMT_TYCD from DWHD037.DWH_PRAR_LMT where ARRG_ID=997000000118846 and PRAR_LMT_TYCD = '3'" <

ARRG_ID PRAR_LMT_TYCD
-------------------- -------------

0 record(s) selected.

IF queried as
db2 "Select ARRG_ID, PRAR_LMT_TYCD from DWHD037.DWH_PRAR_LMT where ARRG_ID=997000000118846 and PRAR_LMT_TYCD like '3%'"

Then

ARRG_ID PRAR_LMT_TYCD
-------------------- -------------
997000000118846 3

1 record(s) selected.

Tried with various trimmings options too , Also this issue arises when the value within the field is less than the length of the field(Character) Or wherever Varchar is used to read the data from the sequential file for that field but only when DB2 Enterprise Stage is used.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 18, 2006 4:00 am

Put a Peek stage on each link in the job to help you to diagnose exactly where any problem is occurring.

ArndW · Post by **ArndW** » Tue Apr 18, 2006 4:28 am

Nitin, I took a look at your job (that project name was too familiar). Your source dataset has the column defined as CHAR(1) while in the database it is declared as a CHAR(8) and you have no modify stage or handler. Also, you are not doing a rejection stage coming out of your DB/2 stage to see if the rows are being rejected for any reasons.