Articles & Content


DB2 z/OS - Hands Free Approach to Maintenance of Multi Terabyte Tables (B04)

Topic: DB2 for z/OS

Subtopic: 2008

DATE: 2008-5-19 (04:00 PM - 05:00 PM)
SPEAKERS: Thanikachalam 'Billy' Sundarrajan (Fifth Third Bank)

DB2 v8 for z/OS introduced several key features which enhanced DB2ís ability to store large volumes of data: Increased Number of partitions, Table controlled partitioning, DPSIs, and ALTER/ROTATE parts. These new features also enable the ability to roll off data and add/rotate partitions in a table. Managing multiple multi-terabyte tables with NP(S)Is in a 24x7 environment can be a daunting task. At Fifth Third Bank, we have developed innovative approaches to handle the challenges associated with design, space management and partition management of large multi-terabyte tables. Space management and partition rotation are performed in an automated fashion with zero to minimal DBA intervention. This presentation provides an overview of Table controlled partitioning, understanding logical versus physical partitions, best practices for designing large tables, and detailed information on automating partition maintenance and reducing DBA intervention.

EXP. LEVEL: Intermediate,Advanced


Index controlled tables versus Range/Table controlled partitioned tables; key columns in the catalog tables, physical versus logical partitions

Best practices: pre-loading compression dictionary, PCTFREE/FREEPAGE settings, PRIQTY/SECQTY settings, TRACKMOD settings, statistics maintenance, PIECESIZE settings, Data sharing implications

Techniques for speeding up Loads/Inserts and purging data from large tables.

Automate the generation of rotate and reorg scripts - identify the partitions that need to be reorged and how often they need to be reorged, generatig ROTATE statements

DB2 9 enhancements - Compression support for LOADS with 254+ parts, Pairwise joins for Star schema

Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.