Db2 for z/OS: Introduction for Technical Support and Operations Training in Singapore

• describe the relational concepts and theory, and the advantages of a RDBMS
• describe the components of the Db2 system, their function, and how they are controlled
• identify the objects used to store Db2 data and the relationships between them
• code the SQL statements needed to create and modify Db2 data objects, and control access to them
• code SQL statements to read and modify Db2 data and interrogate the Db2 catalog
• describe and fully utilise all the functions available with Db2 Interactive (Db2i)
• understand the role of logging in Db2 system and data recovery
• use online utilities to backup and restore data, and perform other data management tasks
• describe the use of stand-alone utilities and identify situations where they may be required.
• describe and use various Db2 commands to control and monitor the Db2 environment and objects
• describe the actions taken during normal start-up, operation, and shut-down of the Db2 system
• explain the actions to take during start-up following an abnormal termination
• describe the steps involved in program preparation and the purpose of the Db2 precompiler and bind processes.

What is a database?; comparison of hierarchical, network & relational database management systems; a brief history of Db2.

Relational theory; relational model; relational algebra; entity, referential and user-defined integrity.

Architecture overview; Working Storage Areas; Buffer, Sort, RID & EDM pools; Buffer Pools - 64 bit addressing; EDM pool; Sort pool; RID pool; Db2 Attachments; System data sets & databases.

DB2i panels; SPUFI; other functions.

Data Definition Language; Data Manipulation Language; Data Control Language; basic SQL SELECT, INSERT, UPDATE & DELETE statements; COMMIT and ROLLBACK processing; DB2 optimizer.

Pageset structure; Tablespace types and defaults; Db2 object definition; Understanding and choosing options for performance.

Db2 commands; Controlling the Db2 subsystem; Controlling the IRLM; Controlling the TSO Attachment; Controlling the CICS Attachment; Controlling the IMS Attachment; Issuing commands using Db2 Interactive; Controlling Distributed Data Facility; Controlling logging; Controlling buffer pools; Controlling data sharing; Controlling utilities; Controlling Resource Limit Facility; Controlling databases; Controlling threads; Controlling procedures; Controlling functions; Db2 traces; Trace classes; Controlling traces.

Db2 Logging; Db2 log processing; Unit Of Recovery; Two-phase commit protocol; Active Log parameters; Checkpoint parameters; Checkpoint records; Logging commands - SET LOG; Logging commands - DISPLAY; Logging commands - ARCHIVE.

Db2 utilities; Data backup & recovery utilities; System backup & recovery utilities; Data integrity & consistency utilities; Other online utilities; Stand-alone utilities; Using online utilities; Utility control statements; DB2I utilities - main menu; Db2i utilities - Data Set Names panel; Db2i utilities - Control Statement Data Set Names panel; Controlling utilities; Using LISTDEF & TEMPLATE; Using pattern matching; LISTDEF syntax; TEMPLATE ; Dataset sizing; Dataset naming variables; TEMPLATE syntax; OPTIONS syntax.

Db2 command format; Starting Db2; Integrity - normal termination; Integrity - abnormal termination; Log Initialisation; Current Status Rebuild; Forward Log Recovery; Backward Log Recovery; Processing log records; In-doubt resolution; Deferred restart: effect on committed UORs, effect on in-flight UORs; Delaying backout of long running tasks; Effect of delaying backout; Conditional restart: Control Record, log Truncation / limit restart, bypass backout / cold start; Conditional restart implications; Maintaining data integrity with conditional restart; Stopping DB2; BACKUP SYSTEM utility; BACKUP SYSTEM control data; RESTORE SYSTEM utility; Restoring to a point in time.

Precompile, bind, program preparation and execution; plans and packages; BIND PLAN; BIND PACKAGE; BIND QUERY.