OverviewThis course has been specifically designed to provide a thorough introduction to Db2 for z/OS, and covers all features up to and including V 12.<br>Taught from a Technical Support/Computer Operations perspective, this course introduces, describes and explains the fundamental principles of Db2 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 comprises formal classroom tuition along with a series of demonstrations and practical, hands-on exercises that reinforce the students' understanding at every stage.<br><br>This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.
PrerequisitesA conceptual-level knowledge of the z/OS environment and the ability to use TSO/ISPF and JCL.
Delegates will learn how to
- 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.