Formatted length of fields & record<declared length-W

ssunda6 · Post by **ssunda6** » Tue Jul 10, 2007 2:12 am

Hi,

I am getting the following warning in datastage director log:

oraInsertData: When checking operator: Formatted length of fields and record (272) less than declared length (273)

oraInsertData: is the name of oracle stage in the job.

In my job, I am reading data using a sequential file, performing transformations using a transformer and then loading data into oracle.

Job is running successfully and the data is also loaded properly into the table. But why is the job giving this warning??

Thank you.

Regards,
ssunda.

ArndW · Post by **ArndW** » Tue Jul 10, 2007 2:53 am

If you go into your favorite SQL tool and do a "desc {YourTable};" you will most likely see that the field is declared with a length of 273, but you only declared it as 272 in your job.

ssunda6 · Post by **ssunda6** » Tue Jul 10, 2007 2:58 am

Hi,

I have checked the table definition. No field is having length of 273.
The file that I am reading is in EBCDIC format.

Regards,
ssunda.

ArndW · Post by **ArndW** » Tue Jul 10, 2007 3:04 am

I just reread your post and notice that the error message is one that refers to a source file stage. Are you 100% certain that this is referring to your output Oracle stage?

ssunda6 · Post by **ssunda6** » Tue Jul 10, 2007 3:12 am

Hi Arnd,

I have copy pasted the error as it is. Yes, it is referring to the oracle stage for the error in the director log .

In the transformer, I am converting some decimal fields to date type( decimal->string->date) and some decimal fields to varchar. Just FYI.

ArndW · Post by **ArndW** » Tue Jul 10, 2007 3:15 am

What kind of a load are you doing in the Oracle stage? How many columns in your definition and is the total length 272 or 273?

ssunda6 · Post by **ssunda6** » Tue Jul 10, 2007 3:39 am

Hi,

I am using "LOAD" option for write method in oracle stage.( So, this will use SQL Loader ). Total there are 24 columns in the Input file and 16 columns in the target table.

Total length is neither 272 nor 273.

The following is the metadata of table.

Code: Select all

BUS_DT                         NOT NULL DATE                                                                                                                                                                                          
STORE_NUM                      NOT NULL NUMBER                                                                                                                                                                                        
PROD_NUM                       NOT NULL VARCHAR2(20)                                                                                                                                                                                  
CSHR_PRTNR_NUM                 NOT NULL NUMBER                                                                                                                                                                                        
LINE_ITEM_TM                   NOT NULL NUMBER(6)                                                                                                                                                                                     
ACTUAL_DT                               DATE                                                                                                                                                                                          
EDIT_CSHR_PRTNR_NUM                     NUMBER                                                                                                                                                                                        
EDIT_ACTUAL_DT                          DATE                                                                                                                                                                                          
INVTY_MKOUT_QTY                         NUMBER                                                                                                                                                                                        
POS_VOID_CD                             VARCHAR2(1)                                                                                                                                                                                   
COGS_LCL_AMT                            NUMBER(19,2)                                                                                                                                                                                  
RETAIL_LCL_AMT                          NUMBER(19,2)                                                                                                                                                                                  
EDIT_LINE_ITEM_TM                       NUMBER(6)                                                                                                                                                                                     
COST_TYPE_CD                            VARCHAR2(1)                                                                                                                                                                                   
COFFEE_WT_PND_MEAS                      NUMBER(5,2)                                                                                                                                                                                   
INVTY_MKOUT_RSN_CD                      VARCHAR2(3)

Regards,
ssunda.

ssunda6 · Post by **ssunda6** » Fri Jul 13, 2007 12:30 am

Hi,

Please see if the following additional info will help to find a solution.

I checked the statistics on oracle table and the statistics are:

Avg Record length is 70.

And column wise also it listed avg column length. The summation value of all the columns length is 77.

But none of these values are matching with the values listed in the waring. (272 or 273).

Any inputs like what might be the problem will help me a lot.

Regards,
ssunda.

ArndW · Post by **ArndW** » Fri Jul 13, 2007 12:37 am

Just for testing sake, can you put a PEEK stage into your job output instead of the Oracle stage and see if the error message persists?

ssunda6 · Post by **ssunda6** » Fri Jul 13, 2007 1:51 am

Hi Arnd,

As told by you, i tested the job by repalcing Oracle stage with PEEK stage.
Now, the warning is not coming.

Regards,
ssunda

ArndW · Post by **ArndW** » Fri Jul 13, 2007 1:56 am

I just wanted to confirm the source of the error, as the message doesn't normally relate to an Oracle write. What happens if you change the Oracle stage to do an UPSERT, does the message also go away?

ray.wurlod · Post by **ray.wurlod** » Fri Jul 13, 2007 2:08 am

Does the source file perhaps come from a Windows system, so that there's a two character line terminator rather than the single character line terminator expected by the "UNIX" record delimiter property setting?

ssunda6 · Post by **ssunda6** » Fri Jul 13, 2007 2:39 am

Arnd,

I tried with UPSERT. No warning when using UPSERT.

Ray,
The input file properties mentioned in the sequential stage are

Record Type =implicit
Field delimiters = none
character set=EBCDIC
Data format=binary
Decimal- allow all zeros=yes

ArndW · Post by **ArndW** » Fri Jul 13, 2007 3:01 am

This is getting odder and odder... Do you have other jobs that use the LOAD method to Oracle that are working without this warning? Might you have changed any of the environment variables related to Oracle?

ssunda6 · Post by **ssunda6** » Fri Jul 13, 2007 3:05 am

Arnd,

I have 6 similar jobs and all them are having this warning(with diff record lengths) when using 'LOAD' option.

I checked the control file created in /opt/etl/scratch directory in unix.
The record length calculated by sql loader is 272.

table definition in the control file is as follows:

Code: Select all

  Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BUS_DT                               1:10    10           DATE YYYY/MM/DD
STORE_NUM                           11:50    40           CHARACTER
PROD_NUM                            51:70    20           CHARACTER
CSHR_PRTNR_NUM                     71:110    40           CHARACTER
LINE_ITEM_TM                      111:114     4           INTEGER
ACTUAL_DT                         115:124    10           DATE YYYY/MM/DD
    NULL if 115:124 = BLANKS
EDIT_CSHR_PRTNR_NUM               125:164    40           CHARACTER
    NULL if 125:164 = BLANKS
EDIT_ACTUAL_DT                    165:174    10           DATE YYYY/MM/DD
    NULL if 165:174 = BLANKS
INVTY_MKOUT_QTY                   175:214    40           CHARACTER
    NULL if 175:214 = BLANKS
POS_VOID_CD                       215:215     1           CHARACTER
    NULL if 215:215 = BLANKS
COGS_LCL_AMT                      216:236    21           CHARACTER
    NULL if 216:236 = BLANKS
RETAIL_LCL_AMT                    237:257    21           CHARACTER
    NULL if 237:257 = BLANKS
EDIT_LINE_ITEM_TM                 258:261     4           INTEGER
    NULL if 258:261 = 0X80000000(character '')
COST_TYPE_CD                      262:262     1           CHARACTER
    NULL if 262:262 = BLANKS
COFFEE_WT_PND_MEAS                263:269     7           CHARACTER
    NULL if 263:269 = BLANKS
INVTY_MKOUT_RSN_CD                270:272     3           CHARACTER
    NULL if 270:272 = BLANKS