Objective

• Select the IBM IDS data types appropriate for storing your data
• Create and maintain databases, tables, and indexes
• Estimate size and extent requirements for tables and indexes
• Create index strategies to improve performance
• Create and maintain table and index partitioning (fragmentation)
• Implement referential and entity integrity
• Manage database and table modes
• Manage violations logging
• Describe how the optimizer builds an optimal query plan
• Use the SET EXPLAIN feature to capture optimizer query plans
• Use directives and environment variables to influence the optimizer
• Optimize the query plan through the use of UPDATE STATISTICS
• Manage end-user concurrency
• Use permissions and roles to manage data security
• Create and use views
• Manage smart large objects
• Create and manage triggers

IBM Informix Course Content:

1.Introduction to IDS Terminology

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

2.IDS Data Types

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

3.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

 4.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

5.Create, Alter, and Drop Indexes

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

6.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

7.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

8.Maintaining Table and Index Partitioning

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

9.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

10.Updating Statistics and Data Distributions

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

11.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

12.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

13.Managing Constraints

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

14.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

15.Concurrency Control

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

16.Data Security

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

17.Views

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

18.Introduction to Stored Procedures

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

19.Triggers

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