The data from Historical Server is in the same format as defined when the view was created on the server. To store the data, Historical Server creates two system tables:
Sessions table provides a record of every recording session that used the output database.
Views table lists the views that were used by each recording session.
In addition to one table for each view that is output to the database, there are two system tables created in the Historical Server output database. These tables are:
The sessions table provides a record of every recording session that has used the output database.
The views table lists the views that were used by each recording session.
The following example shows the possible contents of the sessions table table:
1> select * from sessions 2> go DbSessionId HsSessionId HsName HsAseName StartDate EndDate ----------------------------------------------------------------------------- 0 0 ajax_hs ajax Jul 22 2005 11:26AM Jul 22 2005 11:45AM 1 14 ajax_hs ajax Jul 23 2005 12:25PM Jul 23 2005 6:10AM 2 15 ajax_hs ajax Jul 24 2005 11:41AM Jul 24 2005 11:59AM 3 16 ajax_hs ajax Jul 25 2005 11:56AM Jul 25 2005 12:09PM 4 19 ajax_hs ajax Jul 26 2005 10:59AM Jul 27 2005 11:25AM 5 23 ajax_hs ajax Jul 27 2005 11:26AM Jul 27 2005 11:36AM
The following example shows the possible contents of the views table:
1> select * from views 2> go DbSessionId ViewName ----------- ------------------------------ 1 stored_procs 1 connections 2 stored_procs_shutdown_test 2 connections 3 stored_procs_shutdown_test 3 connections 4 connections 5 stored_procedure_activity 5 connections 5 process_activity
The structures of the view data output tables are similar to the structures of the output data files, where:
First column is the monitoring session ID.
Second column is the monitored server name.
Third column is a date/timestamp.
Subsequent columns per data item specified in the view definition.
This structure, for the date and timestamp, and the data items, is identical to the structure defined in the DDL scripts Historical Server provides when a bulk copy is executed on Historical Server data files into another Adaptive Server.
viewname is the name for the view,
truncated if necessary to conform to the file-naming conventions
of the current platform. On Windows platforms, the file name is
limited to thirty characters.
In the following example, the query reports the sessions that were active on July 27, 2005 and the views that were used by those sessions:
1> select HsAseName, HsName, StartDate, EndDate, ViewName 2> from sessions, views 3> where sessions.DbSessionId = views.DbSessionId 4> and StartDate >= 'July 27, 2005' 5> and EndDate < 'July 28, 2005 6> go HsAseName HsName StartDate EndDate ViewName --------------------------------------------------------------------------- tribble tribble_hs Jul 27 2005 10:59AM Jul 27 2005 11:00AM connections tribble tribble_hs Jul 27 2005 11:00AM Jul 27 2005 11:05AM sproc_activity tribble tribble_hs Jul 27 2005 11:00AM Jul 27 2005 11:05AM connections tribble tribble_hs Jul 27 2005 11:17AM Jul 27 2005 11:24AM sproc_activity tribble tribble_hs Jul 27 2005 11:17AM Jul 27 2005 11:24AM connections tribble tribble_hs Jul 27 2005 11:25AM Jul 27 2005 11:25AM sproc_activity tribble tribble_hs Jul 27 2005 11:25AM Jul 27 2005 11:25AM connections tribble tribble_hs Jul 27 2005 11:26AM Jul 27 2005 11:36AM sproc_activity tribble tribble_hs Jul 27 2005 11:26AM Jul 27 2005 11:36AM connections tribble tribble_hs Jul 27 2005 11:26AM Jul 27 2005 11:36AM process_activity
The following is an example of a Historical Server view:
hs_create_view process_activity, "Login Name", "Value for Sample", "Process ID", "Value for Sample", "Kernel Process ID", "Value for Sample", "Connect Time", "Value for Session", "Page I/O", "Value for Session", "CPU Time", "Value for Session", "Current Process State", "Value for Sample"
The query below shows the contents of the database table generated by the process_activity view illustrated above.
1> select * from process_activity 3> go DbId CPUTime ProcessID KernelProcessID ConnectTime PageIO ProcessState LogName -----------------------------------------------------------------------------5 0.128447 17 3211313 61 28531 6 jsmith 5 0.128447 17 3211313 121 28531 6 jsmith 5 0.032169 19 3276850 60 32072 6 byoung 5 0.128447 17 3211313 182 28531 6 jsmith 5 0.099188 18 3342387 61 28759 6 dcharles 5 0.032169 19 3276850 121 33283 6 byoung 5 0.128447 17 3211313 242 28531 6 jsmith 5 0.105076 18 3342387 121 24373 6 dcharles 5 0.032169 19 3276850 181 33283 6 byoung 5 0.128447 17 3211313 303 28531 6 jsmith
