Friday, November 28, 2008

Tip #20 – Getting Management Information with SQL

Advantage has several system procedures dedicated to retrieving management information. This information ranges from installation and configuration information to information about users.

One of the most useful is sp_mgGetUsageInfo which returns the current, max used, configured and rejected counts like you see in the Advantage Configuration utility. The system procedure does not take any arguments so running EXECUTE PROCEDURE sp_mgGetUsageInfo() will return the following table.

Advantage Usage Info

Other server information can be obtained using sp_mgGetActivityInfo which returns the number of operations, logged errors and up time. For information on communication statistics you can use sp_mgGetCommStats which provides information on communication to the server. To see how much memory your configuration options are using run the sp_mgGetConfigMemory system procedure.

The sp_mgGetInstallInfo system procedure provides information about the installation options. These include the registered owner, serial number, user option, version, install date, character sets (ANSI and OEM) and whether or not replication is enabled. This information can be very valuable if your application relies on a particular server version or if you need replication prior to adding publications and subscriptions to a data dictionary as part of an upgrade.

Several system procedures provide information about current users. You can get a list of connected users with the sp_mgGetConnectedUsers procedure. To get a list of users who have  a specified table or index opened use sp_mgGetTableUsers or sp_GetIndexUsers. You can also get lock information with sp_mgGetLockOwner and sp_mgGetUserLocks. You can also disconnect users with the sp_mgKillUser.

No comments: