Hello All;
The environment is DB2 AIX 8.1.5 (parallel edition) being accessed by a
Windows 8.1.2 admin client via Quest. I'm trying to use the
snapshot_lockwait table function and getting unexpected results:
* I run a query to "select * from tableA for update" from a Quest
session with autocommit turned off. Results are returned as expected
* From another Quest session, "selct * from tableA". As expected, no
results are returned and session "hangs" since tableA is being held
exclusuively by the other uncommitted work.
* I run the following query "select a.* from
TABLE(SYSPROC.SNAPSHOT_LOCKWAIT('DBNAME', -1)) as a;" and get no rows
returned. (DBNAME is really the database name of the AIX target
database)
* As soon as I issue the "commit" on 1st session, the 2nd query returns
the rows that were locked pending "commit".

I would expect the 3rd query (referencing the table function would
return rows describing the 2nd session "waiting" for the 1st session to
release locks... I'm stumped. Is there something I'm missing related to
Snapshot_LockWait? Any help most appreciated.

Pete H

>* I run a query to "select * from tableA for update" from a Quest
>session with autocommit turned off. Results are returned as expected
>* From another Quest session, "selct * from tableA". As expected, no
>results are returned and session "hangs" since tableA is being held
>exclusuively by the other uncommitted work.
>* I run the following query "select a.* from
>TABLE(SYSPROC.SNAPSHOT_LOCKWAI*T('DBNAME', -1)) as a;" and get no
rows
>returned. (DBNAME is really the database name of the AIX target
>database)
>* As soon as I issue the "commit" on 1st session, the 2nd query
returns
>the rows that were locked pending "commit".

All of the above is still true EXCEPT that I have confirmed that the
lock monitor switch is ON and the call to the table function in step 3
passes -2 as the 2nd parameter to indicate that I want lock waits
across all partitions.

I still get no results. Help!? I would really like to use this feature
to give easy access to lock wait activity for on-call support
personnel.

peteh wrote:
>>* I run a query to "select * from tableA for update" from a Quest
>>session with autocommit turned off. Results are returned as expected
>>* From another Quest session, "selct * from tableA". As expected, no
>>results are returned and session "hangs" since tableA is being held
>>exclusuively by the other uncommitted work.
>>* I run the following query "select a.* from
>>TABLE(SYSPROC.SNAPSHOT_LOCKWAI*T('DBNAME', -1)) as a;" and get no
>
> rows
>
>>returned. (DBNAME is really the database name of the AIX target
>>database)
>>* As soon as I issue the "commit" on 1st session, the 2nd query
>
> returns
>
>>the rows that were locked pending "commit".
>
>
> All of the above is still true EXCEPT that I have confirmed that the
> lock monitor switch is ON and the call to the table function in step 3
> passes -2 as the 2nd parameter to indicate that I want lock waits
> across all partitions.
>
> I still get no results. Help!? I would really like to use this feature
> to give easy access to lock wait activity for on-call support
> personnel.
>
Backstage says:
"you need to turn on the default monitor switches for the UDF to return
data. The UDF can't see the switches set in the CLP session."
I have no clue what that means though.... :-(

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge;
That makes sense. Given the monitor switch settings below, would we
incur any significant overhead by turning the "LOCK" switch on? Seems
like if the Statement and Timestamp switches are already on, the impact
would be minimal. Can you (or the the "backstage") confirm? Thanks very
much!

Pete H

---------------------------------------------------------------
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = ON
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = ON
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = OFF
----------------------------------------------------------------

peteh wrote:
> Hi Serge;
> That makes sense. Given the monitor switch settings below, would we
> incur any significant overhead by turning the "LOCK" switch on? Seems
> like if the Statement and Timestamp switches are already on, the impact
> would be minimal. Can you (or the the "backstage") confirm? Thanks very
> much!
>
> Pete H
>
> ---------------------------------------------------------------
> Default database monitor switches
> Buffer pool (DFT_MON_BUFPOOL) = ON
> Lock (DFT_MON_LOCK) = OFF
> Sort (DFT_MON_SORT) = ON
> Statement (DFT_MON_STMT) = ON
> Table (DFT_MON_TABLE) = OFF
> Timestamp (DFT_MON_TIMESTAMP) = ON
> Unit of work (DFT_MON_UOW) = OFF
> Monitor health of instance and databases (HEALTH_MON) = OFF
> ----------------------------------------------------------------
>
Backstage says impact of the monitor switch should be below 4% (erring
on the save side). I'm told that the LOCK snapshot itself will hit
though unlike the other snapshots. You will see a blip when it's taken.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab