Faysal AzizIBM Informix Database Administration: Objects and Query Optimisation
Data management, integrity and performance foundations
Manage sample Informix database objects and examine indexes, query plans, statistics, integrity and access controls.
Why this course
This four-day course covers Informix data-management tasks: databases, tables, indexes, fragmentation, query plans, statistics, integrity, concurrency, privileges, views, procedures and triggers.
The source used Informix 12.10. Its concepts are retained, with version-specific syntax and available features checked against the agreed training release. IBM’s lifecycle information places 12.10 Enterprise Edition in Extended Support from April 2026; it is not presented as the current default for a new installation or as automatically covered by standard support.
Exercises use a disposable database with known data and permissions. The course focuses on objects and query behaviour, not a complete server administration, high-availability, backup or disaster-recovery programme.
Learning outcomes
The course teaches participants to:
- Create and inspect sample databases, tables and indexes using appropriate SQL and utilities.
- Compare index benefits/costs and basic fragmentation options.
- Read SET EXPLAIN output and distinguish estimated plans from measured performance.
- Use selected statistics and optimiser options and review their effects.
- Manage sample constraints and investigate violation records.
- Explain isolation, locking, privileges and roles in the chosen release.
- Create or review a simple view, stored procedure and trigger.
- Record evidence before changing objects and validate the result in the training database.
Prerequisites
Knowledge of Informix terminology and data types, or equivalent experience; no particular external course or qualification is required.
- Knowledge of Structured Query Language (SQL)
- Experience using basic Linux functionality
An authorised Informix training instance, compatible client tools and a disposable database. Confirm the selected release, platform requirements, feature availability and any licensing/support terms before delivery.
4 modules
01Day 1 — Databases, Tables and Indexes1 topics
Databases and Tables
- Databases
- Tables
- Introduce dbschema, oncheck and onstat for defined inspection tasks; check options and required permissions in the selected release.
- Sysmaster
Object Changes and Space Reclamation
- ALTER
- Data space reclamation
- Renaming
- Drop only disposable training objects after reviewing dependencies and the saved baseline.
Index Types and Creation
- Indexes
- Sysmaster
- Forest-of-trees indexes: purpose, applicability and how to recognise a FOT plan where supported.
Index Maintenance
- Benefits and costs
- Guidelines
- Estimate index size and compare it with observed storage in the example.
02Day 2 — Fragmentation and Query Optimisation1 topics
Partitioning and Fragmentation
- Fragmentation
- Distribution schemes
- Sysmaster
Maintaining Fragmentation
- ALTER FRAGMENT
Cost-Based Optimiser
- Access plan
- Join methods
- SET EXPLAIN output: access paths, estimated cost/rows and comparison with measured query behaviour.
- Scans
Statistics and Data Distributions
- UPDATE STATISTICS options appropriate to the selected training release.
- Distribution information
- Process
- Updating statistics
Optimiser Options and Directives
- Optimization options
- Review optimiser directives cautiously; compare evidence rather than assume a directive always improves performance.
03Day 3 — Integrity, Concurrency and Access1 topics
Referential and Entity Integrity
- Integrity
- Constraints
Constraint Management
- Transaction modes
- Dropping a constraint
Object Modes and Violation Records
- Object modes
- Change an object mode only in the disposable example and examine its consequences.
- Recording violations
- Sysmaster
Concurrency and Isolation
- Isolation
- Locks
- Release-specific concurrency/read-consistency options; do not assume all Informix versions use identical row-versioning behaviour.
Data Security
- Database/object privilege boundaries, authentication context and least-privilege examples.
- Privileges
- Roles
04Day 4 — Views, Procedures and Triggers1 topics
Views
- Working with views
- WITH CHECK OPTION
- Sysmaster
Stored Procedures
- Stored procedures
- Sysmaster
Triggers
- Working with triggers
- Triggers and stored procedures
- Drop only a designated training trigger and validate the expected behaviour.
- Cursors and triggers
- Triggers and constraint checking
- Sysmaster
- Manage and review a small trigger example, including interaction with transactions and constraints.
A programme built around your team.
Share your training goals and requirements.