DB2 UDB Training - UD03 - DB2 UDB Development for Database Administrators

UD03 - DB2 UDB Development for Database Administrators
Duration	5 Days
Availability	Check course price and availability

Target Audience

This course provides advanced DB2 UDB training, and covers all of the key tasks normally performed by a Data Base Administrator.

Pre-requisites

Familiarity with the host environment is required. Prior exposure to DB2 or another relational database would be advantageous but not essential.

Course Objectives

The aim of this course is to provide the delegate with the necessary skills required to function as a DBA within a LUW environment. On completion of this course the student will be able to:

use the DB2 for Windows GUI Toolset
define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
manipulate data using DML statements SELECT, INSERT, UPDATE and DELETE statements
use the Command Line Processor
define Referential Integrity constraints and Table Check constraints
resolve integrity violations
install Stored Procedures
install User Defined Functions
understand the application development process
bind plans and packages
resolve security issues using DCL statements GRANT and REVOKE
understand and resolve locking issues
understand and run all DB2 Utilities
perform database backup and recovery tasks
monitor database / application performance
perform database replication tasks

Course Environment

The lectures apply to DB2 UDB running in a Linux, Unix or Windows environment.

Course Details

The UDB Environment
GETTING STARTED WITH UDB: The UDB Environment; Accessing DB2; UDB Product Family; UDB Interactive Tools; Setting up Initial Security; Windows NT Setup - Creating a new Administrator / Group; What is an Instance?; Setting up Instances; Configuring Instances; Implementing Changes; Attaching to an Instance; Client Configuration Assistant (CCA)
DATA DEFINITION LANGUAGE: The Structure of UDB Objects; Definition of DB2 Objects - DDL; Database Definition; Database Creation using the GUI; Database Definition Tasks; Database Placement; Database Connectivity; Catalog Tables; Database Configuration; Database Directories; Tablespace Organisation; Table Placement; Data Placement Considerations; Containers, Pages and Extents; SMS Tablespaces; DMS Tablespaces; DMS Minimum Space Requirements; DMS Maximum Space; Creating a Tablespace using the GUI; Displaying Tablespace Information; Altering a Tablespace; Dropping a Tablespace; SMS vs DMS Considerations; Create Bufferpool; Page and Row Organisation; Table Definition; Table / Column Names; Copying Table Definitions; Table Authority; Data Types; Null Values; User Defined Default Values; Lob Data Options; Lob Data Manipulation; User Defined Distinct Types; Data Capture; The Alter Table Statement; The Rename Table Statement; Listing Table / Tablespace Information; Schema Definition; Indexes; Index Organisation - The B Tree Index; Index Definition; Index Clustering; Index Design Considerations; Views Definition; Read Only Views; View Restrictions; Views - Check Options; Creating a View of Two Tables; Alias Definition; Overview of Triggers; Trigger Definition; The Drop Statements
COMMAND LINE PROCESSOR: CLP Syntax; On-Line Help; Interactive / Non-Interactive Modes; CLP Option Flag; CLP Termination
DATA MANIPULATION LANGUAGE: SQL - Structured Query Language; SQL Features; SQL Query Results; DB2 Environments; The Select Statement; The Where Clause; Special Operators; Not Operand; In Operand; Like Operand; Between Operand; User Keyword; Statements using Nulls; SQL Built-In Functions; Using 'Distinct'; Group By Clause; Group By Rollup; The Grouping Function; Group By Cube; Group By Grouping Sets; Having Clause; Order By Clause; Fetch First Clause; The Values Statement; The Update Statement; The Delete Statement; The Insert Statement; Column Functions (Scalar Functions); Working with Date, Time and Timestamp Columns; DB2 Special Date/Time Registers; Current Date; Current Time; Current Timestamp; DB2 Join; Inner Joins; Outer Joins; Outer Join - Where Clause; Nested Table Expression; SQL Union / Intersect / Except; Subqueries; Subqueries using In; Exists; The 'All' Subquery; The 'Any' or 'Some' Subquery
REFERENTIAL INTEGRITY: What is Referential Integrity?; The Primary Key; The Foreign Key; Referential Constraint Rules; Constraint Names; More Complex Referential Structures; Check Pending / Set Constraints; Running Set Constraints via the GUI
TABLE CHECK CONSTRAINTS: Overview of Check Constraints; Constraint Syntax; Allowable Constraints; When Are Constraints Enforced; Check Pending; Running Set Constraints via the GUI; Catalog Information
MOVING DATA: Import / Export Utilities; Import Utility Syntax; Import Authorities; Export Utility Syntax; Export Authorities; Load Utility; Load Utility Features; Load Examples; Load Utility Syntax; Recovering from Load Failure / Load Pending; Load Utility and Referential / Check Constraints:; Load Authorities; Load / Import Differences; Tablespace Pending States; Backup Pending; Check Pending / Set Constraints; Running Set Constraints via the GUI; Replication - Data Propagator
BACKUP AND RECOVERY: Data Recovery Overview; Methods of Recovery; Logging Overview; Circular Logging; Archival Logging; Log File Information; Recovery History File; The Backup Utility; Backup File Names; Backup Examples; The Restore and Rollforward Utilities; Rollforward Utility / Rollforward Pending; Rollforward Considerations; Recovery Examples; Logging / Backup / Recovery Summary; The Quiesce Utility
DATABASE CONNECTIVITY: Type 1 / Type 2 Connectivity; The Connect Statement; Connect Reset; Disconnect; Release
APPLICATION PROGRAMMING: DB2 Environments; Database Engine Access; Development Cycle for Embedded SQL; SQL Statement Format - C; SQL Statements used in Application Programs; SQL Include; SQL Communication Area; Host Variable Declaration; Retrieving Data into Host Variables; Retrieving a 'Set' of Columns; Cursors; Declare Cursor; Open Cursor; Fetch a Row; Row Update; Row Deletion; Close Cursor; With Hold Option; Fetch First Clause; The Optimize Statement; Handling Nulls; Sample C Program; Handling Large Objects; Declaring Lob Variables; Example using Lob Variables; Using Lob Locators; Example using Lob Locators; Lob File References; Example using File References; Lob Limitations; Retrieving Current Date; Table Names in Application Programs; SQL Error Codes; The SQLCA; Error Handling - The Whenever Statement; Precompiling a Program; Binding a Program; Levels of Optimisation; Identifying a Collection within a Program; Call Level Interface (CLI); Open Database Connectivity (ODBC); Importing Data into Microsoft Access; Java Database Connectivity (JDBC)
USER DEFINED FUNCTIONS: Functions Overview; Function Path; Writing an External Function; Step 1 - Write the Function; Step 2 - Compile the Function; Step 3 - Define the Function to UDB; Step 4 - Test the Function
STORED PROCEDURES: Overview; Advantages of Stored Procedures; The SQL Call Statement; Executing a Procedure; Writing a Stored Procedure - The Server Side; The CASESRV.SQC Example; The CASESRV.SQC Listing; Calling a Stored Procedure - The Client Side; The CASECLI.SQC Example; Implementing a Stored Procedure; The Create Procedure Statement; Stored Procedure Limitations
LOCKING: Implications of Concurrent Processing; Database Manager Locks; Objects of Locks; Lock Modes; Lock Example; Lock Compatibility; Isolation Levels; Lock Escalation; The Lock Table Statement; Commit Points; Lock Wait and Deadlocks; The QUIESCE Utility
MONITORING: Error Logging; Database Monitoring; Snapshot Monitoring; Alerts; Monitoring Commands; Turning Monitoring Switches On; Event Monitoring; The Create Event Monitor Command; DB2 Trace Facility; Explain; Explain Facility Tools; The DB2 Explain Function; Explain Tables; The DB2EXPLN Applet; The Visual Explain Tool
SECURITY: Authorities and Privileges; Instance / System Authorities; Database Authorities; Database Authority Summary; Schema Privileges; Table Privileges; View Privileges; Index Privileges; Package Privileges; The Grant Statement; The Control Privilege; The Revoke Statement; Catalog Information; Granting using the GUI; Privileges Required for Programming; Grant Examples; Revoke Examples
APPLICATION PERFORMANCE: The DB2 Optimizer; Default Catalog Statistics; Levels of Optimisation; Operational Utilities; Rebinding; The RUNSTATS Utility; The REORGCHK Utility; The REORG Utility; Other Application Performance Issues; Data Sorts; Concurrent Application Tuning; Asynchronous Page Cleaner; Blocking Data
UDB TOOLS: The Control Centre; The Command Centre; Writing Scripts; Saving a Script; The Access Plan Panel; The Script Centre; The Journal; The Information Centre; The Alert Centre; Tool Settings
DATA REPLICATION: Overview; Replication Tools; Steps Required to set up a Replication Environment; Defining a Replication Source; Defining a Replication Subscription; Configuring the Capture Program; Configuring the Apply Program; End-User Authentication File Creation; Starting the Capture Program; Starting the Apply Program; Stopping the Capture and Apply Programs; Troubleshooting

Course Format

The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop Java or C programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing DB2 data. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.