SQL Stored Procedure Progress / Status

#1

Is there any possible way to get an SQL SP to report progress mid-
stream so to speak ?

I have a very lengthy SQL SP that performs many inserts, updates,
merges, calls other Functions etc and I am wondering if there is any
possible way to get it to report it's progress as if goes through the
various tasks. I have tried dynamic result sets in the middle of the
SP with something like "VALUES 'Progress 1'" but it does nothing at
all.

Any admin SP's, functions or anything that can report text back to the
CLP when running the SP ?

#2

"Fin" <tdavidge (AT) hotmail (DOT) com> wrote

Quote:

Is there any possible way to get an SQL SP to report progress mid-
stream so to speak ?

I have a very lengthy SQL SP that performs many inserts, updates,
merges, calls other Functions etc and I am wondering if there is any
possible way to get it to report it's progress as if goes through the
various tasks. I have tried dynamic result sets in the middle of the
SP with something like "VALUES 'Progress 1'" but it does nothing at
all.

Any admin SP's, functions or anything that can report text back to the
CLP when running the SP ?

You can insert rows into another table which reports the status, and then
query the table.

#3

On 3/30/2011 10:58 AM, Mark A wrote:

Quote:

"Fin"<tdavidge (AT) hotmail (DOT) com> wrote in message
news:f9fa95c0-d3eb-4eb5-9632-72f8e048e174 (AT) d19g2000yql (DOT) googlegroups.com...
Is there any possible way to get an SQL SP to report progress mid-
stream so to speak ?

I have a very lengthy SQL SP that performs many inserts, updates,
merges, calls other Functions etc and I am wondering if there is any
possible way to get it to report it's progress as if goes through the
various tasks. I have tried dynamic result sets in the middle of the
SP with something like "VALUES 'Progress 1'" but it does nothing at
all.

Any admin SP's, functions or anything that can report text back to the
CLP when running the SP ?

You can insert rows into another table which reports the status, and then
query the table.

....using uncommitted read of course.

Another twist on this is to use an autonomous procedure for the same
purpose.
Finally you can send email. DB2 9.7 has a system defined module for
that, so no need to write your own.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL Stored Procedure Progress / Status

comp.databases.ibm-db2 comp.databases.ibm-db2

SQL Stored Procedure Progress / Status - 03-29-2011 , 03:57 PM

Re: SQL Stored Procedure Progress / Status - 03-30-2011 , 09:58 AM

Re: SQL Stored Procedure Progress / Status - 03-31-2011 , 07:01 AM