Informix Dynamic Server Database Administration: Managing and Optimizing Data Training  

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 db schema 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

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

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

• Understand how to set OPTCOMPIND
• Understand how to set OPT_GOAL
• 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

Agenda

Day 1

• Welcome
• Unit 1 - Introduction to IDS Terminology
• Exercise 1
• Unit 2 - IDS Data Types
• Exercise 2
• Unit 3 - Creating Databases and Tables
• Exercise 3
• Unit 4 - Altering and Deleting Databases and Tables
• Exercise 4

Day 2

• Unit 5 - Create, Alter, and Drop Indexes
• Exercise 5
• Unit 6 - Managing and Maintaining Indexes
• Exercise 6
• Unit 7 - Table and Index Partitioning
• Exercise 7
• Unit 8 - Maintaining Table and Index Partitioning
• Exercise 8
• Unit 9 - Cost-Based Query Optimizer
• Exercise 9
• Unit 10 - Update Statistics and Data Distributions
• Exercise 10

Day 3

• Unit 11 - Managing the Optimizer
• Exercise 11
• Unit 12 - Referential and Entity Integrity
• Exercise 12
• Unit 13 - Managing Constraints
• Exercise 13
• Unit 14 - Modes and Violation Detection
• Exercise 14
• Unit 15 - Concurrency Control
• Exercise 15

Day 4

• Unit 16 - Data Security
• Exercise 16
• Unit 17 - Views
• Exercise 17
• Unit 18 - Introduction to Stored Procedures
• Exercise 18
• Unit 19 - Triggers
• Exercise 19