WARNING ORA-00942 in Server-Job 11.7

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Kerem2222
Participant
Posts: 16
Joined: Thu Apr 11, 2013 9:33 am

WARNING ORA-00942 in Server-Job 11.7

Post by Kerem2222 »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Kerem2222
Participant
Posts: 16
Joined: Thu Apr 11, 2013 9:33 am

Post by Kerem2222 »

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>
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Kerem2222
Participant
Posts: 16
Joined: Thu Apr 11, 2013 9:33 am

Post by Kerem2222 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Doesn't create mode include an option to drop first? (I don't have access to DataStage to check at the moment.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply