Introduction to IDS Terminology

• Define basic Informix Dynamic Server terms
• Query the sysmaster database for information about the server

IDS Data Types

• Identify the Informix data types
• Choose the appropriate data type for a data column

Creating Databases and Tables

Create databases and tables

• Determine database logging and storage requirements
• Locate where the database server stores a table on disk
• Create temporary tables
• Locate where the database server stores temporary tables
• Use the system catalog tables to gather information
• Use the dbschema utility

Altering and Deleting Databases and Tables

• Drop a database
• Drop a table
• Alter a table
• Convert a simple large object to a smart large object

Create, Alter, and Drop Indexes

• Build an index
• Alter, drop, and rename an index
• Identify the four index characteristics

Managing and Maintaining Indexes

• Explain the benefits of indexing
• Evaluate the costs involved when indexing
• Explain the maintenance necessary with indexes
• Describe effective management of indexes
• Enable or disable indexes

Table and Index Partitioning

• List the ways to fragment a table
• Create a fragmented table
• Create a detached fragmented index
• Describe temporary fragmented table and index usage

Maintaining Table and Index Partitioning

• Alter a fragmented table
• Alter a fragmented index
• Explain how to skip inaccessible fragments

Cost-Based Query Optimizer

• Write queries that produce the following index scans:
• Sequential with a temporary table
• Sequential scan with filter
• Key-only index scan
• Index Scan: lower and upper index filters
• Dynamic Hash Join
• Key-first index scans

Updating Statistics and Data Distributions

• Execute the UPDATE STATISTICS statement and explain the results
• Use the system catalogs to monitor data distributions

Managing the Optimizer

• Describe the effect on the engine of the different values of OPTCOMPIND
• Describe the effects of setting the OPT_GOAL parameter
• Write optimizer directives to improve performance

Referential and Entity Integrity

• Explain the benefits of referential and entity integrity
• Specify default values, check constraints, and referential constraints
• Determine when constraint checking occurs

Managing Constraints

• Determine when constraint checking occurs
• Drop a constraint
• Delete and update a parent row
• Insert and update a child row

Modes and Violation Detection

• Enable and disable constraints and indexes
• Use the filtering mode for constraints and the indexes
• Reconcile the violations recorded in the database

Concurrency Control

• Use the different concurrency controls
• Monitor the concurrency controls for lock usage
• Use the Retain Update Lock feature

Data Security

• Use the database, table, and column level privileges
• Use the GRANT and REVOKE statements
• Use role-based authorization

Views

• Create views
• Use views to present derived and aggregate data
• Use views to hide joins from users

Introduction to Stored Procedures

• Explain the purpose of stored procedures
• Explain advantages of using stored procedures

Triggers

• Create and execute a trigger
• Drop a trigger
• Use the system catalogs to access trigger information