TIMESTAMP(6) converted to Unknown while loading Metadata

bandish · Post by **bandish** » Wed Dec 06, 2006 11:17 pm

Hi,

In the Database we have Timestamp Fields with Datatype TIMESTAMP(6).

When I imported this table definition to Datastage using Datastage Manager, it got Converted to Unknown Datatype with Length Not Specified and Scale = 6.

Now when we try to view data from this table in datastage, we get the following error:

Code: Select all

##F TUXX 000002 04:18:21(000) <main_program> Internal Error: (colDesc):orautils.C: 1714: getColDesc failed.
Traceback: [6] APT_FatalPath::msgAssertion(0xfca3b7c7, 0xffbec5d0, 0xfca3b7cf, 0x6b2, 0x0, 0xcd4a8), at 0xff0db308 
[7] APT_OraUtils::querySchema(0x0, 0xfca5dde8, 0xffbec560, 0xffbec6b0, 0xffbec6bc, 0xfca55f60), at 0xfc9a4488 
[8] APT_OraReadOperator::describeOperator(0xaf1f0, 0x0, 0x123d30, 0xffbecc08, 0xfca5d7e8, 0xfc9861a8), at 0xfc97ee4c 
[9] APT_OperatorRep::wrapDescribeOperator(0x1715c0, 0x0, 0x1, 0x709228, 0xffbece54, 0xb4), at 0xfe70b3c4 
[10] APT_OperatorRep::check1a(0x1715c0, 0x1800, 0xfed6e3e0, 0xff12de18, 0xfee1425c, 0xfee4eecc), at 0xfe70b92c 
[11] APT_StepRep::sequenceAndCheck1Operators(0x132e48, 0x0, 0x0, 0xfed7994e, 0xfee1425c, 0x0), at 0xfe788580 
[12] APT_StepRep::check(0x132e48, 0x68cb98, 0x0, 0x0, 0xfee1425c, 0x132e54), at 0xfe7878c8 
[13] APT_Step::check(0xeb3a0, 0xffbed618, 0xffbed55c, 0xff135138, 0x0, 0xfee1425c), at 0xfe77b9d4 
[14] APT_OSL::createAndCheckStep(0x1493b0, 0xffbee9b0, 0xff1aee24, 0xffbed5e0, 0xffbed618, 0x3c794), at 0xff220d90 
[15] APT_ORCHESTRATE_main(0x1, 0x2b8fa, 0xffbeeae8, 0x4, 0xffbeec50, 0xffbeec6c), at 0x1fcdc

Even we tried to change the datatype in job as well as DataStage Table Definition to Timestamp or Date , but its not working.

What is the possible solution for this error.

Thanks
Bandish

DSguru2B · Post by **DSguru2B** » Thu Dec 07, 2006 10:58 am

In your sql select, try to change the timestamp to char in your sql and see if your query executes. SOmething like

Code: Select all

select .... TO_CHAR(timestamp_col,YYYY-MM-DD HH24:MI:SS.FF6) from table

bandish · Post by **bandish** » Sat Dec 23, 2006 6:57 am

I would like to know, why DataStage is unable to identify a TIMESTAMP(6) column while importing metadata ?

If Datatype of this column is specified to TimeStamp, Length = 38,
Scale = 6, we are able to view data and run our jobs.

I_Server_Whale · Post by **I_Server_Whale** » Sat Dec 23, 2006 10:47 am

What is the database that you are talking about?

chulett · Post by **chulett** » Sat Dec 23, 2006 11:10 am

Oracle, I would wager.

ray.wurlod · Post by **ray.wurlod** » Sat Dec 23, 2006 2:43 pm

It's all a matter of representation and standards. Oracle tends not to follow the standards used by the rest of the world. For example, a timestamp with no fractional seconds is TIMESTAMP(19) most everywhere else. With milliseconds it's TIMESTAMP(23,3) and with microseconds it's TIMESTAMP(26,6). Double the precision figure for multi-byte character sets.

Standards such as SQL92 and ODBC do not recognize the Oracle-only variant TIMESTAMP(6). DataStage attempts to be standards-compliant where possible, hence the conversion/rejection.

bandish · Post by **bandish** » Tue Dec 26, 2006 1:38 am

Database is Oracle 10g.

bandish · Post by **bandish** » Tue Dec 26, 2006 1:47 am

bandish
Posted: Sat Dec 23, 2006 6:57 am

--------------------------------------------------------------------------------

I would like to know, why DataStage is unable to identify a TIMESTAMP(6) column while importing metadata ?

If Datatype of this column is specified to TimeStamp, Length = 38,
Scale = 6, we are able to view data and run our jobs.

Actually, the above solution is working in Oracle 9i not in 10g.