Brad,
Again, thanks for the info!! Most of it is on using SQL select
statment, I would like to see some documentation with MS Access. Any
Ideas??
Rick Smith
Maritz, Inc.
Storage & Security Administration
smithrr AT maritz DOT com
(314) 827-1584
> ----------
> From: Brad Diefendorf[SMTP:bradd AT SELECT DOT COM]
> Sent: Tuesday, January 27, 1998 10:12 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: SQL & ODBC Readme
>
> <<File: vcard.vcf>>
> 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
> 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
> 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),
> 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
> 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
> 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
> 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
> 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
> 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 ]
>
>
>
|
