Automatic runstats

SUBSCRIBE DB2-L Anonymous

Automatic runstats
I have a stored procedure which is called by db2 "call ....."
statement from ksh script. When running from AIX DB2 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.


The diagnose log shows these error over and over...


2008-01-10-05.27.44.787553-360 I16857571A307 LEVEL: Error
PID : 331782 TID : 1147 PROC : db2acd 0
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.080110112742
FUNCTION: DB2 UDB, Automatic Table Maintenance, Atm::iter, probe:200
MESSAGE : ZRC=0xFFFFFFFF=-1

2008-01-10-06.10.39.061586-360 I16857879A509 LEVEL: Error
PID : 331782 TID : 1148 PROC : db2acd 0
INSTANCE: db2inst1 NODE : 000
APPID : *LOCAL.db2inst1.080110120743
FUNCTION: DB2 UDB, Automatic Table Maintenance, atmRefreshInfoTable,
probe:300
MESSAGE : ZRC=0xFFFFFC71=-911
DATA #1 : <preformatted>
AutoStats: [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction
has been rolled back because of a deadlock or t
imeout. Reason code "68". SQLSTATE=40001


This is a function of automatic runstats being turned and resources not being
available for automatic runstats to complete it's job. Could this be the
reason why the stored procedure is hanging? If yes, why?

Thanks in advance

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

tim malamphy

Re: Automatic runstats
(in response to SUBSCRIBE DB2-L Anonymous)
You might want to set up an event monitor to capture
the deadlocks. This will give you the actual
resources being locked and by whom.
--- Roman Gelfand <[login to unmask email]> wrote:

> I have a stored procedure which is called by db2
> "call ....."
> statement from ksh script. When running from AIX
> DB2 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.
>
>
> The diagnose log shows these error over and over...
>
>
> 2008-01-10-05.27.44.787553-360 I16857571A307
> LEVEL: Error
> PID : 331782 TID : 1147
> PROC : db2acd 0
> INSTANCE: db2inst1 NODE : 000
> APPID : *LOCAL.db2inst1.080110112742
> FUNCTION: DB2 UDB, Automatic Table Maintenance,
> Atm::iter, probe:200
> MESSAGE : ZRC=0xFFFFFFFF=-1
>
> 2008-01-10-06.10.39.061586-360 I16857879A509
> LEVEL: Error
> PID : 331782 TID : 1148
> PROC : db2acd 0
> INSTANCE: db2inst1 NODE : 000
> APPID : *LOCAL.db2inst1.080110120743
> FUNCTION: DB2 UDB, Automatic Table Maintenance,
> atmRefreshInfoTable,
> probe:300
> MESSAGE : ZRC=0xFFFFFC71=-911
> DATA #1 : <preformatted>
> AutoStats: [IBM][CLI Driver][DB2/AIX64] SQL0911N
> The current transaction
> has been rolled back because of a deadlock or t
> imeout. Reason code "68". SQLSTATE=40001
>
>
> This is a function of automatic runstats being
> turned and resources not being
> available for automatic runstats to complete it's
> job. Could this be the
> reason why the stored procedure is hanging? If yes,
> why?
>
> Thanks in advance
>
> 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
>



____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

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