Using the Informix Dynamic Server Scheduler and the SQL API

Introduction to new database management features

IBM IDS 11 provides a new set of expected features that form the basis of RDBMS self-management. These tools enable DBAs to collect server information and adjust the database server using SQL as a remote administration tool. In addition, the scheduler can also schedule the execution of SQL API commands, much like the Unix®cron task. IBM IDS itself is a reputable management-free database, and now lowers the cost of management and problem diagnosis, and is worth a step further.

The management enhancements implemented by IBM IDS 11 are:

A new system database (sysadmin)

Database Scheduler

Tube Logic SQL command API

SQLTrace characteristics

ONCONFIG Simplification

Familiarize yourself with the concepts of these features and how to troubleshoot them. In addition, learn the basics of using a database management system for automating tasks. This article provides detailed syntax for using these features.

New features for the system

New administrative options

In addition to the scheduler, the SQL API provides a programmatic way to perform most of the functions of the current management tool.

What the SQL API can and cannot do is:

IBM IDS now provides SQL interfaces for the Finderr, OnInit, Onmode, Onspaces, Onparams, Ondblog, Oncheck (only-C options), OnLog, and Onstat utilities. Almost all of the functionality of these management utilities can be integrated into a single SQL script and executed on a scheduled or temporary basis. Now, by tracking the results in the engine, you can audit the engine settings and administrative tasks.

However, there is currently no SQL API for startup features, shutdown features, Onaudit, Oncheck–p, Dbschema, Onperf, ER management tasks, and data backup/recovery and data load/unload/export utilities.

Initially, only "Informix" users have access to the sysadmin database, but can extend access to any user or system administrator in the DBSA group. If you do not add the appropriate permissions for the user or session user ID, the SQL API command will fail with the error " -387 no connection permission". As a DBA, if you decide to grant the insert permission for the Ph_task table to a user, be cautious about the objects that are granted permissions-those tasks/monitors run as Informix users, which may inadvertently grant access to other attributes to the user. Therefore, it is strongly recommended that you do not grant direct access to Ph_task to users, but instead add trusted users to the DBSA group.

New Environment parameters

This feature does not add new session environment variables.

New Onconfig parameters

IBM IDS 11 Adds a new Onconfig variable to control the SQL Trace utility. SQLTrace enables you to monitor the most recently executed SQL statements and to obtain a variety of performance metrics about the query.

SQLTrace has 4 optional parameters:

Level: This parameter is used to set the number of tracked information. The default value is "off" and the acceptable values are "low", "Med", and "high."

The low level tracks the following SQL statement information:

User ID

Session ID

Current database name

Statement type

Statement Execution time

Rsam State (Buffer and page read/write, sort, lock request and wait, logical logging, and index buffer read)

Connection block status (expected rows returned, expected cost, number of rows returned, and statement type)