Easysoft ODBC-ODBC Bridge User's Guide - Reporting and Statistics

The Easysoft ODBC-ODBC Bridge provides a suite of performance management reports and statistics that allow you to monitor and manage your system.

Chapter Guide

Introduction

One of the most frequent requests that Easysoft receive from enterprise customers is for greater reporting and statistics, so that they can see exactly what the OOB Server is doing at any time.

The Web Administrator Statistics screen displays a number of statistics detailing server up time, total connections, total successful connections, active threads/processes, peak concurrent threads/processes and the time of the last connect or disconnect:

Figure 67: General data on the Web Administrator Statistics screen

The Statistics Screen

The Web Administrator Statistics screen provides the following information:

Changing the refresh frequency

The Web Administrator uses a set of template files into which the dynamic data is inserted before sending it back to your browser.

The template file for the Statistics screen is index.html, which is located in the templates directory wherever you installed the OOB Server.

Edit the index.html file and near the top you will see:

meta http-equiv="refresh" content="60"; URL=/index.html

Change the 60 (the refresh time in seconds) to your preferred setting.


NB

Note that setting the refresh time to a very low value will increase the workload on the OOB Server process which handles HTTP requests. As this may reduce the response time to the Easysoft ODBC-ODBC Bridge ODBC server thread, times much less than 60 seconds are not recommended.


DSN statistics

The Easysoft ODBC-ODBC Bridge records each DSN to which it is connected and displays a table of DSN statistics:

Figure 68: DSN data on the Web Administrator Statistics screen

The information shown in the table per DSN is:

Host Statistics

From the Web Administrator Statistics screen there is a link called Number of different client hosts which takes you to a table of statistics per connecting client:

Figure 69: The Web Administrator Client Hosts screen

Information is per client and shows:

Termination statistics

The contents of the Web Administrator Statistics screen are written on exit to a file called esoob_stats_<dd>_<mm>_<yyyy>_<hhmmss>.stats where <dd> is the day number, <mm> is the month number, <yyyy> is the year number and <hhmmss> is the time in hours, minutes and seconds.

This file will be written into the directory defined by the server configurable parameter LogDir:

Server up time 0 days, 18 hours, 5 minutes and 23 seconds

Server CPU time(s): u=92.66 k=242.72

Total Connections: 58966

Total Threads/Processes: 58966

Active Threads/Processes: 4

Peak concurrent Threads/Processes: 74

Connections/minute: 54

Last Connection time: Wed Jul 04 08:55:13 2001

Last Disconnect time: Wed Jul 04 08:55:12 2001

DSN Connections Total Connections Average

DSN Time(s) per minute Time per

DSN connection(s)

dsn1 3217 4153 2 1.29

dsn2 55566 212747 51 3.83

dsn3 42 8060 0 191.90

dsn4 111 101 0 0.91

Number of different client hosts: 3

192.168.0.1 (test1.easysoft.com)

192.168.0.2 (test2.easysoft.com)

192.168.0.3 (test3.easysoft.com)

Log of Failing SQL

The OOB Server can keep a log of any failed SQLPrepare, SQLExecute or SQLExecDirect functions.

To enable this feature check the LogFailingSQL parameter on the Web Administrator Configuration screen (see LogFailingSQL), which will create a file called oob_sql.log when the first failing SQL occurs.

This file is located in the directory defined by the LogDir parameter on the Web Administrator Configuration screen (see LogDir).

Any future SQL commands that fail are appended to the file once it has been created and you can rename or delete this file at any time.

The failing SQL log file has two types of entry:

[a] an entry showing a piece of SQL which failed:

date time "DSN" SQL text

e.g.

12-04-2002 10:32:55 "test" insert into mytable values (1,2)

---------- -------- ------ --------------------------------

date time DSN name the SQL text which failed

An error may be for a number of reasons, such as the SQL failing to parse correctly, or the database engine being unable to comply (a duplicate key error, for example):

31-07-2001 11:24:24 "example" update Title set Count=Count+1 where id = 1

01-08-2001 09:08:08 "web" select columnnoexist from stores

01-08-2001 09:05:05 "test" update sales set stor_id = 8042 where stor_id = "fred"

01-08-2001 09:06:06 "test" select * from tablenoexist

The DSN name field is extremely useful when identifying problems.

In this example the "web" and "test" DSNs both point at the same database, but "web" is the live version of the application and "test" is used by software developers.

As the client applications are on different machines, the TargetDSN used by the OOB Client is set to "web" on one client and to "test" on the other.

Failing SQL in the log to "test" would not be uncommon as this is under development, but failures for "web" indicate a problem a real user might have experienced running live software.

[b] an entry logging the first database error retrieved AFTER a piece of SQL failed:

date time SQLState="xxxxx", NativeError=nnnn, Msg="xxxxxxxx"

e.g.

12-04-2002 10:32:55 SQLState="23000", NativeError=2627 Msg="[Microsoft][ODBC SQL Server Driver][SQL Server] Violation of PRIMARY KEY constraint"

The failing SQL file is written to as follows:

1.  each piece of SQL executed with either SQLPrepare, SQLExecute or SQLExecDirect is documented where those APIs fail.

2.  an entry is written in the format in [a] if an SQLPrepare, SQLExecute or SQLExecDirect function fails.

3.  if [2] occurs then an entry is written in the format in [b] if the application calls SQLError or SQLGetDiagRec.

4.  once the application is finished with the SQL in [1] (or executes more SQL) then the previous SQL is no longer available to log.

This means that failing SQL is written to one line of the failing SQL log, followed by the error text returned from the database engine on the next line.

However, this is NOT always the case, as the failing SQL statements and the error text can get mixed up if the server is very busy (for example, if multiple concurrent connections all fail at the same time).

Auditing

The OOB Server records all activity to an audit trail file if the AuditODBCAccess parameter is checked (see AuditODBCAccess or Changing Server Configurable Parameters under Unix) on Unix).

The Audit File link on the Web Administrator Statistics screen displays a page showing:

Audit File Description

The current Audit File is called esoob_access.log and will be placed in the directory specified by the server configurable parameter LogDir.

Here is an example of a few lines from the audit file:

Mon, 02 Jul 2001 15:01:07 GMT CONNECT 192.168.0.1(test1.easysoft.com)

Mon, 02 Jul 2001 15:01:12 GMT DISCONNECT 192.168.0.1(test1.easysoft.com)

Fri, 06 Jul 2001 13:10:46 GMT AUTH_DENIED 192.168.0.1(test1.easysoft.com)

Fri, 06 Jul 2001 14:34:36 GMT REFUSED_BY_DSN_RULE test(Martin Evans)

Fri, 06 Jul 2001 15:18:34 GMT REFUSED_MAXCLIENTCONNECT 192.168.0.3(unknown)

Fri, 06 Jul 2001 15:19:33 GMT REFUSED_MAXCONCURRENT 192.168.0.2(test2.easysoft.com)

Mon, 09 Jul 2001 13:04:02 GMT REFUSED_BY_RULE 192.168.0.1(test1.easysoft.com)

The fields in each line of the audit file are:

The events and their descriptions are:

Daily Renaming

The audit files are renamed once a day at midnight. At midnight the current audit trail file (esoob_access.log) is renamed to esoob_access_<dd>_<mm>_<yyy>.log where <dd> is the day number, <mm> is the month number and <yyyy> is the year number.

The audit file for each day should be visible in the Web Administrator.

Graph Generation

The Web Administrator is capable of analysing a particular audit file and producing graphs of connections per minute and connections per hour for any selected day.

These graphs are accessed from the Audit File link on the Web Administrator Statistics screen.

Connections are displayed as blue bars and warning events are displayed as coloured warning bars on top of the relevant blue bar:

failed connects (Purple bar):

attempts denied access due to access control lists of server limits (Red bar):

authentication failures (Orange bar):

Figure 70: The Web Administrator Connections per Minute graph

Figure 71: The Web Administrator Connections per Hour graph

Event Logging On Windows

The Windows OOB Server uses the application event log to record status, diagnostic, and security information. Events logged by the OOB Server include the following:

Much of the information written to the application event log is also written to the OOB Server log files (by default, these are stored in the c:\temp directory). Refer to the application event log if:

To view the application event log

1.  Do one of the following:

2.  In the left hand pane of the Event Viewer window, click Application.

Browsing System Data Sources in the Web Administrator

The Web Administrator Data Sourcesscreen displays the ODBC system data sources found by the server and their configured drivers. Note that if the AllowDSNBrowse parameter is disabled, no data source or driver information will display in this page.

Figure 72: The Data Sources screen of the Web Administrator

Viewing data sources, tables, columns and data

The Web Administrator facility lets you browse data sources for lists of tables, table composition and rows of data in the tables.

In order to browse system data sources on the machine where the OOB Server is running you must enable the AllowDBBrowse configurable parameter on the Web Administrator Configuration screen.

This converts the list of data source names on the Data Sources page into links to further pages, allowing you to browse down through the data source and its tables, columns and data.

You may be prompted for a database user name and password when first clicking on a DSN.

The OOB Server attempts an initial connection without the ODBC UID/PWD attributes, but if that fails with an authentication error (28000) an authentication challenge will be issued, where the realm is the name of the DSN.


NB

DSNs cannot be browsed using trusted connections.


Figure 73: The DSN Realm Enter Network Password dialog box

Having gained access, DSN details can then be displayed, as in the following example:

The initial screen displays the tables and views in the DSN test (i.e. it is the result set generated by calling SQLTables when connected to a DSN called test):

Figure 74: The Web Administrator Data Sources screen DSNs

Click on BENCH to display a list of the columns of data in that table (generated by calling SQLColumns on table BENCH in DSN test):

Figure 75: The Web Administrator Data Sources screen DSN data

Click on View rows from this table to display the first ten rows in that table (generated by a "select * from BENCH" statement):

Figure 76: The Web Administrator Data Sources screen row data

Click Next or Prevto move forwards or backwards in the result set by ten rows at a time.

Easysoft ODBC-ODBC Bridge Resources

The Web Administrator Informationscreen displays a list of links to Easysoft support resources:

Figure 77: The Information screen of the Web Administrator

Viewable Licenses

To view all your installed Easysoft licenses, click Information on the main web page followed by the Installed Licenses link:

Figure 78: The Web Administrator Licenses screen

The table shows four columns:

Exporting Licenses

On Windows the Information page also allows you to export the registry entries in RegEdit 4 format to a file called LogDir\easysoft_licenses.reg where LogDir is an OOB Server configurable parameter defined on the Configuration screen.

Click the Export button to export your licenses. If your registry becomes damaged you can restore the license entries by double clicking on the easysoft_licenses.reg file.


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.