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



Joined: 29 Apr 2005
Posts: 85
Location: India
Points: 720

Post Posted: Thu Oct 31, 2019 6:26 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,

Hope all are doing great. We migrated 8.5 version of jobs (Unix env.) to 11.5 version (Linux env.) recently. We are having trouble with a job that is executing a SQL Server stored procedure from ODBC connector. The same job runs fine in 8.5 but in 11.5 job completes but does perform the required activity, it completes in 0.02 sec in 11.5 version and takes 3 mins in 8.5.

Code:
ODBC connector -> peek


The design is simple and straight forward, ODBC connector and peek, just kicks off the SQL Server stored procedure in the "Before SQL" section.

Used this in the Before SQL section:
Code:
Begin
exec dbname.dbo.sp_name
End;


Please note this code is working fine in 8.5 version.

Could you please let me know if anything is missing or needs correction for having it run in 11.5 version?

Thanks in advance.
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Fri Nov 01, 2019 6:43 am Reply with quote    Back to top    

Assuming you meant to say "but does not perform the required activity", yes? The new timing would certainly indicate it is doing nothing mighty fast but a bit surprised no error is generated if there's a problem. Have you checked to see if you are missing any fixpacks for your version?

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
xch2005
Participant



Joined: 29 Apr 2005
Posts: 85
Location: India
Points: 720

Post Posted: Mon Nov 04, 2019 1:55 pm Reply with quote    Back to top    

Yes, that is correct, it was a typo. I meant that "does NOT perform the required activity".

The version that I have is 11.5.0.2.

Unfortunately not getting a clue on why the job behaves like this.

Thanks.
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2894
Location: USA
Points: 21956

Post Posted: Tue Nov 05, 2019 5:42 am Reply with quote    Back to top    

Once upon a time with the Oracle Connector stage, I found that I had to CALL the SP instead of EXEC the SP. Hope that helps. The end.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
xch2005
Participant



Joined: 29 Apr 2005
Posts: 85
Location: India
Points: 720

Post Posted: Tue Nov 05, 2019 2:18 pm Reply with quote    Back to top    

I tried to use CALL instead of EXEC to run the stored procedure (in ODBC connector) and unfortunately that did not work either.
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2894
Location: USA
Points: 21956

Post Posted: Wed Nov 06, 2019 5:51 am Reply with quote    Back to top    

Have you compared the ODBC driver being used and the driver settings?

Have you tried an ODBC trace?

Have you opened a support case?

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Nov 06, 2019 6:09 am Reply with quote    Back to top    

You never answered the fixpack question and definitely need to open a support case if you haven't done so already.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
jerome_rajan



Group memberships:
Premium Members

Joined: 07 Jan 2012
Posts: 376
Location: Piscataway
Points: 2955

Post Posted: Sun Dec 01, 2019 12:56 am Reply with quote    Back to top    

xch2005 wrote:
I tried to use CALL instead of EXEC to run the stored procedure (in ODBC connector) and unfortunately that did not work either.


Try wrapping your SP with a BEGIN ... END. The workaround worked for me

_________________
Jerome
Senior Consultant At Deloitte
Connect With Me On LinkedIn

Life is really simple, but we insist on making it complicated.
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Mon Dec 02, 2019 5:07 am Reply with quote    Back to top    

Also known as an "anonymous block".

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Mon Dec 02, 2019 5:08 am Reply with quote    Back to top    

Also known as an "anonymous block".

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
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