Course Details:
Advanced Ingres Programming

Location: On-site, Worldwide
Format: Lecture, Case Studies and Labs
Course Code: AIP

This 4-day intensive, advanced course is designed to help experienced designers and application programmers understand the robust capabilities of Ingres (on UNIX® or Windows®). The program trains clients whose IT solutions require very fast, efficient - and scalable - enterprise solutions to manage huge data volumes. Managing and preventing I/O and concurrency problems is emphasized. Also, query tuning and advanced SQL techniques are covered in depth. Both On-Line Transaction Processing (OLTP) and batch processing problems are discussed. Transformation from a row-at-a-time to set-at-a-time processing, to achieve a 1,500% increase in processing speed, is illustrated with a case study. Examples of techniques for improving performance are illustrated with benchmarks. A large database is supplied for use in lab exercises.

This course is aimed at programmers, programmer team-leaders, and application architects. DBA topics such as physical database design, selection of table structure, key design, indexing, and integrity constraints are not discussed.

A 3-day version of the class is also available, which omits some details, especially in the Advanced SQL section.

Prerequisites:

A strong knowledge of at least one programming language (e.g., C, Cobol, ABF/4GL, OpenROAD, Java™), good basic knowledge of SQL (or successful completion of Intermediate SQL for Ingres), some previous exposure to Ingres, and a strong desire to write fast, effective and reliable code. This is a master class and requires significant Ingres experience to participate to full advantage.

Contents

Day 1

Chapter 1: Introduction

design for performance / development for performance / programmer's responsibilities / standards and style / the two great costs

Chapter 2: What Makes Programs Slow

why programs are slow / understanding disk I-O / Forced Flow Law / lock contention / the users' perspective

Chapter 3: Locking, Lockmodes, and Isolation Levels

locking as a programming issue / database consistency / Ingres locking / setting lockmodes / transaction isolation levels / choosing an isolation level / row-level locking / deadlock

Chapter 4: Transaction Management

transaction basics / transactions and locking /autocommit / The Transaction Management Problem / automating transaction management / common problems / generating key values / gap-free numbering / unrelated work / stateless clients / optimistic locking / user think-time / logging

Day 2

Chapter 5: Tracing and Benchmarking

tracing I-O, logging and locking / tracing query plans / re-directing trace logs / program-controlled tracing / systemwide statistics / diagnosing lock-contention / IMA / how to benchmark / finding problem queries / query-level response time / application-level response time / continuous response-time monitoring

Chapter 6: Interpreting Query Execution Plans (QEPs)

goal-orientation of SQL / candidate query plans / reading QEPs / node types / join techniques / query flattening

Chapter 7: Reducing Query Execution Time

QEP tell-tales / common errors / function joins / QEP v. reality / key statistics / completeness / case studies / problems with REPEATED queries

Chapter 8: Advanced SQL

exploiting Ingres SQL / logical order of execution / outer joins / select-expressions / unions / subqueries / scalar subqueries / CASE expressions / any()

Day 3

Chapter 8: Advanced SQL (continued)

Chapter 9: Cursors, Row-Oriented Processing, and Set-Oriented Processing

REPEATED queries / cursors and select-loops / UPDATE...FROM / row-at-a-time processinng / set-at-a-time processing / temporary tables / set-oriented thinking

Chapter 10: Parallelism

sequential processing / parallel processing / parallel scripting / problems with parallel scripting

Day 4

Chapter 11: Error Handling

the programmer's agenda / language-specific support / error detection / three error indicators / SQLCA / SQLCODE / ANSI SQLSTATE / implicit error checking / custom error-handlers / local and generic errors / errors in DBPs / remote error logging / unrecoverable errors

Chapter 12: Procedures and Rules

reducing inter-process communication / defining DBPs / flow of control / passing temporary table arguments / security / DBPs and locking / rules / statement-level rules / tracing rules

Chapter 13: The Programmer/DBA Team

the role of the DBA / getting what you need / keys / statistics / controlled redundancy