DATATYPE CHECK

[CelesteT]

I want to extract data from a sequential flat file and then load the data into an Informix database. Is there a way I can check to ensure the data type integrity from the flat file before passing it through to the database? I understand you can create a constraint in the transformer to reject any records that cannot pass through to the database (for whatever reason). Is there a function where I can check the datatype of the data prior to loading into the database?

Thanx!!!

[ray.wurlod]

No.

There is only one data type in a text file, "text".

Once the data are in DataStage, you can use expressions or Routines to check data types. For example:

Code: Select all

FUNCTION IsInteger(Arg1)
* Returns 1 if Arg1 is an integer, 0 otherwise.
* Can not use arithmetic functions in case Arg1 is non-numeric.

Pattern = "1N0N" : @VM : "'-'1N0N" : @VM : "'+'1N0N"
Ans = (Arg1 Matches Pattern)

RETURN(Ans)

Code: Select all

FUNCTION IsDecimal2(Arg1)
* Returns 1 if Arg1 is decimal number with two decimal places, 0 otherwise.
* Can not use arithmetic functions in case Arg1 is non-numeric.

Pattern = "1N0N'.'2N" : @VM : "'-'1N0N'.'2N" : @VM : "'+'1N0N'.'2N"
Ans = (Arg1 Matches Pattern)

RETURN(Ans)

[chulett]

There are many functions that can help in your quest. Define all of the fields in your input Sequential stage as character and then run sanity checks in the transform. For example, use num to check to see if a field is numeric.

If you are not familiar with what functions are available, check the online help. Switch to the Index tab and type in 'BASIC', then select 'BASIC functions, alphabetic list of' from there. You'll get a nice starting point where all functions are listed, but make sure you read the second sentence on the page: "If you are unsure of the function or statement you need, see BASIC Tasks." BASIC tasks breaks them down by what you are trying to accomplish to help find the appropriate function.

Also look into the Status function. It can be used in a Custom Routine to check that a date is valid by attempting to convert the date using IConv and then checking the success or failure of same. The online help for Status shows examples of it being used for this very purpose.

[chulett]

From a private message:

Is there a way I can validate the length of a field?

Do you know if this will work in a Parallel job?

I only 'do' Server, so can't speak for the PX side of the house. However, my understanding is that anything you can do in a Transformer in Server, you can do in a BASIC Transformer in PX, but it would not be the optimal choice.

That being said, one thing you'll find is that string lengths are not tightly enforced in DataStage. You can declare something as Char(10) in a Sequential file stage and (assuming a delimited file) it will happily read in and store 50 characters in it. It's not until you go to push it into a database that the truncation warning or some other error pops up, which is why it can be important to do what you are asking - validate string lengths.

If you simply want to check, there is the len function. Be aware you may need to trim it first, I don't recall if 'len' counts trailing whitespace or not. Or, what I usually do is whack off any 'extra' characters that are not supposed to be there using the substring operator - square brackets. So, for example and still using our 10 character field, in your transformer derivation, move it to the output side like this:

Code: Select all

InputLink.YourField[1,10]

This substrings out only the characters from position 1 to 10, all else (if anything) is pitched.

[ray.wurlod]

On the topic of using private messages or private email (I was also sent one) please read this item from the February 2004 Newsletter.