DB2 for z/OS Administration

The materials in the course reflect DB2 13, but can cover earlier releases on request. This course provides attendees with a comprehensive introduction to designing and administering DB2 databases. The course covers the complete spectrum from initial data model, through logical and physical database design, data security, and ongoing administration to ensure that the data remains complete, consistent, and in its most efficient state.

This course will be of interest to anyone needing an understanding of the design, implementation, support and maintenance of DB2 databases up to DB2 13.

No prior experience with DB2 is required, although an understanding of data processing in general is useful. Familiarity with the z/OS operating systems and TSO/ISPF would also be of benefit.

On completion delegates will appreciate:

• Data modelling techniques.
• The process of normalisation of data.
• Mapping data models to DB2 objects.
• Choosing the correct data types, and physical design characteristics.
• Data integrity issues.
• Page layouts and storage estimations.
• Indexing, locking strategy and other performance implications.
• Use of utilities and service aids in ongoing maintenance.
• Security and auditing considerations.

The System Architecture
The Big Picture.
DBAS (DATA BASE ADDRESS SPACE).
IRLM.
DDF.
Buffer Manager.
EDM Pool.
Databases.
SORT.
Working Storage.
Data transfer:
• Page.
• Prefetch/Parallelism.
• Parallelism.
Storage.
Log Manager.
DB2 Traces.
DSNZPARMS.

DDL
SQL Limits prior Version 8.
SQL Limits.
DB2 Objects:
• Stogroups.
• Database.
• Tablespace:
• • Simple/Segmented.
• • Partitioned.
• • Parameters.
• • Universal.
• Tables.
• Temporal Tables.
Numeric Data Types.
Character Data Types.
Date/Time Data Types.
Large Objects.
Rowid.
Identity Column.
Sequence Column.
UIDT.
Biginit.
Binary.
Decfloat.
XML.
Attribute Qualifiers.
Referential Integrity.
Views.
Materialized Query Tables.
Online Schema.
Indexes.
Versioning.

The Catalog and Directory
DB2 System Catalog.
DB2 System Catalog History Tables.
Catalog Relationship.
Obtaining the index names.
DB2 directory.

Locking Strategy
Introduction.
What resources can be locked.
Different Locks available.
Duration of a lock.
Commit/Rollback.
Savepoints.
Locking Notes.
Locking Problems and Detection.

Indexes
Benefits of indexing.
Candidates for indexing/not for indexing.
Page.
Record Identifier.
Creating indexes.
Index guide lines.
RUNSTATS/REORG.
Access Strategy.
Index / Stage 1 / Stage 2 Table.
Access Types.
Multiple table Access Path.
The Optimizer.
EXPLAIN.
Querying the PLAN_TABLE.
Sortn/Sortc.
Explain DB 10.
Statement Cost Estimation.
Reoptimization/Rebind.
VISUAL EXPLAIN.
Optimization Service Centre.
Volatile.
DB2 Administration Tools.
DB2 Performance Analyzer. 
Hash Access.
Include Non-Unique Columns Within a Unique Index.
DB2 10 Index Changes.

Advanced SQL
The importance of Predicates.
General guidelines for efficient SQL.
Using Predicates.
OPTIMIZE FOR n ROWS.
FOR FETCH/READ ONLY.
WITH clause.
Table joins:
• Introduction.
• Joins on equality.
• Subselects/Subqueries.
• Correlated Subselects.
• Unions.
• Intersect/Except.
• Inner joins.
• Left joins.
• Right joins.
• Full joins.
• Joining adding a WHERE clause.
• Nested Tables.
CTE(Common Table Expressions).
COALESCE.
CASE.
UPDATE.
UPDATE Using A Subselect.
UPDATE Features.
DELETE.
INSERT.
MERGE.

Utilities
Who Can RunDB2 Utilities.
Invoking DB2 Utilities.
DB2 Administration Tool.
DB2I.
LISTDEF/TEMPLATE.
Using DSNUPROC.
General Utility Flow.
SYSUTILX Directory Tablespace.
LOAD Utility.
DSNTIAUL Utility.
UNLOAD Utility.
RUNSTATS.
REORG.
CHECK.
COPY.
MERGECOPY.
QUIESCE.
RECOVERY.
MODIFY.

DB2 Security
Security Overview.
First Level of Security.
Connection Security.
Internal Security 1.
Other Security.
Security Strategy.
Internal Security.
DB2 Security Mechanism.
DB2 Security Tables.
DB2 Security Terms.
Authorisation ID.
Privilege.
Resource.
SQL:
• SQL Grant and Revoke Statements.
• Cascading Revoke.
• Package, Plan and Collection Privileges.
• Database, Table and View Privileges.
• Other Object Privileges.
• System Privileges.
• Example 1: Application Development.
• Example 2: Bind.
• Example 3: Program Execution.
• Insufficient Authority.
DB2 Catalog Security Tables:
• Common Tables Columns.
• Catalog Tables.
• Auditing Tables.
• Audit Trace.
RACF/ACF2/TOP SECRET Security Overview:
• What is RACF/ACF2/TOP SECRET.
• Identify and Verify Users.
• Checking Authorizations.
• Recording and Reporting.
• Terminology.
• Users and Groups.
• Resources and Classes.
• Profiles.
• User Profile.
• Resource Profile.
• Discrete and Generic Profiles.
• Creating Generic Profiles.
• Maintaining RACF Security.
Multi Level Security.
Distributed Data Considerations.
SECADM.

Application Programming Features Affecting DBAs
Fetch/Read Only.
Optimize For.
Nulls.
Temporary Tables.
Rowset/multi row fetch/update/insert.
Reordered Row Format.
Implicitly Hidden Columns.
Truncate Table.
Temporal Tables.
Program Development.

Triggers / Stored Procedures
TRIGGERS:
• Types of Trigger.
• Requirements.
• Syntax.
• Instead of Triggers.
STORED PROCEDURES:
• Types of Stored Procedures.
• An SQL Procedure.
• An External Procedure.
• The Catalog.
• Writing an External Procedure.
• Calling the Stored Procedure.
• The SQL Procedure Language.
• Stored Procedure Builder.

Logs
The Active Log.
The Archive Log 2.
The Boot Strap Dataset.
The Log Buffer.
DB2 zParms.
Log Avoidance.
DDL Operations.
Reorg and Load Utilities.
SQL Operation.
Mass Delete.
Reorder Row Format.
Log Stand Alone Utilities.
Tools for Handling Logs.

XML
XML Layout.
Data Modelling.
Database Creation.
Tablespace Creation.
Table creation.
Catalog Information.
Creating a full Text Index.
Comparing XML and Relational Indexes.
Index Data Types.
Xpath Expressions.
Create Index.
Using Explain.
Define Lean Indexes.
DB2 Catalog.
DB2 Commands.
zPARMS.
Query Language.
XML Functions:
• XMLPARSE.
• XMLQUERY.
• XMLEXISTS.
• XMLTABLE.
Views.
MQT.
XML With Utilities.
XML Performance Notes.
XML Version 11.