ODBC Connector w/ SQL Server Stored Proc in Before SQL hangs

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

ODBC Connector w/ SQL Server Stored Proc in Before SQL hangs

Post by asorrell »

I'm just posting this in case anyone else comes across this.

We're moving about 50 jobs from 8.0 to 8.7 that use SQL Server stored procedures in the Before SQL of Enterprise Stages to populate temp tables so the job can then process the data from the temp tables.

As we moved the jobs to ODBC Connectors on 8.7 the jobs were all hanging after a message in the Director log reading "Before SQL command executed successfully".

I finally realized that for some reason the ODBC Connector wasn't issuing a commit after it ran the stored procedure, so the job was waiting forever.

Changed syntax from "EXEC stored_procedure;" to "EXEC stored_procedure;COMMIT;" and they all worked.

I suspect its a bug, might report it later if I get time...
Last edited by asorrell on Tue Dec 10, 2013 4:23 pm, edited 1 time in total.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Or I assume the proc could have been changed to issue the commit.

Regardless, nice find. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
bobyon
Premium Member
Premium Member
Posts: 200
Joined: Tue Mar 02, 2004 10:25 am
Location: Salisbury, NC

Post by bobyon »

Very interesting. Can you tell me if that time spent in the before SQL was reported as part of the job startup time in the Director log?
Bob
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Not easily, no. It either hung up (and we had to kill it), which didn't report statistics, or ran, which took less than a second. I'll see if I can spot it on some other jobs.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
bobyon
Premium Member
Premium Member
Posts: 200
Joined: Tue Mar 02, 2004 10:25 am
Location: Salisbury, NC

Post by bobyon »

The reason I am asking is because I was just asked to research why several of our jobs have LONG startup time. 10-20 minutes startup time. All of them have before SQL in the Oracle connector stage. And, from what I can see in the Dir log, it appears that SQL is what is taking the so much time.

I just did not expect to see Before SQL time reported as part of the startup time.

Do you know if the startup time includes that Before SQL time?

Seems odd to me that it would, but it sure looks that way from these few jobs.

Thanks
Bob
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I am seeing the same thing: Long reported Startup time ranging from 2 minutes to 8 minutes and production run time of 0:00, day after day.

This job is Oracle Connector to Peek stage. In Oracle it selects a dummy row from the dual table. The Before SQL runs 80+ DELETE statements and commits.

Apparently all the Before SQL execution time is reported in the job log as Startup time.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply