- 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 & Concepts
Relational theory; relational model; relational algebra; entity, referential and user-defined integrity.Db2 System & Operations
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.Introduction to Db2 Interactive (Db2i)
DB2i panels; SPUFI; other functions.Introduction to SQL
Data Definition Language; Data Manipulation Language; Data Control Language; basic SQL SELECT, INSERT, UPDATE & DELETE statements; COMMIT and ROLLBACK processing; DB2 optimizer.Physical Database Design & Data Definition Language
Pageset structure; Tablespace types and defaults; Db2 object definition; Understanding and choosing options for performance.Db2 Monitoring & Control
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 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.Managing Data with Db2 Utilities - Overview
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 Shutdown, Startup & Recovery
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.Program Preparation & Execution
Precompile, bind, program preparation and execution; plans and packages; BIND PLAN; BIND PACKAGE; BIND QUERY.