Take Advantage over DB 2 LUW BLU Features

Take Advantage over DB 2 LUW BLU Features - Migration, Performance, Maintenance, and Best Practice Sigen Chen Lockheed Martin Session Code: D 7 Tuesday, 15 November 2016 15: 10 – 16: 10 | Platform: LUW

Outline • Multiple usage scenarios • • Set up BLU feature on a new database (under V 10. 5) Enable BLU feature after database was migrated from earlier versions Migrate data from Oracle to DB 2 LUW (V 10. 5 or newer) Best practice. • Performance comparison results of using DB 2 AESE V 10. 5 vs earlier version of DB 2 V 10. 1 and Oracle 11 g. • Use the DB 2 administrative tools and queries to measure the performance changes before and after the migration. • Lessons and experience 2

Requirements / Characteristics • • • OS level pre-requisites 8 CPU 64~128 GB memory (8~16 GB RAM per core) Warehouse / row or column organized New start-up data warehouse may be better – migration path Unicode (code set UTF-8, code page 1208) with IDENTITY or IDENTITY_16 BIT collating sequence • Can be an issue for a database that was carried over from a system built with non UTF-8 code set. As of time we did migration, there is no migration path for non Unicode database 3

Requirements / Characteristics • BLU requires Unicode (code set UTF-8, code page 1208) • For multiple TB database, without staging space, could not afford a few days down time. What is the migration path here? ex: Database territory Database code page Database code set Database country/region code Database collating sequence Alternate collating sequence = US = 819 = ISO 8859 -1 =1 = IDENTITY (ALT_COLLATE) = 4

Start Using BLU with New Database on V 10. 5 • Setup environment • Db 2 set DB 2_WORKLOAD=ANALYTICS • Restart instance • Create database mydb using CODESET UTF-8 Territory US Collate using IDENTITY autoconfigure using mem_percent ? ? apply DB and DBM • 32 k TS and extent size 4 pages • Create database My. DB AUTOMATIC STORAGE YES ON '/udb 1', '/udb 2', '/udb 3' USING CODESET UTF-8 TERRITORY US COLLATE USING IDENTITY PAGESIZE 32768 DFT_EXTENT_SZ 4 5

Start Using BLU with New Database on V 10. 5 (Cont. ) • Configure and Verify • autoconfigure apply none / autoconfigure apply DB and DBM Current and Recommended Values for Database Manager Configuration Description Parameter Current Value Recommended Value -------------------------------------------------------Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY 4 Current and Recommended Values for Database Configuration Description Parameter Current Value Recommended Value ---------------------------------------------------------Default table organization (DFT_TABLE_ORG) = ROW COLUMN Current and Recommended Values for Bufferpool(s) Description Parameter Current Value Recommended Value ----------------------------------------------------------IBMDEFAULTBP Bufferpool size = -2 456996 6

Start Using BLU with New Database on V 10. 5 (Cont. ) • Configure and Verify (Cont. ) …… • CREATE STOGROUP "STG 1" ON '/udb 1/SG', '/udb 2/SG' set as default; • Create tablespace my. TS pagesize 32 k extentsize 4; • Create table my. Tab (c 1 int, c 2 char(10)) in my. TS ORGANIZED by column; • Alternatively update DB CFG DFT_TABLE_ORG to column • When DB 2_WORKLOAD is set to ANALYTICS, default table organization is Column 7

Start Using BLU with New Database on V 10. 5 (Cont. ) • Configure and Verify • Load from mydata. del of del replace into my. Tab • To check the status of loading process load query table ITMUSER. "KLZ_Disk_H” • select tabname, tableorg, compression, pctrowscompressed, pctpagessaved from syscat. tables where tabname like ‘KLZ_Disk%‘ TABNAME TABLEORG COMPRESSION PCTROWSCOMPRESSED PCTPAGESSAVED ----------------------------KLZ_Disk_H C -1. 00000 E+000 90 KLZ_Disk_H 2 R N -1. 00000 E+000 -1 (for comparison) 8

Upgrade from Previous DB 2 Release to V 10. 5 or Newer • • Install, db 2 iupgrade, upgrade db / migrate db… Db 2 set WORKLOAD=ANALYTIC Autoconfigure Create automatic storage if AS was not used in previous TS (alter TS, move table, rebalance)… 9

Upgrade from Previous DB 2 Release to V 10. 5 or Newer (Cont. ) e. g. , CALL SYSPROC. ADMIN_MOVE_TABLE( 'ITMUSER', 'KLZ_IO_Ext_H', 'TSP 32 KAS', '', '', 'MOVE') Result set 1 -------KEY VALUE -------------------------------------------------------------------------------AUTHID DB 2 INST 1 CLEANUP_END 2016 -07 -12 -20. 33. 23. 692915 CLEANUP_START 2016 -07 -12 -20. 33. 23. 510905 COPY_END 2016 -07 -12 -20. 19. 50. 399939 COPY_OPTS ARRAY_INSERT, NON_CLUSTER COPY_START 2016 -07 -12 -19. 21. 27. 603805 COPY_TOTAL_ROWS 43547440 INDEXNAME KLZIOEXT_HX INDEXSCHEMA ITMUSER INDEX_CREATION_TOTAL_TIME 262 INIT_END 2016 -07 -12 -19. 21. 27. 543532 INIT_START 2016 -07 -12 -19. 21. 27. 238710 ORIGINAL_TBLSIZE 72286720 REPLAY_END 2016 -07 -12 -20. 33. 23. 085403 REPLAY_START 2016 -07 -12 -20. 19. 50. 406447 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 0 STATUS COMPLETE SWAP_END 2016 -07 -12 -20. 33. 23. 501714 SWAP_RETRIES 0 SWAP_START 2016 -07 -12 -20. 33. 23. 107307 UTILITY_INVOCATION_ID 000000010000 B 03900000008000000201607121921275446400000 VERSION 10. 05. 0005 23 record(s) selected. Return Status = 0 10

Upgrade from Previous DB 2 Release to V 10. 5 or Newer (Cont. ) • db 2 convert -d My. DB -z ITMUSER -ts TS 32 K Tables that will be converted --------------(Total number: "2") ITMUSER. KLZ_Disk_H: ITMUSER. KLZ_Disk_HCOMP: Enter 1 to proceed with the conversion. Enter 2 to quit. 1 Table Rows. Num Rows. Comm Status Progress (%) -------------------- --------------"ITMUSER". "KLZ_Disk_H" 14258352 0 UNSTARTED 0. 00 "ITMUSER". "KLZ_Disk_HCOMP" 0 0 UNSTARTED 0. 00 Total Progress (0. 00%): | 11

Upgrade from Previous DB 2 Release to V 10. 5 or Newer (Cont. ) Table Rows. Num --------------------"ITMUSER". "KLZ_Disk_H" 0 "ITMUSER". "KLZ_Disk_HCOMP" 0 Rows. Comm Status Progress (%) --------------0 SWAP 100. 00 0 INIT 0. 00 (INIT, COPY, REPLAY, SWAP) Total Progress (100. 00%): ----------------------------------------------| Table Rows. Num Rows. Comm --------------------"ITMUSER". "KLZ_Disk_H" 0 0 "ITMUSER". "KLZ_Disk_HCOMP" 0 0 Status Progress (%) --------------SWAP 100. 00 Total Progress (100. 00%): -----------------------------------------------| Final Summary: Table Rows. Num -------------------"ITMUSER". "KLZ_Disk_H" 14258352 "ITMUSER". "KLZ_Disk_HCOMP" 0 Init. Size (MB) Final. Size (MB) ---------9517. 12 774. 12 0. 50 1. 50 Comp. Rate (%) ---------91. 87 -200. 00 State ------Completed Total Pre-Conversion Size (MB): 9517. 62 Total Post-Conversion Size (MB): 775. 62 Total Compression Rate (Percent): 91. 85 12

Issues • Multiple TB database (compressed) carried over from non UTF 8 codeset, what is the path to use BLU? - Recreate DB, Reload Data? -Time and staging storage do not allow • z. Linux does not have db 2 convert tool - "DB 2 v 10. 5", "special_33597", "IP 23635_33597", and Fix Pack "5". 13

Performance Comparison - Size Matters for Warehouse Database Fig. 1. Data Size Change 1 0, 9 0, 8 0, 7 0, 6 0, 5 0, 4 0, 3 0, 2 0, 1 0 15% Uncompressed Compressed 10% Column Orged 14

Performance Comparison (Cont. ) - Data Loading Time Fig. 2. Data Loading Time 1 0, 9 0, 8 0, 7 0, 6 0, 5 0, 4 0, 3 0, 2 0, 1 0 Row organized Column organized 15

Performance Comparison (Cont. ) - Select / Insert Fig. 3. Select / insert for Row Organized vs Column Organized Table of Same Number of Records 1 0, 9 0, 8 0, 7 0, 6 0, 5 0, 4 0, 3 0, 2 0, 1 0 Row organized Column organized 16

Performance Comparison (Cont. ) - Select count(*) Fig. 4. Select count(*) for Row Organized vs Column Organized Table of Same Number of Records 1 0, 9 0, 8 0, 7 0, 6 0, 5 0, 4 0, 3 0, 2 0, 1 0 Row organized Column organized 17

Performance Comparison (Cont. ) - Backup Fig. 5. Backup (no compression) of Row Organized vs Column Organized Databases 1 0, 9 0, 8 0, 7 0, 6 0, 5 0, 4 0, 3 0, 2 0, 1 0 Row organized (no comp) Column organized 18

Performance Comparison (Cont. ) - Restore Fig. 6. Restore Time for Row Organized (no compression) vs Column Organized Databases 1 0, 9 0, 8 0, 7 0, 6 0, 5 0, 4 0, 3 0, 2 0, 1 0 Row organized (no comp) Column organized 19

Performance Comparison (Cont. ) - Data migration from Oracle to DB 2 LUW • • • Customer Applications Warehouse database (TBs) existed on Oracle Response time sensitive workload License Built/Created DB 2 V 10. 5 database environment Data transfer tool – IBM Infosphere Initiate MDM was used to migrate data from Oracle to DB 2 (Phase-1, Oracle to DB 2, row oriented, compression enabled) 20

Performance Comparison (Cont. ) - Data migration from Oracle to DB 2 LUW • Phase-1, row oriented, compression • Application workloads performance 1 Fig. 7. Application Overall Response Time 0, 8 0, 6 47% 0, 4 0, 2 0 Oracle DB 2 V 10. 5 21

Performance Comparison (Cont. ) - Data migration from Oracle to DB 2 LUW • Phase-2, Column Organized Table convert • Application performance – Stay tuned • If anyone has experience, would like to hear 22

Performance Measurement - Tools to use • OS Level • top (Unix, Linux) / topas (Aix) • mem, cpu, paging/swap etc. . Find who are the top consumers • vmstat • Mem, cpu, io, wait, system or user • • • Prstat (Solaris) iostat sar netstat …… 23

Performance Measurement - Tools to use • Database Level • Snapshot (turn ON when collecting snapshot) • First thing to look at • • event monitoring data db 2 top (examples shown later) db 2 pd explain plan, db 2 exfmt db 2 advis time db 2 –tvf Queryfile. sql db 2 batch -h views and queries (write your own queries) 24

DB 2 System Health Status Monitoring • Database Level (Cont. ) • alerts • Add contact LUWDBA type email address UDBDBA@? ? ? . com description 'DB 2 on LUW'; • Update notification list add contact • update notification list add contact LUWDBA; • Turn dbm cfg health_mon ON • Update alert cfg for databases using mon_indicators YES (more detail following slide) • Update alert cfg for databases using db. deadlock_rate SET THRESHOLDSCHECKED YES; • get alert config for databases -> 25

DB 2 System Health Status Monitoring • Database Level (Cont. ) • alerts db 2 get alert config for databases Get Indicator Name Default Type Warning Alarm Unit Sensitivity Formula Actions Threshold or State checking = db. deadlock_rate = No = Threshold-based =5 = 10 = Deadlocks per hour =0 = delta(db. deadlocks, 12); = Disabled = Enabled …… 26

Performance Measurement - Tools to use (Cont. ) • Database Level (Cont. ) - more alerts…… • db 2 get alert config for databases | grep Indicator Name Indicator Name Indicator Name Indicator Name Indicator Name Indicator Name = db. db_op_status = db. sort_shrmem_util = db. spilled_sorts = db. max_sort_shrmem_util = db. log_fs_util = db. deadlock_rate = db. locklist_util = db. lock_escal_rate = db. apps_waiting_locks = db. pkgcache_hitratio = db. catcache_hitratio = db. shrworkspace_hitratio = db. db_heap_util = db. tb_reorg_req = db. hadr_op_status = db. hadr_delay = db. db_backup_req = db. fed_nicknames_op_status = db. fed_servers_op_status = db. tb_runstats_req = db. auto_storage_util 27

Performance Measurement - Tools to use (Cont. ) • Database Level (Cont. ) - alerts…… • Update admin cfg using SMTP_SERVER smtp_relay. yourorg • Fine tune threshold • Write Your Own Scripts • Third Party Tools 28

db 2 top –d dbname 29

db 2 top –d dbname B 30

db 2 top –d dbname D 31

Sigen Chen Lockheed Martin sigen. chen 1@cms. hhs. gov Session: D 7 Title: Take Advantage over DB 2 BLU Features Please fill out your session evaluation before leaving!