• IsValid function in DataStage

    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.