DB2 for LUW: Administration in Linux/UNIX/Windows Environments

Introduction to Db2

• Db2 LUW editions; The Db2 Tool 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; XML; XML basics; Parsing; SQL compatibility (9.7); Oracle and Db2 terminology.

SQL Execution

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

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.

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.

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.

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.

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.

Extra DB2 Facilities

• Table check constraints; Triggers: Types of trigger, Requirements, Syntax, Triggers Wizard; TRIGGERS – the catalog; 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.

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.

Embedded Applications

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

Logs, Snapshot, Event Monitor DB2PD & Trace

• Recovery Logs; 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.

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.

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.

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.

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.

Relocate the Database

• Introduction to relocating databases; Tools for data and object movement; Why use the db2relocatedb tool?; How the db2relocatedb tool works; The db2relocatedb parameters; db2relocatedb examples.