DSXchange

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!

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.

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.

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 .

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

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

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).

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.

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.

Which DataStage versions honor this environment variable?

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...?

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

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.