Advantage Developer Zone

 
 
 

Obtaining Advantage Management Information using SQL Statements

Sunday, March 14, 2004

Advantage Database Server includes a set of management APIs that return information about the status of the server. These include getting a list of connected users, open tables and indexes, configuration parameters and other usage information. However, these management APIs are not available to all clients due to language limitations.

All 32bit Advantage clients can use SQL statements to access data via Advantage. Therefore, providing a mechanism for obtaining the management information through SQL would allow any 32bit Advantage client access to this information.

Advantage Management Procedures

The Advantage Management Procedures are a set of Advantage Extended Procedures (AEPs) that encapsulate the Advantage Management API. These procedures are available to all Advantage clients capable of using SQL.

Advantage Management Procedures return information about the Advantage Database Server through static SQL cursors. Because Advantage Extended Procedures require an Advantage Data Dictionary, an active connection to an Advantage Data Dictionary is required to execute an Advantage Management Procedure.

Installing the Advantage Management Procedures

Since the Advantage Management Procedures are implemented as a set of Advantage Extended Procedures, they must be registered in an Advantage Data Dictionary. The ADSMG.aep file contains the Advantage Management Procedures and should be placed in the same directory as the Advantage Data Dictionary.

The Advantage Management Procedure example comes with a data dictionary containing definitions for all of the management procedures. The SQL file ADSMG_Test.SQL contains example syntax for each of the available management procedures.

You can also add the management procedures to your own data dictionary by using the install script in ADSMG_Install.SQL. Running this script will create the definitions for all of the management procedures in your application. Simply open the ADSMG_Install.SQL file in the Advantage Data Architect (ARC) Native SQL window. Connect to your data dictionary and execute the script.

Using the Advantage Management Procedures

From the Native SQL Window in Advantage Data Architect, connect to a data dictionary that has the Advantage Management Procedures included. You must connect to Advantage Database ( Remote ) Server to run the procedures.

For example, to get a list of connected users, use the amp_GetConnectedUsers procedure. Using amp_GetConnectedUsers returns a recordset that contains the following fields: UserName, ConnNumber, DictionaryUser and Address. More details about each procedure can be found in the Advantage_Management_Procedures Help File included in the download. Run the amp_GetConnectedUsers procedure using the following SQL statement.

EXECUTE PROCEDURE amp_GetConnectedUsers()

To obtain usage information similar to the Database Information Tab on the Advantage Management Utility in the Advantage Data Architect, use the amp_GetUsageInfo().

EXECUTE PROCEDURE amp_GetUsageInfo()

This procedure will return a recordset like the following:

Item

InUse

MaxUsed

Configured

Rejected

Users

1

1

5

0

Connections

1

1

5

0

Work Areas

2

2

125

0

Tables

1

1

100

0

Indexes

1

1

150

0

Locks

0

0

0

TPS Header Elems

0

0

400

0