Difference between APL & DOL

[Praveen]

Hi,

Could someone please explain on below:

(i) What is APL & DOL tables?
(ii) what are the differences b/w APL & DOL tables?
(iii) How do we create APL & DOL tables?
(iv) For a given table in a database, how do i know which locking
scheme is used? is there any method to retrieve this info?
(v) By default, when we create a table in ASE, which scheme (APL or
DOL) is used?
(vi) How do we convert APL -> DOL tables ....vice-versa?

I am having this doubt for a long time, but was unable to get clear
clarification. Could you please help on this query?

[bslade]

Post by Praveen
Hi,
(i) What is APL & DOL tables?
(ii) what are the differences b/w APL & DOL tables?
(iii) How do we create APL & DOL tables?
(iv) For a given table in a database, how do i know which locking
scheme is used? is there any method to retrieve this info?
(v) By default, when we create a table in ASE, which scheme (APL or
DOL) is used?
(vi) How do we convert APL -> DOL tables ....vice-versa?
I am having this doubt for a long time, but was unable to get clear
clarification. Could you please help on this query?

APL is "all pages locked". Ie. the old normal locking scheme. "All
pages" refers to index and data pages.

DOL is "Data [page] Only Locking". This is a fancy scheme where
updates can be made without taking out locks on the index tree.
Benefits are less contention and you can use various "reorg" commands
to incrementally defrag data. Drawbacks are non-parallel select
statements with no order by clause are no longer guaranteed to return
data in clustered index order. This restriction is poorly documented
even though it's a really big deal for some big data applications
(doing a sort on hundreds of gbytes of data is sometimes just not an
option)

There's also data rows locking which is similar to DOL except for
rows.

To see the current scheme, use sp_help <table_name> and look for the
line starting with "Lock scheme".

To change a table's locking scheme use "alter table mytable lock
<lock_scheme>" where <lock_scheme> is allpages, datapages, or
datarows. Be careful, changing to/from allpages requires more than
2x times the space of the table during the change (it has to rewrite
the whole table into a new table).

See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/X25549.htm
for more details.

Ben

[bslade]

Just one more note, if you need to guarantee clustered index retrieval
order for data/rows only locking tables without sorting in tempdb, use
an "(INDEX my_clustered_index_name PARALLEL 1)" hint after the table
name together with an 'order by' clause with the columns in the
clustered index. The server should scan the index tree to get the
clustered index fields (in clustered index order), then retrieve each
data row for the key fields just fetched. Only slightly slower than
scanning just the data pages in a all pages locked table.

Ben