Richard:
Here is the info you requested on the ODBC piece for NT.
This section covers the new ADSM server SQL interface introduced with
Version 3. The following topics are covered:
Admin select command
A new administrative command, SELECT, has been introduced. This
command can be used to query the ADSM server database using SQL select
statements.
ODBC driver
An Open Database Connectivity (ODBC) driver is also provided with
Version 3. This driver allows desktop database products such as Lotus
Approach
or Microsoft Access to be connected to the ADSM server database.
This enables administrators to use these tools to perform ADSM server
queries in
an easy to use, graphical manner.
Admin Select Command
The ADSM database stores information about storage management policy,
client data objects (directories and files) and storage hierarchy. With
Version 2 this
information was only available through a set of ADSM query commands.
These commands produced formatted output with little opportunity to
produce
customized outputs. Version 3 addresses this requirement by providing an
SQL interface.
SELECT Command
The SQL interface consists of an SQL SELECT command for the ADSM server.
The select interface is consistent with relational database products and
presents server information in the form of relational tables containing
rows and columns. Select statements can be issued from:
ADSM admin command line interface
ADSM Web admin interface
The interface is read only and allows queries on the ADSM database. The
contents or structure of ADSM database cannot be changed. Because of
this, stored
procedures cannot be held in the ADSM database (a stored procedure is a
predefined SQL statement similar to an ADSM macro).
System Catalog Tables
Three system catalog tables are implemented to assist the administrator
in determining the information available.
SYSCAT.TABLES
contains information about all tables that are available for
querying with the select statement
SYSCAT.COLUMNS
describes the columns that reside in each of the tables
SYSCAT.ENUM
for columns that have an enumerated data type this table defines
the legal values for each enumerated data type and the ordering of the
different values for
the type; an enumerated data type is a value that is assigned a
numerical number rather than text
The following select statements give an example for querying information
from the system catalog tables:
+--------------------------------------------------------------------------------+
?adsm> select tabschema, tabname, unique_index from
syscat.tables ?
?
?
?TABSCHEMA TABNAME
UNIQUE_INDEX ?
?--------- ------------------
------------ ?
?ADSM ACTLOG
?ADSM ACTLOG
FALSE ?
?ADSM ADMINS
TRUE ?
?ADSM ADMIN_SCHEDULES
TRUE ?
?ADSM ARCHIVES
FALSE ?
?
+--------------------------------------------------------------------------------+
?adsm> select tabname, colname, typename from
syscat.columns ?
?
?
?TABNAME COLNAME
TYPENAME ?
?------------- ------------------
------------------------- ?
?ACTLOG DATE_TIME
?ACTLOG DATE_TIME
TIMESTAMP ?
?ACTLOG MSGNO
INTEGER ?
?ACTLOG SEVERITY
ENUMERATED(SEVERITY_TYPE) ?
?ACTLOG MESSAGE
VARCHAR ?
?ACTLOG ORIGINATOR
VARCHAR ?
?
+--------------------------------------------------------------------------------+
?adsm> select typename, values from
syscat.enumtypes ?
?
?
?TYPENAME
VALUES ?
?--------------
-------------------------------------------------- ?
?OBJECT_TYPE DIR(0), FILE(1),
?OBJECT_TYPE DIR(0), FILE(1),
UNKNOWN(2) ?
?BACKUPSTATE ACTIVE_VERSION(0), INACTIVE_VERSION(1),
UNKNOWN(2) ?
?YESNO_TYPE NO(0),
YES(1) ?
?COMPRESSTYPE NO(0), YES(1),
CLIENT(2) ?
?OPENCLOSED CLOSED(0),
OPEN(1) ?
?LOGGINGMODE NORMAL(0),
ROLLFORWARD(1) ?
?SEVERITY_TYPE I(0), W(1), E(2), S(3),
D(4) ?
+--------------------------------------------------------------------------------+
Confirmation Message
For SQL queries that operate on large tables or require significant time
and resources from the ADSM server, a confirmation message is displayed
indicating that
the query is resource intensive and may take a lot of time to generate
results. This gives the opportunity to abort the query before resources
are consumed or to
run it anyway. Since queries may tie up sessions for some time, they
cannot be executed from the server console. This keeps the console
session available for
managing other administrative functions.
Select Statement Syntax
To use the SQL interface a basic understanding of the SQL select
statement is required. The syntax of the ADSM select statement is
compliant to ANSI SQL
syntax but limited from the full specification provided by the SQL92/93
standard. These limitations reduce query complexity and the
corresponding load on the
ADSM server database.
A SQL select statement can be divided into a number of clauses.
SELECT and FROM
The basic clause is SELECT, used to select columns FROM tables. The
columns selected can optionally be aggregated and displayed with a
different output
column name:
column_name
column name of the table or tables specified in the FROM clause
aggregate_function
functions such as SUM, COUNT, MAX, or AVG that extract a single
value from a group of columns
output_column_name
column title displayed by the query output for the specified column
name (default is to display the original column name)
table_name
table name from which query rows and columns are extracted
The simplest form of a select statement is to SELECT all columns FROM a
table; the following example selects all rows from the SESSIONS table:
+--------------------------------------------------------------------------------+
?adsm> select * from
sessions ?
?
?
? SESSION_ID:
1126 ?
? START_TIME: 1997-08-19
10:48:36.000000 ?
? COMMMETHOD:
Tcp/Ip ?
? STATE:
Run ?
? WAIT_SECONDS:
0 ?
? BYTES_SENT:
2301 ?
? BYTES_RECEIVED:
175 ?
? SESSION_TYPE:
Admin ?
?CLIENT_PLATFORM:
WinNT ?
? CLIENT_NAME:
TIM ?
?
OWNER_NAME:
?
?
MEDIA_STATE:
?
?
?
+--------------------------------------------------------------------------------+
Note
The SESSIONS table is a dynamic table created and updated as sessions
start and stop.
WHERE
As part of a SELECT statement the WHERE clause can be used to predicate,
or limit, the rows returned from the query:
predicate
expression to limit rows returned from the query:
node_name='POLONIUM'
logical_mb>=1000
platform_name IS NULL
bytes_sent BETWEEN 100 AND 500
nodes.node_name=occupancy.node_name (join criteria for
selecting one value from two tables)
The following statement is an example for using a simple where clause.
It selects all client schedules that are run on a Sunday:
+--------------------------------------------------------------------------------+
?adsm> select schedule_name, action, starttime from
client_schedules ?
? where dayofweek=
'SUNDAY' ?
?
?
?SCHEDULE_NAME ACTION
STARTTIME ?
?------------------ ------------------
--------- ?
?WEEKLY SELECTIVE
?WEEKLY SELECTIVE
01:00:00 ?
?
?
+--------------------------------------------------------------------------------+
The following statement gives an example for using join criteria. The
join criteria is used to avoid redundant output lines if more than one
table is queried in a
select statement. The statement provides information of client schedules
and the associated client nodes:
+--------------------------------------------------------------------------------+
?adsm> select node_name, client_schedules.schedule_name,
dayofweek, ?
? starttime from associations, client_schedules
where ?
? associations.schedule_name =
client_schedules.schedule_name ?
?
?
?NODE_NAME SCHEDULE_NAME DAYOFWEEK
STARTTIME ?
?--------------- ------------------ --------------
--------- ?
?DANUBE DAILY_INC ANY
?DANUBE DAILY_INC ANY
19:00:00 ?
?SEVERN DAILY_INC ANY
19:00:00 ?
?NOBELIUM DAILY_INC ANY
19:00:00 ?
?DANUBE WEEKLY SUNDAY
01:00:00 ?
?SEVERN WEEKLY SUNDAY
01:00:00 ?
?NOBELIUM WEEKLY SUNDAY
01:00:00 ?
?
?
+--------------------------------------------------------------------------------+
GROUP BY
The GROUP BY clause is used to summarize the output from a query by
column name:
column_name
rows to be grouped for resolving aggregate functions
This clause is generally used in conjunction with an aggregate function.
The following example shows the sum of files and server storage used for
each node:
+--------------------------------------------------------------------------------+
?adsm> select node_name, sum(num_files) as NUM_FILES, sum(logical_mb)
as ?
? STORAGE_MB from occupancy group by
node_name ?
?
?
?NODE_NAME NUM_FILES
STORAGE_MB ?
?------------------ -----------
-------------------------------- ?
?DANUBE 20
?DANUBE 20
0.70 ?
?NOBELIUM 16
14.44 ?
?SEVERN 25
2.30 ?
?
?
+--------------------------------------------------------------------------------+
HAVING
The HAVING clause is used to apply a condition to an aggregated column
to limit the returned output:
predicate
expression to filter output of aggregated values before displaying
The having clause is used to include a condition for an aggregate
function. The following example builds on the previous by only showing
those nodes that are
using greater than one MB of server storage:
+--------------------------------------------------------------------------------+
?adsm> select node_name, sum(num_files) as NUM_FILES,
sum(logical_mb) ?
? as STORAGE_MB from occupancy group by node_name
having ?
?
sum(logical_mb)>1
?
?
?
?NODE_NAME NUM_FILES
STORAGE_MB ?
?------------------ -----------
-------------------------------- ?
?NOBELIUM 16
?NOBELIUM 16
14.44 ?
?SEVERN 25
2.30 ?
?
?
+--------------------------------------------------------------------------------+
ORDER BY
The ORDER BY clause is used to format the output returned from a query
based on a sorted column:
output_column_name
specification of output sorting, either in ascending (ASC) or
descending (DESC) order; if more than one column_name is specified,
first column has
primary sort order, then second column, etc.
"SQL Select Statement Example" illustrates an example of using ORDER BY
in conjunction with these other clauses.
SQL Select Statement Example
The example above shows how a select statement can be used powerfully to
manipulate the output report as desired.
An administrator may be interested in the amount of data that is used by
each node. Information of this kind is stored in the occupancy table.
Each row in the
occupancy table holds information for one filespace of one node and of
one type (e.g. filespace is C drive, node is polonium, type is backup).
Instead of listing information for each file space (this is done using
the QUERY OCCUPANCY command), the select statement groups and calculates
information for each node. The aggregation function SUM is used to add
up the number of files and the amount of data for each node. An
aggregation function
requires a group by clause that defines on which group of objects it has
to operate. In the example, this is the node_name. The having clause
eliminates output
lines of nodes that have not backed up at least one file. The output
order is controlled by the order by clause. In this example it is
ordered by the amount of data
per node in descending order.
Server SQL Processing
SQL statements request output reports. To gather and format the
requested information the ADSM server uses virtual and temporary tables.
Virtual SQL Tables
ADSM server information is mapped into virtual tables to make it
available for SQL select statements. This is also true for dynamic
information. For example the
SESSIONS table can be queried with a select statement to display client
sessions that are currently logged on to the server.
Temporary Tables
Many forms of SQL select statements generate intermediate data before
the final select output report is displayed. Functions causing this are,
for example nested
select statements, order conditions, or matching criteria. This
intermediate data is stored in temporary tables. Temporary tables reside
in the ADSM database in
order to save memory resources. They are deleted upon completion of the
SELECT statement.
Impact on ADSM Server
Due to generating temporary tables SQL activity requires a minimum of a
4 MB partition in the ADSM database. For larger queries substantially
more free
space is needed.
Since the SQL processing uses the database bufferpool and I/O resources,
SQL select statements affect ADSM server performance. Very complicated
or
long-running select statements can slow down server performance
significantly during processing.
SQL Parameters and Commands
With the SQL interface new parameters and commands are available on the
ADSM server. The main command is the SELECT command which has been
covered in the previous chapters. SQL parameters can be used to control
the output reports generated by the SELECT command.
SQLDISPLAYMODE
The SQLDISPLAYMODE parameter controls the manner in which SQL data types
are displayed. It can be set to one of the following values:
Narrow
Column display width is 18; any wider string is displayed on
multiple lines
Wide
Column display width is 250
SQLDATETIMEFORMAT
This parameter controls the format in which SQL date and time data are
displayed. One of the following formats can be chosen:
ISO
International Standard Organization
USA
IBM USA standard
EUR
IBM European standard
JIS
Japanese Industrial standard
Local
Site-defined
Currently JIS and Local are the same as ISO.
SQLMATHMODE
The SQLMATHMODE parameter defines the mode in which SQL arithmetic is
carried out. There are two options:
Truncate
Decimal numbers are truncated
Round
Decimal numbers are rounded
QUERY SQLSESSION
The command QUERY SQLSESSION displays the current SQL settings:
+--------------------------------------------------------------------------------+
?adsm> query
sqlsession ?
?
?
?Column Date-Time Arithmetic
Cursors ?
?Display Format Mode
Allowed? ?
?Format
?
?-------- --------- ----------
-------- ?
?Narrow ISO Truncate
?Narrow ISO Truncate
Yes ?
+--------------------------------------------------------------------------------+
The "Cursors Allowed?" stanza relates to the SQL cursor support which is
used to support Open Database Connections (ODBC).
ODBC Interface
In ADSM Version 3, to compliment the ability to use SQL to query the
contents of the ADSM database, an Open Database Connectivity (ODBC)
interface has
also been implemented. This allows the output from the ADSM server
database to be tailored for presentation to meet particular business
requirements.
Graphical SQL Interface
ODBC is a standard interface between database engines and front-ends. It
allows products such as Lotus Approach or Microsoft Access to be used to
graphically construct SQL select statements, which are then dispatched
to the database. The information produced by these select statements is
then returned in
an tabular form, enabling further processing to be undertaken. This
further processing is normally in the form of producing graphs or
tables.
ODBC Driver
The ODBC driver supplied with Version 3 is part of the client package.
The ODBC client application, Lotus Approach for example, interfaces to
the ADSM
client which in turn interacts with the ADSM server. This functionality
is only available for the following Win32 clients: Windows 95, Windows
NT 3.51, and
Windows 4.0.
ODBC Version 2.5
The ODBC driver in Version 3 supports the ODBC 2.5 application program
interface (API). The ADSM SQL interface supports read-only SQL SELECT
statements and does not allow any alteration of the information in the
database. This means that the interface does not completely conform to
any of ODBC API
or SQL grammar conformance levels. Applications that require particular
conformance levels may have problems connecting to ADSM.
ODBC Implementation
The chart above illustrates the flow of information, both to and from
the server, during the processing of an SQL SELECT operation from an
ODBC application.
Client
The ODBC interface through which the client application accesses the
server is built into the ADSM admin client. Using the ODBC API which
interfaces into the
ADSM ODBC driver, the client application submits requests to the common
code of the ADSM client. At this point a login prompt is displayed
within the client
application and a valid admin id and password must be provided. An admin
session is then started with the ADSM server using normal client
communications.
Server
The server, on receiving the ODBC request, starts an admin session which
may be tracked with the QUERY SESSION command. The admin session then
submits the SQL SELECT statement to the server which in turn queries the
database for the information. Once the results of the query have been
generated, they
are passed back through the common client code to the ODBC API in the
application and the session is closed.
ODBC Configuration
To ensure that the ODBC implementation with ADSM is as flexible as
possible, the ODBC connectivity is implemented as an ODBC driver within
the operating
system. This enables the operating system to offer ODBC connectivity in
a standardized way to all ODBC-compliant applications.
Setup Utility
The Version 3 client package includes the ODBC driver and a copy of the
ODBC Database Source Administrator. At client installation the ODBC
driver is an
optional component. If selected this creates an icon in the control
panel named ODBC. This icon starts the ODBC Data Source Administrator
which can be
used to configure the ADSM driver as a User, System or File data source.
User data sources are only available to the defining user. System data
sources may be
accessed by any user of the system. To configure the ADSM ODBC driver as
a data source the server name, TCP/IP address, and TCP port number must
be
provided.
ODBC Applications
An application running in an environment with the ADSM ODBC driver
installed and configured as a data source, can import or link to ADSM
database tables.
This is normally done through an "Open ODBC Data Source" or "Get
External Data" menu item. It is important to note that an import will
import all the data
associated with the tables as well as the tables themselves. Standard
ODBC supports database modification as well as query. However, the ADSM
database is
only available as read only and an ODBC application cannot update it.
Having linked local application tables to their equivalent server tables
SQL queries may then be constructed by the application. The results can
either be
manipulated in the application or saved and used with a third
application such as a graphing package or report generator.
Server authentication for ODBC applications is performed in the same way
as for other administrative interfaces. When the application accesses
the server a
prompt will be issued for an administrator user ID and password. These
must be entered before the application can access the server.
[ Top of Page : Previous Page : Next Page : Table of Contents ]
|
