IBM - CL461 - DB2 9 for LUW, Advanced DB Administrators for Experts

CCyD - Centro de Capacitación y Desarrollo
En Buenos Aires

Precio a consultar
¿Preferís llamar ya al centro?
11527... Ver más

Información importante

  • Capacitación
  • Buenos aires
  • Duración:
    5 Días
Descripción


Dirigido a: This is an advanced course for DB2 LUW experienced database administrators who support DB2 for UNIX, Windows, and Linux databases

Información importante
Sedes

Dónde se enseña y en qué fechas

inicio Ubicación
Consultar
Buenos Aires
Buenos Aires, Argentina
Ver mapa

Preguntas Frecuentes

· Requisitos

DB2 9 Database Administration Workshop for Linux (CL201) or DB2 9 Database Administration Workshop for UNIX (CL211) or DB2 9 Database Administration Workshop for Windows (CL231) or Fast Path to DB2 9 for Experienced Relational DBAs (CL281) or DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL481) or DB2 9 for LUW Multiple Partition DBA Workshop (CF24) or DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (CG24) or have equivalent experience

Temario

After completing this course, you should be able to:

Monitor a DB2 LUW database using command line processor queries
Use the db2diag command to search the DB2 diagnostic log messages to resolve various common database problems
Implement DB2 Instance audit data collection using the db2audit command or database level auditing by creating audit policy objects and assigning the policies to objects using the AUDIT command.
Configure a DB2 database for autonomic utilities, including database statistics collection and table reorganization
Analyze REORGCHK reports to determine if the table or the index reorganization would improve database efficiency. Invoke and monitor the processing for the REORG utility running offline or online
Manage the disk space allocated in Database Managed Storage (DMS) table spaces using ALTER TABLESPACE to extend or to reduce the containers, and monitor the progress of the DB2 rebalancer process
Implement automatic resize for DMS table spaces or Automatic Storage management for table spaces to reduce the complexity of managing DB2 Linux, UNIX, and Windows (LUW) databases
Use the DB2LOOK utility to extract the Data Definition Language (DDL) for existing database objects
Plan and execute the DB2MOVE utility to copy selected table data for an entire schema for objects from one DB2 database to another
Configure the TCP/IP connections from DB2 clients to DB2 database servers to support application access or remote administration by database administrators
Plan the movement of DB2 databases or table spaces using the Backup and Restore utilities or using the db2relocatedb command with non-DB2 utilities
Plan and implement Multidimensional Clustering (MDC) tables to improve application performance, including selecting the appropriate table space extent size
Utilize range-based partitioned tables to support large DB2 tables that require very efficient roll-in and roll-out capabilities
Course outline:
Advanced Connectivity and Remote Administration
Describe and verify the relevant registry variables for distributed architecture
Describe the flow through the different directories used by DB2 for connectivity between clients and servers
Maintain the directories for database connections
Enable DB2 LUW Lightweight Directory Access Protocol (LDAP) support
Explain the differences between the different options for client connectivity including the IBM Data Server Client and IBM Data Server Run Time Client
Implement automatic client rerouting and list the relevant entries
Describe the purpose of DB2 Administration Server (DAS)
Establish the TOOLS CATALOG database and identify how it is used
Utilize the Configuration Assistant to maintain database connection configurations for clients
Advanced Locking for DB2 9
Describe the types of locks used by DB2 to support applications using different isolation levels
Configure the database parameters locklist and maxlocks to minimize lock escalations and lock waits
Utilize SNAPSHOT and Event monitors to analyze application lock waits and deadlocks
Set the DB2 registry variables to improve application efficiency, including DB2_EVALUNCOMMITTED, DB2_SKIPINSERTED and DB2_SKIPDELETED
Describe using DB2 expressions and functions to implement optimistic locking, including RID_BIT and ROW CHANGE TOKEN
Explain the use of Block level locks for MDC tables
Advanced Monitoring
Compare using GET SNAPSHOT commands to running SQL queries to collect snapshot monitor data
Describe the impact of enabling the Snapshot Monitor switches on the monitor data available and the performance overhead associated with collecting more detailed monitor data
Use the DB2 provided Administrative Views and Routines in SQL SELECT statements to retrieve snapshot performance statistics
Check database health indicators, like log space available and table space utilization using CLP queries with the Administrative Views
Advanced Problem Determination
Describe the types of information collected by DB2 that can be used to diagnose problems, including the db2diag.log and the administration notification log
Plan the use of various diagnostic tools to address specific problems, including the db2diag command, db2pd, db2dart, inspect and db2level
Collect supporting information for DB2 database problems using db2support and db2trc
Explain how automatic and manual First Occurrence Data Capture (FODC) facilities can help collect diagnostic information
Analyze DB2 generated messages in the db2diag.log file and choose appropriate tools and commands to resolve the problems
Utilize the Health Monitoring functions of DB2 to check the health indicators for a DB2 instance, database, or table space
DB2 Database Auditing
Describe the tasks for DB2 database auditing performed by the SYSADM user
List the security administration tasks for DB2 databases that require the SECADM database authority in performing database level audits
Utilize the db2audit command in implement instance level auditing and to configure the audit data and archive locations
Create audit policies to enable collection of specific categories of audit data for a DB2 database
Assign audit policies to selected tables, users or database roles using the AUDIT statement
Advanced Table Space Management
Describe the benefits and limitations of using System Managed Storage (SMS), DMS and Automatic Storage management for table spaces
Examine GET SNAPSHOT FOR TABLESPACES reports to obtain the current disk space usage, the High Water Mark and describe the mapping of extents to the DMS table space containers
Utilize the DB2DART utility to list and analyze the current High Water Mark for a DMS table space
Monitor the processing done by the Rebalancer using LIST UTILITIES and GET SNAPSHOT FOR TABLESPACES output
Plan and implement changes to disk space allocations using ALTER TABLESPACE options: ADD, EXTEND, RESIZE, DROP, and BEGIN NEW STRIPE SET
Plan the implementation of Large Row Identifiers and Large table spaces to support increased table size
Use the Health Monitor and Storage Management functions to track and monitor table space utilization
Advanced Data Movement
Describe the options for moving data between tables including using the Import, Export and Load utilities as well as an SQL Insert with a subselect
Configure the LOAD Utility options to optimize the performance of loading data into DB2 tables
Select the best method for duplication of table and index definitions, including the db2look utility
Utilize the db2move utility to move a group of tables into the same or a different database
Copy the objects for a schema using the db2move utility or the ADMIN_COPY_SCHEMA procedure
Change the Automatic Storage paths for a database
Use the RESTORE Utility with a REDIRECT option to move an 1entire database or selected table spaces to a different location
Setup a db2relocatedb command file to rename a database, copy a database or move table space containers
Advanced Table Reorganization
Describe the reasons for reorganizing tables and indexes
Examine a REORGCHK report to determine which tables and indexes to reorganize
Use GET SNAPSHOT commands, the db2pd command, or queries with DB2 provided table functions to monitor REORG Utility progress
Analyze DB2 generated messages in the db2diag.log file and administration notification log to resolve the problems
Utilize the REORG utility to implement row compression for a table
Compare using REORG to build a compression dictionary to automatic dictionary creation
Plan the use of offline and online table and index reorganizations to minimize the impact to applications and optimize performance
Understand the locking and logging required for online and offline REORGs
Multidimensional Clustering (MDC)
Compare the features and performance advantages of MDC to single-dimensional clustering
Define the concepts of MDC tables, including cell, slice, and dimension
Describe the characteristics of the block indexes used for MDC tables including the index maintenance performed for SQL INSERT, DELETE, and UPDATEs
Explain how the block and row indexes can be combined to efficiently process SQL statements
Utilize the LOAD Utility to roll-in new data into a MDC table
Select options for efficient data roll-out and roll-in
Analyze the effects on table space size of selecting alternative dimensions and extent sizes
Autonomic DB2 Utilities
Describe the automatic database maintenance facilities provided by DB2 for database backups, table statistics collection and table reorganization
Configure a database for automated utilities using the Configure Automatic Maintenance wizard
Explain the evaluation cycles for each of the automated utilities
Select options for automatic statistics collection, including real time statistics
Analyze the DB2 event messages generated by automatic utility evaluation and execution
Table Partitioning
Describe the options for handling data roll-in and roll-out using DB2 Version 8.2 features, including Database Partitioning Feature (DPF), MDC and UNION ALL views.
Describe the basic concepts for range-based table partitioning, including global indexing and multiple table spaces.
Define the data partition ranges for a table using the short and long form syntax.
List the steps used for data roll-in and roll-out for table partitioning, including ATTACH, DETACH and ADD for data partitions.
Compare the roll-in and roll-out functions for table partitioning to using DPF database partitions or MDC tables.
Plan the use of online SET INTEGRITY as part of the roll-in and roll-out processing for range-partitioned tables.
Describe the maintenance for refresh immediate materialized query tables when used with table partitioning.
Select between table partitioning, MDC, and DPF database partitioning depending on the application and data characteristics.
Agenda
Day 1
Welcome
Unit 1 - Advanced Connectivity and Remote Administration
Exercise 1: DB2 Advanced Connectivity
Unit 2 - Advanced Locking
Unit 3 - Advanced Monitoring
Exercise 2: DB2 Advanced Monitoring with SQL
Day 2
Unit 4 - Advanced Problem Determination
Exercise 3: DB2 Advanced Problem Determination
Unit 5 - DB2 Database Auditing
Exercise 4: DB2 Database Audit implementation
Unit 6 - Advanced Table Space Management
Exercise 5: DB2 Advanced DMS Table Space Management
Day 3
Unit 7 - Advanced Data Movement
Exercise 6: DB2 Advanced Data Movement
Unit 8 - Advanced Table Reorganization
Exercise 7: DB2 Advanced Table Reorganization
Day 4
Unit 9 - Multiple Dimension Clustering
Exercise 8: DB2 Multidimensional Clustering
Unit 10 - Autonomic DB2 Utilities
Exercise 9: Autonomic Database Utilities
Day 5
Unit 11 - Table Partitioning
Exercise 10: Table Partitioning