Rare Oracle Error

[jguerrero]

Dear Friends

We are experienced a very rare problem with Oracle Enterprise Stage, de big picture is:

a) We have DS 7.5.1 on AIX 5.3, running fine but with some "crash" problems
b) The solution was to migrate to DS 7.5.3.1 on the same AIX
c) Now, we can see data in an Oracle Enterprise Read using View Data, a very simple query like Select Field1, Field2 from Table
d) We compile jobs fine, but when the job run it aborted saying that there is a problem with the query (the query runs fine in view data, sqlplus, aqua data studio and other tools)
e) Because the error is in the first stage (Oracle is the source) if we point the link to a flat file or a dataset and delete the rest of the design the job runs fine....
f) if we delete just a part of the desing, a 50% for example, the same error appears but in other Oracle Enterprise Stage (an LU table)

So, the questions are:

Can designer, version 7.5.2 (not 7.5.3), be causing this kind of rare error when generate code?

It is possible that in 7.5.3 compiler options change and the version upgrade don't reflect that changes (use the old DSParams?)


Thanks in advance

[chulett]

when the job run it aborted saying that there is a problem with the query

Post the errors. And why ask about 7.5.2 when you upgraded from 7.5.1 to 7.5.3?

[ray.wurlod]

Perhaps using 7.5.2 client with 7.5.3 server?

[chulett]

Wondered about that or it could just be a typo. We'll see.

[jguerrero]

Hi friends,

Well, this are the errors:

Oracle_Enterprise_5: The provided query statement did not prepare correctly;
please verify that your statement is correct;
statement: SELECT UNQ_ID_SRC_STM from RI
where DSC='IMSI'
main_program: Creation of a step finished with status = FAILED.

I simplify the desing, now is this:
Oracle
|
|
Oracle ____>LU_____>Flat File
|
|
Flat File

I repeat the same design creating a job from zero, the same error apperar but not in the first Oracle Stage (main stream) ....it appear in the second Oracle Stage !!! (Oracle LU)

Any suggestions will help

TIA

[chulett]

Is that the only error?

It should have logged the sql it couldn't prepare and stated a reason for the failure. Was anything else like "table or view does not exist" logged? And with no owner specified for the table, you either need to be logged in as the table owner or have a synonym in place to resolve the table name. Are you? Do you?

[jguerrero]

Hi Craig

I believe that we test every possible combination, like use schema, use SQL Builder instead of User Define SQL, Create a View with the fields, synonims, and so on. Still the error is present in some jobs and not in others.

DataStage only show that error, we try to find more information inside the Osh generated code, dump and explain, but we can't detect anything weird.

I'm sure is not only DataStage, i think something in memory, processes, or C compiler is "helping" this situation, so, any ideas or clues about what we need to search are very apreciate.

Best Regards!

[chulett]

Ok. How about Oracle specifics. What is the database version and what is the client version? Is the database local to the DataStage server?

[jguerrero]

Hi Craig

In one Server there is Oracle 10gR2, in the other is 9.1, Oracle Client is 10.2.0.

Privileges from catalog are correct for the user trying to extract data, bu don't forget that we can see date with the View Data option, but job abort when we run it.

In the last test, we copy an SQL that is working in one job with similar design to other job that is not working, and again, the same error.

In all this years is the first time that i see this erratic behavior in one stage.

Best Regards!

[chulett]

I doubt this is "erratic stage behaviour" but rather erratic Oracle behaviour, more than likely caused be using a 10.2 client against a 9.1 instance. Do you see this behaviour in the 10g database or just the 9i one? And any chance you can get a 9i client installed on the DataStage server? 9i will work with 10g but no guarantees of the reverse.

[jguerrero]

Hi Craig,

The problem is present if i read from oracle 9 or from oracle 10, and don't forget that in 7.5.1 jobs works fine.

Now, we are planning a complete installation (do you remember "Format C:"...well...something like that).

Let me inform you later about the evolution of these

Best Regards!!

[chulett]

Probably wasting your time but the results will be good information, I suppose. While you are doing that, you should open a case with your official support provider and see if this is a known bug in the latest release.

[jguerrero]

Dear Friends,

Finally i can explain what is going on....yesterday we re-install 7.5.3 on AIX 5.3, after a complete cleaning of software, still we had the same problem.

Today, again re-install but in this installation we DON'T give the Oracle_Home directory, this cause the error "...i can't find orchoracle..." an error very common in this forum

Well, we create a symbolic link in ..PXEngine/lib for orchoracle.o ---> orchoracle10g.o and this cause the error "..stage not initilized..", we recreate the link to orchoracle9i.o and the same problem. We discover that in 7.5.1 exists orchoracle8i.o, so we copy in the PXEngine/lib directory, create the symbolic link and ....it work.

Finally, we uninstall 7.5.3 and install 7.5.2, made a symbolic link to orchoracle10g (the 7.5.2 library NOT the 7.5.3) and works fine.

My conclusions? I think one of two things:
a) orchoracle10g.o and orchoracle9i.o in 7.5.3 on AIX 5.3 have problems
b) Our AIX 5.3 has a weird configuration in languaje, libraries, compiler or something that produces the problem in the oracle libraries from 7.5.3

Thanks to all of you for your suggestions and ideas.

[chulett]

Did you ever talk to support? 7.5.2 on HP Itanium didn't install correctly and we had to create a symbolic link to get Oracle connectivity, hence the question.

[lstsaur]

In my 7.5.1 environment, orchoracle8i is not even included in the .tar.gz file.