Informix 14.10 for DBAs

Description

At SIXE we have been providing official IBM training worldwide for over 15 years. Get the best training delivered by our specialists across Europe.

Course Details

Course Description

This course provides participants with the knowledge and skills needed to efficiently design, create and manage IBM Informix databases. Over 24 hours of intensive training, it covers in depth schema design, SQL administration, data partitioning, query optimization, security, and the latest features of Informix 14, including native JSON/BSON document support and query optimizer enhancements.

Target Audience

The primary audience for this course is Informix Database Administrators. It is also appropriate for Informix System Administrators and Informix Application Developers who need to deepen their understanding of data schema design and management.

Desired Prerequisites:

• IX101 – Introduction to Informix Terminology and Data Types (or equivalent experience or knowledge)
• Knowledge of Structured Query Language (SQL)
• Experience using basic Linux functionality

Upon completion of this course, participants will be able to:

• Create, modify and manage databases, tables and indexes in Informix
• Design and implement partitioning (fragmentation) strategies to improve performance
• Analyse and optimize query plans using the cost-based optimizer
• Configure isolation levels and manage concurrency effectively
• Implement data-level security, including privileges, roles and LBAC
• Develop stored procedures, triggers and views
• Work with advanced data types, including JSON/BSON collections

Instructors

The vast majority of our IBM courses are taught directly by our engineers. This is the only way we can guarantee the highest quality of instruction. We supplement all training with proprietary materials and hands-on labs developed from our extensive experience in deployments, migrations and courses over the years.

Added Value

Our courses are deeply role-oriented. The technical needs of a development team differ significantly from those responsible for deploying and managing infrastructure. That is why, beyond commands and tasks, we focus on solving the problems that arise in the day-to-day work of each team, providing them with the knowledge, competencies and skills required for each project. Furthermore, our documentation is based on the latest version of each product.

Course Agenda and Syllabus

Unit 1 – Creating Databases and Tables

• Creating and configuring databases
• Defining tables and data types
• Inspection tools: DBSCHEMA, ONCHECK and ONSTAT
• System catalogue: Sysmaster

Unit 2 – Modifying and Dropping Databases and Tables

• ALTER TABLE: structural modifications
• Reclaiming and reorganising data space
• Renaming objects
• Safely dropping databases and tables

Unit 3 – Creating, Modifying and Dropping Indexes

• Index types in Informix
• Functional and conditional indexes
• Index information in Sysmaster
• Forest of Trees (FOT) structure

Unit 4 – Index Management and Maintenance

• Benefits and costs of indexing
• Index selection guidelines
• Size estimation and capacity planning
• Index monitoring and maintenance

Unit 5 – Table and Index Partitioning

• Fragmentation concepts
• Distribution schemes: round-robin, expression-based, list and interval
• Range and hash partitioning
• Querying fragments in Sysmaster

Unit 6 – Table and Index Partition Maintenance

• ALTER FRAGMENT: add, drop, modify and init
• Data migration between fragments
• Attach and detach partitions

Unit 7 – Cost-Based Query Optimizer

• Access plans and cost estimation
• Join methods: nested loop, hash join, sort-merge
• SET EXPLAIN and execution plan analysis
• Scan types: sequential, index and key-only
• Optimizer enhancements in Informix 14

Unit 8 – Updating Statistics and Data Distributions

• UPDATE STATISTICS: LOW, MEDIUM and HIGH modes
• Data distribution information
• Statistics collection process
• Automating and scheduling statistics

Unit 9 – Optimizer Management

• Optimization options: OPTCOMPIND, DIRECTIVES
• Optimizer directives
• Hints and forced execution plans

Unit 10 – Referential and Entity Integrity

• Entity integrity: primary and unique keys
• Referential integrity: foreign keys and cascading actions
• CHECK constraints

Unit 11 – Constraint Management

• Transaction modes: logging, ANSI and no-logging
• Creating, modifying and dropping constraints
• Performance impact of constraints

Unit 12 – Violation Detection Modes

• Object modes: enabled, disabled, filtering
• Disabling and re-enabling constraints
• Violations logging tables
• Diagnostics via Sysmaster

Unit 13 – Concurrency Control

• Isolation levels: dirty read, committed read, cursor stability, repeatable read
• Lock management and deadlock handling
• Row versioning (MVCC) for lock-free reads
• Last committed read

Unit 14 – Data Security

• Data security levels
• Database, table and column-level privileges
• Roles and access management
• Introduction to Label-Based Access Control (LBAC)
• Column encryption

Unit 15 – Views

• Creating and using views
• Updatable views and WITH CHECK OPTION
• View information in Sysmaster

Unit 16 – Stored Procedures

• Creating and managing stored procedures in SPL
• Variables, flow control and exception handling
• Stored procedure information in Sysmaster

Unit 17 – Triggers

• Creating and types of triggers (INSERT, UPDATE, DELETE)
• Triggers and stored procedures
• FOR EACH ROW and FOR EACH STATEMENT triggers
• Cursors and triggers
• Trigger verification and debugging
• Trigger management in Sysmaster

Unit 18 – Advanced Data Types and JSON/BSON (Informix 14)

• Collections: SET, MULTISET and LIST
• ROW data types (named and unnamed)
• Storing and querying JSON/BSON documents
• Access via Wire Listener and REST API

Appendix A – Terminology

• Informix glossary of terms

Appendix B – Data Types

• Complete reference of Informix native data types

Appendix C – XML Publishing

• Generating and publishing data in XML format

Appendix D – Basic Text Search DataBlade Module

• Configuration and use of the BTS DataBlade Module

Appendix E – Node DataBlade Module

• Using the Node DataBlade Module for hierarchical data

Appendix F – External Tables

• Creating and using external tables for data loading and unloading

Do you need to adapt this syllabus to your needs? Are you interested in other courses? Contact us with no obligation.

On-site Training Locations

• Spain: Madrid, Seville, Valencia, Barcelona, Bilbao, Málaga
• Portugal: Lisbon, Braga, Porto
• Argentina: Buenos Aires, Córdoba
• Bolivia: La Paz
• Chile: Santiago de Chile
• Colombia: Bogotá, Medellín, Cartagena, Cali
• Costa Rica: San José
• Ecuador: Quito
• Mexico: Mexico City, Guadalajara, Monterrey
• Paraguay: Asunción
• Peru: Lima
• Uruguay: Montevideo