1

There are some 20 tables in a database of Sybase instance, where i need to find the locking schema of tables. Once locking schema is found, i need to compare it in other database which also have same 20 tables. build-ASE16 SP2

2

Use the following to determine the locking schema for each user table in an ASE database:

SELECT TableName = CONVERT(VARCHAR(30), so.name)
    , LockScheme = CONVERT(VARCHAR(30), lockscheme(so.id))
FROM sysobjects so
WHERE so.type IN ('S', 'U');

The CONVERT(VARCHAR(30), xxx) is simply to make the output easier to see in a console session. If you are using a GUI tool to connect to the ASE instance, they are probably not necessary. Be aware that if you have table names longer than 30 characters, they will be truncated in the output.

Run that in both instances and simply compare the output in BeyondCompare, or some other differencing tool.

4
  • Thanks Max, the statement you provided didn't work. -- AllPages Lock Tables select name 'AllPages Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 8192 = 8192 and name = "<obj name>" -- DataPages Lock Tables select name 'DataPages Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 16384 = 16384 and name = "<obj name>" -- DataRows Lock Tables select name 'DataRows Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 32768 = 32768 and name = "<obj name>"
    – user115806
    Oct 12 '16 at 17:32
  • use the above statements to identify the schema of table
    – user115806
    Oct 12 '16 at 17:34
  • I tested my answer on SAP ASE 15.7. It worked for me.
    – Hannah Vernon
    Oct 12 '16 at 19:54
  • If you have a query that works for you, add it as an answer, instead of a comment.
    – Hannah Vernon
    Oct 12 '16 at 19:55
1

As suggested by Max, adding the answer that worked for me.

This is in ASE16 SP2 version,
To Check if database object has AllPages Lock schema

select name 'AllPages Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 8192 = 8192 and name = **"<obj name>"**

To Check if database object has DataPages Lock schema

select name 'DataPages Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 16384 = 16384 and name = **"<obj name>"**

To Check if database object has DataRows Lock schema

select name 'DataRows Lock Tables', sysstat2 from sysobjects where type = 'U' and sysstat2 & 32768 = 32768 and name = **"<obj name>"**

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.