EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

IX221MY-Informix Dynamic Server 11 Database Administration-Managing and Optimizing Data

Course outline

4 days
Discuss this programme →

Duration

4 days

Course Description

This course will teach you the basic concepts of data management within IDS. It discusses how to determine correct data types; how to create, manage and maintain tables and indexes; how the IDS optimizer works; how to manage data; and how to utilize the SET EXPLAIN feature to determine query effectiveness.

Audience

This course is designed for IDS Administrators, Database Administrators, and Application Developers.

Pre-requisites

You should have a working knowledge of Structured Query Language (SQL).

Course Objective

  • Select the IBM IDS 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 through the use of 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

Day 1

Welcome

  • Define basic Informix Dynamic Server terms
  • Query the sysmaster database for information about the server
  • Exercise 1
  • Identify the Informix data types
  • Choose the appropriate data type for a data column
  • Exercise 2
  • 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
  • Exercise 3
  • Delete a database
  • Delete a table
  • Alter a table
  • Convert a simple large object to a smart large object
  • Exercise 4

Day 2

  • Build an index
  • Alter, drop, and rename an index
  • Identify the four index characteristics
  • Exercise 5
  • 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
  • Exercise 6
  • List the ways to fragment a table
  • Create a fragmented table
  • Create a detached fragmented index
  • Describe temporary fragmented table and index usage
  • Exercise 7
  • Alter a fragmented table
  • Alter a fragmented index
  • Explain how to skip inaccessible fragments
  • Exercise 8

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 Exercise 9
  • Execute the UPDATE STATISTICS statement and explain the results
  • Use the system catalogs to monitor data distributions
  • Exercise 10

Day 3

  • 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
  • Exercise 11
  • Explain the benefits of referential and entity integrity
  • Specify default values, check constraints, and referential constraints
  • Determine when constraint checking occurs
  • Exercise 12
  • Determine when constraint checking occurs
  • Drop a constraint
  • Delete and update a parent row
  • Insert and update a child row
  • Exercise 13
  • Enable and disable constraints and indexes
  • Use the filtering mode for constraints and the indexes
  • Reconcile the violations recorded in the database Exercise 14
  • Use the different concurrency controls
  • Monitor the concurrency controls for lock usage
  • Use the Retain Update Lock feature
  • Exercise 15

Day 4

  • Use the database, table, and column level privileges
  • Use the GRANT and REVOKE statements
  • Use role-based authorization
  • Exercise 16
  • Create views
  • Use views to present derived and aggregate data
  • Use views to hide joins from users
  • Exercise 17
  • Explain the purpose of stored procedures
  • Explain advantages of using stored procedures
  • Exercise 18
  • Create and execute a trigger
  • Drop a trigger
  • Use the system catalogs to access trigger information
  • Exercise 19

Programme scope, delivery arrangements and suitability are confirmed during enquiry.