INTERVAL datatype handling in Datastage

[anil.kumarm]

Hi,
Can anyone tell me how to read/write the columns of datatype INTERVAL in NETEZZA and ORACLE databases?
INTERVAL datatype stores the time-span.But there is not datatype called INTERVAL in datastage. I am using Datastage EE 7.5.2.

[mahadev.v]

An example of the data would have helped. I assume it would be a number. No of seconds, No of minutes, or hours or days. So an integer would do. Or maybe a decimal.

[ray.wurlod]

Welcome aboard.

When you import the table definition into DataStage, what data type is recorded in the table definition?

[anil.kumarm]

Hi,
Can anyone tell me how to read/write the columns of datatype INTERVAL in NETEZZA and ORACLE databases?
INTERVAL datatype stores the time-span.But there is not datatype called INTERVAL in datastage. I am using Datastage EE 7.5.2.

An example of the data is as given below:
CHR_NM_ID
-------------------
3 days 04:33:01.3
3 days 12:30:06.7
3 days 12:30:06.7
3 years 4 mons
9 days 04:33:01.3


The metadata of the filed is defined in the table as follows:
Attribute | Type | Modifier | Default Value
-----------+--------------------+----------+---------------
CHR_NM_ID | INTERVAL | NOT NULL |



When i try to import the metadata from netezza database,into the datastage, the datatype is coming as VARCHAR into the datastage.

Does this mean that datastage doesnot support INTERVAL datatype?If so how can we handle this in datastage?

[ray.wurlod]

As VARCHAR, since that's how the importer is seeing it.

All data can be managed as VARCHAR.

[anil.kumarm]

Ray,
When you import teh metadata inot datastage VARCHAR datatype will come in place on INTERVAL. But when you are trying to read/insert the data by keeping the datatype as VARCHAR, the datastage throws an error "main_program: Datatype Not Supported ". So i am not able to read the data and neither i am able to write teh data to the table

[gsym]

Hello,
I'm facing same problem, does anyone have workaround.
Thanks - gsym

[chulett]

The INTERVAL datatype is not directly supported, but I'm not sure there's really a need to. Typically they are handled as strings, so a varchar should be perfectly satisfactory. Or a numeric datatype if all you want is an individual component of the interval.

Can't speak to DB2 but seeing as how these INTERVAL datatypes are a SQL 99 standard I imagine it's the same as the Oracle implementation. Which means you would use INTERVAL when working with the full duration and EXTRACT when you want an individual component. Since it's not supported natively there's no generated sql option, so user-defined / custom sql would be the order of the day for this.

There are thousands of helpful references out there in the tubes for this that a simple google would turn up. See if any of them tickle your fancy. One such example:

http://www.psoug.org/reference/timestamp.html