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.

Audience

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.

Prerequisites

Those attending the course should be familiar with computing environments.

Duration

5 days

Outline for DB2 UDB: Administration in Linux/UNIX/Windows Environments

1. Introduction to DB2

  • DB2 LUW editions
  • The DB2 Tol Family
  • Basic terms: Instance, Database, Partitioned database, Tablespace, Tablespace management, Table/Row/Column
  • Result Set
  • View
  • Materialized Query Tables (MQT)
  • Nulls
  • Referential Integrity
  • DB2 System Catalog
  • 1 Syscat Views
  • SYSSTAT Views
  • SYSIBM Views
  • Tables Relationships
  • Attributes
  • The Attribute or Built-in Datatype
  • LOBS - Large Objects
  • Identity column
  • Sequences
  • DB2 Version 9 XML
  • XML basics
  • Parsing
  • SQL compatibility (9.7)
  • Oracle and DB2 terminology.

2. SQL Execution

  • Command Line Processing: Advantages, Disadvantages, Execution
  • Command Line Processing parameters
  • Command Editor advantages
  • Command Editor
  • Wizard Control Center
  • Other products.

3. Data Studio

  • Features
  • Database development
  • Working with an instance
  • Creating a database
  • Instance details
  • Database details
  • Creating database objects
  • Managing database security
  • Table creation
  • Viewing the columns
  • Analyze impact
  • Generate DDL
  • Generating an Entity-Relationship diagram
  • Data development projects and creating scripts
  • Creating a Data Development project
  • Default application process settings
  • Creating SQL AND XQuery scripts
  • Using the SQL and XQuery editor to create SQL scripts
  • 6 SQL scripts assist
  • Executing the script
  • Viewing the results
  • Execution log
  • Editing table data
  • Maintaining the database
  • Buffer pool
  • Tablespaces
  • Reorganizing data
  • Updating the catalog statistics
  • Running Runstats
  • Export
  • Running Export
  • Moving data format
  • Load/Import data
  • DB2 logging
  • Changing the logging
  • Backing up and recovering databases
  • Restore
  • Rollforward
  • Recover
  • Data Studio
  • Perspective
  • Database administration views
  • Instances
  • Schemas
  • Tables
  • Indexes
  • Security
  • Creating users
  • Managing database security permissions
  • Analyze Impact
  • Generating an Entity-Relationship diagram
  • Populating a table
  • Generate DDL
  • Database maintenance
  • Tablespaces
  • Buffer Pools
  • Reorganizing Data And Gathering Statistics
  • To reorganize data using data studio tooling
  • Updating the catalog statistics
  • Scripts
  • Scripts data development
  • Utilities - Export
  • Running Export
  • Moving Data Format
  • Load/Import data
  • Utilities - Logging
  • Utilities - Backing up and recovering databases
  • Backup
  • Utilities - Restore
  • Utilities - Rollforward
  • Utilities - Recover.

4. Data Definition Language

  • Control Center
  • Command Line Processing
  • CONNECT
  • A Script File
  • Instance
  • Create/Drop database
  • Parameters
  • Create/Drop Database Wizard
  • Create/Drop database - possible errors
  • Tuning the database
  • Control Center execution
  • Adaptive Compression DB2 10
  • Database configuration parameters
  • Partitioned database
  • Tablespace
  • Physical Storage organization
  • Tablespace management
  • Database-Managed Space (DMS)
  • How to create and view your tablespaces
  • Containers
  • Tablespace settings
  • Viewing tablespaces
  • Output explanation
  • Viewing Containers
  • Bufferpools
  • Block-Based Buffer Pools
  • Viewing Buffer pools
  • Which buffer pool is assigned to tablespaces
  • Buffer Pool utilization
  • Performance implications
  • Self-Tuning Memory Manager (STMM)
  • STMM modes of operation
  • Activating Self-Tuning Memory
  • Determining which memory consumers are enabled for self tuning
  • Verifying which buffer pools are enabled for self tuning
  • Controlling DB2 memory consumption for an instance
  • Disabling Self Tuning Memory
  • Tablespace Creation (Wizard)
  • Create/Drop Table
  • Command Line Processing
  • Create/Drop Table (Wizard)
  • Control Center Execution
  • Table partitioning feature
  • Creating a range partitioned table command line prompt
  • Adding partitions
  • Removing partitions
  • Table partitioning feature wizard
  • Materialized Query Tables (MQT)
  • Why use MQTS
  • MQT drawbacks
  • Creation of MQTs
  • MQT parameters
  • Data initially deferred
  • Refresh Deferred/Immediate
  • Parameters - Maintained by System/Users
  • Disable/Enable Query Optimisation
  • MQT Materialized Query Tables
  • DB2 9
  • Views
  • ALTER command
  • Generating DDL
  • Generating DDL - Control Center
  • Set integrity
  • Set Integrity Wizard
  • Partitioned table
  • Deep compression.

5. SQL

  • SQL Limits
  • SELECT
  • SELECT with a predicate
  • Fetch First
  • DB2 9 FETCH FIRST in ROWS ONLY AND ORDER BY
  • Built In Functions
  • SCALAR functions
  • GROUP BY
  • GROUP BY HAVING
  • Version 8 changes
  • SCALAR FULLSET
  • Complex SQL
  • Table Joins Equality
  • Table Joins Not Equal
  • Subselects or Subqueries
  • Correlated Subselects
  • Unions
  • INTERSECT/EXCEPT Version 9
  • EXCEPT/EXCEPT ALL
  • INTERSECT/INTERSECT ALL
  • JOINS: Inner Joins, Left Joins, Right Joins, Full Joins, Joins adding a WHERE clause
  • Nested Tables
  • COALESCE
  • CASE
  • UPDATE
  • UPDATE using subSELECT
  • UPDATE features
  • DELETE
  • TRUNCATE Table
  • INSERT
  • INSERT USING subSELECT/SELECT FROM INSERT
  • SELECT FROM INSERT
  • SELECT FROM UPDATE/DELETE
  • MERGE Version 9
  • Common Table Expressions.

6. Locking Strategy

  • What resources can be locked?
  • How to set the locking
  • Locking configuration parameters
  • 1 Locking terms
  • Duration of a lock
  • Isolation level
  • Commit/Rollback
  • To find out what locks are held
  • SAVEPOINTS.

7. Indexing

  • Physical data management
  • Indexes
  • Candidates for indexing/not for indexing
  • Types of index
  • B-Tree layout
  • Types of indexes
  • SQL index creation
  • SQL creation wizard
  • SYIBM.SYSINDEXES
  • Design Advisor
  • Utilities
  • RUNSTATS
  • REORG
  • Access strategy: Stage 1/2 predicates Version 8
  • DB2 access paths
  • Single table access: scan, index access, direct index lookup, matching index scan, non matching index scan, index access only, multiple index access
  • Join methods
  • Outer/Inner Table
  • Optimizer considerations
  • NESTED LOOP JOIN (NLJ)
  • MERGE JOIN (MJ)
  • HYBRID JOIN
  • HASH JOIN
  • Conclusion
  • FILTER FACTOR
  • EXPLAIN
  • EXPLAIN tables
  • DB2EXPLN OUTPUT
  • PARALLELISM
  • Visual Explain
  • Query tuning
  • Query Tuning execution
  • MDC - Multi-Dimensional Clustering
  • MDC: how it works, terminology, inserting records, creation
  • Statistical view
  • Statistical view overview syntax.

8. Extra DB2 Facilities

  • Table check constraints
  • Triggers: Types of trigger, Requirements, Syntax, Triggers Wizard
  • TRIGGERS - the catalog
  • Stored procedures 8-12 1 Stored procedures: advantages, Types of stored procedures, An SQL procedure, An external procedure
  • Stored procedures: writing external, Calling the stored procedure, SQL procedure language
  • UDF (User Defined Functions): Creation of the UDF, Registration of the UDF, Using the UDF, UDF Wizard
  • Creating A New User Defined Function Using Data Studio
  • Security levels
  • Label Based Access Control (LBAC)
  • Security Label Component - examples
  • Security policies
  • Grant security label to user
  • Security labels
  • Create table
  • Security levels data control language commands
  • Row protection inserting
  • Row protection Select/Insert
  • Column level security: select, insert
  • Removing or modifying LBAC definitions
  • ADMIN_MOVE_TABLE
  • ADMIN_MOVE_TABLE syntax.

9. Utilities

  • Utility summary
  • Export
  • Import
  • Differences between Import/Load utilities
  • LOAD: LOAD parameters, Load Graphical Mapper on ASC files
  • A backup and recovery strategy
  • Backup utility
  • Parameters
  • Backup wizard
  • Restore utility
  • Restore wizard
  • RUNSTATS
  • RUNSTATS wizard
  • REORG index/table
  • REORG wizard
  • QUIESCE
  • QUIESCE wizard
  • INSPECT
  • REORGCHK
  • Output table stats
  • Output index stats.

10. Embedded Applications

  • Sample C program
  • Development stages
  • PRECOMPILE command
  • BIND command
  • db2bfd - BIND FILE DESCRIPTION tool command
  • JAVA(SQLJ)
  • Declaring Host Variables db2dclgn.

11. Logs, Snapshot, Event Monitor DB2PD & Trace

  • Recovery Logs
  • .1 What parameters are available to control logging
  • How are the log files allocated?
  • Where are the log files stored?
  • Configuring database logging Wizard
  • Error Logs
  • Manual reading of the log
  • Snapshot & event monitor
  • EVENT MONITOR
  • DB2PD
  • TRACE DB2TRC
  • TRACE parameters.

12. Configuration Parameters

  • Database Manager (or Instance) configuration parameters
  • Viewing configuration parameters
  • Instance configuration parameters
  • Instance configuration parameters recommendations
  • Performance Drawer
  • Monitoring Drawer
  • Administration Drawer
  • Diagnostic Drawer
  • Applications Drawer
  • Environment Drawer
  • To obtain configuration parameters
  • Database configuration parameters
  • Database configuration parameters recommendations
  • Logging Drawer.

13. Remote Administration

  • DAS - DB2 Administration Server
  • DAS - DB2 Administration Server creation
  • Configuration Assistant:Configuration Assistant Wizard, To configure a client, Discovery
  • Instance attachment versus DB connection: Instances,Databases, How to achieve an instance attachment, How to achieve a database attachment,Remote Administration example - Local.

14. DB2 UDB Governor

  • Creating the configuration file
  • Required rules
  • Rules that govern actions
  • Starting the Governor
  • Stopping the Governor
  • Governor log files.

15. DB2 Security

  • Authentication
  • Authorization
  • Permissions
  • Categories
  • Administrative authority
  • Instance authorities
  • Database authorities
  • Privilege
  • Database roles
  • Trusted contexts and connections
  • Data Control Language: GRANT, REVOKE
  • DB2 security tools
  • SYSADM authority: Granting SYSADM authority, Viewing SYSADM authorities, Revoking SYSADM authority
  • DBADM authority: Viewing DBADM authorities, Revoking DBADM authorities
  • Viewing Authorities Control Center
  • Roles
  • Trusted contexts
  • Catalog tables
  • DB2 audit: Concept, Auditable objects.

16. Automatic Computing

  • Configuration parameters
  • Command line prompt
  • Configuration parameters AUTO_MAINT
  • AUTO_RUNSTATS/ AUTO_STATS_PROF
  • STATS profiling
  • Configuration parameters automatic maintenance
  • Configure automatic maintenance
  • Alternative ways of collecting catalog statistics.

17. Other Options

  • Recommended tools Versus Control Center tools
  • Activity Monitor
  • Memory visualization
  • Health Center: Using, Configure, The database health indicator settings, Recommendation Adviser
  • Journal
  • Replication Center: The CAPTURE program, The APPLY program, The CAPTURE program
  • Task Center
  • Event Analyzer
  • Configuration Assistant
  • Indoubt Transaction Manager.

18. XML

  • Traditional methods for managing XML data
  • XML: introduction, XML layout example, XML layout
  • Terms - Elements
  • Elements syntax
  • Document Element
  • Terms: NAMESPACE, ATTRIBUTES
  • XML elements vs. attributes
  • Avoid xml attributes?
  • XDM
  • Well-formed XML
  • Table creation: xml datatype, what happens when you create an table
  • Creating a full-text index
  • Create INDEX STATEMENT
  • Comparing xml indexes with relational indexes
  • Index data types
  • Accessing the data example
  • Understanding of XPATH expressions
  • Creating the index using the Wizard LUW
  • How are the indexes used?
  • Logical/Physical index
  • EXPLN ACCESS METHODS USING XML
  • Query language
  • Querying XML
  • CREATE TABLE - example
  • INSERT - example
  • Plain SQL
  • XPATH text search and retrieval of XML data
  • DB2 SQL/XML functions: XMLPARSE - example, XML Document, XMLEXISTS, XMLEXISTS examples: XMLTABLE
  • XQUERY
  • Executing XQUERY
  • Transforming the result set
  • Functions
  • Converting XML to HTML
  • Conditional logic
  • Hybrid queries
  • Embedding Xqueries in SQL.

19. Temporal Tables

  • Temporal Tables in DB2 10
  • Temporal Types
  • System Time: how it works, definition, manipulating the data, System Time Option
  • Business Time (Application Period):Querying, Portion of Business_Time
  • The Catalog.