You can use a standard SQL SELECT statement to get information from the ADSM database. The SELECT statement that ADSM allows is a subset of the SQL92 and SQL93 standards.
ADSM also provides an open database connectivity (ODBC) driver. The driver allows you to use a relational database product such as Lotus Approach to query the database and display the results.
ADSM provides an ODBC driver for Windows 95, Windows NT 3.51, and Windows NT 4.0 operating systems. The driver supports the ODBC Version 2.5 application programming interface (API). Because ADSM supports only the SQL SELECT statement (query), the driver does not conform to any ODBC API or SQL grammar conformance level. After you install this driver, you can use a spreadsheet or database application that complies with ODBC to access the ADSM database for information.
ADSM ODBC driver set-up is included in the ADSM client installation package. The ADSM client installation program can install the ODBC driver and set the corresponding registry values for the driver and data sources. For more information on setting up the ODBC driver, see ADSM Installing the Clients.
When you open the ADSM database through an ODBC application, you must log on to the ADSM server (the defined data source) using the name and password of a registered ADSM administrator. After you log on to the server, you can perform query functions provided by the ODBC application to access ADSM database information.
You can issue the ADSM SELECT command from the command line of an administrative client. You cannot issue this command from the server console.
For the ADSM SELECT command, ADSM supports a subset of the syntax of the SELECT statement as documented in the SQL92 and SQL93 standards. For complete information about how to use the SELECT statement, refer to these standards or to other publications about SQL.
Issuing the SELECT command to the ADSM server can use a significant amount of server resources to run the query. Complicated queries or queries that run for a long time can interfere with normal server operations. If the SELECT command that you issue is complex or large enough to require significant time and resource from the server to generate the results, you will receive a message asking you to confirm that you wish to continue.
| Database Space Requirements: | To allow any use of the SELECT command, the ADSM database must have at least 4MB of free space. For complex queries that require significant processing, more free space is required in the database. |
To help you find what information is available in the database, ADSM provides three system catalog tables:
For example, to get a list of all tables available for querying in the database, enter the following command:
select * from syscat.tables
The following shows part of the results of this command:
+--------------------------------------------------------------------------------+ | TABSCHEMA: ADSM | | TABNAME: ACTLOG | | CREATE_TIME: | | COLCOUNT: 10 | |INDEX_COLCOUNT: 1 | | UNIQUE_INDEX: FALSE | | REMARKS: Server activity log | | | | TABSCHEMA: ADSM | | TABNAME: ADMINS | | CREATE_TIME: | | COLCOUNT: 14 | |INDEX_COLCOUNT: 1 | | UNIQUE_INDEX: TRUE | | REMARKS: Server administrators | | | | TABSCHEMA: ADSM | | TABNAME: ADMIN_SCHEDULES | | CREATE_TIME: | | COLCOUNT: 15 | |INDEX_COLCOUNT: 1 | | UNIQUE_INDEX: TRUE | | REMARKS: Administrative command schedules | | | | TABSCHEMA: ADSM | | TABNAME: ARCHIVES | | CREATE_TIME: | | COLCOUNT: 10 | |INDEX_COLCOUNT: 5 | | UNIQUE_INDEX: FALSE | | REMARKS: Client archive files | +--------------------------------------------------------------------------------+
The following examples can give you an idea of the power and flexibility of the ADSM SELECT command. For many more examples of the command, see the ADSM Administrator's Reference.
Example 1: Find the number of nodes by type of operating system by issuing the following command:
select platform_name,count(*) as "Number of Nodes" from nodes group by platform_name
This command gives results like the following:
+--------------------------------------------------------------------------------+ |PLATFORM_NAME Number of Nodes | |------------- --------------- | |OS/2 45 | |AIX 90 | |Windows 35 | | | +--------------------------------------------------------------------------------+
Example 2: For all active client sessions, determine how long have they been connected and their effective throughput in bytes per second:
select session_id as "Session", client_name as "Client", state as "State", current_timestamp-start_time as "Elapsed Time", (cast(bytes_sent as decimal(18,0)) / cast((current_timestamp-start_time)seconds as decimal(18,0))) as "Bytes sent/second", (cast(bytes_received as decimal(18,0)) / cast((current_timestamp-start_time)seconds as decimal(18,0))) as "Bytes received/second" from sessions |
This command gives results like the following:
+--------------------------------------------------------------------------------+ | Session: 24 | | Client: ALBERT | | State: Run | | Elapsed Time: 0 01:14:05.000000 | | Bytes sent/second: 564321.9302768451 | | Bytes received/second: 0.0026748857944 | | | | Session: 26 | | Client: MILTON | | State: Run | | Elapsed Time: 0 00:06:13.000000 | | Bytes sent/second: 1638.5284210992221 | | Bytes received/second: 675821.6888561849 | | | +--------------------------------------------------------------------------------+
If a SELECT command will require a significant amount of resources, ADSM asks if you want to continue. You can cancel the command at that time. If a SELECT command runs longer than you expect or produces output that you do not need, you can cancel the command by canceling the administrative client session from the console session or another administrative client session.
ADSM provides commands to control the format of results of SELECT commands. You can control:
When you change these settings using these SET commands, the settings are in effect only for the administrative client session in which you issued the commands. You can query these settings by using the QUERY SQLSESSION command.
You can redirect the output of SELECT commands to a file in the same way as you would redirect the output of any command. However, when redirecting the output of a SELECT command for use in another program (for example, a spreadsheet or database program), it would be useful to write the output in a format easily processed by the program to be used.
Two standard formats for tabular data files are comma-separated values (CSV) and tab-separated values (TSV). Most modern applications that can import tabular data can read one or both of these formats.
Use the administrative client command line options -COMMADELIMITED or -TABDELIMITED to select one of these formats for tabular query output. All tabular output during the administrative session, regardless of what command generated it or where it is written, will be formatted into either comma-separated or tab-separated values. For details about using command line options, see the ADSM Administrator's Reference.
The use of command output redirection and one of the delimited output format options lets you create queries whose output can be further processed in other applications. For example, based on the output of a SELECT command, a spreadsheet program could produce graphs of average file sizes and file counts summarized by type of client platform.
For details about redirecting command output, see the ADSM Administrator's Reference.