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"