Audience
This course is designed for IBM Informix Dynamic Server Database Administrators.
Pre-requisites
In order to attend this course you must have the following skills or completed the following courses
Relational Database Design or equivalent knowledge
Structured Query Language or equivalent knowledge
Advanced Structured Query Language or equivalent knowledge
Objective
After completing this course, you should be able to:
Select the IBM Informix Dynamic Server 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 ON statement 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
Control data security using permissions and roles
Create and use views
Manage smart large objects
Create and manage triggers
:: Course FN848 ::
Key topics
Introduction to IBM Informix Dynamic Server Terminology
Define basic IBM Informix Dynamic Server terms
Query sysmaster database to determine information about a database
IBM Informix Dynamic Server Data Types
Identify the IBM 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
Create memory resident tables
Use the system catalog tables to gather information
Use the dbschema utility
Altering and Deleting Databases and Tables
Delete a database
Delete 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
Explain the benefits of memory resident indexes
Table and Index Partitioning
List the ways to fragment a table
Create a fragmented table
Create a detached fragmented index
Understand 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
:: Course FN848 ::
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; and Keyfirst index scan
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
Understand how to set OPTCOMPIND to change optimizer performance
Understand how to set OPT_GOAL to change optimizer performance
Write optimizer directives to improve performance
Referential and Entity Integrity
Explain the benefits of having referential and entity integrity at the database server level (versus the application level)
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 the 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
:: Course FN848 ::
Triggers
Create and execute a trigger
Drop a trigger
Use the system catalogs to access trigger information
Certification
Supports Informix System Administrator Certification. Required course in the "IBM Certified Informix Dynamic
Server Version 9 System Administrator".
This is a version-specific reference outline. Confirm the current syllabus and delivery arrangements with EIEIO.