informix - database administration

By attending Informix - Database Administration workshop, Delegates will learn to:

  • Select the Informix 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 using 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

In Informix - Database Administration training course, Delegates will learn the basic concepts of data management within Informix. They will learn how to determine correct data types; how to create, manage, and maintain tables and indexes; how the Informix optimizer works; how to manage data; and how to use the SET EXPLAIN feature to determine query effectiveness.

  • Working knowledge of Structured Query Language (SQL)

This course is for Informix Administrators, Database Administrators, and Application Developers.

COURSE AGENDA

  • Delete a database
  • Delete a table
  • Alter a table
  • Convert a simple large object to a smart large object
  • 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
  • Identify the Informix data types
  • Choose the appropriate data type for a data column
  • Define basic Informix Dynamic Server terms
  • Query the sysmaster database for information about the server
  • Build an index
  • Alter, drop, and rename an index
  • Identify the four index characteristics
  • 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
  • List the ways to fragment a table
  • Create a fragmented table
  • Create a detached fragmented index
  • Understand temporary fragmented table and index usage
  • Alter a fragmented table
  • Alter a fragmented index
  • Explain how to skip inaccessible fragments
  • 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
  • Execute the UPDATE STATISTICS statement and explain the results
  • Use the system catalogs to monitor data distributions
  • Understand how to set OPTCOMPIND
  • Understand how to set OPT_GOAL
  • Write optimizer directives to improve performance
  • Explain the benefits of referential and entity integrity
  • Specify default values, check constraints, and referential constraints
  • Determine when constraint checking occurs
  • Determine when constraint checking occurs
  • Drop a constraint
  • Delete and update a parent row
  • Insert and update a child row
  • Enable and disable constraints and indexes
  • Use the filtering mode for constraints and the indexes
  • Reconcile the violations recorded in the database
  • Use the different concurrency controls
  • Monitor the concurrency controls for lock usage
  • Use the Retain Update Lock feature
  • Use the database, table, and column level privileges
  • Use the GRANT and REVOKE statements
  • Use role-based authorization
  • Create views
  • Use views to present derived and aggregate data
  • Use views to hide joins from users
  • Explain the purpose of stored procedures
  • Explain advantages of using stored procedures
  • Create and execute a trigger
  • Drop a trigger
  • Use the system catalogs to access trigger information