Richard,
If you have access installed, you probably have several help files on
your hard drive for access & sql. Try searching for .HLP files and look at
the most likely.
Dan T.
----------
> From: Smith, Richard <smithrr AT MARITZ DOT COM>
> From: Smith, Richard <smithrr AT MARITZ DOT COM>
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: SQL & ODBC Readme
> Date: Tuesday, January 27, 1998 11:19 AM
>
> 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 ]
> >
> >
> >
|
