Stored Procedure Issue

SUBSCRIBE DB2-L Anonymous

Stored Procedure Issue
I have a stored procedure which is called by db2 "call ....."
statement from ksh script. When I run it in db2 v. 9.1.1 environment,
everything works great. When running from v. 9.1.3 environment, after
a while the stored procedure starts to hang and snapshot says it is on
fetch statement. The job of the stored procedure is to loop through
the cursor executing ddl command found in one of the columns and
updating the table which is cursored. It is using handler and hold
options.

Now, I don't mean to suggest that the problem stems from the version
difference. This is strictly for information purposes. In fact, I
think it has more to do with db configuration. Below, is the
snapshot.

Also, there is significantly more data on the db where it didn't work and on the
db where it worked.

I have attached, the snapshot log.

Any help is greatly appreciated.




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: Stored Procedure Issue
(in response to SUBSCRIBE DB2-L Anonymous)
From your remark that this happens on a busyier system I would suspect
locking. Most DB2 qurries by default run with an issolation level of RR or
repeatable read. The Lots of locks are held for the duration of the qurey.
I would suggest issolation level UR if possible or if not CS, you may need an
administrator to help you with this change.

If you get delayed with CS its some other thread running on the same DB2.
If you get delayed with UR its most likly a non thread related configuration
issue.

Regards
Avram Friedman

On Fri, 11 Jan 2008 14:02:11 +0000, SUBSCRIBE DB2-L Anonymous
<[login to unmask email]> wrote:

>I have a stored procedure which is called by db2 "call ....."
>statement from ksh script. When I run it in db2 v. 9.1.1 environment,
>everything works great. When running from v. 9.1.3 environment, after
>a while the stored procedure starts to hang and snapshot says it is on
>fetch statement. The job of the stored procedure is to loop through
>the cursor executing ddl command found in one of the columns and
>updating the table which is cursored. It is using handler and hold
>options.
>
>Now, I don't mean to suggest that the problem stems from the version
>difference. This is strictly for information purposes. In fact, I
>think it has more to do with db configuration. Below, is the
>snapshot.
>
>Also, there is significantly more data on the db where it didn't work and on
the
>db where it worked.
>
>I have attached, the snapshot log.
>
>Any help is greatly appreciated.
>
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Philip Sevetson

Re: Stored Procedure Issue
(in response to Avram Friedman)
The DB2-L listserv strips attachments from files it posts. Your
snapshot log attachment never made it.

--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of SUBSCRIBE DB2-L Anonymous
Sent: Friday, January 11, 2008 9:02 AM
To: [login to unmask email]
Subject: [DB2-L] Stored Procedure Issue

I have a stored procedure which is called by db2 "call ....."
statement from ksh script. When I run it in db2 v. 9.1.1 environment,
everything works great. When running from v. 9.1.3 environment, after
a while the stored procedure starts to hang and snapshot says it is on
fetch statement. The job of the stored procedure is to loop through
the cursor executing ddl command found in one of the columns and
updating the table which is cursored. It is using handler and hold
options.

Now, I don't mean to suggest that the problem stems from the version
difference. This is strictly for information purposes. In fact, I
think it has more to do with db configuration. Below, is the
snapshot.

Also, there is significantly more data on the db where it didn't work
and on the
db where it worked.

I have attached, the snapshot log.

Any help is greatly appreciated.


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Seibert

Re: Stored Procedure Issue
(in response to Philip Sevetson)
Avram,

I'm curious what you mean by most DB2 queries run with ISO RR.

I must be misunderstanding you. Or you and I have completely opposite
experiences.
In my experience use of RR is extremely rare.

Yes, for some ridiculous reason, the SPUFI default isolation level is
RR, but almost every place I have worked has sooner or later (usually
sooner) made it so that one needs special authorization to use the
DSNESPRR plan.

And BINDing an application program with RR is seriously questioned and
would require significant justification.

In what type of environment do you see lots of RR access being
successful?

Thanks

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Avram Friedman
Sent: Friday, January 11, 2008 9:27 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Stored Procedure Issue

From your remark that this happens on a busyier system I would suspect
locking. Most DB2 qurries by default run with an issolation level of RR
or repeatable read. The Lots of locks are held for the duration of the
qurey.
I would suggest issolation level UR if possible or if not CS, you may
need an administrator to help you with this change.

If you get delayed with CS its some other thread running on the same
DB2.
If you get delayed with UR its most likly a non thread related
configuration issue.

Regards
Avram Friedman

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Stored Procedure Issue
(in response to David Seibert)
Since I couldn't attach the snapshot log, I decided to breakup the log into 3
postings.


Posting 1.
----------



Any help is greatly appreciated.


Application handle = 1034


Application status = UOW Executing


Status change time = Not Collected


Application code page = 819


Application country/region code = 1


DUOW correlation token = *LOCAL.db2i.080108234372


Application name = db2bp


Application ID = *LOCAL.db2i.080108234372


Sequence number = 00001


TP Monitor client user ID =


TP Monitor client workstation name =


TP Monitor client application name =


TP Monitor client accounting string =


Connection request start timestamp = 01/08/2008
17:42:56.522646


Connect request completion timestamp = 01/08/2008
17:42:56.523299


Application idle time = Not Collected


CONNECT Authorization ID = DBUSER


Client login ID = dbuser


Configuration NNAME of client = mybox


Client database manager product ID = SQL09013


Process ID of client application = 541054


Platform of client application = AIX 64BIT


Communication protocol of client = Local Client


Inbound communication address = *LOCAL.db2i


Database name = MYDB


Database path = /home/db2inst1/db2i/
NODE0000/RG


L00002/


Client database alias = MYDB


Input database alias =


Last reset timestamp =


Snapshot timestamp = 01/09/2008
11:42:14.587376


The highest authority level granted =


Direct DBADM authority


Direct CREATETAB authority


Direct BINDADD authority


Direct CONNECT authority


Direct CREATE_NOT_FENC authority


Direct LOAD authority


Direct IMPLICIT_SCHEMA authority


Direct CREATE_EXT_RT authority


Direct QUIESCE_CONN authority


Indirect SYSADM authority


Coordinating database partition number = 0


Current database partition number = 0


Coordinator agent process or thread ID = 753790


Agents stolen = 0


Agents waiting on locks = 0


Maximum associated agents = 13


Priority at which application agents work = 0


Priority type = Dynamic


Lock timeout (seconds) = 180


Locks held by application = 6189


Lock waits since connect = 0


Time application waited on locks (ms) = Not Collected


Deadlocks detected = Not Collected


Lock escalations = 0


Exclusive lock escalations = 0


Number of Lock Timeouts since connected = 0


Total time UOW waited on locks (ms) = Not Collected


Total sorts = 194


Total sort time (ms) = Not Collected


Total sort overflows = 1


Buffer pool data logical reads = Not Collected


Buffer pool data physical reads = Not Collected


Buffer pool temporary data logical reads = Not Collected


Buffer pool temporary data physical reads = Not Collected


Buffer pool data writes = Not Collected


Buffer pool index logical reads = Not Collected


Buffer pool index physical reads = Not Collected


Buffer pool temporary index logical reads = Not Collected


Buffer pool temporary index physical reads = Not Collected


Buffer pool index writes = Not Collected


Buffer pool xda logical reads = Not Collected


Buffer pool xda physical reads = Not Collected


Buffer pool temporary xda logical reads = Not Collected


Buffer pool temporary xda physical reads = Not Collected


Buffer pool xda writes = Not Collected


Total buffer pool read time (milliseconds) = Not Collected


Total buffer pool write time (milliseconds)= Not Collected


Time waited for prefetch (ms) = Not Collected


Unread prefetch pages = Not Collected


Direct reads = Not Collected


Direct writes = Not Collected


Direct read requests = Not Collected


Direct write requests = Not Collected


Direct reads elapsed time (ms) = Not Collected


Direct write elapsed time (ms) = Not Collected


Number of SQL requests since last commit = 1834


Commit statements = 0


Rollback statements = 0


Dynamic SQL statements attempted = 1016


Static SQL statements attempted = 818


Failed statement operations = 0


Select SQL statements executed = 142


Xquery statements executed = 0


Update/Insert/Delete statements executed = 678


DDL statements executed = 336


Inactive stmt history memory usage (bytes) = 0


Internal automatic rebinds = 1


Internal rows deleted = 0


Internal rows inserted = 0


Internal rows updated = 0


Internal commits = 1


Internal rollbacks = 0


Internal rollbacks due to deadlock = 0


Binds/precompiles attempted = 0


Rows deleted = 0


Rows inserted = 0


Rows updated = 677


Rows selected = 477


Rows read = 243340


Rows written = 6304


UOW log space used (Bytes) = Not Collected


Previous UOW completion timestamp = Not Collected


Elapsed time of last completed uow (sec.ms)= Not Collected


UOW start timestamp = Not Collected


UOW stop timestamp = Not Collected


UOW completion status = Not Collected


Open remote cursors = 0


Open remote cursors with blocking = 0


Rejected Block Remote Cursor requests = 0


Accepted Block Remote Cursor requests = 1


Open local cursors = 1


Open local cursors with blocking = 1


Total User CPU Time used by agent (s) = 1.334523


Total System CPU Time used by agent (s) = 0.338933


Host execution elapsed time = Not Collected


Package cache lookups = 1573


Package cache inserts = 685


Application section lookups = 1922


Application section inserts = 375


Catalog cache lookups = 856


Catalog cache inserts = 98


Catalog cache overflows = 0


Catalog cache high water mark = 0


Workspace Information


Shared high water mark = 172600


Total shared overflows = 0


Total shared section inserts = 3

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Stored Procedure Issue
(in response to SUBSCRIBE DB2-L Anonymous)
Posting 2
---------


Total shared section lookups = 3


Private high water mark = 810144


Total private overflows = 0


Total private section inserts = 372


Total private section lookups = 434


Most recent operation = Fetch


Cursor name =


Most recent operation start timestamp = Not Collected


Most recent operation stop timestamp = Not Collected


Agents associated with the application = 7


Number of hash joins = 0


Number of hash loops = 0


Number of hash join overflows = 0


Number of small hash join overflows = 0


Agent process/thread ID = 753790


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 131072


High water mark (bytes) = 131072


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 1179648


High water mark (bytes) = 1179648


Configured size (bytes) = 8585216


Agent process/thread ID = 270794


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 131072


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 569492


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 196608


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 688266


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 299466


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 123216


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 229846


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 131072


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

I have a stored procedure which is called by db2 "call ....."
statement from ksh script. When I run it in db2 v. 9.1.1 environment,
everything works great. When running from v. 9.1.3 environment, after
a while the stored procedure starts to hang and snapshot says it is on
fetch statement. The job of the stored procedure is to loop through
the cursor executing ddl command found in one of the columns and
updating the table which is cursored. It is using handler and hold
options.

Now, I don't mean to suggest that the problem stems from the version
difference. This is strictly for information purposes. In fact, I
think it has more to do with db configuration. Below, is the
snapshot.


Any help is greatly appreciated.


Application handle = 1034


Application status = UOW Executing


Status change time = Not Collected


Application code page = 819


Application country/region code = 1


DUOW correlation token = *LOCAL.db2i.080108234372


Application name = db2bp


Application ID = *LOCAL.db2i.080108234372


Sequence number = 00001


TP Monitor client user ID =


TP Monitor client workstation name =


TP Monitor client application name =


TP Monitor client accounting string =


Connection request start timestamp = 01/08/2008
17:42:56.522646


Connect request completion timestamp = 01/08/2008
17:42:56.523299


Application idle time = Not Collected


CONNECT Authorization ID = DBUSER


Client login ID = dbuser


Configuration NNAME of client = mybox


Client database manager product ID = SQL09013


Process ID of client application = 541054


Platform of client application = AIX 64BIT


Communication protocol of client = Local Client


Inbound communication address = *LOCAL.db2i


Database name = MYDB


Database path = /home/db2inst1/db2i/
NODE0000/RG


L00002/


Client database alias = MYDB


Input database alias =


Last reset timestamp =


Snapshot timestamp = 01/09/2008
11:42:14.587376


The highest authority level granted =


Direct DBADM authority


Direct CREATETAB authority


Direct BINDADD authority


Direct CONNECT authority


Direct CREATE_NOT_FENC authority


Direct LOAD authority


Direct IMPLICIT_SCHEMA authority


Direct CREATE_EXT_RT authority


Direct QUIESCE_CONN authority


Indirect SYSADM authority


Coordinating database partition number = 0


Current database partition number = 0


Coordinator agent process or thread ID = 753790


Agents stolen = 0


Agents waiting on locks = 0


Maximum associated agents = 13


Priority at which application agents work = 0


Priority type = Dynamic


Lock timeout (seconds) = 180


Locks held by application = 6189


Lock waits since connect = 0


Time application waited on locks (ms) = Not Collected


Deadlocks detected = Not Collected


Lock escalations = 0


Exclusive lock escalations = 0


Number of Lock Timeouts since connected = 0

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Stored Procedure Issue
(in response to SUBSCRIBE DB2-L Anonymous)
posting 3
---------



Total time UOW waited on locks (ms) = Not Collected


Total sorts = 194


Total sort time (ms) = Not Collected


Total sort overflows = 1


Buffer pool data logical reads = Not Collected


Buffer pool data physical reads = Not Collected


Buffer pool temporary data logical reads = Not Collected


Buffer pool temporary data physical reads = Not Collected


Buffer pool data writes = Not Collected


Buffer pool index logical reads = Not Collected


Buffer pool index physical reads = Not Collected


Buffer pool temporary index logical reads = Not Collected


Buffer pool temporary index physical reads = Not Collected


Buffer pool index writes = Not Collected


Buffer pool xda logical reads = Not Collected


Buffer pool xda physical reads = Not Collected


Buffer pool temporary xda logical reads = Not Collected


Buffer pool temporary xda physical reads = Not Collected


Buffer pool xda writes = Not Collected


Total buffer pool read time (milliseconds) = Not Collected


Total buffer pool write time (milliseconds)= Not Collected


Time waited for prefetch (ms) = Not Collected


Unread prefetch pages = Not Collected


Direct reads = Not Collected


Direct writes = Not Collected


Direct read requests = Not Collected


Direct write requests = Not Collected


Direct reads elapsed time (ms) = Not Collected


Direct write elapsed time (ms) = Not Collected


Number of SQL requests since last commit = 1834


Commit statements = 0


Rollback statements = 0


Dynamic SQL statements attempted = 1016


Static SQL statements attempted = 818


Failed statement operations = 0


Select SQL statements executed = 142


Xquery statements executed = 0


Update/Insert/Delete statements executed = 678


DDL statements executed = 336


Inactive stmt history memory usage (bytes) = 0


Internal automatic rebinds = 1


Internal rows deleted = 0


Internal rows inserted = 0


Internal rows updated = 0


Internal commits = 1


Internal rollbacks = 0


Internal rollbacks due to deadlock = 0


Binds/precompiles attempted = 0


Rows deleted = 0


Rows inserted = 0


Rows updated = 677


Rows selected = 477


Rows read = 243340


Rows written = 6304


UOW log space used (Bytes) = Not Collected


Previous UOW completion timestamp = Not Collected


Elapsed time of last completed uow (sec.ms)= Not Collected


UOW start timestamp = Not Collected


UOW stop timestamp = Not Collected


UOW completion status = Not Collected


Open remote cursors = 0


Open remote cursors with blocking = 0


Rejected Block Remote Cursor requests = 0


Accepted Block Remote Cursor requests = 1


Open local cursors = 1


Open local cursors with blocking = 1


Total User CPU Time used by agent (s) = 1.334523


Total System CPU Time used by agent (s) = 0.338933


Host execution elapsed time = Not Collected


Package cache lookups = 1573


Package cache inserts = 685


Application section lookups = 1922


Application section inserts = 375


Catalog cache lookups = 856


Catalog cache inserts = 98


Catalog cache overflows = 0


Catalog cache high water mark = 0


Workspace Information


Shared high water mark = 172600


Total shared overflows = 0


Total shared section inserts = 3


Total shared section lookups = 3


Private high water mark = 810144


Total private overflows = 0


Total private section inserts = 372


Total private section lookups = 434


Most recent operation = Fetch


Cursor name =


Most recent operation start timestamp = Not Collected


Most recent operation stop timestamp = Not Collected


Agents associated with the application = 7


Number of hash joins = 0


Number of hash loops = 0


Number of hash join overflows = 0


Number of small hash join overflows = 0


Agent process/thread ID = 753790


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 131072


High water mark (bytes) = 131072


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 1179648


High water mark (bytes) = 1179648


Configured size (bytes) = 8585216


Agent process/thread ID = 270794


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 131072


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 569492


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 196608


High water mark (bytes) = 262144

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Stored Procedure Issue
(in response to SUBSCRIBE DB2-L Anonymous)
posting 4
---------


Configured size (bytes) = 8585216


Agent process/thread ID = 688266


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 299466


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 123216


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 65536


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216


Agent process/thread ID = 229846


Agent Lock timeout (seconds) = 180


Memory usage for agent:


Memory Pool Type = Other Memory


Current size (bytes) = 65536


High water mark (bytes) = 131072


Configured size (bytes) = 25769803776


Memory Pool Type = Application Heap


Current size (bytes) = 262144


High water mark (bytes) = 262144


Configured size (bytes) = 8585216

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Stored Procedure Issue
(in response to SUBSCRIBE DB2-L Anonymous)
Thanks for your help. There is a piece of info I neglected to mention, this db2
is on aix 5.3. Based on the way you answered the question, I suspect you
understood it correctly.

I am, also, deducing based on your answer that with dynamic sql, unless
explicitly defined, db2 chooses an isolation level. That isolation level is RR
most of the time. Is my understanding correct?


Thanks again

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms