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

Learn via : Virtual Classroom / Online
Duration : 5 Days
Download PDF
  1. Home
  3. DB2 for LUW: Administration in Linux/UNIX/Windows Environments

Description

    The course is designed for those applications programmers, designers, analysts and DBAs responsible for developing and maintaining an efficient Db2 LUW environment. The presentation combines formal classroom tuition with hands-on, practical workshop sessions, which will introduce all aspects of relational technology as implemented by Db2 for Linux, UNIX or MS Windows.

     

    Delegates will learn how 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
    • 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 for Linux, UNIX, AIX or Windows environment.

Outline

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.

Prerequisites

Db2 for LUW: Introduction & Fundamentals