DB2 for Linux, UNIX & Windows - Boot Camp


The comprehensive and challenging course is ideal for Database Administrators, applications programmers, designers and/or analysts who are responsible for developing and maintaining an efficient DB2 LUW environment. The presentation combines formal classroom teaching with hands-on, practical workshop sessions. The course introduces and explains all aspects of relational technology as implemented by DB2 for Linux, UNIX or MS Windows. This is followed by a detailed explanation of how to use advanced DB2 functions for tuning programs in order to optimise their performance.

This course can also be presented as an exclusive, on-site presentation, on dates of your choice.

Classroom dates (High Wycombe) - click to book!

30 October 2017

Objectives

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

  • use Command Center or other SQL execution environments (e.g. WinSQL) to code SQL statements
  • use the System Catalog
  • 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
  • understand and use 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
  • develop DB2 applications with optimised performance attributes
  • maintain and tune applications for optimised performance
  • describe effective techniques for advanced database management.

Who Should Attend

Systems Administrators, Database Administrators, programmers and all technical personnel who are involved in designing, planning, implementing and/or maintaining DB2 for LUW databases within Linux, UNIX, or Windows environments.

Prerequisites

Those attending the course should be familiar with computing environments, but no specific knowledge of DB2 is required.

Duration

10 days

Fee (per attendee)

£4250 (ex VAT)

Course Code

LUWB

Contents

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; DB2 Version 9 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.

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.

DB2 UDB Governor

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

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.

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.

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.

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.

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.


© RSM Technology 2017