CPU utilization

[Robert Stearns]

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.

[Ian]

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.

You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Leo]

Have you tried explaining the SQL that is running in the procedure?
Maybe you have a really bad access path on some SQL. Look for bad
joins or large sorts.

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.

You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Leo]

Have you tried explaining the SQL that is running in the procedure?
Maybe you have a really bad access path on some SQL. Look for bad
joins or large sorts.

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.

You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Robert Stearns]

Leo wrote:

Have you tried explaining the SQL that is running in the procedure?
Maybe you have a really bad access path on some SQL. Look for bad
joins or large sorts.

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.

You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Is there some simple way to do this? All the SQL statements in the
procedure use some program variables, so they would be fairly cumbersome
to reproduce outside of the procedure.

[Robert Stearns]

Leo wrote:

Have you tried explaining the SQL that is running in the procedure?
Maybe you have a really bad access path on some SQL. Look for bad
joins or large sorts.

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.

You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Is there some simple way to do this? All the SQL statements in the
procedure use some program variables, so they would be fairly cumbersome
to reproduce outside of the procedure.

[Leo]

Have a look at db2expln. Since all the SQL in you procedure is bound
in the package associated with the proc, this tool will explain all
the SQL and write the output to a file for you to review. Snapshoting
the SQL will not reveal what is happening in your proc because
snapshots only show dynamic sql and procedues are static.
Robert Stearns <rs**********@charter.net> wrote in message news:<40************@charter.net>...

Leo wrote:

Have you tried explaining the SQL that is running in the procedure?
Maybe you have a really bad access path on some SQL. Look for bad
joins or large sorts.

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.
You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Is there some simple way to do this? All the SQL statements in the
procedure use some program variables, so they would be fairly cumbersome
to reproduce outside of the procedure.

[Leo]

Have a look at db2expln. Since all the SQL in you procedure is bound
in the package associated with the proc, this tool will explain all
the SQL and write the output to a file for you to review. Snapshoting
the SQL will not reveal what is happening in your proc because
snapshots only show dynamic sql and procedues are static.
Robert Stearns <rs**********@charter.net> wrote in message news:<40************@charter.net>...

Leo wrote:

Have you tried explaining the SQL that is running in the procedure?
Maybe you have a really bad access path on some SQL. Look for bad
joins or large sorts.

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

Robert Stearns wrote:

I am trying this again, since I got no reply the first time.

Is there a special register (or any other variable/function) which will
return the cpu time my sql procedure has used? Or is there a profile
product or IDE which will display the cpu time used, by statement?

I have a fairly long but simple procedure which reads two input tables
of ~20,000 rows, writes 3 tables with a combined count of < 50,000 rows
with a commit every 1,000 input rows. It is taking an inordinate amount
of both real time (> 1 hour) and cpu time. This is on a new server
(intel pentium 4 3.0GHz, linux, db2 8.1.5) and top reports that the
db2agent for this job is getting 75%-90% of the available time and no
other task is getting any appreciable amount. I have refrained from
posting the routine both because of its length (280 lines) and I really
don't want to expose my first ever db2 sql procedure.
You can use a statement-level event monitor to capture this information.

You may also be able to gather it using the db2batch utility, although
I'm not sure if it has visibility into fenced stored procedures.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Is there some simple way to do this? All the SQL statements in the
procedure use some program variables, so they would be fairly cumbersome
to reproduce outside of the procedure.