DB2 Training and Courseware

This intensive, five-day, hands-on course has been designed to provide a comprehensive understanding of the DB2 UDB for z/OS Structured Query Language and how it is embedded in application programs. Throughout the course reference will be made to best practices to ensure that the SQL and application programs will run efficiently and effectively. This course is suitable for programmers working in DB2 for z/OS environments up to and including DB2 9 for z/OS.

This course builds upon the skills taught in the RSM courses DB2 9 for z/OS: Database Administration. It provides attendees with an understanding of additional and advanced administration tasks. It also examines in-depth a number of the subjects covered by the earlier course. This course is suitable for DBAs working in DB2 for z/OS environments up to and including DB2 9 for z/OS

This course provides a detailed understanding of the DB2 UDB for z/OS facilities and procedures required to provide database users with efficient administration and support. It explains the system architecture and operational facilities of DB2 including monitoring, security and recovery techniques. This course is suitable for all environments up to DB2 9.

This course is designed to review many aspects creating and using Stored Procedures in DB2 for UNIX and Windows. DB2 commands and tools, created by IBM for DB2, will be used to support workshop activities. SQL statements will be used to create objects for application design and development. Design issues for functions and stored procedures will be reviewed and applied in workshops.

This intensive course has been designed to provide a comprehensive understanding of the DB2 UDB for z/OS Structured Query Language. It is intended for all personnel who are going to use SQL to report from, and modify DB2 data, in any environment other than embedded in application programs.

This course introduces, describes and explains the fundamental principles of DB2 UDB for z/OS. The course also explains the relational theory and concepts, and shows how DB2 adheres to them. The concepts of DB2 data storage, the DB2 system and its operation, Structured Query Language, the use of DB2 Interactive (DB2I), and the management of DB2 data using standard utility tasks are also introduced and explained. The course includes demonstrations and practical, hands-on exercises.

First, the concepts of DB2 are presented and discussed. The major features of the Structured Query Language (SQL) are presented as the means to access DB2 data (and to create and secure DB2 components).  Several of the “special features” of SQL are presented. Optionally, guided, hands-on, practice sessions give each attendee an opportunity to use SQL to access data.  QMF and/or SPUFI are used to execute SQL statements. DB2 database design and Referential Integrity (RI) are discussed.

Review overall DB2 SQL Performance Considerations. Discuss the issues in Performance “problems”. Discuss the "behavior" or “mis-behavior” of the DB2 Optimizer. Explore DB2 SQL performance issues as revealed via the EXPLAIN Plan / VISUAL EXPLAIN feature. The major columns PLAN_TABLE and DSN_STATEMNT_TABLE are analyzed in detail.  The results of the DB2 EXPLAIN Plan / VISUAL EXPLAIN process are reviewed to obtain insights into the Way(s) that the Optimizer chooses the access path to access DB2 data. Extensive workshops allow the students to test the performance “goodness” of several SQL statements. The Performance Guidelines are applied to COBOL Application programs. Discuss how & when the Optimizer uses Filter Factors based on Valid Statistics & Defaults

The concepts of DB2 are briefly presented and discussed. The Structured Query Language (SQL) is presented as the means to access DB2 data. Several guided, hands-on, practice sessions give each attendee an opportunity to use QMF to build “custom:” reports from DB2 data using the extensive powers of the QMF FORMs panels. Use several other QMF facilities such as Help, Retrieve/”?”, Save/Display, Export/Import, Prompted and SQL Query modes, PROCs.

First, the concepts of DB2 are presented and discussed. The Structured Query Language (SQL) is presented as the means to access DB2 data (and to create and secure DB2 components). Several guided, hands-on, practice sessions give each attendee an opportunity to use SQL to access data.  QMF and/or SPUFI are used to execute SQL statements.  Referential Integrity concepts and implementation are covered.

First, the concepts of DB2 are presented and discussed. The Structured Query Language (SQL) is presented as the means to access DB2 data (and to create and secure DB2 components).  Several guided, hands-on, practice sessions give each attendee an opportunity to use SQL to access data.  QMF and/or SPUFI are used to execute SQL statements.  Referential Integrity concepts and implementation are covered.

DB2 (SQL) programming / embedding SQL in a COBOL application program is presented and discussed in detail.  A "shell" program is modified to SELECT a single row, FETCH multiple rows, INSERT, UPDATE, and DELETE row(s), use Referential Integrity, and use column functions and grouping.

DB2 performance guidelines are discussed.  The SQL statement EXPLAIN PLAN is used to help to evaluate the efficiency of SQL statements.

The following “Release Dependent” features can be covered as needed –

• Version 8 – Table-Controlled Partitioning, Common Table Expression, Dynamic Scrollable Cursors, multi-row FETCH and INSERT, GET DIAGNOSTICS, Recursive SQL, Sequence Objects, Scalar Full Select, Select Statement In An Insert statement, Delimited LOAD / UNLOAD, MQT’s
• Version 9 – PureXML, New Data Types - bigger BINARY, VARBINARY, BIGINT, DECFLOAT, INSTEAD OF triggers, INTERSECT and EXCEPT, TRUNCATE Statement, MERGE Statement, Native SQL Stored Procedures, Optimistic Concurrency Control, Automatic Object Creation, Skip Locked Data, REOPT (AUTO)
• Version 10 - Preview

Review overall DB2 SQL Performance Considerations.  Discuss the issues in Performance “problems”.  Discuss the "behavior" or “mis-behavior” of the DB2 Optimizer. Explore DB2 SQL performance issues as revealed via the EXPLAIN Plan / VISUAL EXPLAIN feature. 

The major columns PLAN_TABLE and DSN_STATEMNT_TABLE are analyzed in detail.  The results of the DB2 EXPLAIN Plan / VISUAL EXPLAIN process are reviewed to obtain insights into the Way(s) that the Optimizer chooses the access path to access DB2 data.

Extensive workshops allow the students to test the performance “goodness” of several SQL statements. The Performance Guidelines are applied to COBOL Application programs. Discuss how & when the Optimizer uses Filter Factors based on Valid Statistics & Defaults

The concepts of DB2 are briefly presented and discussed.  The Structured Query Language (SQL) is presented as the means to access DB2 data.  Several guided, hands-on, practice sessions give each attendee an opportunity to use QMF to build “custom:” reports from DB2 data using the extensive powers of the QMF FORMs panels. Use several other QMF facilities such as Help, Retrieve/”?”, Save/Display, Export/Import, Prompted and SQL Query modes, PROCs.

The concepts of DB2 are presented and discussed.

Structured Query Language (SQL) is presented as the means to access DB2 data (and to create and secure DB2 components).  Several guided, hands-on, practice sessions give each attendee an opportunity to use SQL to access data.  QMF and/or SPUFI are used to execute SQL statements.  Referential Integrity concepts and implementation are covered.

DB2 (SQL) programming / embedding SQL in a COBOL application program is presented and discussed in detail.  A "shell" program is modified to SELECT a single row, FETCH multiple rows, INSERT, UPDATE, and DELETE row(s), use Referential Integrity, and use column functions and grouping.  DB2 performance guidelines are discussed.  The SQL statement EXPLAIN PLAN is used to help to evaluate the efficiency of SQL statements. 

The following “Release Dependent” features can be covered as needed –

• Version 8 – Table-Controlled Partitioning, Common Table Expression, Dynamic Scrollable Cursors, multi-row FETCH and INSERT, GET DIAGNOSTICS, Recursive SQL, Sequence Objects, Scalar Full Select, Select Statement In An Insert statement, Delimited LOAD / UNLOAD, MQT’s
• Version 9 – PureXML, New Data Types - bigger BINARY, VARBINARY, BIGINT, DECFLOAT, INSTEAD OF triggers, INTERSECT and EXCEPT, TRUNCATE Statement, MERGE Statement, Native SQL Stored Procedures, Optimistic Concurrency Control, Skip Locked Data, REOPT (AUTO)
• Version 10 - Preview

This course is custom designed to review the Administration of DB2 version 9.7 for LINUX, UNIX and Windows. This course has 4 major sections; basic administration, advanced recovery, “performance and tuning” and SQL Procedures.  DB2 commands and SQL will be used to focus on relevant DB2 activities.  DB2 administration functions and utilities will be reviewed and applied in workshops.  Performance and tuning issues will be reviewed and applied in workshops. Snapshot commands and administrative views will be used to gather performance data. In addition the design and use of stored procedures will be reviewed.  GUI tools may be used to create, test and debug procedures. Design issues for functions and stored procedures will be reviewed and applied in workshops. EXPLAIN tools can be used to analyze SQL access strategy. The impact of index design will be reviewed. The Developers Workbench or IBM Data Studio may be used in workshops.

This course is designed to review many aspects of performance and tuning of DB2 version 9.7,  for Linux, UNIX and Windows. DB2 commands and tools created by IBM for DB2, will be used to support workshop activities. Performance and tuning varies with type of user to be supported.  We will review this topic for support of SQL users, programmers, data designers and database administrators.

This course is designed to review the concepts and components of DB2 version 9.7 for Linux, UNIX and Windows. DB2 commands and SQL will be used to focus on DB2 administration activities. The relationships between object components will be presented along with communication and security aspects. Design issues of referential Integrity, table check constraints, user defined data types and functions, triggers, large objects and MDC will be reviewed. Program design issues will be considered which effect concurrency, integrity and performance. DB2 administration functions and utilities will be reviewed and applied in workshops.

This course is designed to provide information and practise that will allow users of DB2 for UNIX and WINDOWS, to create tables, use them and also access remote databases. Different environments will be reviewed for client server processing. DB2 components will be reviewed along with basic communication characteristics. The major components of DB2 will be discussed and basic/intermediate SQL for DB2 reviewed and practised.

This course is designed to review many aspects creating and using SQL Stored Procedures in DB2 for UNIX and Windows. DB2 commands and tools, created by IBM for DB2, will be used to support workshop activities. SQL statements will be used to create objects for application design and development. The development center will also be used to create, test and debug procedures. Design issues for functions and stored procedures will be reviewed and applied in workshops.

This course is designed to present some application design topics that can improve performance, using DB2 for UNIX and WINDOWS. SQL statements will be used to create objects for application design and development. Design issues of referential Integrity, table check constraints, generated columns, user defined data types, functions, stored procedures and triggers will be reviewed. Advanced techniques for using SQL will be applied in workshops. Design issues will be considered which effect concurrency, integrity and performance. Basic UDB administration functions will be reviewed and some DB2 utilities will be used.

This course is designed to provide information and practise that will allow users of DB2, to create tables, use them and also access remote databases. Different environments will be reviewed for client server processing. DB2 components will be reviewed. The major components of DB2 will be discussed and basic SQL for DB2 reviewed and practised.

This course is designed to review the concepts and components of DB2 for UNIX and/or Windows. DB2 commands and SQL will be used to focus on DB2 administration activities. The relationships between object components will be presented along with communication and security aspects. Design issues of referential Integrity, table check constraints, user defined data types and functions, triggers and large objects will be reviewed. Program design issues will be considered which effect concurrency, integrity and performance. DB2 administration functions and utilities will be reviewed and applied in workshops.

This course is designed to present some advanced topics for application developers, using DB2 for UNIX and WINDOWS. SQL statements will be used to create objects for application design and development. Design issues of referential Integrity, table check constraints, user defined data types, functions, triggers and large objects will be reviewed. Advanced techniques for using SQL in programs and stored procedures will be applied in workshops. Program design issues will be considered which effect concurrency, integrity and performance. Basic UDB administration functions will be reviewed and some DB2 utilities will be used.

This course is designed to review concepts of using DB2 UDB (Universal Database). UDB object components will be presented along with communication and security aspects. Basic and advanced SQL techniques will be used. CLP commands and SQL statements will be used to create objects for application design and development. Design issues of referential Integrity, table check constraints, user defined data types and functions, triggers and large objects will be reviewed. Techniques for using EMBEDDED STATIC and DYNAMIC SQL in either COBOL or ‘C’ programs, will be applied in workshops. Program design issues will be considered which effect concurrency and integrity (ie. locking) and performance. Basic UDB administration functions will be reviewed and some DB2 utilities will be used.

This course is designed to provide instruction with advanced DB2 programming techniques for OS/390 or z/OS. Performance improving techniques will be stressed. Techniques involving BATCH and ONLINE programming techniques will be reviewed. The EXPLAIN facility of DB2 will be used to analyse DB2 programs and SQL. The DB2 Catalog will be used to analyse physical objects.

This workshop is designed to provide knowledge of using SQL in DB2 for OS/390 or z/OS. This knowledge can be applied to implementing SQL into programs (COBOL programs will be used). Hands-on practice with DB2 will be included, in order to reinforce the concepts and techniques studied in the course. Performance improving techniques will be discussed and the output from the DB2 optimizer will be analysed, using the EXPLAIN facility.

This course is designed to provide end users with concepts and techniques for accessing DB2 Data. SQL is used to formulate queries to obtain data. QMF is used to modify the appearance of this data through QMF FORMS. Various techniques for using QMF are highlighted through class notes and practice in exercise activities.

This course is designed to present some advanced SQL topics, using DB2. SQL statements will be used to create objects for application design and development. Design issues of referential Integrity, table check constraints, user defined data types, functions and triggers will be reviewed. Performance related issues will also be reviewed.

This course is designed to review the SQL Procedure Language (SQL/PL) in DB2. SQL and SQL/PL statements will be used to create objects for application design and development. DB2 functions, triggers and stored procedures are objects which use SQL/PL and they will be created in workshop activities.

This comprehensive 2 day workshop provides the hands on experience to effectively apply Structured Query Language (SQL) to many business problems.

This course is designed to review the concepts and components of DB2 for UNIX and Windows. DB2 commands and SQL will be used to focus on DB2 administration activities. The relationships between object components will be presented along with communication and security aspects. Design issues of referential Integrity, table check constraints, user defined data types and functions, triggers and large objects will be reviewed. Program design issues will be considered which effect concurrency, integrity and performance. DB2 administration functions and utilities will be reviewed and applied in workshops. Performance and tuning issues will be reviewed.

This course is designed to review the concepts and components of DB2 for UNIX and Windows. DB2 commands and SQL will be used to focus on DB2 administration activities. The relationships between object components will be presented along with communication and security aspects. Design issues of referential Integrity, table check constraints, user defined data types and functions, triggers and large objects will be reviewed. Program design issues will be considered which effect concurrency, integrity and performance. DB2 administration functions and utilities will be reviewed and applied in workshops. Performance and tuning issues will be reviewed.

This workshop is designed to provide instruction and practice with DB2 UDB for UNIX or WINDOWS. Commands and SQL statements will be used to create objects for application design and development. Design issues of referential Integrity, table check constraints, user defined data types and functions, triggers and large objects will be reviewed. Some performance improving techniques will be reviewed. Techniques for using EMBEDDED STATIC SQL will be used to code 'C' or COBOL programs and 'C' stored procedures. Program design issues will be considered which effect concurrency and integrity (ie. locking).

This course presents the use of Stored Procedures as a solution to specific application needs in a Client / Server environment. DB2 SQL is used as the tool to build and test these Stored Procedures. This course covers the use of CREATE PROCEDURE DDL statement for coding Stored Procedures using DB2 SQL Procedure Language (SQL/PL). Several hands-on exercises demonstrate the use of INPUT and / or OUTPUT parameters and internal Stored Procedures logic. Several DB2 Stored Procedure examples are reviewed as an insight into examples of the capabilities of Stored Procedures. The use of the CALL statement is presented as the means to invoke a Stored Procedure. Various methods for debugging a Stored Procedure are also presented.

DB2 Version 8 is a total revision of Universal Database for z/OS. This course is designed for those already familiar with DB2 and planning to migrate to Version 8 of DB2. The course covers migration from Version 7. Functional and operational enhancements and their impact on the use of V8 are addressed. New functions, new utilities and existing utility enhancements, reliability, scalability, and availability are discussed. Detailed examples of new features are presented and each section ends with review questions to re-enforce key points.

One section covers the required preparation of Version 7 before an installation can migrate to version 8. Applications, Database Administration, and Systems Administration personnel will all be required to condition their respective environments before migration can begin.