Monday, 19 September 2016

#1 DataStage Solutions to Common Warnings/Errors - Datatype

Warnings/Errors Related to Datatype

This Warnings/Errors described in this section are based on Data type like data type mismatch, Column length variations.
Few common Warnings/Errors we get based on data type are :

1.1    Conversion error calling conversion routine decimal_from_string data may have been lost
Cause:    This can happen when the input is in incorrect format while converting into target data type or is contains null value, so that the conversion function is not able to convert it to target data type
Resolution:Check for the correct date format or decimal format and also null values in the date or decimal fields before passing to StringToDecimal functions.
    Similar issue can come for the datastage StringToDate, DateToString,DecimalToString  conversion functions as well

1.2    Possible truncation of input string when converting from a higher length string to lower length stringCause:    This can happen when the input is having length which is more than the length given in output of the same stage
Resolution: Change the length of the specified column in specified stage by giving same length in output as of it is in input.
This can happen in stages like Merge, Sort, Join, Lookup etc

1.3    APT_CombinedOperatorController,0: Numeric string expected for input column 'XXXXX’. Use default value. Cause:    This can happen when the input data type and output data type are different and the type conversion is not handled in transformer.
Resolution: Type conversion function should be applied based on target data type.
Ex:    Input data type = Char, Output data type= BigInt
In this case, the direct mapping with out any type conversion will give this message. Need to provide the type conversion function

i. The Log normally doesn’t show this message as Warning/ Errror, it will be mentioned as “Info”  
ii. When this happen the records will not be inserted into the table/file.
iii. The stage name will not mentioned in the log, to get the stage name where this issue is happening, need to include 1 Environment Variable in job properties. i.e. $APT_DISABLE_COMBINATION and set it to “True”
1.4    Reading the WVARCHAR database column XXXXX into a VARCHAR column can cause data loss or corruption due to character set conversionsCause:    This can happen when the data type is supposed to be Unicode but it’s not mentioned in stage.
Resolution:Change the data type for the column to Varchar along with “Unicode” instead of Varchar alone.  i.e. select Unicode from the Drop down provided in Extended column.

1.5    Schema reconciliation detected a type mismatch for field SSSSS. When moving data from field type CHAR(min=100,max=100) into INT64Cause:    This can happen when the data type is supposed to be Char  but it’s mentioned as BigInt in stage.
Resolution:Change the data type for the column to Char with length 100 instead of BigInt in corresponding stage.

Like the below page to get update