The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

DB2 9 DBA exam 731 prep, Part 2: Data placement

Dwaine Snow (dsnow@us.ibm.com), Senior DB2 Product Manager, IBM Toronto Lab

Dwaine R. Snow, a senior product manager for DB2 Data Server, is helping to determine the future direction of the product and decide what functional enhancements need to be made to ensure that it meets customer requirements. Dwaine worked in a DB2 services consultant role with IBM Canada for several years. He provided on-site assistance with DB2 database and application planning, project planning and execution, large complex OLTP and Decision Support system design, database and application design, and performance tuning, as well as client/server and legacy system integration.

Dwaine has written a number of books and articles on DB2, including the DB2 Universal Database Advanced DBA Certification Guide and Reference and Understanding DB2: Learning Visually with Examples, and has presented at conferences around the world.

Summary: Learn how to create DB2 databases, and about the mechanisms available for storing your tables, indexes, and data within the database. This tutorial focuses on partitioning, compression, and XML, which are all important performance and application development concepts that you need to know to store and access data quickly and efficiently. This is the second in a series of seven DB2 DBA certification prep tutorials that you can use to help prepare for the DB2 V9 for Linux, UNIX, and Windows Database Administration (Exam 731).

View more content in this series

Date: 05 Jul 2006
Level: Introductory
PDF: A4 and Letter (249 KB | 37 pages)Get Adobe® Reader®

Activity: 21420 views
Comments:

Before you start

About this series

If you are preparing to take the DB2 DBA certification exam 731, you've come to the right place -- a study hall, of sorts. This series of seven DB2 certification preparation tutorials covers the major concepts you'll need to know for the test. Do your homework here and ease the stress on test day.

About this tutorial

This tutorial discusses the creation of DB2 databases, as well as the various methods used for placing and storing objects within a database. This tutorial focuses on partitioning, compression, and XML, which are all important performance and application development concepts that you need to know to store and access data quickly and efficiently. This tutorial is the second in a series of seven tutorials that you can use to help you prepare for the DB2 Database Administration Certification (Exam 731). The material in this tutorial primarily covers the objectives in Section 2 of the exam, titled Data Placement. You can view these objectives at: http://www-03.ibm.com/certify/tests/obj731.shtml.

You should also review the Resources at the end of this tutorial for more information about DB2 server management.

Objectives

After completing this tutorial, you should be able to:

Create databases
Use schemas
Determine the various table space states
Create and manipulate DB2 objects
Create an SMS table space and understand its characteristics
Understand the characteristics of, and use, DB2's automatic storage
Implement table partitioning and MDC on your tables
Use table compression
Use XML

Prerequisites

To understand some of the material presented in this tutorial, you should be familiar with the following terms:

Object: Anything in a database that can be created or manipulated with SQL (for example, tables, views, indexes, packages).
Table: A logical structure that is used to present data as a collection of unordered rows with a fixed number of columns. Each column contains a set of values, each value of the same data type (or a subtype of the column's data type); the definitions of the columns make up the table structure, and the rows contain the actual table data.
Record: The storage representation of a row in a table.
Field: The storage representation of a column in a table.
Value: A specific data item that can be found at each intersection of a row and column in a database table.
Structured Query Language (SQL): A standardized language used to define objects and manipulate data in a relational database. (For more on SQL, see the fourth tutorial in this series.
DB2 optimizer: A component of the SQL precompiler that chooses an access plan for a Data Manipulation Language (DML) SQL statement by modeling the execution cost of several alternative access plans and choosing the one with the minimal estimated cost.

To take the DB2 9 DBA exam, you must have already passed the DB2 9 Fundamentals exam 730. We recommend that you take the DB2 Fundamentals tutorial series before starting this series.

Although not all materials discussed in the Family Fundamentals tutorial series are required to understand the concepts described in this tutorial, you should at least have a basic knowledge of:

DB2 products
DB2 tools
DB2 instances
Databases
Database objects

System requirements

You do not need a copy of DB2 to complete this tutorial. However, you will get more out of the tutorial if you download the free trial version of IBM DB2 9 to work along with this tutorial.

Comments