Learning. Talent. Advisory
 Home > IBM Trainings

IBM Training

  • DB2 Family FundamentalsOpen or Close

    Description

    • This course provides you with information about the functions of IBM's DB2, a relational database manager which may be installed under a variety of operating systems on many hardware platforms.
    • DB2 runs under the z/OS, VM, Linux, UNIX, and Windows operating systems, to name a few.
    • The course includes discussion of how the DB2 products provide services. The focus is on the services DB2 provides and how we work with DB2, not on its internal workings.
    • If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should have basic knowledge in data processing.

      Topics

        Understanding a Table

        • Identify the advantages of a relational database
        • Define a relation
        • Name the language used to talk to a relational database manager (RDBM)
        • List three characteristics assigned to each column
        • Define the tasks performed by DB2 when running an application
        • Define the roles that are performed within DB2

        Data Modeling and Database Design

        • State the purpose of a business model
        • Identify an Entity-Relational Diagram (ERD) model
        • List several DB2 column data types
        • Identify non-standard column and table names
        • Identify the characteristics of a primary key and a foreign key
        • State the purpose of referential integrity
        • State the purpose of triggers

        How does a User use DB2?

        • List several ways to talk to DB2
        • List multiple ways to generate an SQL statement
        • List several ways to ADD, REMOVE, or CHANGE table rows
        • List several ways to READ data and produce reports

        How does a Programmer use DB2?

        • List the steps needed to create a test environment
        • List the necessary steps to coding SQL in a program
        • Describe the purpose of SQL delimiters
        • Describe the purpose of an SQLCA
        • List the steps involved in preparing a program for execution
        • State the differences between static and dynamic SQL

        What does an Administrator do in DB2?

        • List some of the tasks performed by a DB2 System Administrator: Identifying the DB2 product, Installing DB2, Creating subsystems/instances, databases and table spaces, Authorizing, and Monitoring
        • List some of the tasks performed by a DB2 Database Administrator: Creating, altering and dropping tables, views and indexes, Planning locking strategies, Running utility jobs and Authorizing

        Information Management with DB2

        • List several planning considerations for distributing data
        • List some of the skills required to successfully distribute data
        • Differentiate between remote unit of work, distributed unit of work, and distributed request
        • List some of the security concerns when dealing with distributed data
        • List some of the other products that interface with DB2

        Agenda

        • Welcome
        • Understanding a Table
        • Data Modeling and Database Design
        • How does a User use DB2?
        • How does a Programmer use DB2?
        • What does an Administrator do in DB2?
        • Information Management with DB2
  • DB2 SQL for Experienced Users (WBT)Open or Close

    Description

    • This course teaches you how to make use of advanced SQL techniques to access DB2 databases in different environments. This course is appropriate for customers working in all DB2 environments, specifically for z/OS, Linux, UNIX, and Windows.
    • IBM Maximo Asset Configuration Manager (ACM) provides a new method for managing assets, providing key capabilities needed by organizations that manage aviation, aerospace and defense, rail, nuclear and pharmaceutical assets. This workshop focuses on the ACM industry solution.
    • If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should have experience with coding and executing basic SQL statements.These skills can be developed by attending SQL Workshop (CE120), or equivalent experience.

      Topics

        Introduction

        • Identify the purpose of the clauses in the SELECT statement
        • Describe the key differences among the IBM DB2 platforms
        • Describe and use some of the OLAP features of DB2, such as GROUPING functions like CUBE and ROLLUP, and the RANK, DENSE_RANK and ROW_NUMBER functions

        Create Objects

        • Code statements to: Create tables and views, Alter tables, Create indexes, Implement referential integrity (RI), and Define triggers and check constraints
        • Identify impacts and advantages of referential integrity, including impacts of delete rules
        • Identify considerations when using triggers and check constraints
        • Define and make use of INSTEAD OF triggers

        Join

        • Retrieve data from more than one table via inner and outer joins
        • Use outer joins (LEFT, RIGHT, FULL)
        • Use ANTI JOINS
        • Join a table to itself
        • Use UNION and UNION ALL
        • Use EXCEPT and INTERCEPT

        CASE, CAST, Summary Tables, and Materialized Query Tables

        • Identify when CASE expressions can be used
        • Code CASE expressions in SELECT list and in the WHERE clause
        • Identify when CAST specifications can be used
        • Identify the advantages of using Summary (Materialized Query) Tables and Temporary tables
        • Identify the advantages of using Materialized Query Tables (MQTs)
        • Identify when and how to use Temporary tables

        Using Subqueries

        • Code subqueries using the ALL, ANY/SOME, and EXISTS keywords
        • Code correlated subqueries
        • Choose the proper type of subquery to use in each case

        Scalar Functions

        • Extend your knowledge of scalar functions which: Manipulate arithmetic data, Manipulate date values, and Manipulate character data. Examples of scalar functions that are addressed in this course:
          • SUBSTR
          • POSSTR
          • COALESCE/VALUE
          • DECIMAL
          • ROUND
          • DIGITS
          • CHAR
          • DATE/TIME

        Table Expressions and Recursive SQL

        • Identify reasons for using table expressions and recursive SQL
        • Use nested and common table expressions
        • Identify the difference between views and table expressions
        • Code recursive SQL
        • Control the depth of recursion when coding recursive SQL

        UDTs/UDFs and Performance

        • Describe the concepts behind User-Defined Types, User-Defined Functions and Stored Procedures
        • Predict when queries will use indexes to get better performance
        • Identify concepts of predicate processing
        • State introductory concepts about index structure
        • State general best practices advice
  • Fast Path to DB2 10.1 for Experienced Relational DBAsOpen or Close

    Description

    • This course is intended to instruct the student on the installation and use of IBM InfoSphere Optim Performance Manager Extended Edition and includes hands-on labs. The student should be able to describe the features and key components of IBM InfoSphere Optim Performance Manager Extended Edition. Preliminary steps, including sizing for hardware and software are also taught. Detailed use of the various dashboards are covered, using several problem scenarios. Maintenance and troubleshooting techniques will be addressed, as well as the method to work with technical support.
    • If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      Topics

        Contents

        • Overview of DB2 on Linux, UNIX, and Windows
        • Command Line Processor (CLP) and GUI Usage
        • The DB2 Environment
        • Creating Databases and Data Placement
        • Creating Database Objects
        • Moving Data
        • Backup and Recovery
        • Locking and Concurrency
        • Database Maintenance, Monitoring and Problem Determination
        • Security

        Agenda

        The planned agenda follows. Here are the considerations:

        The first five units must be taught in the order specified:

        • Overview of DB2
        • Getting Started with DB2 GUIs
        • Creating Databases and Data Placement
        • Create Objects
        • Moving Data
        • Backup and Recovery
        • Database Maintenance, Monitoring and Problem Determination
        • Concurrency and Locking
        • Security
  • DB2 10 Administration Workshop for Linux and WindowsOpen or Close

    Description

    • This course teaches you to perform, basic database administrative tasks using DB2 10.1 for Linux, UNIX, and Windows. These tasks include creating and populating databases and implementing a logical design to support recovery requirements. The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools. Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands
    • http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      Topics

        • Overview of DB2 on Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI usage
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Database Maintenance, Monitoring and Problem Determination
        • Locking and concurrency
        • Security

        Agenda

        • Unit 1: Overview of DB2 on Linux, UNIX and Windows
        • Unit 2: Command Line Processor (CLP) and GUI usage
        • Unit 3: The DB2 Database Manager Instance
          • Exercise 1: Create a New DB2 Instance
        • Unit 4: Creating databases and data placement
          • Exercise 2: Creating databases and data placement
        • Unit 5: Creating database objects
          • Exercise 3: Create objects
        • Unit 6: Moving data
          • Exercise 4: Moving data
        • Unit 7: Backup and recovery
          • Exercise 5: Backup and recovery
        • Unit 8: Database Maintenance, Monitoring and Problem Determination
          • Exercise 6: Using DB2 Tools for Performance
        • Unit 7: Locking and concurrency
          • Exercise 7: Investigating DB2 Locking
        • Unit 8: Security
          • Exercise 8: Database Security
  • Query XML Data with DB2 9 (SPVC)Open or Close

    Description

      This course is designed to introduce you to non-traditional ways of using DB2 to work with native XML data. It is also designed to enable database administrators to manage the DB2 - native XML environment.

      You query XML data stored in DB2 tables and create XML documents from traditional DB2 data. You access XML documents as though the documents were relational tables. This content is addressed and can be taken by users or other personnel who only need to access XML data stored in DB2 tables.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should have a working knowledge of SQL and DB2 concepts.

      It would be helpful to have an introductory knowledge of XML, XML - related concepts - but the course is designed not to assume any prior knowledge of XML and related matters.

      Topics

        XML basics

        • Explain the business value of using XML technology
        • Describe why and when XML can be advantageous
        • Differentiate between relational data and XML
        • Determine whether a given XML document follows the basic XML standards

        XML Namespaces

        • Explain the need for and importance of namespaces
        • List the rules for defining and using namespaces within an XML document
        • Use namespaces in common scenarios

        XML Schemas

        • Describe the alternative approaches to schemas for XML
        • Identify the components of a DTD and how they are used
        • Define the components and constructs that make up an XML schema
        • Identify approaches to using XML schemas for XML document processing
        • Create a simple XML schema
        • Validate an XML document against an XML schema

        Introduction to XPath

        • Describe the reasons for using XPath
        • Define the components and constructs that make up the XPath language
        • Identify abbreviated XPath expressions
        • Write simple XPath expressions to navigate through XML documents

        Introduction to XQuery

        • Describe the XQuery and XPath data model
        • Retrieve DB2 data using XQuery functions
        • Construct the most often used FLWOR (for, let, where, order by, and return) expression

        SQL/XML

        • using the SQL/XML built-in functions: Retrieve XML data, Publish XML data, and Shred XML data

        Updating and Transforming XML Documents

        • List the common methods that can be used to update or transform XML documents
        • Describe the reasons for using TRANSFORM
        • Use the TRANSFORM clause to COPY, MODIFY, and RETURN results
        • Use the TRANSFORM clause to replace values and nodes, to insert nodes, delete nodes, and rename nodes in an existing XML document
        • Describe the purpose and functionality of XSLT transformations

        Introduction to Programming with XML

        • Use IBM Optim Development Studio to develop basic stored procedures, triggers, and user-defined functions that handle XML documents
        • Pass XML parameters to stored procedures and user-defined functions
        • Compare modes of access to XML data from common programming languages
        • List XML features that are available through various programming languages
        • List XML capabilities available through Java programming

        Advanced queries

        • Use conditional (if-then-else) logic
        • Include arithmetic calculations
        • Join XML data in different tables
        • Join relational and XML data
        • Embed XQuery inside SQL
        • Pass parameters

        Optim Development Studio and XML Queries

        • Create XML queries (XMLQUERY) using the query builder
        • Create queries containing SQL/XML using the query builder
  • Query and Manage XML Data with DB2 9 (SPVC)Open or Close

    Description

      This course is designed to introduce you to non-traditional ways of using DB2 to work with native XML data. It is also designed to enable database administrators to manage the DB2 - native XML environment.

      You query XML data stored in DB2 tables and create XML documents from traditional DB2 data. You access XML documents as though the documents were relational tables. This content can be taken by users or other personnel who only need to access XML data stored in DB2 tables.

      In the class, you store native XML data in DB2 and create XML indexes. You analyze plan information for queries and take steps for improving query execution. The course also teaches how to manage the XML schemas and the techniques available for decomposing the XML documents.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should have a working knowledge of:

      • SQL and DB2 concepts.

      It would be helpful to have an introductory knowledge of XML, XML-related concepts - but the course is designed not to assume any prior knowledge of XML and related matters.

      Topics

        XML basics

        • Explain the business value of using XML technology
        • Describe why and when XML can be advantageous
        • Differentiate between relational data and XML
        • Determine whether a given XML document follows the basic XML standards

        XML Namespaces

        • Explain the need for and importance of namespaces
        • List the rules for defining and using namespaces within an XML document
        • Use namespaces in common scenarios

        XML Schemas

        • Describe the alternative approaches to schemas for XML
        • Identify the components of a DTD and how they are used
        • Define the components and constructs that make up an XML schema
        • Identify approaches to using XML schemas for XML document processing
        • Create a simple XML schema
        • Validate an XML document against an XML schema

        Introduction to XPath

        • Describe the reasons for using XPath
        • Define the components and constructs that make up the XPath language
        • Identify abbreviated XPath expressions
        • Write simple XPath expressions to navigate through XML documents

        Introduction to XQuery

        • Describe the XQuery and XPath data model
        • Retrieve DB2 data using XQuery functions
        • Construct the most often used FLWOR (for, let, where, order by, and return) expression

        SQL/XML

        • using the SQL/XML built-in functions, Retrieve XML data, Publish XML data, and Shred XML data

        Updating and Transforming XML Documents

        • List the common methods that can be used to update or transform XML documents
        • Describe the reasons for using TRANSFORM
        • Use the TRANSFORM clause to COPY, MODIFY, and RETURN results
        • Use the TRANSFORM clause to replace values and nodes, to insert nodes, delete nodes, and rename nodes in an existing XML document
        • Describe the purpose and functionality of XSLT transformations

        Introduction to Programming with XML

        • Use IBM Optim Development Studio to develop basic stored procedures, triggers, and user-defined functions that handle XML documents
        • Pass XML parameters to stored procedures and user-defined functions
        • Compare modes of access to XML data from common programming languages
        • List XML features that are available through various programming languages
        • List XML capabilities available through Java programming

        Advanced queries

        • Use conditional (if-then-else) logic
        • Include arithmetic calculations
        • Join XML data in different tables
        • Join relational and XML data
        • Embed XQuery inside SQL
        • Pass parameters

        IBM Optim Development Studio and XML Queries

        • Create XML queries (XMLQUERY) using the query builder
        • Create queries containing SQL/XML using the query builder

        Introduction to XML storage

        • Distinguish between transient and persistent XML data types
        • Describe how XML data is stored in DB2
        • Use different techniques to handle XML data in and out of XML storage
        • Use EXPORT, IMPORT, and LOAD utilities with XML data

        XML Indexing

        • Describe the new index structure
        • Create indexes on XML columns
        • Explain the DB2 catalog changes to support the new index structure
        • Use db2dart tool to format the index

        Compression for XML Data and XML Indexes

        • Discuss the pros and cons of compressing XML data and XML indexes
        • List the mechanisms used to compress the XML data and XML indexes
        • Run administrative functions to determine the storage savings achievable or achieved by XML data and XML index compression

        Optimization and performance

        • Discuss the pros and cons of the different options for querying XML data
        • Design XML queries and indexes in a consistent manner so that indexes can be used as much as possible
        • Describe other XML performance guidelines, for example, for XML inserts and XML schema validation

        XML schema management

        • Register XML schema
        • Validate XML document using XML schema
        • Remove XML schema
        • Administer Grant and Revoke privileges

        XML decomposition

        • Annotate XML schema for a document
        • Decompose XML document
        • Control what data is inserted

        Optim Development Studio and XML Schemas

        • Edit user-defined XML schemas
        • Annotate XML schemas for decomposition
        • Register XML schemas for validation and/or decomposition

        Partitioning and Clustering with XML Data

        • Describe the possible use of XML in data warehouses
        • List the DPF, DP, and MDC environments where XML can be used and the benefits of using these environments
  • Manage XML data with DB2 9 (SPVC)Open or Close

    Description

      This course is designed to introduce you to non-traditional ways of using DB2 to work with native XML data. It is also designed to enable database administrators to manage the DB2 - native XML environment.

      You store native XML data in DB2 and create XML indexes. You analyze plan information for queries and take steps for improving query execution. The course also teaches how to manage the XML schemas and the techniques available for decomposing the XML documents.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should have:

      • a working knowledge of SQL and DB2 concepts.

      It would be helpful to have an introductory knowledge of XML, XML-related concepts - but the course is designed not to assume any prior knowledge of XML and related matters.

      Topics

        Introduction to XML storage

        • Distinguish between transient and persistent XML data types
        • Describe how XML data is stored in DB2
        • Use different techniques to handle XML data in and out of XML storage
        • Use IMPORT and EXPORT utilities with XML data

        XML indexing

        • Describe the new index structure
        • Create indexes on XML columns
        • Explain the DB2 catalog changes to support the new index structure
        • Use db2dart tool to format the index

        Compression for XML Data and XML Indexes

        • Discuss the pros and cons of compressing XML data and XML indexes
        • List the mechanisms used to compress the XML data and XML indexes
        • Run administrative functions to determine the storage savings achievable or achieved by XML data and XML index compression

        Optimization and performance

        • Discuss the pros and cons of the different options for querying XML data
        • Design XML queries and indexes in a consistent manner so that indexes can be used as much as possible
        • Describe other XML performance guidelines, for example, for XML inserts and XML schema validation

        XML schema management

        • Register XML schema
        • Validate XML document using XML schema
        • Remove XML schema
        • Administer Grant and Revoke privileges

        XML decomposition

        • Annotate XML schema for a document
        • Decompose XML document
        • Control what data is inserted

        Optim Development Studio and XML Schemas

        • Edit user-defined XML schemas
        • Annotate XML schemas for decomposition
        • Register XML schemas for validation and/or decomposition

        Partitioning and Clustering with XML Data

        • Describe the possible use of XML in data warehouses
        • List the DPF, DP, and MDC environments where XML can be used and the benefits of using these environments
  • DB2 9 Administration Workshop for Linux - SPVCOpen or Close

    Description

      This course teaches you how to perform basic database administration tasks using DB2 9. These tasks include creating DB2 instances, creating and populating databases, and using logical design to support concurrency and recovery requirements. New features, such as range partitioning, data row compression, and pure XML (native XML storage) will be introduced.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX 5L Basics
        • Linux Basics and Administration
        • Windows Systems Administration Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Fundamentals

      Topics

        • Overview of DB2 on Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI usage
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Locking and concurrency
        • Problem determination
        • Security
  • DB2 10 Administration Workshop for Linux - SPVCOpen or Close

    Description

      This course teaches you to perform, basic database administrative tasks using DB2 10.1 for Linux. These tasks include creating and populating databases and implementing a logical design to support recovery requirements. The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools. Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX 5L Basics
        • Linux Basics and Administration
        • Windows Systems Administration
        • Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Fundamentals

      Topics

        • Overview of DB2 on Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI usage
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Database Maintenance, Monitoring and Problem Determination
        • Locking and concurrency
        • Security

        Agenda

        • Welcome
        • Unit 1: Overview of DB2 on Linux, UNIX and Windows
        • Unit 2: Command Line Processor (CLP) and GUI usage
        • Unit 3: The DB2 Database Manager Instance
          • Exercise 1: Create a New DB2 Instance
        • Unit 4: Creating databases and data placement
          • Exercise 2: Creating databases and data placement
        • Unit 5: Creating database objects
          • Exercise 3: Create objects
        • Unit 6: Moving data
          • Exercise 4: Moving data
        • Unit 7: Backup and recovery
          • Exercise 5: Backup and recovery
        • Unit 8: Database Maintenance, Monitoring and Problem Determination
          • Exercise 6: Using DB2 Tools for Performance
        • Unit 7: Locking and concurrency
          • Exercise 7: Investigating DB2 Locking
        • Unit 8: Security
          • Exercise 8: Database Security
  • DB2 9 Administration Workshop for Windows - SPVCOpen or Close

    Description

      This course teaches you how to perform basic database administration tasks using DB2 9. These tasks include creating DB2 instances, creating and populating databases, and using logical design to support concurrency and recovery requirements. New features, such as range partitioning, data row compression, and pure XML (native XML storage) will be introduced.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX 5L Basics
        • Linux Basics and Administration
        • Windows Systems Administration
        • Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Fundamentals

      Topics

        • Overview of DB2 on Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI usage
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Locking and concurrency
        • Problem determination
        • Security
  • DB2 10 Administration Workshop for Windows - SPVCOpen or Close

    Description

      This course teaches you to perform, basic database administrative tasks using DB2 10.1 for Windows. These tasks include creating and populating databases and implementing a logical design to support recovery requirements. The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools. Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX 5L Basics
        • Linux Basics and Administration
        • Windows Systems Administration
        • Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Fundamentals

      Topics

        • Overview of DB2 on Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI usage
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Database Maintenance, Monitoring and Problem Determination
        • Locking and concurrency
        • Security
  • DB2 10 Administration Workshop for Windows - SPVCOpen or Close

    Description

      This course teaches you to perform, basic database administrative tasks using DB2 10.1 for Windows. These tasks include creating and populating databases and implementing a logical design to support recovery requirements. The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools. Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

      Prerequisits

        You should be able to:

        • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
        • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
        • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

        These skills can be developed by taking:

        • OS Training:
          • AIX 5L Basics
          • Linux Basics and Administration
          • Windows Systems Administration
          • Or by having equivalent HP-UX or Solaris administration experience
        • DB2 SQL Workshop
        • DB2 Fundamentals

        Topics

          • Overview of DB2 on Linux, UNIX and Windows
          • Command Line Processor (CLP) and GUI usage
          • The DB2 environment
          • Creating databases and data placement
          • Creating database objects
          • Moving data
          • Backup and recovery
          • Database Maintenance, Monitoring and Problem Determination
          • Locking and concurrency
          • Security
  • DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs - SPVCOpen or Close

    Description

      This course teaches you how to perform database administration tasks using IBM DB2 Enterprise 9 with the Database Partitioning Feature. This course provides a fast path to DB2 Enterprise 9 partitioned database administration skills for experienced DB2 single partition database administrators. These tasks include customization for the DB2 Enterprise 9 with DPF system, creating and populating partitioned databases, designing a database for parallel use, and using partitioned DB2 utilities. There is no actual installation of DB2 Enterprise software in this workshop. A DB2 for Linux system is used to exercise various administrative functions.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic UNIX functions such as utilities, file permissions, hierarchical file system, commands, and the vi editor.
      • Administer a DB2 for Linux, UNIX and Windows single partition database

      Topics

        DB2 DPF Partitioned Database Components and Concepts

        • Identify the kinds of business applications that use parallel databases
        • Describe the basic architecture of each of the hardware platforms on which parallel databases may run
        • Define the strengths of the DB2 DPF partitioned architecture
        • Identify the key features that set DB2 in a DPF partitioned environment apart from other members of the DB2 family

        Getting Started with DB2

        • Describe the purpose of DAS

        Installation

        • Identify the steps to install and customize DB2 in a partitioned environment
        • Use db2_all and rah commands

        Creating a Partitioned DB2 Database

        • Describe DB2 database partitioning
        • Create a database in a DB2 partitioned environment with or without Automatic Storage enabled
        • List the three types of Storage Management for table spaces
        • Describe the three default system table spaces
        • Access and update the database manager configuration file and the database configuration files
        • Access and update the system database directory and list the local database directory
        • Use tools to issue commands and SQL statements

        Partitioning and Database Partition Groups

        • Identify considerations when choosing a distribution key
        • Describe join strategies in partitioned databases
        • Identify the syntax for creating database partition groups

        Data Placement on the Partitions

        • List the advantages and disadvantages for each type of table space management
        • Create SMS table spaces
        • Create DMS tablespaces
        • Create Automatic Storage managed table spaces
        • Use the GET SNAPSHOT commands and db2pd commands to check table space status
        • Use SQL functions specific to the partitioned environment
        • Identify catalog views that contain information about your partitioned environment

        Moving Data

        • Utilize the EXPORT Utility to extract data from a DB2 database
        • Identify the different methods for inputting data, including the Import Utility, using buffered and unbuffered SQL INSERT and the LOAD Utility
        • Describe the process of partitioning and loading data
        • Identify how the partitioned database options on the LOAD command can be used to control the data partitioning and load processing

        Partitioned Database Backup and Recovery

        • Describe the three types of recovery support provided by DB2
        • Explain the principles DB2 uses for its recovery/restart functions
        • Describe the configuration options for DB2 logging and explain the differences between circular and archive logs
        • Use the BACKUP, RESTORE and ROLLFORWARD commands to back up and recover a DPF partitioned database
        • Recover the database to a prior point in time using the RECOVER DATABASE command
        • State general considerations regarding disaster recovery and implementation of a server cluster for high availability

        Scaling the Database

        • Identify how the partitioned database configuration can be scaled by adding new database partitions
        • Differentiate between the three options on the REDISTRIBUTE command
        • Utilize the REDISTRIBUTE command to add or remove database partitions from a database partition group
        • Troubleshoot problems that may occur during redistribution

        Database and Application Performance

        • Describe the steps used by the DB2 Optimizer to generate access plans
        • List the major influences for SQL optimization, including Catalog statistics, database memory configuration, optimization class selection and database partition groups
        • Utilize the RUNSTATS utility to collect detailed table and index statistics
        • Explain the major goals for table and index reorganization
        • Use the REORGCHK report to plan effective use of the REORG utility
        • Describe the use of the DB2 explain tools to analyze access strategies for SQL statements

        Monitoring and Problem Determination

        • Use error logs
        • Identify the different monitor types
        • Identify how to use the independent trace facility (db2trc)
        • Use the dp2pd problem determination tool to obtain statistics from a running instance
        • Identify additional commands for listing application information

        Locks and Concurrency

        • List objects that may be locked by the database manager
        • Discuss available lock modes and their compatibility
        • Influence locking strategies used by the database manager

        Agenda

        • DB2 DPF Partitioned Database Components and Concepts
        • Getting Started with DB2
        • Installation
        • Creating a Partitioned Database
        • Partitioning and Database Partition Groups
        • Data Placement on the Partitions
        • Moving Data
        • Partitioned Database Backup and Recovery
        • Scaling the Database
        • Database and Application Performance
        • Monitoring and Problem Determination
        • Locks and Concurrency
  • DB2 Stored Procedures Programming Workshop - SPVCOpen or Close

    Description

      This is the Self Paced version of Classroom course DB2 Stored Procedures Programming Workshop (CL711) and Instructor led online version DB2 Stored Procedures Programming Workshop - Instructor Led Online (3L711)

      This is a self-paced online course and includes hands-on remote labs. The average amount of time to complete this course is 16 hours. Once you receive your access information, you will have 30 days to complete this course and its labs.

      This course is designed to provide the database application designer with knowledge to recognize when a stored procedure is the correct solution for an application. The development of stored procedures is taught using the SQL Procedural Language. This course exposes the student to the IBM Optim Development Studio GUI tool to rapidly develop and test SQL stored procedures using the SQL Procedural Language. In addition to using the GUI tool to develop and implement stored procedures, you are taught how to implement these SQL stored procedures using a text editor with the IBM DB2 Command Window and the Command Editor.

      The course engages the student in a series of lectures, each followed by a hands-on exercise to create and call SQL procedures using extensive process flows and cursor processing to return result sets to the invoking client or caller. Each exercise builds on the previous exercise to allow the student to use condition handling, dynamic SQL, user-defined types, large objects, nested procedures, IBM Optim Development Studio debugging facility, SQL procedure tracing using sqlpltrc, security considerations, and tuning and performance techniques.

      The self-paced format gives you the opportunity to complete the course at your convenience, at any location, and at your own pace. The course is available 24 hours a day, but lab system access is allocated on a first-come, first-served basis. You will have a total of 40 hours allocated to complete all of the lab exercises, so when you are not using the system, ensure that you log out to maximize ability to use the lab system. Once you have accessed the course, instructor help is available via the course forum Monday through Friday; questions will be responded to within 24 hours.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      Database application designers and database application programmers supporting DB2 stored procedures with the SQL database language. This prerequisite knowledge can be from prior experience or attendance of the following IBM courses:

      • DB2 Family Fundamentals (CE03)
      • DB2 SQL Workshop (CE12)

      Topics

        DB2 Stored Procedure Introduction

        • Describe a stored procedure
        • Determine when a stored procedure should be used
        • Describe execution flow using stored procedures
        • List DB2 stored procedure design and general tips

        IBM Optim Development Studio or Command Line Tools

        • Describe the IBM Data Studio Developer
        • Identify the capabilities of the IBM Data Studio Developer
        • Create and execute simple stored procedures using IBM Data Studio Developer, Command Editor, and Command Window

        Basic SQL Procedure Structure

        • Describe the structure of an SQL procedure
        • Explain various clauses of the CREATE PROCEDURE statement
        • Describe the structure of the procedure body
        • List the statements that can be coded in the procedure body
        • Alter Procedure
        • Drop Procedure
        • Create Module
        • Replace Module
        • Alter Module
        • Drop Module

        Overview of SQL PL Language Elements

        • Discuss DB2 data types
        • Select the proper data types
        • Work with user-defined data types
        • Work with Row data type
        • Work with Associative arrays
        • Implement data type anchoring

        Using Flow of Control Statements

        • Use Compound statements (ATOMIC and NOT ATOMIC)
        • Use labels in both compound statements and loops
        • Work with Conditional Statements (IF and CASE)
        • Implement Looping (FOR, WHILE,REPEAT, and LOOP)
        • Transfer Control using (GOTO, LEAVE, ITERATE, RETURN, COMMIT, and ROLLBACK)

        Cursors and Result Sets

        • Explain cursor processing
        • Use cursors inside SQL procedures
        • Perform positioned deletes and positioned updates
        • Describe cursor behavior on commit and rollback
        • Use cursor processing to return result sets to an application
        • Explain how cursors affect locking

        Condition Handling

        • Explain the difference in SQLSTATE and SQLCODE
        • Describe a condition handler
        • Explain when to use a condition handler
        • Use condition handlers
        • Force an application exception and set the appropriate message
        • Check the success or failure of an SQL statement
        • Raise an SQL error

        Nested SQL Procedures

        • Call a stored procedure
        • Describe a nested stored procedure
        • Pass parameters between nested SQL procedures
        • Return and receive result sets from within nested SQL procedures
        • Write recursive SQL procedures

        IBM Optim Development Studio SQL Procedure Debug

        • Describe the IBM Optim Development Studio Routine Debugger capabilities
        • Describe platforms supported for debugging
        • Deploy a stored procedure for debugging
        • Debug a stored procedure with the assistance of the Routine Debugger

        SQL Procedure Tracing using sqlpltrc

        • Describe the purpose of sqlpltrc
        • Define the process for installing sqlpltrc
        • Define the procedures for trace management
        • Define the procedures for trace reporting
        • Define the procedures for trace entries
        • Show the steps to create a sample trace
        • Discuss steps used to identify and resolve problems

        Security Considerations in SQL Procedures

        • Describe the relationship between a DB2 SQL procedure and a package
        • Rebind a package associated with an SQL PL procedure
        • Define who a package owner is and describe the package owner's responsibilities
        • Define who a routine definer is and describe the routine definer's responsibilities
        • Describe the routine invoker's behavior and authorization requirements

        SQL Stored Procedures Performance

        • Analyze and improve SQL access plans
        • Avoid or simplify multiple statements
        • Exploit set-at-a-time semantics of SQL
        • Improve cursor performance
  • DB2 10.1 for Linux, UNIX, and WIN Quickstart for EXP. Relational DBAs - SPVCOpen or Close

    Description

      This course replaces course number DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL483).

      This course teaches you to perform, basic and advanced, database administrative tasks using DB2 10.1 for Linux, UNIX, and Windows. These tasks include creating and populating databases and implementing a logical design to support recovery requirements. The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools. Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands and DB2 traces. You will learn how to implement automatic archival for database logs and how to plan a redirected database restore to relocate either selected table spaces or an entire database. The selection of indexes to improve application performance and the use of SQL statements to track database performance and health will be covered. This course provides a quick start to DB2 database administration skills for experienced relational Database Administrators (DBA).

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Perform basic database administration tasks on a relational database system
      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL) and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX Basics and Administration
        • Linux Basics and Administration
        • Windows Systems Administration
        • Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Fundamentals

      Topics

        • Overview of DB2 for Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI tools
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Locks and concurrency
        • Problem determination
        • Security
        • DB2 Recovery Log Management
        • DB2 database and table space relocation
        • Using Explain tools
        • Using Indexes for Performance
        • Advanced Monitoring
  • DB2 9.7 for LUW Quickstart for Experienced Relational DBAs (SPVC)Open or Close

    Description

      Contains: PDF course guide, as well as a lab environment where students can work through demonstrations and exercises at their own pace.

      This course teaches you to perform, basic and advanced, database administrative tasks using DB2 9 for Linux, UNIX, and Windows. These tasks include creating and populating databases and implementing a logical design to support recovery requirements. The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools. Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands and DB2 traces. Students will learn how to implement automatic archival for database logs and how to plan a redirected database restore to relocate either selected table spaces or an entire database. The selection of indexes to improve application performance and the use of SQL statements to track database performance and health will be covered. This course provides a quick start to DB2 database administration skills for experienced relational Database Administrators (DBA).

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Perform basic database administration tasks on a relational database system
      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL) and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX 5L Basics
        • Linux Basics and Administration
        • Windows Systems Administration
        • Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Fundamentals

      Topics

        • Overview of DB2 for Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI usage
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Locks and concurrency
        • Problem determination
        • Security
        • DB2 Recovery Log Management
        • DB2 database and table space relocation
        • Using Explain tools
        • Using Indexes for Performance
        • Advanced Monitoring
  • DB2 10.5 for LUW Advanced Database Administration with DB2 BLU Acceleration - SPVCOpen or Close

    Description

      This course is designed to teach you how to:

      • Fully use the advanced technical functions and features of DB2 LUW 10.1 and 10.5.
      • Implement DB2 BLU Acceleration, column-organized table support, for a new or existing DB2 database.
      • Describe how the column dictionaries used for DB2 BLU Acceleration are built and utilized to provide extreme compression for column-organized tables.
      • Explain the default workload management used for DB2 BLU Acceleration processing and how you can tailor the WLM objects to efficiently use system resources.
      • Monitor a DB2 database or application that uses column-organized tables using SQL monitor functions.
      • Create optimization profiles that allow applications to control specific operations included in the access plans selected by the DB2 Optimizer, like which index is used to access a table or which join method to utilize for joining tables.
      • Perform advanced monitoring using the DB2 administrative views and routines in SQL queries.
      • Configure and manage the implementation of DB2 instance or database level auditing, including using the db2audit command and creation of audit policies which can be assigned to specific tables, users or database roles to perform selective collection of audit records.
      • Explore DB2's management of disk space usage in Database Managed Storage (DMS) table spaces, including the activities of the rebalancer. Use SQL queries and utilities to check the high water mark on table spaces and to monitor the rebalance operation.
      • Move data from one table to another or from one database to another using utilities like db2move.
      • Utilize the ADMIN_MOVE_TABLE procedure to implement table changes with a minimal impact to data availability for applications.
      • Implement automatic storage management for table spaces and storage groups or enable automatic resize options for DMS managed table spaces to reduce administration requirements and complexity.
      • Exploit and monitor the REORG utility processing for offline and online table, and index reorganization. This includes planning for the disk space and database log space necessary for reorganization.
      • Utilize the REORG Utility to implement row compression for large tables, to reduce disk utilization and improve I/O performance for a DB2 database and understand the automatic creation of compression dictionaries.
      • For Multidimensional Clustering (MDC) tables, determine how to select the dimension columns and table space extent size for efficient implementation of MDC tables. Compare the block indexes used with MDC tables with rows based indexes. Select the MDC rollout option that best matches application needs and achieves the best performance results.
      • Plan and implement range based table partitioning for large DB2 tables. Utilize the ALTER TABLE ATTACH and DETACH options to support roll-in and roll-out operations for range-partitioned tables. Compare the advantages of selecting or combining range partitioning with the hash-based partitioning used in DB2 partitioned databases or the multiple dimensions provided by MDC tables.

      You get practical experience in the planning and utilization of a wide variety DB2 LUW utilities and functions by performing a series of lab exercises using DB2 Advanced Enterprise Edition 10.5 installed on a Linux platform. The exercises build skills that can be applied to DB2 database servers on any Linux, UNIX or Windows environment.

      The course materials cover DB2 10.5 for Linux, UNIX, and Windows.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should:

      • Complete:
        • DB2 10 for LUW: Basic Administration for Linux and Windows (CL2X3) or
        • DB2 10 for LUW: Basic Administration for AIX (CL213) or
        • DB2 10.1 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL484) or
      • Have equivalent experience.

      Topics

        • Advanced Monitoring
        • Advanced Table Space Management
        • DB2 10.5 BLU Acceleration Concepts
        • DB2 10.5 BLU Acceleration Implementation and Use
        • Using Optimizer Profiles to control Access Plans
        • Table Partitioning
        • Advanced Data Movement
        • Advanced Table Reorganization
        • Multiple Dimension Clustering
        • DB2 Database Auditing
  • Implementation and Use of DB2 BLU Acceleration - SPVCOpen or Close

    Description

      This course is designed to teach you how to:

      • Implement DB2 BLU Acceleration, column-organized table support, for a new or existing DB2 database.
      • Describe how the column dictionaries used for DB2 BLU Acceleration are built and utilized to provide extreme compression for column-organized tables.
      • Explain the default workload management used for DB2 BLU Acceleration processing and how you can tailor the WLM objects to efficiently use system resources.
      • Monitor a DB2 database or application that uses column-organized tables using SQL monitor functions.
      • You get practical experience in the planning and utilization of a the DB2 BLU Acceleration functions, using DB2 LUW commands, utilities and SQL statements performing a lab exercise using DB2 Advanced Enterprise Edition 10.5 installed on a Linux platform

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should have completed:

      • DB2 10 for LUW: Basic Administration for Linux and Windows (CL2X3) or
      • DB2 10 for LUW: Basic Administration for AIX (CL213) or
      • DB2 10.1 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL484)

      Or have equivalent experience

      Topics

        • DB2 10.5 BLU Acceleration Concepts
        • DB2 10.5 BLU Acceleration Implementation and Use
  • DB2 LUW Performance Tuning - Monitoring for Single & Multiple Partition DBs - SPVCOpen or Close

    Description

      Learn how to tune for optimum performance the IBM DB2 10 for Linux, UNIX, and Windows relational database management system and associated applications written for this environment. Learn about DB2 10 for Linux, UNIX, and Windows in support of single partition and multiple partition database environments. Explore performance issues affecting the design of the database and applications using the database, the major database performance parameters, and the different tools that assist in performance monitoring and tuning. Use tools in class that are common across the Linux, UNIX, and Windows operating systems environments. During exercises running on DB2 10.1, develop your ability to use monitoring tools, Explain tools and DB2 utilities like RUNSTATS, REORG and db2batch to tune a database running on your local LINUX workstation using single and multiple partition DB2 databases.

      The course materials cover DB2 10.1 for Linux, UNIX, and Windows.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should complete:

      • DB2 9 for LUW Multiple Partition DBA Workshop (CL240) or
      • DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (CL250)

      Topics

        • Database Monitoring
        • Database I/O Management
        • Table space and Table Design for Performance
        • DB2 Memory Management
        • Automated Memory Management
        • Application Performance Considerations
        • Using Explain Tools
        • The DB2 Optimizer
        • Using Indexes for Performance
        • Complex SQL Performance
        • Tools and Utilities for Performance
        • Event Monitor
        • Partitioned Databases Performance Tuning Considerations
        • Row Relocation Strategies and FCM Performance Considerations
  • DB2 LUW Performance Tuning and Monitoring for Single and Multiple Partition - SPVCOpen or Close

    Description

      Learn how to tune for optimum performance the IBM DB2 9 for Linux, UNIX, and Windows relational database management system and associated applications written for this environment. Learn about DB2 9 for Linux, UNIX, and Windows in support of single partition and multiple partition (DPF) database environments. Explore performance issues affecting the design of the database and applications using the database, the major database performance parameters, and the different tools that assist in performance monitoring and tuning.

      Use tools that are common across the Linux, UNIX, and Windows environments. During labs running on DB2 9.7, develop your ability to use monitoring tools, Explain tools and DB2 utilities like RUNSTATS, REORG and db2batch to tune a database running on your local LINUX workstation.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should have completed:

      • DB2 9 for LUW Multiple Partition DBA Workshop (CL240) or
      • DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (CL250)

      or have equivalent experience

      Topics

        Database monitoring

        • describe the basic principles in monitoring a DB2 database
        • list the tools for monitoring database and application activity
        • use GET SNAPSHOT commands to produce reports for analysis of database performance
        • utilize the administrative routines and views provided by to DB2 to simplify application access to database performance statistics
        • use the db2pd to perform performance analysis or problem determination for a DB2 database
        • utilize the enhanced relational monitoring interfaces with SQL in reporting and monitoring of the database system, data objects, and the package cache to help you quickly identify issues that might be causing problems
        • configure the DB2 Database configuration options that control the collection of request, activity and object level metrics on the entire database
        • compare the enhanced monitoring features with the snapshot monitoring facilities provided by previous DB2 releases

        Database Input/Output (I/O) management

        • describe processing for Reading Database Pages into Buffer Pools
        • describe processing for Writing Database Pages from Buffer Pools
        • monitor Database Read and Write Activity using GET SNAPSHOT commands or Administrative Routines and Views
        • monitor Database Logging Activity and select appropriate values for SOFTMAX and MINCOMMIT
        • implement and monitor use of a block based buffer pool to improve table scan performance
        • describe the alternate page cleaning processing associated with the DB2 Registry variable DB2_USE_ALTERNATE_PAGE_CLEANING
        • describe how scan sharing can reduce the I/O workload for accessing large tables

        Tablespace and Table design for performance

        • select appropriate values for table space page size and extent size to support application performance requirements
        • describe the calculation of prefetch size when PREFETCHSIZE is set to AUTOMATIC
        • list the advantages of selecting DMS or SMS table space management as well as using Automatic Storage managed table spaces
        • set file system caching options for table spaces to optimize table space performance
        • describe the various row insertion algorithms for tables that are based on using the APPEND option or defining a clustering index
        • plan and implement Row Compression to reduce disk and memory requirements and improve application performance
        • describe how DB2's Index compression option can reduce disk storage requirements for indexes
        • explain how DB2 can compress temporary data to improve application performance
        • utilize the DB2 provided tools and functions to estimate compression results for Indexes and XML data

        Database memory management

        • describe memory heap usage for instance memory, database shared memory, and agent private memory
        • explain the management of database shared memory based on setting the configuration option DATABASE_MEMORY to AUTOMATIC, COMPUTED or a specific number of pages
        • select the mode for managing data sort memory using SHEAPTHRES, SORTHEAP, and SHEAPTHRES_SHR
        • monitor DB2 memory usage using the db2mtrk command or the graphical application Memory Visualizer.
        • utilize the db2pd for monitoring current database memory usage
        • use AUTOCONFIGURE to set database configuration defaults when a new database is created or after a database workload changes

        Automatic memory management

        • describe how Self-Tuning Memory Manager (STMM) can be used to automatically manage database shared memory heaps
        • explain the differences in STMM processing based on the setting of DATABASE_MEMORY
        • plan and configure a database for self-tuning memory
        • activate or deactivate STMM for selected memory heaps
        • describe the management of sortheap memory based on the configuration of sheapthres and sheapthres_shr
        • explain how DB2 can automatically increase or decrease database memory for multiple DB2 databases running on the same server

        Using explain tools

        • describe the advantages of using Visual Explain
        • describe the advantages of using db2exfmt
        • create special tables used by Visual Explain and db2exfmt
        • identify how to set the Explain snapshot and Explain mode registers to capture the information of interest
        • differentiate between the different methods of viewing Explain information

        The optimizer

        • describe the stages of the SQL compiler
        • choose the appropriate optimization class
        • describe the catalog statistics and database configuration options impact on access plan selection
        • implement a statistical view to provide better cardinality estimates for complex queries
        • enable the statement concentrator using the stmt_conc database manager configuration parameter to reduce SQL compilation overhead for dynamic SQL statements
        • utilize the db2look utility to extract catalog statistics from existing tables to mimic an existing database environment
        • create an optimizer profile to influence the access plan selected by the DB2 optimizer

        Using indexes for performance

        • describe the Indexing options that can be used to improve performance including: Index Only Access, Clustered Index, Reverse Scans, Include Columns, and Index Freespace
        • use the MON_GET_INDEX table function to monitor index usage
        • describe the Block Indexing capability for MDC tables
        • explain how multiple indexes can be combined using Index ORing and Dynamic Bitmap Index ANDing
        • use the Design Advisor to predict performance gains from adding new indexes
  • DB2 for LUW Performance Tuning and Monitoring Workshop - SPVCOpen or Close

    Description

      Learn how to tune for optimum performance the IBM DB2 10 for Linux, UNIX, and Windows relational database management system and associated applications written for this environment. Learn about DB2 10 for Linux, UNIX, and Windows in a single partition database environment. Explore performance issues affecting the design of the database and applications using the database, the major database performance parameters, and the different tools that assist in performance monitoring and tuning.

      Use tools in class that are common across the Linux, UNIX, and Windows environments. During labs running on DB2 10.1, develop your ability to use monitoring tools, Explain tools and DB2 utilities like RUNSTATS, REORG and db2batch to tune a database running on your local LINUX workstation.

      The course materials cover DB2 10.1 for Linux, UNIX, and Windows.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should complete:

      • DB2 10 for LUW: Basic Administration for Linux and Windows (CL2X3) or
      • DB2 10.1 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL484)

      Topics

        • Database Monitoring
        • Database I/O Management
        • Table space and Table Design for Performance
        • DB2 Memory Management
        • Automated Memory Management
        • Application Performance Considerations
        • Using Explain Tools
        • The DB2 Optimizer
        • Using Indexes for Performance
        • Complex SQL Performance
        • Tools and Utilities for Performance
        • Event Monitor
  • DB2 for Linux, UNIX and Windows Performance Tuning and Monitoring Workshop - SPVCOpen or Close

    Description

      This course enables you to learn how to tune for optimum performance the IBM DB2 9 for Linux, UNIX, and Windows relational database management system and associated applications written for this environment. You will also learn about DB2 9 for Linux, UNIX, and Windows in a single partition database environment. Explore performance issues affecting the design of the database and applications using the database, the major database performance parameters, and the different tools that assist in performance monitoring and tuning.

      In this course, you will also use tools that are common across the Linux, UNIX, and Windows environments. During labs running on DB2 9.7, you will enhance your ability to use monitoring tools, explain tools and DB2 utilities like RUNSTATS, REORG and db2batch to tune a database running on your local LINUX workstation.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should complete:

      • DB2 9 Database Administration Workshop for Linux, UNIX, and Windows (CL2X2) or
      • DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL482)
      • or have equivalent experience

      Topics

        Database Monitoring

        • Describe the basic principles in monitoring a DB2 database
        • List the tools for monitoring database and application activity
        • Use GET SNAPSHOT commands to produce reports for analysis of database performance
        • Utilize the administrative routines and views provided by to DB2 to simplify application access to database performance statistics
        • Use the db2pd to perform performance analysis or problem determination for a DB2 database
        • Utilize the enhanced table functions with SQL in reporting and monitoring of the database system, data objects, and the package cache to help you quickly identify issues that might be causing problems
        • Configure the DB2 Database configuration options that control the collection of request, activity and object level metrics on the entire database
        • Compare the enhanced table functions with the snapshot monitoring facilities provided by previous DB2 releases

        Database Input/Output (I/O) Management

        • Describe processing for reading database pages into buffer pools
        • Describe processing for writing database pages from buffer pools
        • Monitor database read and write activity using GET SNAPSHOT commands or Administrative Routines and Views
        • Monitor database logging activity and select appropriate values for SOFTMAX and MINCOMMIT
        • Describe how scan sharing can reduce the I/O workload for accessing large tables
        • Explain the alternate page cleaning processing associated with the DB2_USE_ALTERNATE_PAGE_CLEANING DB2 Registry variable

        Tablespace and Table Design for Performance

        • Select appropriate values for table space page size and extent size to support application performance requirements
        • Describe the calculation of prefetch size when PREFETCHSIZE is set to AUTOMATIC
        • List the advantages of selecting DMS or SMS table space management as well as using Automatic Storage-managed table spaces
        • Set file system caching options for table spaces to optimize table space performance
        • Describe the various row insertion algorithms for tables that are based on using the APPEND option or defining a clustering index
        • Plan and implement Row Compression to reduce disk and memory requirements and improve application performance
        • Describe how DB2's Index compression option can reduce disk storage requirements for indexes
        • Explain how DB2 can compress temporary data to improve application performance
        • Utilize the DB2 provided tools and functions to estimate compression results for Indexes and data

        DB2 memory management

        • Describe memory heap usage for instance memory, database shared memory and application memory
        • Explain the management of database shared memory based on setting the configuration option DATABASE_MEMORY to AUTOMATIC, COMPUTED or a specific number of pages
        • Select the mode for managing data sort memory using SHEAPTHRES, SORTHEAP, and SHEAPTHRES_SHR
        • Monitor DB2 memory usage using the db2mtrk commands and SQL statements
        • Utilize the db2pd command for monitoring current database memory usage

        Automated Memory Management

        • Describe how STMM can be used to automatically manage database shared memory heaps
        • Explain the differences in STMM processing based on the setting of DATABASE_MEMORY
        • Plan and configure a database for self tuning memory
        • Activate or deactivate STMM for selected memory heaps
        • Describe the management of sortheap memory based on the configuration of sheapthres and sheapthres_shr
        • Explain how DB2 can automatically increase or decrease database memory for multiple DB2 databases running on the same server

        Application Performance Considerations

        • List examples of application coding techniques that can effect performance
        • Describe the performance advantages of using stored procedures
        • Design applications to maximize record blocking, minimize sort operations and minimize lock contention
        • Monitor application performance and lock waits using GET SNAPSHOT commands and SQL queries
        • Set the DB2 registry variables DB2_EVALUNCOMMITTED, DB2_SKIPINSERTED and DB2_SKIPDELETED to reduce lock contention based on application requirements
        • Utilize the currently committed form of locking to reduce lock contention between read-only and update applications

        Using Explain Tools

        • Describe the advantages of using Visual Explain
        • Describe the advantages of using db2exfmt
        • Create special tables used by Visual Explain and db2exfmt
        • Identify how to set the Explain snapshot and Explain mode registers to capture the information of interest
        • Differentiate between the different methods of viewing Explain information

        The DB2 Optimizer

        • Describe the stages of the SQL compiler
        • Choose the appropriate optimization class
        • Describe the catalog statistics and database configuration options impact on access plan selection
        • Implement a statistical view to provide better cardinality estimates for complex queries
        • Enable the statement concentrator using the STMT_CONC database manager configuration parameter to reduce SQL compilation overhead for dynamic SQL statements
        • Utilize the db2look utility to extract catalog statistics from existing tables to mimic an existing database environment
        • Create an optimizer profile

        Using Indexes for Performance

        • Describe the Indexing options that can be used to improve performance: Index Only Access, Clustered Index, Reverse Scans, Include Columns, and Index Freespace
        • Describe the Block Indexing capability for MDC tables
        • Monitor index usage using the MON_GET_INDEX function and db2pd commands
        • Explain how multiple indexes can be combined using Index ORing and Dynamic Bitmap Index ANDing
        • Use the Design Advisor to predict performance gains from adding new indexes

        Complex SQL Performance

        • Review Explain reports for costly sort operations
        • Describe the differences between Nested Loop, Merge Scan and Hash Joins
        • Plan the implementation of Refresh Immediate or Refresh Deferred Materialized Query Tables to improve query performance
        • Utilize the Design Advisor to analyze SQL statements and recommend new MQTs
        • Describe the features of range-partitioned tables to support large DB2 tables using multiple table spaces, including the roll-in and roll-out of data ranges
        • Explain the difference between partitioned and non-partitioned indexes for a range-partitioned table
        • Implement partitioned indexes to improve performance when you roll data out or roll data into a range-partitioned table
        • Use the DB2 Explain tools to determine if partition elimination is being used to improve access performance to large range-partitioned tables

        Tools and Utilities for Performance

        • Use the RUNSTATS utility to collect table and index statistics to enable the DB2 Optimizer to generate efficient access strategies
        • Select appropriate RUNSTATS options to collect Distribution Statistics or Column Group Statistics to improve cardinality estimates during SQL compilation
        • Use the table and indexes statistics to plan for table and index reorganization using the REORG utility
        • Set the policies and options for automation of catalog statistics collection
        • Monitor the activity associated with implementing real-time statistics collection
        • Use the db2batch utility to run SQL workloads and collect performance statistics that can be used to benchmark database and application changes

        Event Monitoring

        • Create Event Monitors to collect performance statistics at the database, application or SQL statement level
        • Implement simple Workload Management definitions to utilize the Workload Manager-based event monitoring including activities, statistics and threshold violations
        • Evaluate Event Monitor data using the Event Analyzer tool, the db2evmon text-based tool the db2evmonfmt application or using SQL queries
        • Define a LOCKING Event Monitor to capture deadlocks, lock timeout or lock waits
        • Configure a DB2 database to control information captured for deadlocks, lock timeouts or extended lock waits
        • Implement Event Monitors for units of work or lock-related events that store information in unformatted Event Monitor tables
        • Capture SQL section information using an ACTIVITIES Event Monitor and use the data to generate Explain reports

        Agenda

        • Unit 1: Database monitoring
        • Unit 2: Database I/O Management
        • Unit 3: Table Space and Table Design for Performance
        • Unit 4: DB2 Memory Management
        • Unit 5: Automatic Memory Management
        • Unit 6: DB2 Application Performance Considerations
        • Unit 7: Using Explain Tools
        • Unit 8: The DB2 Optimizer
        • Unit 9: Using Indexes for Performance
        • Unit 10: Complex SQL Performance
        • Unit 11: Tools and Utilities for Performance
        • Unit 12: Event Monitoring
  • Fast Path to DB2 10.1 for Experienced Relational DBAs - SPVCOpen or Close

    Description

      This course is designed to teach you how to perform database administration tasks using DB2 10.1. These tasks include creating DB2 instances, creating and populating databases, and using logical design to support concurrency and recovery requirements. Features, such as range partitioning, data row compression, and temporal tables will be introduced.

      This course provides a fast path to DB2 database administration skills for experienced relational DBAs.

      If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

      http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      These skills can be developed by taking:

      • OS Training:
        • AIX 5L Basics
        • Linux Basics and Administration
        • Windows Systems Administration
      • Or by having equivalent HP-UX or Solaris administration experience
      • DB2 SQL Workshop
      • DB2 Family Fundamentals

      Topics

        Contents

        • Overview of DB2 on Linux, UNIX, and Windows
        • Command Line Processor (CLP) and GUI Usage
        • The DB2 Environment
        • Creating Databases and Data Placement
        • Creating Database Objects
        • Moving Data
        • Backup and Recovery
        • Locking and Concurrency
        • Database Maintenance, Monitoring and Problem Determination
        • Security

        Agenda

        The first five units must be taught in the order specified:

        • Overview of DB2
        • Getting Started with DB2 GUIs
        • Creating Databases and Data Placement
        • Create Objects
        • Moving Data
        • Backup and Recovery
        • Database Maintenance, Monitoring and Problem Determination
        • Concurrency and Locking
        • Security
  • DB2 9 for Linux, UNIX and Windows Application Programming Advanced - SPVCOpen or Close

    Description

      This course teaches how to take advantage of advanced programming techniques to access DB2 databases in the workstation environment. These techniques can be utilized in addition to standard embedded SQL calls that are addressed in the prerequisite offering DB2 UDB Programming Fundamentals. These advanced techniques include:

      • Stored Procedures
      • User-Defined Functions
      • Application Programming Interfaces (APIs)

      Performance concepts are also addressed.

    • If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.
    • http://www.ibm.com/training/terms

    Prerequisits

      The following prerequisites are suggested:

      • The ability to code embedded, static SQL applications
      • Familiarity with relational database concepts
      • Use of basic C language syntax components

      These skills can be developed by taking:

      • Introduction to C Programming
      • DB2 UDB Programming Fundamentals

      Equivalent experience may be substituted for these courses. Application programming experience with other relational database systems, such as DB2 MVS, DB2/VSE and VM, and DB2/400, would be appropriate for meeting the DB2 prerequisites.

      Topics

        • Introduction and Static Review
        • Stored Procedures
        • Introduction to Call Level Interface (CLI)
        • Using Object-Relational Capabilities
        • Application Programming Interfaces
  • DB2 9 for Linux, UNIX and Windows Application Programming Fundamentals - SPVCOpen or Close

    Description

    • This course enables you to acquire the skills necessary to produce application programs that manipulate DB2 databases. Emphasis is on embedding Structured Query Language (SQL) statements and preparing programs for execution.
    • If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.
    • http://www.ibm.com/training/terms

    Prerequisits

      Prior to attending this course, you should already have experience with one of the supported programming languages:

      • COBOL or PL/I for z/OS, or
      • C language for Linux, UNIX, and Windows.

      You are also expected to already be able to construct and use SQL statements.

      Topics

        • Incorporate static SQL statements in an application program
        • Prepare the program for execution
        • Validate execution results are correct
        • Produce code to support multiple rows being returned from the database manager using cursors
        • Identify considerations regarding units of work concurrency, and restart of programs
        • Identify differences between static and dynamic SQL
        • Provide test data for applications
        • Discuss program and DB2 options relative to performance of static SQL
  • DB2 SQL for Experienced Users - SPVCOpen or Close

    Description

    • This course teaches you how to make use of advanced SQL techniques to access DB2 databases in different environments. This course is appropriate for customers working in all DB2 environments, specifically for z/OS, Linux, UNIX, and Windows.
    • If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should have experience with coding and executing basic SQL statements.

      These skills can be developed by attending SQL Workshop (CE120), or having equivalent experience.

      Topics

        Introduction

        • Identify the purpose of the clauses in the SELECT statement
        • Describe the key differences among the IBM DB2 platforms
        • Describe and use some of the OLAP features of DB2, such as GROUPING functions like CUBE and ROLLUP, and the RANK, DENSE_RANK and ROW_NUMBER functions

        Create Objects

        • Code statements to: Create tables and views, Alter tables, Create indexes, Implement referential integrity (RI), and Define triggers and check constraints
        • Identify impacts and advantages of referential integrity, including impacts of delete rules
        • Identify considerations when using triggers and check constraints
        • Define and make use of INSTEAD OF triggers

        Join

        • Retrieve data from more than one table via inner and outer joins
        • Use outer joins (LEFT, RIGHT, FULL)
        • Use ANTI JOINS
        • Join a table to itself
        • Use UNION and UNION ALL
        • Use EXCEPT and INTERCEPT

        CASE, CAST, Summary Tables, and Materialized Query Tables

        • Identify when CASE expressions can be used
        • Code CASE expressions in SELECT list and in the WHERE clause
        • Identify when CAST specifications can be used
        • Identify the advantages of using Summary (Materialized Query) Tables and Temporary tables
        • Identify the advantages of using Materialized Query Tables (MQTs)
        • Identify when and how to use Temporary tables

        Using Subqueries

        • Code subqueries using the ALL, ANY/SOME, and EXISTS keywords
        • Code correlated subqueries
        • Choose the proper type of subquery to use in each case

        Scalar Functions

        • Extend your knowledge of scalar functions which: Manipulate arithmetic data, Manipulate date values, and Manipulate character data. Examples of scalar functions that are addressed in this course:
          • SUBSTR
          • POSSTR
          • COALESCE/VALUE
          • DECIMAL
          • ROUND
          • DIGITS
          • CHAR
          • DATE/TIME

        Table Expressions and Recursive SQL

        • Identify reasons for using table expressions and recursive SQL
        • Use nested and common table expressions
        • Identify the difference between views and table expressions
        • Code recursive SQL
        • Control the depth of recursion when coding recursive SQL

        UDTs/UDFs and Performance

        • Describe the concepts behind User-Defined Types, User-Defined Functions and Stored Procedures
        • Predict when queries will use indexes to get better performance
        • Identify concepts of predicate processing
        • State introductory concepts about index structure
        • State general best practices advice
  • DB2 10.1 for LUW New Features and Database Migration ConsiderationsOpen or Close

    Description

    • Get the technical information about the new functions and features available in DB2 10.1 for Linux, UNIX, and Windows and plan the migration for DB2 Version 9.5, 9.7 or 9.8 database systems to DB2 LUW 10.1.
    • DB2 Version 10.1 for Linux, UNIX, and Windows continues to deliver database efficiency, simplicity, and reliability. Important new features and enhancements address the needs of your business. Whether your needs are improving business critical reliability and performance, simplifying consolidation and security, increasing business insight, reducing costs, or providing a resilient system for the valuable information assets of your company, DB2 Version 10.1 delivers.
    • The course will describe the adaptive compression feature of DB2 LUW 10.1 that improves table compression through an advanced row compression technique that uses two levels of compression dictionaries (table-level and page-level) to improve compression ratios, particularly as data changes.
    • DB2 LUW 10.1 support for Multi-temperature storage allows administrators to define storage groups that can be used to assign data based on performance characteristics and migrate data to a storage group with lower cost devices or lower performance when the demand for that data declines.
    • The lectures will describe the DB2 LUW 10.1 performance related features including improved query optimizer techniques and functionality including star schema query optimization, improved data and index prefetching, and improved use of statistical views.
    • We will discuss how to implement system period and application period temporal tables associated with Time Travel Query to assign time-based state information to your data, so you can query the past state of your data.  
    • DB2 LUW 10.1 introduces row and column access control (RCAC), as an additional layer of data security. Row and column access control is sometimes referred to as fine-grained access control or FGAC. We will discuss using RCAC to control access to a table at the row level, column level, or both and how RCAC can be used to complement the table privileges model.
    • The database high availability feature HADR is significantly enhanced with DB2 LUW 10.1. We will discuss how with DB2 10.1 you can define up to three standby databases. The lecture will also explain how to implement one standby database with the new time-delayed replay feature, which helps to recover from application errors that cause data loss on the primary.
    • We will cover various migration planning and installation considerations to help move existing DB2 LUW databases to DB2 LUW 10.1 to take advantage of the new features and functions.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • You should have experience supporting DB2 Linux, UNIX and Windows Version 9.5, 9.7 or 9.8 databases.

      Topics

        • Unit 1 - DB2 10.1 Storage Management Features
        • Unit 2 - DB2 10.1 Database Availability Features and Functions
        • Unit 3 - DB2 10.1 Database Performance Features
        • Unit 4 - DB2 10.1 Adaptive Compression
        • Unit 5 - Temporal Tables and Time Travel Query with DB2 10.1
        • Unit 6 - DB2 10.1 Database Monitoring Enhancements
        • Unit 7 - Using Row and Column Access Controls (RCAC) with DB2 10.1
        • Unit 8 - DB2 10.1 Server Upgrade Essentials
  • DB2 10.5 for LUW New Features and Database Upgrade ConsiderationsOpen or Close

    Description

    • This course is designed to describe the major technical features and enhancements provided by the DB2 for Linux, UNIX and Windows database 10.5 software. DB2 10.5 has two primary areas of focus. One key feature of DB2 10.5 is the new support for column-organized tables to provide accelerated analytic processing called BLU Acceleration. Another important focus for DB2 10.5 is a set of enhancements for the DB2 pureScale feature, including online topology changes and the support for High Availability Disaster Recovery (HADR) with a DB2 pureScale cluster.
    • We will cover various migration planning and installation considerations to help move existing DB2 LUW databases to DB2 LUW 10.1 to take advantage of the new features and functions.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Experience supporting DB2 Linux, UNIX and Windows Version 9.7, 9.8 or 10.1 databases

      Topics

        • Unit 1 - DB2 10.5 BLU Acceleration Concepts
        • Unit 2 - DB2 10.5 BLU Acceleration Implementation and Use
        • Unit 3 - DB2 10.5 enhancements for the DB2 pureScale feature
        • Unit 4 - Planning for DB2 10.5 implementation, and Additional general usage features for DB2 database servers
  • Implementation and Use of DB2 BLU AccelerationOpen or Close

    Description

      This course is designed to teach you how to:

      • Implement DB2 BLU Acceleration, column-organized table support, for a new or existing DB2 database
      • Describe how the column dictionaries used for DB2 BLU Acceleration are built and utilized to provide extreme compression for column-organized tables
      • Explain the default workload management used for DB2 BLU Acceleration processing and how you can tailor the WLM objects to efficiently use system resources
      • Monitor a DB2 database or application that uses column-organized tables using SQL monitor functions

      You get practical experience in the planning and utilization of the DB2 BLU Acceleration functions, using DB2 LUW commands, utilities and SQL statements, and by performing a lab exercise using DB2 Advanced Enterprise Edition 10.5 installed on a Linux platform.

    • http://www.ibm.com/training/terms

    Prerequisits

      You should have completed:

      • DB2 10 for LUW: Basic Administration for Linux and Windows (CL2X3) or
      • DB2 10 for LUW: Basic Administration for AIX (CL213) or
      • DB2 10.1 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL484)

      Topics

        • DB2 10.5 BLU Acceleration Concepts
        • DB2 10.5 BLU Acceleration Implementation and Use
  • DB2 10.5 for LUW Advanced Database Administration with DB2 BLU AccelerationOpen or Close

    Description

      This course is designed to teach you how to:

      • Fully use the advanced technical functions and features of DB2 LUW 10.1 and 10.5.
      • Implement DB2 BLU Acceleration, column-organized table support, for a new or existing DB2 database.
      • Describe how the column dictionaries used for DB2 BLU Acceleration are built and utilized to provide extreme compression for column-organized tables.
      • Explain the default workload management used for DB2 BLU Acceleration processing and how you can tailor the WLM objects to efficiently use system resources.
      • Monitor a DB2 database or application that uses column-organized tables using SQL monitor functions.
      • Create optimization profiles that allow applications to control specific operations included in the access plans selected by the DB2 Optimizer, like which index is used to access a table or which join method to utilize for joining tables.
      • Perform advanced monitoring using the DB2 administrative views and routines in SQL queries.
      • Configure and manage the implementation of DB2 instance or database level auditing, including using the db2audit command and creation of audit policies which can be assigned to specific tables, users or database roles to perform selective collection of audit records.
      • Explore DB2's management of disk space usage in Database Managed Storage (DMS) table spaces, including the activities of the rebalancer. Use SQL queries and utilities to check the high water mark on table spaces and to monitor the rebalance operation.
      • Move data from one table to another or from one database to another using utilities like db2move.
      • Utilize the ADMIN_MOVE_TABLE procedure to implement table changes with a minimal impact to data availability for applications.
      • Implement automatic storage management for table spaces and storage groups or enable automatic resize options for DMS managed table spaces to reduce administration requirements and complexity.
      • Exploit and monitor the REORG utility processing for offline and online table, and index reorganization This includes planning for the disk space and database log space necessary for reorganization.
      • Utilize the REORG Utility to implement row compression for large tables, to reduce disk utilization and improve I/O performance for a DB2 database and understand the automatic creation of compression dictionaries.
      • For Multidimensional Clustering (MDC) tables, determine how to select the dimension columns and table space extent size for efficient implementation of MDC tables. Compare the block indexes used with MDC tables with rows based indexes. Select the MDC rollout option that best matches application needs and achieves the best performance results.
      • Plan and implement range based table partitioning for large DB2 tables. Utilize the ALTER TABLE ATTACH and DETACH options to support roll-in and roll-out operations for range-partitioned tables. Compare the advantages of selecting or combining range partitioning with the hash-based partitioning used in DB2 partitioned databases or the multiple dimensions provided by MDC tables.
      • You get practical experience in the planning and utilization of a wide variety DB2 LUW utilities and functions by performing a series of lab exercises using DB2 Advanced Enterprise Edition 10.5 installed on a Linux platform. The exercises build skills that can be applied to DB2 database servers on any Linux, UNIX or Windows environment.  

      The course materials cover DB2 10.5 for Linux, UNIX, and Windows.

    • http://www.ibm.com/training/terms

    Prerequisits

      You should have completed:

      • DB2 10 for LUW: Basic Administration for Linux and Windows (CL2X3) or
      • DB2 10 for LUW: Basic Administration for AIX (CL213) or
      • DB2 10.1 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL484)
      • Or have equivalent experience

      Topics

        • Advanced Monitoring
        • Advanced Table Space Management
        • DB2 10.5 BLU Acceleration Concepts
        • DB2 10.5 BLU Acceleration Implementation and Use
        • Using Optimizer Profiles to control Access Plans
        • Table Partitioning
        • Advanced Data Movement
        • Advanced Table Reorganization
        • Multiple Dimension Clustering
        • DB2 Database Auditing
  • DB2 10.1 for LUW Quickstart for Experienced Relational DBAsOpen or Close

    Description

    • This course teaches you to perform, basic and advanced, database administrative tasks using DB2 10.1 for Linux, UNIX, and Windows.
    • These tasks include creating and populating databases and implementing a logical design to support recovery requirements.
    • The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools.
    • Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands and DB2 traces.
    • You will learn how to implement automatic archival for database logs and how to plan a redirected database restore to relocate either selected table spaces or an entire database.
    • The selection of indexes to improve application performance and the use of SQL statements to track database performance and health will be covered.
    • This course provides a quick start to DB2 database administration skills for experienced relational Database Administrators (DBA).
    • http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Perform basic database administration tasks on a relational database system
      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL) and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      Topics

        • Overview of DB2 for Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI tools
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Locks and concurrency
        • Problem determination
        • Security
        • DB2 Recovery Log Management
        • DB2 database and table space relocation
        • Using Explain tools
        • Using Indexes for Performance
        • Advanced Monitoring
  • DB2 10.1 for LUW Quickstart for Experienced Relational DBAsOpen or Close

    Description

    • This course teaches you to perform, basic and advanced, database administrative tasks using DB2 10.1 for Linux, UNIX, and Windows.
    • These tasks include creating and populating databases and implementing a logical design to support recovery requirements.
    • The access strategies selected by the DB2 Optimizer will be examined using the DB2 Explain tools.
    • Various diagnostic methods will be presented, including using the db2diag.log file messages to direct your investigation of problems, as well as using the db2pd commands and DB2 traces.
    • You will learn how to implement automatic archival for database logs and how to plan a redirected database restore to relocate either selected table spaces or an entire database.
    • The selection of indexes to improve application performance and the use of SQL statements to track database performance and health will be covered.
    • This course provides a quick start to DB2 database administration skills for experienced relational Database Administrators (DBA).
    • http://www.ibm.com/training/terms

    Prerequisits

      You should be able to:

      • Perform basic database administration tasks on a relational database system
      • Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor
      • State the functions of the Structured Query Language (SQL) and be able to construct DDL, DML, and authorization statements
      • Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

      Topics

        • Overview of DB2 for Linux, UNIX and Windows
        • Command Line Processor (CLP) and GUI tools
        • The DB2 environment
        • Creating databases and data placement
        • Creating database objects
        • Moving data
        • Backup and recovery
        • Locks and concurrency
        • Problem determination
        • Security
        • DB2 Recovery Log Management
        • DB2 database and table space relocation
        • Using Explain tools
        • Using Indexes for Performance
        • Advanced Monitoring
  • Implementing DB2 Connect and Client Connections to DB2 for z/OSOpen or Close

    Description

    • This course is designed for DB2 database and system administrators responsible for implementation and management of connectivity between applications running on distributed platforms like Microsoft Windows, Linux and UNIX based systems and DB2 for z/OS database servers.
    • Through lectures and lab exercise demonstrations, you will learn how to configure a DB2 for z/OS subsystem as a Distributed Relational Database Architecture (DRDA) server.
    • You will learn about alternative methods to implement application connectivity including using IBM data server drivers, a DB2 data server client or a DB2 Connect server.

    • http://www.ibm.com/training/terms

    Prerequisits

      You should:

      • be able to use Linux, UNIX or Windows, and z/OS (including TSO), and
      • be familiar with DB2 concepts

      Topics

        • DB2 distributed database environment and configurations
        • Configuring DB2 for z/OS as a DRDA application server
        • Connecting to DB2 for z/OS from DB2 Connect and DB2 drivers
        • DB2 Connect Enterprise client connectivity
        • Accessing and moving data across platforms
        • Problem determination and performance considerations
        • Planning for migration to direct connections from data server drivers
  • DB2 9 pureScale Implementation and Control for DB2 LUW Administrators - WBTOpen or Close

    Description

    • This course is intended to help you prepare to plan, implement and provide on-going support for a DB2 9.8 pureScale database cluster.
    • The course explains the differences between a DB2 9.8 pureScale database and the standard DB2 LUW database system running on a single host system.
    • You are expected to have experience working with DB2 LUW databases on UNIX, Microsoft Windows or Linux systems.
    • The course is lecture-only, no lab exercises are performed during the class.
    • http://www.ibm.com/training/terms

    Prerequisits

      You could attend any of the following training courses to obtain the database administration skills that are prerequisites for the DB2 9.8 pureScale Implementation and Control for DB2 LUW Administrators (CL800) course:

      • DB2 9 Database Administration Workshop for Linux, UNIX, and Windows (CL2X2)
      • DB2 9 Administration Workshop for Linux (CL202) or
      • DB2 9 Database Administration Workshop for UNIX (CL212) or
      • DB2 9 Administration Workshop for Windows (CL232) or
      • Fast Path to DB2 9 for Experienced Relational DBAs (CL283) or
      • DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL483) or
      • DB2 9 for LUW Multiple Partition DBA Workshop (CL240) or
      • DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (CL250)

      or have equivalent experience

      Topics

        DB2 pureScale Database Concepts

        • List the basic components and functions in a DB2 pureScale database system
        • Describe the types of information managed in the Cluster Caching Facility for a DB2 pureScale database
        • Explain how Remote Direct Memory Access (RDMA) is used to efficiently process requests within a DB2 pureScale database
        • Describe the workload balancing and automatic client routing options used for application connections to a pureScale database
        • List the steps used to perform restart light processing when a database member fails
        • Compare some of the characteristics of a DB2 partitioned database to the cluster implementation used by a DB2 pureScale database

        Inside a DB2 pureScale database

        • Describe the use of local and global locking to support application concurrency in a DB2 pureScale database
        • Explain the use of page level locks with a pureScale database
        • Monitor lock related statistics for a DB2 pureScale clustered database
        • Explain how multiple log streams are used and managed for a pureScale database
        • Describe the use of local and group buffer pools for reading pages with pureScale
        • Monitor the buffer pool efficiency of local and group buffer pools
        • Explain how page reclaims are used in pureScale databases to access pages being changed by multiple database members
        • Describe how castout processing is used to perform page writes with DB2 pureScale databases

        Managing a DB2 pureScale Database Cluster

        • Use db2start and db2stop commands to manage the components of a DB2 pureScale cluster
        • Examine the db2instance command report to check the current status of the pureScale cluster
        • Configure database manager and database member options for a DB2 pureScale cluster
        • Invoke the db2iupdt command to add or remove database members or cluster caching facilities from the cluster
        • Quiesce a host system in a pureScale cluster to perform system maintenance
        • Explain how Automatic Client Reroute is used to maintain high availability for application client systems
        • Implement transaction-level workload balancing for application client systems to improve cluster resource usage
        • Configure client affinity for application systems that need to control connections to specific database member host systems

        DB2 pureScale Database Backup and Recovery

        • Explain the difference between Member Crash Recovery and Group Crash Recovery
        • Plan for online or offline database BACKUP for a DB2 pureScale database
        • Use the RESTORE utility or the RECOVER DATABASE command to perform database recovery or to make a database copy for a pureScale database
        • Describe the impact of database cluster topology changes on database backup and recovery operations
        • Explain the impact of multiple log streams for database roll forward processing with a standby database copy

        Planning and Installation for a DB2 pureScale Database Cluster

        • Select a set of supported system hardware and software components for a DB2 pureScale cluster
        • Perform the pre-installation planning to insure the system software includes all necessary functions and service levels
        • Choose the cluster interconnect network options for the pureScale cluster
        • Estimate Cluster Caching Facility (CF) CPU and memory resources that will be needed to support your databases in the cluster
        • Set the DBM and database configuration options and DB2 registry variables used to allow multiple active databases
        • Perform the DB2 pureScale feature installation for the DB2 cluster

        Migrating a DB2 LUW database to a DB2 pureScale Database Cluster

        • List the steps required to migrate an existing DB2 9.7 database system to a DB2 9.8 pureScale cluster
        • Select the best method to migrate any non-automatic storage table spaces into automatic storage table spaces based on the application requirements and current database structure
        • Implement the GPFS file systems planned to support the DB2 pureScale database

        Storage Management for a DB2 pureScale Database Cluster

        • Use the db2cluster command to create the GPFS file systems needed to support DB2 pureScale databases
        • Describe the difference between DB2 managed and user managed GPFS file systems
        • Select the number of automatic storage paths that will be used to support the DB2 databases
        • Create a new DB2 pureScale database using GPFS based file systems
        • Use the db2cluster command to add and drop disk devices with DB2 managed GPFS file systems
        • Utilize the -rebalance option of db2cluster to improve efficiency of DB2 managed GPFS file systems

        Monitoring and Problem Determination for a DB2 pureScale Database Cluster

        • Monitor application activity in a DB2 pureScale database cluster
        • Utilize db2pd commands to display Cluster Caching Facility memory allocations and connection pool information
        • Use the db2instance and db2cluster commands to show the current status and alter information for database members and cluster hosts
        • Query the DB2 cluster status using SQL functions and views
        • Perform problem determination using the diagnostic messages generated by the DB2 instance and Cluster Caching facilities
        • Use TSA lssam report to view status of cluster resources
        • Examine the message logs maintained by DB2 Cluster Services components

        Agenda

        • Day 1
          • Unit 1: DB2 pureScale Database Concepts
          • Unit 2: Inside a DB2 pureScale database
          • Unit 3: Managing a DB2 pureScale Database Cluster
          • Unit 4: DB2 pureScale Database Backup and Recovery
        • Day 2
          • Unit 5: Planning and Installation for a DB2 pureScale Database Cluster
          • Unit 6: Migrating a DB2 LUW database to a DB2 pureScale Database Cluster
          • Unit 7: Storage Management for a DB2 pureScale Database Cluster
          • Unit 8: Monitoring and Problem Determination for a DB2 pureScale Database Cluster
  • DB2 for z/OS New Functions and Features in V10Open or Close

    Description

    • Learn about the new features and enhancements of DB2 10 for z/OS, including the technical detail of the functional enhancements of  this significant new version of DB2 for z/OS.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should have practical experience with DB2 for z/OS and have good knowledge of the functions and usage of DB2 9 for z/OS.

    Topics

      • Availability
      • SQL
      • Security
      • Data Sharing
      • Utilities
      • Application Enablement
      • Connectivity and Administration Routines
      • XML
      • Performance
      • Scalabiity
      • Installation and Migration
  • DB2 11 for z/OS Application Programming WorkshopOpen or Close

    Description

      This course enables you to acquire the skills necessary to produce application programs that manipulate DB2 databases.
    • Emphasis is on embedding Structured Query Language (SQL) statements and preparing programs for execution.
    • http://www.ibm.com/training/terms

    Prerequisits

      The Health, Safety, and Environment Manager 7.5.1 solution is built on top of Maximo Asset Management 7.5.  It is strongly recommended that students have experience with IBM Maximo 7.5, or complete course IBM Maximo Asset Management 7.5 Fundamentals (SPVC) (TOS43).

    Topics

      • DB2 Concepts
      • Program Structure I
      • Program Preparation
      • Program Structure II
      • Recovery and Locking Concepts
      • Dynamic SQL Introduction
      • Managing Test Data
      • Performance Considerations
  • DB2 10 for z/OS Database Administration Workshop Part 1Open or Close

    Description

    • This course provides you with instruction on how to physically implement a logical database design in DB2. The course includes instruction on DB2 data management, DB2 catalog tables, the bind process, database utilities such as LOAD and REORG, and security considerations.
    • Note: This course material is at the DB2 10 for z/OS level.
    • http://www.ibm.com/training/terms

    Prerequisits

      You should take:

      • DB2 Family Fundamentals (CE030) and
      • SQL Workshop (CE120) or
      • have equivalent experience.

      Topics

        • Setting up a DB2 database
        • Referential integrity
        • Getting data into and out of DB2
        • Keeping your DB2 data in good shape
        • Application data recovery basics
        • Program preparation and Bind
        • Security
        • Serialization