DSXchange

When I tried to view data in a Oracle9i stage got the following error - 'Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project STPHCMSIM'. I tried reindexing the project from the Administrator. But that didn't help. The stage uses a column generated SQL and the SQL runs fine when it is run from sqlplus. So switched to User-defined SQL and the error stopped. Couldn't reason out why the error occured or what solved it. There is no difference between user-defined SQL and column generated one. What am I doing wrong?

Thanks for the help.

Welcome aboard! :D

Did you check that DS.REINDEX was actually successful? Or did it skip reindexing one or more tables because exclusive access could not be obtained?

I could switch back to column generated and replicate the same error again in the stage. When I tried reindexing, exclusive locks were acquired for each object and they seem to be reindexed. But when I go back to stage the error persists.

Thanks again

I don't have any answer here, but I can sympathise.

I have a similar situation where the column generated SQL gets an "invalid column" error, while the exact same SQL runs fine as user generated. I even exported and imported the job design and the same thing happened.

Let us know if you find an answer!

Larry

I don't know if I have the answer for you but here are some thoughts:

1) At one point, I was getting a similar error (DSD.Browse) and I ended up fixing it by reinstalling the plug-in. The only thing I could figure out was that we had recently reinstalled the Oracle drivers and so maybe something in that process corrupted things. All I know is that reinstalling the plug-ing corrected the situation.

2) You may be adequately familiar with the OC9i plug-in but it has a lot of options when it comes to generated SQL. You probably already have, but I will ask anyway -- just to be sure: Have you compared the generated SQL to the user SQL and confirmed they are identical; i.e., can you cut-n-paste the generated SQL in to the user SQL and it works?

More than once I found errors because I hadn't selected things correctly when using generated SQL rather than user.

I had typed in a quite big where clause in the stage and I had it formated so it was easy for me to read it. Just as I was trying things out I removed all the extra white spaces I had in there. The stage works fine now.
I don't think this would help to understand what went wrong. My idea was that white spaces doesn't matter in a SQL.
Or am I wrong?

White space is not supposed to matter in SQL. However, as you are finding, it does... or more properly - it can!

I've seen people add all kinds of blank lines and carriage returns to either generated or user defined sql so it is "easier to read". Bah. If I'm really worried about it, I'll copied the query out of the job into TOAD and format it there while working on the job. However, when all is said and done the job gets the plain vanilla un-fancy-format sql.

[quote="chulett"]However, when all is said and done the job gets the plain vanilla un-fancy-format sql.[/quote]
Will follow your advice.

Thanks everybody

After bumping into the same problem more than once, figured out that this is related to Ecase 57785 - A known issue where jobs would not work if the Column-Generated SQL Query is longer than 1024 bytes.
Work around is, obviously, to copy the column-generated SQL and stick it in the User defined tab.
We run on server 7.1r1 and this issue has been resolved in 7.5(its documented in the release notes of 7.5).

Thanks