Snapshot_LockWa it table function question

[peteh]

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_lockwa it 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.S NAPSHOT_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".

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_LockWa it? Any help most appreciated.

Pete H

[peteh]

>* 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_LOCKWA I*T('DBNAME', -1)) as a;" and get no rowsreturned. (DBNAME is really the database name of the AIX target
database)
* As soon as I issue the "commit" on 1st session, the 2nd query returnsthe 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.

[Serge Rielau]

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
exclusuivel y by the other uncommitted work.
* I run the following query "select a.* from
TABLE(SYSPROC .SNAPSHOT_LOCKW AI*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

[peteh]

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_BUFPOO L) = ON
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = ON
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMEST AMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = OFF
----------------------------------------------------------------

[Serge Rielau]

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_BUFPOO L) = ON
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = ON
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMEST AMP) = 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