[DataStage][SQL Client]Data has been truncated

[Seyed]

All,
When I run a DataStage job to load an Oracle table, the data gets written to the table, but for each record written I get the following warnings:

Code: Select all

SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated

I have already searched this forum, and went through all columns, but can't find any target columns that are too small for the input file. Any suggestions will be greatly appreciated.

Seyed

[ray.wurlod]

Welcome aboard.

I can only suggest search harder. Could it be decimal numbers with rightmost decimal places being truncated?

Perhaps your DBA could enable a higher level of tracing while you solve it.

[chulett]

What kind of target columns do you have? You'll primarily see this with CLOB or unbounded NUMBER fields, from what I recall.

[Seyed]

Welcome aboard.

I can only suggest search harder. Could it be decimal numbers with rightmost decimal places being truncated?

Perhaps your DBA could enable a higher level of tracing while you solve it.

Ray,
Thank you for your response. To isolated the problem, I limited the transforms to only non-null columns. I am listing the transform used for the DataStage job. Maybe you could see something that I missed. I wanted to list the transform in a table to make it easier to see the transform code, but my HTML code didnt' work here.

Again, thank you for your help,

Seyed

Code: Select all

Source Columns:
No.  Source Column                                                           Source Data Type   
 1   DSLink3.CS_CASE_FIN_INPUT_TYPE                                          Char(1)            
 2   Oconv(Iconv(DSLink3.CS_CASE_FIN_END_PERIOD,"DYMD"),"D-YMD[4,2,2]")      Char(8)            
 3   DSLink3.CS_CASE_FIN_CSCAPNUM                                            Char(8)            
 4   DSLink3.CS_CASE_FIN_CSCBPSEQ                                            Char(3)            
 5   DSLink3.CS_CASE_RPT_PER_YR_MO                                           Decimal(6)         
 6   Oconv(Iconv(DSLink3.CS_CASE_FIN_BEGIN_PERIOD,"DYMD"),"D-YMD[4,2,2]")    Char(8)            
 7   DSLink3.CS_CASE_FIN_CSCOFFIC                                            Char(3)            
 8   DSJobStartTimestamp (DS Macro)                                          Timestamp          
 9   "DATASTGE"                                                              Char(8)            
 10  "CSL1CFIN"                                                              Char(8)            
 11  DSJobStartTimestamp (DS Macro)                                          Timestamp          
 12  "DATASTGE"                                                              Char(8)            
 13  "CSL1CFIN"                                                              Char(8)            


Target Columns:
No.  Target Column      Target Data Type 
 1   RPT_PER_TYPE_CD    Char(1) 
 2   RPT_PER_END_DT     Timestamp(19) 
 3   NCP_NUM            Char(9) 
 4   BP_SEQ_NUM         Char(3) 
 5   RPT_PER_YR_MO_NUM  Decimal(38) 
 6   RPT_PER_BGN_DT     Timestamp(19) 
 7   CAS_OFC_CD         Char(3) 
 8   CREATE_DT          Timestamp(19) 
 9   CREATE_USER        Character(8) 
 10  CREATE_PGM         Character(8) 
 11  UPDATE_DT          Timestamp(19) 
 12  UPDATE_USER        Character(8) 
 13  UPDATE_PGM         Character(8) 

[Seyed]

What kind of target columns do you have? You'll primarily see this with CLOB or unbounded NUMBER fields, from what I recall.

Craig,
Thank you for your response. Please see my respond to Ray's comments. In it, I have listed the transform in two sections (source and target). It might help you detect the cause of these warnings.

Thanks again,

Seyed

[ray.wurlod]

The message about data being truncated is coming from the database server (ODBC driver), not from the Transformer stage. Verify that the table metadata correspond to what's in your job. Establish a trace to determine which rows are generating the fault, and isolate the cause by that means.

[ray.wurlod]

Good detective work.

(That's what I meant by suggesting that you "search harder".)