Monitor Historical Server
-------------------------

The accompanying file, "views", contains a number of sample 
monitoring views.  Each view is built from a collection of 
related data items, and is meant to focus on a particular area 
of Adaptive Server Enterprise performance.

The "views" file should not be used directly as input
to the isql program.  For one thing, it is unlikely that
you will want to define all of the views contained in this
file for a single recording session.  Also, comment lines that
describe the views are intermingled with lines that
contain the actual text of the view definitions.
The Historical Server will not ignore the comment lines,
so as you cut and paste the text of views you want to define,
you must discard the comment lines.

In many cases, you may find that the sample views contain
exactly the information that is of interest to you.  If you
find that none of the samples is an exact match for your
requirements, you may still find the sample views to be useful
as templates for creating views that collect the monitoring
data that you need.

Some of the sample views differ from one another only in the
time interval over which the data is accumulated (either time
since the start of the recording session, or just the most recent
sample interval).  Other views may contain similar data items,
but in different orders.  The order in which data items appear
in a view is significant, since the data is sorted according to
the values of the key fields.  The first key field appearing
in a view definition acts as the primary sort key, the
second key field is the secondary sort key, and so on.


DIRECT LOAD feature, quick starting guide
-----------------------------------------
This version of Historical Server contains the new direct database load feature.

Introduction:

The Historical Server records monitoring data to output files, often these
file are then imported to an ASE database using bcp. The direct database
load feature enables the Historical Server to output the monitoring data 
to a database and therefore simplifies this process.

The steps involved in using the direct database load feature are as
follows:-

1- Preparing an ASE for recieving the Historical Server output
a) Elect an ASE to contain the database in which your output tables will
   be stored. 
b) Create a database (default name is "hs_monitoring") on that ASE.
c) Run the install script hs_directload.sql on that database to create two
   catalog tables and a stored procedure.

2- Starting Up Historical Server:
The user can specify on the command line that they want theHistorical Server to
output to a database rather than flat files by providing the name of the ASE
and the name of the database in which they want the monitoring output to stored.
Command line options are provided for specifing a username and password for the
output ASE (if they are different than the username and password used on the
monitored ASE). If these are not specified, Historical Server by default
will try to use the username and password it gets from the -U and -P options.

The modified command line syntax is: 
histserver -U<user name> - P<password> -D<output dir> -l<log file>
  -I<interfaces file> [-d<delimiter>] [-O<ASE name>] [-o<DATABASE name>]
  [-f ] -u<outputASE user name> - p<outputASEpassword>
-O is used to specify the name in the interfaces file of the ASE that will be
   used to output the Historical Server monitoring data 
-o is used to specify the database name in which to store the monitoring data 
-u is used to enter the login name for connecting to the output ASE
-p is used to enter the corresponding password.
-f allows the user to specify that he still wants the output files in addition
   to output to an ASE database.  This is only used in combination with the
   -O and -o parameters.

Note: The target server must be available at the time the Historical Server is
started, and the target database must already exist on that server.  

Note: The login provided to the Historical Server must have access to this
server and update permission in the target database (use the -u and -p
parameters if the login to the output server is different from that on the
monitored server).

3- The View Monitoring Output
3.1- Data Storage
To store the data, Historical Server creates one table per view.  The
structure of these tables is very close to the structure of the output data
files: 
	- The first column is the monitoring session ID
	- The second column is a date/time stamp. 
	- Then there is one column per Data Item specified in the view
	  definition.
In fact that structure, as far as the date/time stamp and the data items are
concerned, is identical to the structure already defined in the DDL scripts
Historical Server provides to bcp the Historical Server data files into an ASE. 

Example:

Recording Session View
----------------------
		hs_create_view procedure_cache_io,

		"Procedure Database ID", "Value for Sample",
		"Procedure Database Name", "Value for Sample",
		"Procedure ID", "Value for Sample",
		"Procedure Name", "Value for Sample",
		"Page I/O", "Value for Sample",
		"Page Hit Percent", "Value for Sample",
		"Logical Page Reads", "Value for Sample",
		"Physical Page Reads", "Value for Sample",
		"Page Writes", "Value for Sample",
		"Procedure Execution Count", "Value for Sample",
		"Procedure Execution Count", "Value for Session"
		go
		hs_initiate_recording
		go

Output Table
------------
		create table procedure_cache_io (
			DbSessionId                     int                              not null  ,
			Timestamp                       datetime                             null  ,
			ProcedureDatabaseID_ValSmp      int                                  null  ,
			ProcedureDatabaseName_ValSmp    varchar(30)                          null  ,
			ProcedureID_ValSmp              int                                  null  ,
			ProcedureName_ValSmp            varchar(30)                          null  ,
			PageIO_ValSmp                   int                                  null  ,
			PageHitPercent_ValSmp           float(16)                            null  ,
			LogicalPageReads_ValSmp         int                                  null  ,
			PhysicalPageReads_ValSmp        int                                  null  ,
			PageWrites_ValSmp               int                                  null  ,
			ProcedureExecutionCount_ValSmp  int                                  null  ,
			ProcedureExecutionCount_ValSes  int                                  null   
		)


3.2- View Table Naming Conventions - View Table Reuse
The name of the tables is the exact name of the view as defined in the view
creation. Historical Server won't prepend the session id nor the view id to
the name. The output results of a view will always be stored in the same
output table.  So, a view table is created by Historical Server during the
creation of the first session that needs the view. 

Later sessions that use a view with the same name and the same items will
reuse that table. Later sessions using views with the same name but a subset
of the columns will use the same table. Columns no longer defined in the view
will receive NULL values. 

Later sessions using the same view but with extra items added will cause
Historical Server to add the corresponding extra columns to the view table.
Historical Server will write a warning message to the log file whenever it
had to add columns to an existing output table.

3.3- Table Update Frequency
The data is updated in the monitoring tables refresh after refresh like it is
in the output files. Session after session, the same tables are updated with
new results. The user can then look at the whole data, or filter on session
ID to just select the results of a specific session. 

3.4- Special Repository Database Tables
Two repository (the catalog) tables are used by Historical Server:

- A Sessions table which is updated with the Ids startTime and endTime of the
  sessions
- A Views table in which we store the names of the view tables used for each
  session.

