Database Monitor

Databases are a critical component of the information technology infrastructure of business houses. The performance of databases defines the success of organizations in providing the 24x7 level of customer satisfaction and smooth operations of day to day business activities. The AppPerfect Database Monitor, monitors the performance of your databases and provides extensive information so that necessary steps are taken before a break down of vital business processes occurs.

Managing Database monitors

To view the list of all available monitors defined with the AppPerfect Monitor, log in as administrator and click the "Monitors" tab. A list of all defined monitors is displayed. You can edit the properties of a monitor by clicking it and updating the properties page for that monitor.

To delete a monitor, select the checkbox to the left of that monitor and click "Delete". When you delete a monitor, you may choose to preserve the data already recorded for that monitor or you may delete the monitor along with all its historical data.

Defining a new monitor

Please refer to the chapter Managing Monitors for the common settings required.

1. Oracle v8 to v12
2. Microsoft SQL Server
3. MySQL Server
4. DB2 Server
5. Sybase Server

Oracle v8 to v12

Oracle makes the industry's leading databases. The high reliability, enhanced security features and lower transaction time of the Oracle databases make them the most sought after databases among business houses. To monitor an Oracle database, you will need the following additional details:

• JDBC Driver Jar: The jar file which contains the JDBC driver for your Oracle database. Default Oracle JDBC driver for JDK 1.4 is packaged in a jar file called ojdbc14.jar. For JDK 1.2/1.3, file is called classes12.zip. Provide the absolute path for these files while adding.
• JDBC Main class: The main class for your JDBC driver. If using default Oracle driver, it should be: oracle.jdbc.driver.OracleDriver. If you are using a third party driver, check documentation for that product.
• JDBC URL: Provide URL to connect to your server. A sample URL might look as follows: jdbc:oracle:thin:@<hostname>:<portnumber>:dbname. Check with your database administrator for URL to your database.
• Username: User name to log on to Oracle. The default user is 'system'.
• Password: Password for the user name above.

MS SQL Server

There is full support for Microsoft SQL Server and related components. To monitor an Microsoft SQL Server database, you will need the following additional details:

• JDBC Driver Jar: The jar file which contains the JDBC driver for your MS SQL Server database. The jar files for MS SQL Server that have to be provided are: <absolute_path>\msbase.jar;<absolute_path>\mssqlserver.jar;<absolute_path>\msutil.jar.
  Note: The above jar files should be semicolon separated with no space after the semicolon.
• JDBC Main class: The main class for your JDBC driver. If using MS SQL Server default driver, it should be: com.microsoft.jdbc.sqlserver.SQLServerDriver. If you are using a third party driver, check documentation for that product.
• JDBC URL: Provide URL to connect to your server. A sample URL might look as follows: jdbc:microsoft:sqlserver://<hostname>:<portnumber>. Check with your database administrator for URL to your database.
• Username: User name to log on to MS SQL server. The default user is 'sa'.
• Password: Password for the user name above.

MySQL Server

Provide the following setting to monitor the popular open source MySQL database:

• JDBC Driver Jar: The jar file which contains the JDBC driver for your MySQL Server database. The jar files for MS SQL Server that have to be provided are: <absolute_path>\mysql-connector-java-3.0.9-stable-bin.jar.
  
• JDBC Main class: The main class for your JDBC driver. If using default driver, it should be: com.mysql.jdbc.Driver. If you are using a third party driver, check documentation for that product.
• JDBC URL: Provide URL to connect to your server. A sample URL might look as follows: jdbc:mysql://<hostname>:<portnumber>/mysql. Check with your database administrator for URL to your database.
• Username: User name to log on to MySQL server. The default user is 'root'.
• Password: Password for the user name above.

DB2 Server

Provide the following setting to monitor the DB2 database:

• JDBC Driver Jar: The jar file which contains the JDBC driver for your DB2 database. The jar files to be provided for DB2 are: <absolute_path>\db2jcc.jar;<absolute_path>\db2java.zip
• JDBC Main class: The main class for your JDBC driver. If using default DB2 driver, it should be: com.ibm.db2.jdbc.net.DB2Driver. If you are using a third party driver, check documentation for that product.
• JDBC URL: Provide URL to connect to your server. A sample URL might look like: jdbc:db2://host/sample. Check with your database administrator for URL to your database.
• Username: User name to log on to DB2. The default user is 'db2admin'.
• Password: Password for the user name above.

Sybase Server

Provide the following setting to monitor the Sybase database:

• JDBC Driver Jar: The jar file which contains the JDBC driver for your Sybase database. The jar files to be provided for Sybase are: <absolute_path>\jconn2.jar
• JDBC Main class: The main class for your JDBC driver. If using default driver, it should be: com.sybase.jdbc2.jdbc.SybDriver. If you are using a third party driver, check documentation for that product.
• JDBC URL: Provide URL to connect to your server. A sample URL might look like: jdbc:sybase:Tds:<hostname>:<portnumber>. Check with your database administrator for URL to your database.
• Username: User name to log on to Sybase.
• Password: Password for the user name above.

Note: If 'Monitoring Tables' are not installed in your Sybase database installation, then the following additional settings are required:

1. Run the following scripts from the command prompt :

   Details for the scripts that need to run are found at http://www.sybase.com/detail?id=1013610#instmsgs

   The commands to run for installing the scripts are :

   1. Running the installmaster (instmstr on Windows) Script

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\instmstr

   2. Running the installcommit (instcomm on Windows) Script

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\instcomm

   3. Running the installsecurity (instsecu on Windows) Script

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\instsecu

   4. Running the instmsgs.ebf Script

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\instmsgs.ebf

   5. Running the installhasvss (insthasv on Windows) Script

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\insthasv

   6. Running the installdbccdb (instdbcdb on Windows) Script for 12.5.0.3 or Higher

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\installdbccdb

2. Check the server is running fine :

   1. Start Sybase Central.

   2. Connect to the server to check that you are able to connect to the server properly.

3. Run the following commands to enable the monitoring tables:

   1. Log on to the server.

   isql -Usa -P -S<ServerName>

   2. First, ensure that the configuration parameter 'enable cis' is set to 1 (if not, an ASE restart is needed).

   sp_configure 'enable cis'

   go

   3. Add 'loopback' server name alias.

   use master

   go

   sp_addserver loopback, null, <ServerName>

   go

   4. Test this configuration.

   set cis_rpc_handling on

   go

   exec loopback...sp_who  -- note: 3 dots!

   go

   5. Quit from the shell.

   exit

   6. Install the MDA (Monitoring Data Access) tables from the command prompt.

   isql -Usa -P -S<ServerName> -n -i<SYBASE_HOME>\ASE-12_5\scripts\installmontables

   7. Connect to the server back and Assign 'mon_role' to user to allow MDA (Monitoring Data Access) tables access.

   isql -Usa -P -S<ServerName>

   use master

   go

   grant role mon_role to sa

   go

   8. Quit from the shell.

   exit

   9. Connect to the server back - this you need to do to activate 'mon_role'.

   isql -Usa -P -S<ServerName>

   10. Now enable all configuration parameters; these are all dynamic (except the last one) For all 'pipe' tables, the number of messages is set to 100 here, but you may want to choose a large size. Dynamic parameters means server does not require restart.

   sp_configure "enable monitoring", 1

   go

   sp_configure "sql text pipe active", 1

   go

   sp_configure "sql text pipe max messages", 100

   go

   sp_configure "plan text pipe active", 1

   go

   sp_configure "plan text pipe max messages", 100

   go

   sp_configure "statement pipe active", 1

   go

   sp_configure "statement pipe max messages", 100

   go

   sp_configure "errorlog pipe active", 1

   go

   sp_configure "errorlog pipe max messages", 100

   go

   sp_configure "deadlock pipe active", 1

   go

   sp_configure "deadlock pipe max messages", 100

   go

   sp_configure "wait event timing", 1

   go

   sp_configure "process wait events", 1

   go

   sp_configure "object lockwait timing", 1

   go

   sp_configure "SQL batch capture", 1

   go

   sp_configure "statement statistics active", 1

   go

   sp_configure "per object statistics active", 1

   go

   sp_configure "max SQL text monitored", 2048

   go