ORA OCI stage appends ".FF19" to date format on ti

alow · Post by **alow** » Sun Sep 17, 2006 8:18 pm

Hi All,

I have a group of very simple server jobs which currently source data from one Oracle 9i Database and write it to another Oracle 9i Database.

Basically all of these jobs are structured as follows;

OCI -> Transformer -> OCI

The source database for these jobs is being upgraded from 9i to 10g. Therefore these jobs will soon be sourcing data from an Oracle 10g database and writing data to an Oracle 9i database.

As a result, I am in the process of testing the impact of this upgrade on my server jobs.

According to the DataStage Plugin Documentation, the ORAOCI9 stages can connect to a 10g database.

When I try to source data from the 10g source, I am seeing some very strange behaviour in regards to columns specified as "Timestamp". When you view the SQL from the SQL tab within the OCI stage, the timestamp columns are to_char'd (which I would expect) as shown below;

SELECT TO_CHAR(Date_Column, 'YYYY-MM-DD HH24:MI:SS') FROM TABLE

Although, when I run these jobs, this isn't the SQL which is being executed against the database. The SQL executed against the 10g database is as follows;

SELECT TO_CHAR(Date_Column, 'YYYY-MM-DD HH24:MI:SS.FF19') FROM TABLE

Note the ".FF19" which has been appended to the format specification in the TO_CHAR command. This causes the jobs to abort with the Oracle error "ORA-01821: date format not recognized"

As a test, I changed the data type from timestamp to varchar and manually specified the TO_CHAR in the derivation of the column.... and the job run fine.

Im currently thinking this behaviour may have something to do with the ORACLE client software (or its configuration) installed on our DataStage application server and the OCI plugin interaction with it.... but .... Has anyone encountered this sort of OCI behaviour before?

Our DataStage application environment is as follows;

Operating System = Red Hat Linux 3.2.3-54
DataStage = Enterprise Edition 7.5.1.A
Oracle Client = 10.2.0

Any input would be greatly appreciated!

ray.wurlod · Post by **ray.wurlod** » Sun Sep 17, 2006 10:19 pm

Welcome aboard! :D

I have not seen that behaviour, so tend to agree that it may have something to do with the manner in which your Oracle client software is configured.

alow · Post by **alow** » Mon Sep 18, 2006 5:32 pm

Thanks Ray.

IBM Ascential support have been able to replicate my problem. As to what causes it, not quite sure just yet, but they are leaning towards what we suspect.... problem with the OCI stage interacting with the Oracle client. If an explanation is provided I will post it.

alow · Post by **alow** » Mon Oct 02, 2006 12:31 am

As suspected....

It is indeed a problem with the configuration of the Oracle Client software.

IBM Ascential support provided us with a patch a few weeks back, which replaced the library file oraoci9.so in the <install_dir>/DSEngine/lib directory. This patch sorted out a seperate issue, but caused this one.....

so... solve one problem and get another in its place

.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 02, 2006 1:16 am

Can you please post the ECASE number, so that anyone else encountering this problem in the future can rapidly request the correct patch?

alow · Post by **alow** » Mon Oct 02, 2006 5:19 pm

Ray, I dont know if this is the information you are after, but this is all I have from Ascential;

Ascential Support Survey for Case no.: 488298*AUA

ray.wurlod · Post by **ray.wurlod** » Mon Oct 02, 2006 6:11 pm

That will suffice. The number is the primary key to the table in which support cases are recorded (the "*AUA" suffix indicates that it was first accepted by the Australian support group).

smaripally · Post by **smaripally** » Wed Mar 05, 2008 3:35 pm

This can be rectified by adding a new Environment variable to the user defined category in Datastage Admin(environment variables section).

the new variable name is
DS_NO_FF which is string type and set to value 1.

smaripally · Post by **smaripally** » Wed Mar 05, 2008 3:36 pm

This can be rectified by adding a new Environment variable to the user defined category in Datastage Admin(environment variables section).

the new variable name is
DS_NO_FF which is string type and set to value 1.

chulett · Post by **chulett** » Wed Mar 05, 2008 3:49 pm

Which DataStage versions honor this environment variable?

alow · Post by **alow** » Wed Mar 05, 2008 3:57 pm

This can be rectified by adding a new Environment variable to the user defined category in Datastage Admin(environment variables section).

the new variable name is
DS_NO_FF which is string type and set to value 1.

Pardon my ignorance, but how exactly do you do this...?

chulett · Post by **chulett** » Wed Mar 05, 2008 4:08 pm

Administrator / Project / Properties / Environment / User Defined and then simply add it. It will be in play for all jobs in the project.

smaripally · Post by **smaripally** » Thu Mar 06, 2008 3:58 pm

chulett wrote:Which DataStage versions honor this environment variable?

we just upgraded to Datastage 8.0.1 from 7.1.
so had gone through this issue and got rectified by the environment variable setting.