Informix11.70 System Management: system activity monitoring

In this tutorial, you will learn about IBMInformix database tools, database monitoring utilities, and how to diagnose problems. Learn how to use the system monitoring interface (SMI) and SQL management API. This tutorial helps you prepare for part 1 of system management certification exam 919 of Informixv11.70.

In this tutorial, you will learn about IBM Informix database tools, database monitoring utilities, and how to diagnose problems. Learn how to use the system monitoring interface (SMI) and SQL management API. This tutorial helps you prepare for part 1 of system management certification exam 919 of Informix v11.70.

Are you considering Obtaining System Management Certification for Informix 11.70 (exam 919? If yes, you have come to the right place. This Informix certification preparation series covers all topics you need to understand before taking the test. Even if you do not have the idea of getting certification, this series is still a good place for you to learn about the new features in Informix 11.70.

About this tutorial

In this tutorial, you will learn how to use an Informix monitoring tool to monitor and diagnose problems. The materials provided here mainly cover the Goals in Part 1 of the exam ("system activity monitoring. Topics covered in this tutorial include:

• Use System Monitoring and isolation
• Message Log
• Use the Informix utility to view information about the database
• Use Scheduler and SQL Administration APIs to view information about database servers
• Concurrency and locking
• Operating System Tools

Target

After completing this tutorial, you should know:

• How to Use sysmaster Database
• What content should I find in the message log?
• How to Use the onstat and oncheck utilities
• How to Use the dbschema Utility
• How to Use the Scheduler
• How to use SQL Administration API
• Different types of locks
• How to use operating system tools for monitoring

Prerequisites

To understand the materials provided in this tutorial, you should be familiar with the following:

• Informix Environment, including configuration files, parameters, installation and management
• Informix concepts and terminologies, including dbspace, data blocks, physical logs, logical logs, and checkpoints

System Requirements

You do not need an Informix copy to complete this tutorial. However, if you complete this tutorial, you will have a better understanding of this tutorial.

System database monitoring and isolation

The Database Server creates and maintains the sysmaster database. It is similar to the Database System Directory, which has been introduced in IBM Informix Guide to SQL: Reference. Just as the system directory of each database managed by the database server tracks objects and privileges in the database, the sysmaster database of each database server tracks information about the database server.

The sysmaster database contains the System Monitoring Interface (SMI) table. The SMI table provides the status information about the database server. You can query these tables to identify bottlenecks, determine resource usage, and track session or database server activity. This article will introduce these SMI tables, which are slightly different from normal tables.

The sysmaster database has many tables. For more information, see the manual. Table 1 provides a new set of tables introduced in Informix version 11.

Table 1. New sysmaster table

Table Name	Description
Sysbaract_log	OnBar Activity Log File
Syscheckpoint	Checkpoint Information
Syscluster	High Availability cluster information
Syscmsmsla	Connection Manager Information
Syscmsmtab	Connection Manager Information
Syscompdicts_full	Compress dictionary Information
Sysdual	A table with only one row
Sysenv	Online server startup Environment
Sysenvses	Session-level environment variables
Sysha_lagtime	Slave server latency statistics
Sysha_type	Information about the Connected Server
Sysha_workload	Slave server workload statistics
Sysipl	Index page log information
Sysmgminfo	Memory authorization Manager/Parallel Data Query Information
Sysnetclienttype	Client-type network activity
Sysnetglobal	Global Network Information
Sysnetworkio	Network input/output
Sysonlinelog	Online log information
Sysprofile	System Configuration File Information
Sysrsslog	RS slave server information
Sysrstcb	Improved viewing of input/output and lock wait Information
Sysscblst	Improved viewing of the memory used by the session
Syssqlcacheprof	Configuration file information for each SQL Cache
Syssqltrace	SQL statement Information
Syssqltrace_info	SQL configuration file tracking system information
Syssqltrace_iter	SQL statement iterator
Syssrcrss	RS slave server statistics
Syssrcsds	Statistics on SD slave servers
Systcblst	Modify existing tables to add pending statistics
Systrgrss	RS slave server statistics
Systrgsds	Statistics on SD slave servers