LockLevel question
Hello all, I am trying to find a way to determine a tables locklevel from the Sysmaster database. I see that "sysdic" has a "dic_locklevel" that is set to 1 for page and 2 for row but this is only benifitial if the table is being accessed at the time of query. I know I can get it in the systables in each database but security restrictions forbid me from doing this on certain databases. Any help is appreciated as always. Bobby
It seems that the sysptnhdr.flags field holds the key (thanks Dick). It
looks like bitvals 1,2,5,10,25,41,50 and 82 get flipped when altering from
row to page level locking..
So can anyone verify the following select statement to be true......
select dbsname,tabname,"R" locklevel from sysptnhdr,systabnames
where bitval(flags,1) = 0
and bitval(flags,2) = 1
and bitval(flags,5) = 0
and bitval(flags,10) = 1
and bitval(flags,25) = 0
and bitval(flags,41) = 0
and bitval(flags,50) = 1
and bitval(flags,82) = 1
and sysptnhdr.partnum = systabnames.partnum
and tabname not matches "sys*"
union
select dbsname,tabname,"P" locklevel from sysptnhdr,systabnames
where bitval(flags,1) = 1
and bitval(flags,2) = 0
and bitval(flags,5) = 1
and bitval(flags,10) = 0
and bitval(flags,25) = 1
and bitval(flags,41) = 1
and bitval(flags,50) = 0
and bitval(flags,82) = 0
and sysptnhdr.partnum = systabnames.partnum
and tabname not matches "sys*"
Thanks
Bobby Medus wrote:
> Hello all,
>
> I am trying to find a way to determine a tables locklevel from the
> Sysmaster database. I see that "sysdic" has a "dic_locklevel" that is
> set to 1 for page and 2 for row but this is only benifitial if the table
> is being accessed at the time of query. I know I can get it in the
> systables in each database but security restrictions forbid me from
> doing this on certain databases.
>
> Any help is appreciated as always.
>
> Bobby
The information is in the sysptnhdr table in the flags column. If flag & 1
then page level else if flag 2 then row level locking.
So:
select dbsname, tabname, bitval(flags, 2) row_level
from systabnames, sysptnhdr
where systabnames.partnum = sysptnhdr.partnum;
If you have 7.3x you can use the case construct to translate the flag to
text.
Art S. Kagel
Bobby Medus wrote:
>
> Hello all,
>
> I am trying to find a way to determine a tables locklevel from the
> Sysmaster database. I see that "sysdic" has a "dic_locklevel" that is
> set to 1 for page and 2 for row but this is only benifitial if the table
> is being accessed at the time of query. I know I can get it in the
> systables in each database but security restrictions forbid me from
> doing this on certain databases.
>
> Any help is appreciated as always.
>
> Bobby