DB2 UDB Administration in Linux/UNIX/Windows Environments

Objectives

On successful completion of this course attendees will be able to:

• use Command Center or other SQL execution environments (e.g. WinSQL) to code SQL statements
• use the System Catlaog
• describe and control the locking strategy used by DB2 to maintain data integrity
• list the different development environments available
• explain the use and implementation of Stored Procedures and UDFs using the Development Center
• list the different types of indexes and the use of Index Advisor
• use Visual Explain
• use and understand the Health Center
• describe the development and execution of Utilities
• explain the use of Journals
• describe the Replication Center
• explain the use of the Task Center
• describe the use the Event Analyzer
• explain the Indoubt Transaction Manager
• use the Memory Visualizer
• describe the Configuration Assistant.

Who Should Attend

All those who are exposed to, or are required to write and perform activities associated with application development and implementation, in a DB2 UDB Linux, UNIX, AIX or Windows environment.

Contents

Day 1

Introduction

DB2 LUW Editions; The DB2 Family Tools; The Big Picture; Basic terminology; DB2 System CATALOG: SYSCAT VIEWS, SYSSTAT VIEWS, SYSIBM TABLES. Table Relationships; Attributes: LOBs, Identity Column, Sequence Number, XML; SQL Compatibility Feature.

SQL Execution

Command line processing; Command Editor; Wizard Control Center; Obtaining The Error Message Sqlerrm; Other Products.

Day 2

Data Studio

Features; Notes; Database Development; View; Working with an Instance; Creating a Database; Instance Details; Database Details; Creating Database Objects; Schema; Managing Database Security; Adding Users; Other Objects; Table Creation; Viewing the Columns; Creating Indexes; Creating Views; Analyze Impact; Generate DDL; Generating an Entity-relationship Diagram; Projects & Scripts; Creating a Data Development Project; Default Application Process Settings; Creating SQL and Xquery Scripts; Assist; Executing the Script; Viewing the Results; Execution Log; Editing Table Data.

Data Definition Language

Control Center; Add/Remove Instance; Command Line Processing; Control Center Execution; Create Drop Database; Tuning The Database; Database Configuration File; Partitioned Database; Create/Drop Tablespace; Command Line Processing; Control Center Execution; Create/Drop Table; Table Partitioning Feature; MQT; Views; Alter; Generating DDL; Set Integrity; Deep Compression.

Day 3

SQL

Single Table SELECTS; SELECT Predicates; Fetch First; SELECT built in functions; SELECT scalar functions; SELECT GROUP; HAVING; Scalar Fullset; Complex SQL; Joining on Equality; Joining on Equality with NOT; SUBSELECTS/SUBQUERIES; CORRELATED SUBSELECTS; UNIONS; INTERSECT/EXCEPT; Joins; NESTED TABLES; COALESCE; CTE; CASE; UPDATE; DELETE; INSERT; SELECT from an INSERT; SELECT from Update/Delete; MERGE.

Locking Strategy

What resources can be locked; How To Set The Locking; Locking Configuration Parameters; Locking Terms; Duration of a lock; Commit/Rollback; Logging; To find out what locks are held; Savepoints.

Day 4

Extra Facilities

Optimize For; Handling Nulls; Temporary Tables; Table check constraints; Triggers; Stored Procedures.

DB2 LUW Tools Overview

Design Advisor; Index Advisor; Activity Monitor; Memory Visualization; Health Center (Data Studio Administrative Console); Remote Administration; Replication Center; Task Center; Event Analyzer; Recommendation Adviser.

Utilities

Utility Summary; Export; Import; Load; A Backup & Recovery Strategy; Backup; Restore utility; Runstats; Reorg; Quiesce; Inspect; Reorgchk.

Embedded Applications

Sample C Program; Development stages; Precompile command; Bind command; Compile/Link; Java; Declaring Host Variables.

Logs, Snapshot, Event Monitor, REORGCHK, DB2PD and Trace

Logs; Types of Logging; Configuring Database; Logging Information; DB2DIAG; Manual Reading of the Log; Snapshot & Event Monitor: Snapshot, Event Monitor, DB2PD; TRACE DB2TRC; REORGCHK; Syntax; Output Table Stats; Output Index Stats.r.

Configuration Parameters

Reset/Update/View; Examples; Reset/Update/View; Instance Configuration Parameters; Database Configuration Parameters.

Day 5

Remote Administration

DB2 Administration Server; Introduction; Creation; Starting and Stopping; Obtaining the Name of the Server; Updating the DAS Configuration Parameters; Resetting the DAS Configuration Parameters; Configuration Assistant: Wizard, Configure a Client; Instance Attachment Versus DB Connecting: Instance Attachment, Database Connection, Example Local, Example Remote.

DB2 UDB Governor

Introduction; Creating the Configuration File; Required Rules; Rules that Govern Actions; Starting the Governor; Stopping the Governor; Governor Log Files; Reading the Log Files.

Security

Authentication; Authorization; Permissions; Categories; Administrative Authority; Instance Authority; Database Authority; Privilege; Database Roles; Data Control Language; SYSADM Authority; Viewing SYSADM Authority; Revoking SYSADM Authority; DBADM Authority; Revoking DBADM Authority; Viewing Authorities Control Center; Roles; Trusted Context; Catalog Tables; DB2 Audit: Auditable Objects, DB2 Audit Files, Extract.

Automatic Computing

Introduction; Configuration Parameters; AUTO_MAINT; AUTO_RUNSTATS/AUTO_STATS_PROF; Stats Profiling; Configure Automatic Maintenance; Alternative ways of collecting Catalog Statistics.

Other Options

Activity Monitor; Memory Visualization; Health Center; Recommendation Advisor; Journals; Replication Centre; Task Centre; Event Analyzer; Configuration Assistant; Indoubt Transaction Manager.

XML

XML Layout Example; Terms; Object Definition; DB2 Extenders; Net Search Extender; Tablespace; Table; Enabling a Database for Text Search: DB2TEXT; Creating a Full Text Index; Indexes; Index Terms; Using XML Namespaces; Understanding of XPath Expressions; XML Compression; The DB2 Catalog; Using the Explain Statement; Configuration Parameters; XPath Text Search and Retrieval of XML Data; DB2 SQL/XML Functions; Xquery; Executing XQuery; FLWOR; Functions; Db2-fn:xmlcolumn; Filtering on the XML Element Values.