Informix 11.7 Database Administration - SPVC

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