SQL Type (Meta-data columns definition)

luca · Post by **luca** » Tue May 20, 2003 5:37 am

Hi !
I would like to have some light on the "SQL Type" that defines the type for each column (ex: in a transformer).
What are the rules to follow up to avoid problems?
ex: is the following ok: input=numeric, output=decimal ?
When can we get errors due to mismatch types?
When is a conversion mandatory?
...
Thanks

WoMaWil · Post by **WoMaWil** » Tue May 20, 2003 7:42 am

Hi Luca,

best way to solve is to be 100 % correct. If the database accepts the difference even a char on the one side and a numeric on the other work.

Some stage are strict. With ODBC you get for any difference even for char(4) vs. char(8) an error, some other stages accept difference without getting angry.

Try it and you know it.

Wolfgang

kduke · Post by **kduke** » Wed May 28, 2003 10:43 pm

Luca

When you use a sql type other than varchar then DataStage converts the field to that type internally. There is some overhead for this. When you are not doing math with a field then use varchar on both ends (input and output). This is the fastest. What is decimal is numeric in another database. This gets really different on plugins. All these types are used by DataStage to intrepret the column. Most are unimportant to DataStage. You can even do math with varchar fields without converting them. Dates are a little different.

Thanks Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com