DataStage Online Training @ BigClasses.com

DataStage Online Training

                  Sequential files are capable of containing any kind of data. But this data might have some format issues. So after extracting records from file, we might need to validate the fields based on its data type. This can be done using IsValid function in DataStage.

- Rritu

Validating fields from file should be done to remove any invalid fields to be fed into system. We would look into validating date, numeric fields coming from source.
IsValid function requires three parameters, Below is the signature for isvalid function:
isValid('data type','Input Column','Format')

• Data Type: This indicates the data type for which validation should be done.
• Input Column: This maps to the incoming column which needs to be validated.
• Format: This indicates the format which will be used for validation. This is an optional parameter.

We have a date field coming from source and we want to validate this fields whether this field contains correct format and a valid date.
To validate a date field with format mm/dd/yyyy, use a transformer wherein we can use the isvalid function:
isValid('date',StringToDate(trim_leading_trailing(), '%mm/%dd/%yyyy')
First parameter will tell that we are validating for a date. Second field contains the input column. Since input column is a string from source and hence we would first convert it to date by using StringToDate function. Third parameter provides the format which will indicate that this date input field contain format given.
Using this fucntion validation was done and wrong formats can be rejected by using constraint in the transformer.
However format given in above example is not intelligent enough to recognise single digit day and month fields. For this to work, use field function to add a zero before the fields for single digit day and month.
Validating integer or decimal can also be done using isvalid fucntion. Below is given example for the same:
Validating integer field:
IsValid('int32',input column)
Validating decimal field
IsValid('decimal[13,4],input column)
In exmaple given above, we are validating the input column for decimal(13,4).
This fucntion is very useful to validate the records coming from source and put records into reject file which are having wrong format.

Warning: Possible truncation of input string when converting from a higher length string to lower length string in Modify.
Resolution: In the Modify stage explicitly specify the length of output column.
Ex: ACCOUNT_NUM:string[max=16] = string_trim[" ", end, begin](ACCOUNT_NUM)
Warning: A Null result can come up in Non Nullable field.
Resolution: Use a Modify stage in between lookup and Database stage. When using a modify stage, use the handle_null clause.
ACCOUNT_NUM:string[max=21] = handle_null(ACCOUNT_NUM,0)
Zero will replace NULLs coming in ACCOUNT_NUM field.
Warning: A user defined sort operator does not satisfy the requirements.
Resolution: In the job flow just notice the columns on which sort is happening . The order of the columns also must be the same. i.e if you specify sort on columns in the order X,Y in sort stage and specify join on the columns Y,X in order then join stage will throw the warning, since it cannot recognise the change in order.