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
Agenda
- 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
- 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
- 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
- Unit 16 - Data Security
- Exercise 16
- Unit 17 - Views
- Exercise 17
- Unit 18 - Introduction to Stored Procedures
- Exercise 18
- Unit 19 - Triggers
- Exercise 19