DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
Kerem2222
Participant



Joined: 11 Apr 2013
Posts: 16

Points: 420

Post Posted: Tue Aug 28, 2018 7:11 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Server
OS: Unix
Additional info: Migration of repeatable Server-Job from DS V9.1 to V11.7
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54395
Location: Sydney, Australia
Points: 295036

Post Posted: Tue Aug 28, 2018 6:40 pm Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Tue Aug 28, 2018 7:59 pm Reply with quote    Back to top    

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

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
Kerem2222
Participant



Joined: 11 Apr 2013
Posts: 16

Points: 420

Post Posted: Tue Aug 28, 2018 11:42 pm Reply with quote    Back to top    

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! Very Happy

Additionally I have to tell you that the name of table is changing on every run! It is like Log_Table _<Process_ID>
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Wed Aug 29, 2018 7:24 am Reply with quote    Back to top    

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

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Kerem2222
Participant



Joined: 11 Apr 2013
Posts: 16

Points: 420

Post Posted: Wed Aug 29, 2018 7:33 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Wed Aug 29, 2018 8:26 am Reply with quote    Back to top    

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

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54395
Location: Sydney, Australia
Points: 295036

Post Posted: Mon Sep 03, 2018 9:17 am Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Mon Sep 03, 2018 1:01 pm Reply with quote    Back to top    

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

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours