DB2 LUW Deep Compression for

DB2 LUW Deep Compression
for
Enterprise Data Warehouse
Database Environments
Mark Mulligan – [email protected]
http://www.db2ude.com/?q=node/82
PRODUCTION Before Compression
80 Terabytes
IBM 9119-595
22 CPUs
88 GB Real Memory
32 GB Virtual Memory
AIX 5.3.0.0 TL6
DB2 LUW V9.1
PROD001 – 16 Nodes
20 Terabytes
IBM 9119-595
16 CPUs
66 GB Real Memory
26 GB Virtual Memory
PROD002 – 11 Nodes
AIX 5.3.0.0 TL6
8 Terabytes
DB2 LUW V9.1
IBM 9119-595
8 CPUs
32 GB Real Memory
26 GB Virtual Memory
AIX 5.3.0.0 TL6
PROD003 – 11 Nodes
DB2 LUW V9.1
12 Terabytes
IBM 9119-595
11 CPUs
44 GB Real Memory
26 GB Virtual Memory
AIX 5.3.0.0 TL6
PROD004 – 7 Nodes
DB2 LUW V9.1
22 Terabytes
PROD005 – 16 Nodes
18 Terabytes
PRODUCTION After Compression
36 Terabytes
Space Savings 44 Terabytes
IBM 9119-595
22 CPUs
88 GB Real Memory
32 GB Virtual Memory
AIX 5.3.0.0 TL6
DB2 LUW V9.1
PROD001 – 16 Nodes
9 Terabytes
IBM 9119-595
16 CPUs
66 GB Real Memory
26 GB Virtual Memory
PROD002 – 11 Nodes
AIX 5.3.0.0 TL6
4 Terabytes
DB2 LUW V9.1
IBM 9119-595
8 CPUs
32 GB Real Memory
26 GB Virtual Memory
AIX 5.3.0.0 TL6
PROD003 – 11 Nodes
DB2 LUW V9.1
6 Terabytes
IBM 9119-595
11 CPUs
44 GB Real Memory
26 GB Virtual Memory
AIX 5.3.0.0 TL6
PROD004 – 7 Nodes
DB2 LUW V9.1
10 Terabytes
PROD005 – 16 Nodes
7 Terabytes
Detail Steps
•
•
•
•
•
•
•
•
•
•
•
•
•
Alter database managed tablespace (DMS) from REGULAR to LARGE.
Alter DMS tablespace to AUTORESIZE YES.
Alter tables in DMS tablespace to COMPRESS YES and VALUE
COMPRESSION.
Run reorg using the temporary tablespace and RESETDICTIONARY
options.
Run DB2 list tablespaces show detail command.
Recycle the DB2 instance.
Run reorg in place to free up space inside DMS tablespaces.
Run DB2 list tablespaces show detail command.
Recycle the DB2 instance.
Alter tablespaces to reduce the size of containers to recover disk space.
Use the copy/rename table approach for large tables in DMS tablespaces.
Create new DMS LARGE tablespaces to replace SMS tablespaces.
Use the copy/rename table approach for tables in SMS tablespaces.
Pros and Cons
•
•
•
•
•
•
•
•
•
Reduces DASD requirements.
Improves I/O performance.
Less memory required for applications.
More memory available for bufferpools.
5% to 30% performance improvement.
2% to 5% increased CPU utilization.
Requires tablespace conversion of SMS to DMS large.
Requires tablespace conversion of DMS regular to large.
Additional license costs.
Summary
• The amount of space we recovered for reuse
and the performance improvement we have
seen through reduced I/O’s and better database
memory utilization make DB2 LUW Deep
Compression well worth our investment of time
and money.
• In our environments the improved I/O and
memory management performance far outweigh
the small increase in CPU utilization.
Scripts
• The script examples below can help provide information that can be
used to plan and manage projects to implement DB2 LUW Deep
Compression.
• DBA’s can combine and enhance these scripts to generate the
commands needed to alter tablespaces, alter tables and reorganize
tables in order to convert regular record identifiers to large record
identifiers in tablespaces and to build compression dictionaries and
compress data in tables. Ordering the generated command output
so that tablespaces and tables are converted from smallest to
largest will help you recover more space for reuse during a
compression project.
• Small and medium tablepspaces and tables can be converted using
the generated commands and large tables can be skipped in this
process and converted using the copy/rename table approach.
• (Replace the word Database in the script with your database name.)
db2list_candidate_tablespaces_for_conversion.ksh
# Description: This script lists tablespaces that are candidates for
#
: conversion from SMS to DMS and from DMS regular to
#
: DMS large in support of a project to implement large
#
: record identifiers so that DB2 will store more rows
#
: per page. This effort could be part of a project
#
: to implement compression because table compression
#
: requires large record identifiers. (RIDS)
db2 connect to Database
echo "Listing candidate tablespaces for conversion."
db2 -x "select TBSP_TYPE,sum(TBSP_USED_SIZE_KB) as TBSP_USED_SIZE_KB,
char(TBSP_NAME,20) as TBSP_NAME, TBSP_CONTENT_TYPE, TBSP_PAGE_SIZE,
TBSP_AUTO_RESIZE_ENABLED from sysibmadm.tbsp_utilization where
TBSP_CONTENT_TYPE not like '%TEMP%' group by TBSP_TYPE, TBSP_NAME,
TBSP_CONTENT_TYPE, TBSP_PAGE_SIZE, TBSP_AUTO_RESIZE_ENABLED" >
FileName.dat
sort FileName.dat > FileName.txt
cat FileName.txt
rm FileName.dat
echo "Candidate tablespaces for conversion located in FileName.txt"
db2list_candidate_tables_for_compression.ksh
# Description: This script lists candidate tables for compression.
#
: A text file is generated with information about the
#
: server, instance, database, # tablespaces, tablespace
#
: types (D=DMS, S=SMS), schema, table name and row
#
: count (cardinality) from DB2 SYSCAT catalog views.
#
: Output can be imported into a spreadsheet
#
: and used to help plan a compression project.
db2 connect to Database
echo "Identifying tables that are candidates for compression."
db2 -x "select a.tbspace, b.tbspacetype, digits(a.card), a.tabschema, a.tabname
from syscat.tables a, syscat.tablespaces b where a.tabschema not like 'SYS%' and
a.tbspace = b.tbspace and a.card > 0 and a.type in ('T','S') order by a.card
desc,a.tbspace,a.tabschema,a.tabname" > FileName.dat
cat FileName.dat | while read Tbspace TbspaceType Card TabSchema TabName
do
echo '"'$(hostname)'","'${DB2INSTANCE}'","'Database'","'${Tbspace}'","
'${TbspaceType}'","'${TabSchema}'","'${TabName}'","'${Card}'"' | tee -a FileName_excel.txt
done
rm FileName.dat
echo "Candidate tables for compression located in FileName_excel.txt"
Similar pages