des_putdlevel0cnt() wait by waitutil for L0 completion

[former_member197005]

Good afternoon!

We found that on the test server some of the processes work with the wait type "des_putdlevel0cnt () wait by waitutil for L0 completion". But I could not find the description of this wait type.

Except this link https://wiki.scn.sap.com/wiki/display/SYBASE/ASE+Wait+Events+650+to+699 , but no helpfull information here.

Help me understand what this wait type means is and how to deal with it.

Thanks.

[former_member188958 · Accepted solution]

"des_putdlevel0cnt () wait by waitutil for L0 completion"

"L0" refers to scans being done at isolation level zero."DES" is an object descriptor that stores, among other things, the number of isolation level 0 scans currently in progress on the table, or whether a utility command is currently active on the table..

"waitutils" are a subset of the utility commands (CREATE, ALTER, DROP, REORG) that can be run in "online" mode, such as REORG DEFRAG and the ALTER DATABASE OFF (aka "shrink database) commands.

Isolation level 0 scans and utility commands generally cannot run concurrently on the same object and will abort if the other type of command active on the object. However these new online utilities ("waitutils") work in small quick chunks to allow concurrent activities. The waitutils and L0 scans wait for each other to complete rather than abort. There is also a complimentary "des_putdlevel0cnt() wait by L0 for waitutil completion" wait event.

There isn't really anything you can do about these, aside from not using the new "online" utility commands or not using isolation level 0 reads.

-bret

[former_member188958]

Traceflag might help, it causes processes that would normally abort with an 8233 error to wait instead when run on a table undergoing REORG REBUILD.

[former_member197005]

Thanks Bret!

With a small research we found that if disable the "enable utility lvl 0 scan wait" option, then the type of L0 wait does not appear.

However, when "enable utility lvl 0 scan wait" option is enabled. Some operations stop work with ERROR: "Cannot truncate table 'Sync' because the are one or more isolation level 0 scans, or reorg Command, active on the table". Is it possible to make the operation get into some kind of lock, rather than stop working with an error?