Other company, product, or service names may be trademarks or service marks of others.

(1)

I

B

M

X

I

V

®

S

t

o

r

a

g

e

S

y

s

t

e

m

C

o

n

f

i

g

u

r

a

t

i

o

n

C

o

n

s

i

d

e

r

a

t

i

o

n

s

f

o

r

X

I

V

i

n

a

n

I

B

M

D

B

2

2 D

D

a

t

a

b

a

s

e

E

n

v

i

r

o

n

m

e

n

t

W

h

i

t

e

P

a

p

e

r

February 2010

(2)

Copyright IBM Corporation 2010 _ii

Copyright IBM Corporation 2010

IBM, the IBM logo, ibm.com, System Storage, XIV, and the XIV logo are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both These and other IBM trademarked terms are marked on their first occurrence in this information with the appropriate symbol (® or ™), indicating US registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at ibm.com/legal/copytrade.shtml .

Other company, product, or service names may be trademarks or service marks of others. This document could include technical inaccuracies or typographical errors. IBM may not offer the products, services or features discussed in this document in other countries, and the product information may be subject to change without notice. Consult your local IBM business contact for information on the product or services available in your area. Any statements regarding IBM’s future direction and intent are subject to change or withdrawal without notice, and

represent goals and objectives only. The information contained in this document is current as of the initial date of publication only and is subject to change without notice. All performance information was determined in a controlled environment. Actual results may vary. Performance information is provided “AS IS” and no warranties or guarantees are expressed or implied by IBM. Information concerning non-IBM products was obtained from the suppliers of their products, their published announcements or other publicly available sources. Questions on the capabilities of the non-IBM products should be addressed with the suppliers. IBM does not warrant that the information offered herein will meet your requirements or those of your

distributors or customers. IBM provides this information “AS IS” without warranty. IBM disclaims all warranties, express or implied, including the implied warranties of noninfringement,

merchantability and fitness for a particular purpose or noninfringement. IBM products are warranted according to the terms and conditions of the agreements under which they are provided.

askxiv@us.ibm.com

(3)

Copyright IBM Corporation 2010 _iii

Introduction ... 1

Database storage layout and server configuration ... 1

Taking advantage of XIV’s grid architecture with DB2 I/O Parallelism... 5

Achieving DB2 database high availability with XIV Snapshots... 7

Using a XIV Snapshots as a backup and restore image ... 8

Using XIV Snapshots to clone a database... 10

(4)

Copyright IBM Corporation 2010 1

Introduction

The purpose of this document is to provide guidelines and recommendations on how to use the IBM XIV Storage System in an IBM DB2 environment. Consideration will be given to DB2 database administration and migration to XIV. AIX was the host operating system used and some of the recommendations are AIX specific. The intended audience is the XIV technical sales teams and their customers who wish to use or migrate DB2 on an XIV Storage System.

This document assumes a basic understanding of DB2 database technology. It combines the features and best practices of both DB2 and XIV and provides recommendations for:

• Optimum performance • Data layout

• Database Replicating • Backup and recovery • Cloning

The guidelines and procedures in this document relate to: • DB2 9.5 FP3 and above

• AIX 5.3 TL10 or AIX 6.1 TL3 and above

Database storage layout and server configuration

XIV’s unique architecture uniformly distributes data across key internal resources. This data distribution method is fundamentally different from conventional storage subsystems and significantly simplifies database management considerations.

To achieve optimum DB2 performance and availability, it is important to take advantage of the unique capabilities of XIV and DB2. First we will focus on the physical aspects of XIV volumes how these volumes are mapped to the host. Next we will discuss some AIX operating system tuning

recommendations.

LUN Layout on XIV

The most unique aspect of XIV is its inherent ability to utilize all resources (drives/cache/CPU) within its storage subsystem regardless of the layout of the data. However, to achieve maximum performance and availability, there are a few recommendations:

• In partitioned database environment, use separate LUNs per partition to enable independent

backup and recovery of each partition.

• Place your data and logs on separate LUNs to be able to recover to a certain point-in-time instead

just going back to the to the last consistent snapshot image after database corruption occurs. In addition, some backup management and automation tools, for example Tivoli Flash Copy Manager, require separate volumes for data and logs.

(5)

Copyright IBM Corporation 2010 ₂

• For data, use a small number of large LUNs (typically 2 - 4 LUNs). Each LUN should be between

500G - 2TB, depending on size of your database. Using a small number of large LUNs takes better advantage of XIV’s aggressive caching technology and simplifies storage management.

Note: Keep in mind that XIV shows LUN sizes in base 10 (KB = 1000 B), while AIX shows them in base 2 (KB = 1024 B). In addition, filesystem overhead will also claim some storage capacity. When creating a LUN, make sure to plan for the extra capacity required.

• If more then one LUN is used for data, place them in a single XIV Consistency Group. In a

partitioned database environment, create a consistency group per partition. The purpose of pooling all data volumes together per partition is to facilitate the use of XIV’s ability to create a consistent snapshot of all volumes within an XIV consistency group. Do not place your database transaction logs in the same consistency group as data.

• For log files, use only one LUN and match its size to the space required by the database

configuration guidelines.

Host Zoning to the XIV Array

If a host has the ability to spread the load between more than one path utilizing MPIO drivers, it will engage multiple resources within the XIV Grid architecture and therefore yield better performance. Engaging more then two host interfaces assures consistent performance and availability even through a path failure. Our best case configuration recommendation includes the following guidelines:

• Build a redundant SAN configuration • Zone server HBAs to all 6 XIV interface modules

• Use server based multipath software to balance IO across all paths per LUN.

(6)

Copyright IBM Corporation 2010 ₃

For example, in an OLTP environment with the requirement to support a high number of IO/s, it is recommended to use servers with 2 HBAs, zoned to 6 XIV interfaces, balancing the IO across 12 paths (see Figure 1). For data warehouse configuration with high throughput requirements, servers with 4 HBAs should be used, zoned to 6 XIV interfaces, balancing IO across 24 paths.

HBA and HDISK Setup

Host queue depth essentially controls how much data is allowed to be “in flight” onto the SAN from the HBA. A queue depth greater than 1 indicates that multiple host I/O requests may be waiting for response from the storage system. The bigger the host HBA queue depth, the more parallel I/O goes to XIV. Host queue depth also becomes an import factor in maximizing XIV I/O performance because multiple I/O requests coming in parallel best utilize the XIV algorithms that stage data between disk and cache.

Start with a large queue depth and max transfer size for all HBAs to ensure you take advantage of the parallelism of the XIV architecture. For example:

$ chdev -a ‘num_cmd_elems=2048 max_xfer_size=0X200000’ -l fcs0

Next, for each database LUN (data and log) set multipathing algorithm to round-robin, queue depth to value between 64 to 256, and large max transfer rate:

$ chdev -a ‘algorithm=round_robin queue_depth=256 max_transfer=0x100000’ –l hdisk1

Note: While a higher queue depth in general yields better performance with XIV, one must consider the limitations per port on the XIV side. Each port on XIV interface module is designed to sustain up to 1400 concurrent IOs. For example, with a queue depth of 64 per host port, a single XIV port can support 21 (1400/64~=21) concurrent host ports given that each host could fill up the entire 64 depth queue for each request.

Configuring IO completion ports (IOCP) for async IO

AIX operating systems use IOCP for their Async IO (AIO) processing. The AIO server processes manage the I/O requests by processing large numbers of requests in the most optimal way for the system.

It is important to make sure that the IOCP model is installed and configured on the database server.

1. Enter the lslpp command to check whether the IOCP library is installed and configured on your system.

$ lslpp -l bos.iocp.rte

1. Enter the lsdev command to check whether the status of the IOCP port is Available.

(7)

Copyright IBM Corporation 2010 ₄

Figure 2: Validating availability of IOCP

If the IOCP is not configured on the server, enter the following chdev command:

$ chdev -l ocp0 -P available

For more details regarding I/O server configuration for prefetching and parallelism please refer to

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/ doc/c0005399.html

Configuring Volume Group and File Systems

To minimize maintenance overhead, we recommend using one volume group per database partition. The number of files system created should be driven by your backup and recovery strategy, i.e. - being able to restore a single or a group of tablespaces vs. the entire database. Usually, we recommend using 2-4 hdisks in a single volume group. The volume group should be created as Scalable to allow increasing the number of logical volumes or physical partitions in the future. For example:

$ mkvg –S –s 1024 –y vg1 hdisk1 hdisk2

When creating the logical volume, you want to make sure that it spans all the hdisks in the volume group.

$ mklv -t jfs2 -e x -y vg1lv1 –S 1024 vg1 3744 hdisk1 hdisk2

Next, create file system per logical volume group using inline log:

(8)

Taking advantage of XIV’s grid architecture with DB2

I/O Parallelism

The overall design of the XIV grid architecture excels with applications that employ threads to handle the parallel execution of I/Os from a single server, or even better from multiple servers.

In DB2 environment there are multiple ways to achieve parallelism to take advantage of XIV’s grid:

• Intrapartition parallelism refers to the ability to break up a query into multiple parts, subdividing what is usually considered a single database operation such as index creation, database loading, or SQL queries. This is achieved by breaking down SQL prefetch requests into a number of smaller, parallel prefetch requests. In traditional (non XIV) environments this was done based on the number of containers in a table space. In an XIV environment, due to its unique architecture, containers should handle multiple parallel requests to achieve better performance. The DB2_PARALLEL_IO registry variable is used to manually override the parallelism of prefetch requests, and should be set to 64 with XIV:

$ db2set DB2_PARALLEL_IO=*:64

• Interpartition parallelism refers to the ability to break up a query into multiple parts across multiple partitions of a partitioned database, on one machine or multiple machines.

• Interquery parallelism refers to the ability of the database to accept queries from multiple applications at the same time. Each query runs independently of the others, but the database manager runs all of them at the same time.

• Intraquery parallelism refers to the simultaneous processing of parts of a single query, using either intrapartition parallelism, interpartition parallelism, or both.

• DB2 utilities can take advantage of intrapartition parallelism. They can also take advantage of interpartition parallelism. Where multiple database partitions exist, the utilities execute in each of the database partitions in parallel. For example:

o LOAD command takes advantage of I/O parallelism by parallel invocations at each database partition where the table resides. To specify the number of parallel threads that the load utility will create for writing data to the table space containers, use the ‘DISK_PARALLELISM n’ parameter in the call: LOAD FROM orders.tbl OF DEL MODIFIED BY COLDEL| FASTPARSE ANYORDER REPLACE INTO TPCD.orders STATISTICS NO

NONRECOVERABLE DATA BUFFER 150000 DISK_PARALLELISM 64

PARTITIONED DB CONFIG MODE LOAD_ONLY part_file_location /db2fs/flatlinks; For more details about LOAD command please follow this link:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.dm.doc/doc/ t0004590.html

(9)

o Backup and restore utilities are heavily I/O-bound tasks. The DB2 system exploits both I/O parallelism and intrapartition parallelism when performing backup and restore operations. Backup exploits I/O parallelism by reading from multiple table space containers in parallel, and

asynchronously writing to multiple backup media in parallel.

For detailed information and considerations about DB2 Parallel I/O please follow the links below:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c00054 02.html

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.ha.doc/doc/t0052816 .html

(10)

Achieving DB2 database high availability with XIV

Snapshots

IBM Data Server suspended I/O support includes the ability to split mirrored copies of your primary database without taking the database offline. This can be done only with DB2 Database Managed Storage (DMS) table spaces.

This functionality can leverage XIV innovative approach for Snapshots generation and maintenance of a secondary copy of DB2 database. The XIV system offers clear advantages over alternative split mirror approaches currently available in the industry, including the following:

• Snapshot creation in virtually zero time, regardless of the size of replicated volumes. • Practically unlimited number of snapshots in the system.

• Unaffected performance levels in a system that supports snapshots, regardless of the number of snapshots currently defined in the system.

The db2inidb command is used to initialize the snapshot so that it can be used in two ways:

• As a fast clone database

• As a backup image eliminating backup operation overhead from the production machine

This command can only be issued against a snapshot or a clone volume, and it must be run before the snapshot can be used.

In a partitioned database environment, you do not have to suspend I/O writes on all database partitions

simultaneously. You can suspend a subset of one or more database partitions to create split mirrors for performing offline backups. If the catalog partition is included in the subset, it must be the last database partition to be

suspended.

In a partitioned database environment, the db2inidb command must be run on every database partition before the split image from any of the database partitions can be used. The tool can be run on all database partitions simultaneously using the db2_all command. However, if you are using the RELOCATE USING option, you cannot use the db2_all command to run db2inidb on all of the database partitions simultaneously. In this case, a separate configuration file must be supplied for each database partition that includes the NODENUM value of the database partition being changed. For example, if the name of a database is being changed, every database partition will be affected and the db2relocatedb command must be run with a separate configuration file on each database partition. If containers belonging to a single database partition are being moved, the db2relocatedb command only needs to be run once on that database partition.

To review additional information regarding high availability strategies for DB2 database and IBM Data Server please follow the link below:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.ha.doc/doc/c000635 4.html

(11)

Preparing the database for ROLLFORWARD recovery

In order to be able to recover database using snapshots and begin to roll forward the database changes to a desired point in time, a database configuration parameter LOGARCHMETH1 has to be turned on and set to LOGRETAIN or DISK. For example:

$ db2 update db cfg using LOGARCHMETH1 DISK:/vg2lv1/db2inst1/arch_logs

This command will place all archive logs in the /vg2lv1/db2inst1/arch_logs directory. To validate the current set value of this parameter, run the following command:

$ db2 get db cfg | grep LOGARCH

Figure 3: Validating that log archiving is turned on

Using a XIV Snapshots as a backup and restore image

Creating a backup copy

Use the following procedure to create a snapshot of a primary database for use as a backup image. This procedure can be used instead of performing backup database operations on the primary database.

To use a split mirror as a "backup image", follow these steps: 1. Suspend I/O on the primary database:

$ db2 set write suspend for database

Note: While the database is suspended, you should not be running other utilities or tools. You should only be making a copy of the database.

2. Use XIV GUI or XCLI to generate a snapshot of a primary database, data and log. For example: xcli cg_snapshots_create cg=’db2_data_group’

(12)

Note: Ensure that the snapshot includes the entire database directory including the volume directory. You must create a snapshot of the log directory and any container directories that exist outside the database directory. To gather this information, refer to the DBPATHS administrative view which shows all the files and directories of the database that need to be split:

$ db2 “select path from sysibmadm.dbpaths”

3. Resume I/O on the primary database:

$ db2 set write resume for database

Restoring from the snapshot

If a failure occurs on the primary system, or data is corrupted necessitating a restore from backup, follow the steps outlined below to bring the database to a point –in-time before the corruption occurred:

1. Stop the primary database instance:

$ db2stop

2. Unmount the files system from the data LUNs and vary off the logical volume. $ umount /vg2lv1

$ varyoffvg vg1

3. Use XIV GUI or XCLI to restore the primary data volumes from the snapshot. Do not restore the split-off log files unless the primary logs where corrupted too. The primary logs will be needed for roll forward recovery.

xcli snap_group_restore snap_group="db2group.snapgroup_0001"

4. Vary on the volume group and mount the file system

$ varyonvg vg1 $ mount /vg2lv1

5. Start the primary database instance:

$ db2start

6. Initialize the primary database

$ db2inidb database_alias as mirror

7. Roll the primary database forward to the end of the logs or to a point-in-time and stop. For example:

$ db2 rollforward db dtw complete

For more details about rollforward command options please see:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.ha.doc/doc/c000625 6.html

(13)

Using XIV Snapshots to clone a database

Use the following procedure to create a clone of a DB2 database. The AS SNAPSOT option of db2inidb command can be used to provide an instantaneous copy of the database at that time when the I/O is suspended. Any other outstanding uncommitted work will be rolled back after the db2inidb command is executed on the clone. You cannot back up a cloned database and restore the backup image on the original system, or roll forward through log files produced on the original system.

Note: Before a clone database can be created on the secondary server using XIV snapshot, make sure that: • DB2 server software is installed and configured on this server using db2_install command. For details

please see:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.cmd.doc/doc/r00 08305.html

• All post installation manual configuration tasks where executed. Please follow the procedures described in the following document:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/t000 7067.html

To clone a database, follow these steps:

1. To ensure that your copy includes the entire database (data and logs), using XIV GUI or XCLI create an XIV consistency group which encapsulates all database LUNs. For example:

xcli cg_create cg=”db2lpar” pool=”db2lpar” xcli cg_add_vol cg=”db2lpar” vol=”db2_1” xcli cg_add_vol cg=”db2lpar” vol=”db2_2”

2. Suspend I/O on the primary database:

$ db2 set write suspend for database

Note: While the database is suspended, you should not be running other utilities or tools. You should only be making a copy of the database.

3. Generate a consistency group snapshot of a primary database. cg_snapshots_create cg="db2lpar"

4. Resume I/O on the primary database.

$ db2 set write resume for database

(14)

5. Catalog the mirrored database

$ db2 catalog db <dbname> as <db_aliace> at node <sec_node> authentication server with “Snapshot db”

Figure 5: XIV GUI view of Consistency Group Snapshot 6. Start the secondary database instance:

$ db2start

7. Initialize the mirrored database:

$ db2inidb database_alias as snapshot

If required, specify the RELOCATE USING option of the db2inidb command to relocate the clone database to different directory:

$ db2inidb database_alias as snapshot relocate using relocatedbcfg.txt

where the relocatedbcfg.txt file contains the information required to relocate the database.

Notes: This command will roll back transactions that are in flight when the split occurs, and start a new log chain sequence so that any logs from the primary database cannot be replayed on the cloned database.

Summary

To achieve optimum DB2 performance and availability, it is important to take advantage of the unique combined capabilities of XIV and DB2. XIV’s unique architecture uniformly distributes data across key internal resources. This data distribution method is fundamentally different from conventional storage subsystems and significantly simplifies DB2 database management considerations

XIV’s groundbreaking approach to snapshots overcomes the most significant performance penalties currently affecting other vendors’ products. The snapshot mechanism harnesses the full power of XIV technology and relies naturally on the advantages provided by the unique XIV grid architecture. DB2’s ability to split mirrored copies of your primary database without taking the database offline takes full advantage of XIV snapshot architecture, delivering true high availability and simplifies management the database environment.