DSXchange

Hello,

I have a repeatable Server-Job which runs up to now on DS V9.1 and returns in the first run for a Drop-Statement just an INFO ORA-00942.
When I run the exact same Server-Job on DS V11.7 I get in the first run a WARNING ORA-00942.

Is there a way to "Demote to Informational" for Server-Jobs with Message Handler?

What else could I do to turn of that WARNING in DS V11.7?

Maybe in DS V9.1 it was just an INFO because it had no Message-ID, it was just a double "-1". But in DS V11.7 I get now the Message-IDs "IIS-CONN-ORA-03001" and "IIS-CONN-ORA-01003"

Thanks you
Regards

There are no Message Handlers for server jobs.

Are you trying to drop a non-existent table on the first run? Perhaps you could check first for the table's existence.

Yes, I remember that 'problem' since I was a wee lad using Server jobs with that option enabled so we avoided it like the plague... that or created the first iteration of the table as part of the elevation. But do you really need to drop and recreate it every run? Why not truncate?

Thank you for your answers. Of course I don't need to drop the Table in the first run.

But how can I check within the Orcale Connector whether that table exists in the first run??? That would be a great help! :D

Additionally I have to tell you that the name of table is changing on every run! It is like Log_Table_<Process_ID>

You can't. Check within the Connector, that is.

Ah... so you don't need to drop and recreate the table each run, you need to create a new table each run, yes? And from what I recall, the drop is forced during the create option you are using. What about moving the create to the Before SQL (or whatever it is called now) section of the stage? You'd need to manually specify the DDL but should still be able to pass in the Process_ID to bind into the CREATE TABLE which I assume is a job parameter.

You are right actually it is a new table each run, due to the suffix Process_ID.
But as i said, it is a repeatable job, so in case that one Process_ID has to be re-run, I need that drop statement.

Currently my table action is "REPLACE" an changing it to "CREATE" with a explicitly manually written Drop-Statememt in the Before-SQL would change nothing I thin, but I'll try it.

Another option - a stored procedure called Before SQL, one that could do the existence check and conditional DROP then do the CREATE for you.

Doesn't create mode include an option to drop first? (I don't have access to DataStage to check at the moment.)

It does and that is the very issue being discussed - the error thrown on the initial run when the DROP fails.