IDUG ADMINMOVETABLE Why would a DBA move a
#IDUG ADMIN_MOVE_TABLE(): Why would a DBA move a table ? (Waiter as a second job ? ) Andrej Furlanič Interes d. o. o. Session Code: C 06 11. 2014, 14: 00 | Platform: DB 2 for LUW
#IDUG 2 Objectives Review admin_move_table (AMT) procedure’s purpose and usage. Explain my clients database service level requirements and setups and describe the strengths of AMT procedure in this regard. Describe the database manager versions implementation limits, whose relief requires less-than-online operation with regular utilities. Explain how to achieve same goals with AMT in an efficient way. Understand evolution of AMT itself, show hints and quirks.
#IDUG 3 Objective #1: AMT purpose and usage Move a table to a new tablespace, • to a new storage, different pagesize, • large structure, • or to an Automatic storage tablespace possibly changing almost any property of the table, online, having the crucial phases under DBA’s control, efficiently and completely.
#IDUG 4 AMT purpose, cont‘d Which table properties can be changed ? change column data types into compatible ones, indexes, partitioning, compression, pctfree destination table can be prepared beforehand • compression dictionaries also primary key cannot be changed, but you can • • create additional unique index on destination table execute table movement drop primary key add new primary key constraint (additional unique index will be used)
#IDUG 5 AMT purpose, cont‘d Online AMT phases • • init, copy, replay, swap locks on catalog while switching both tables and recreating dependent objects Crucial phases: • replying possible in several attempts • swap time under DBA’s control • can keep old table
#IDUG 6 AMT purpose, cont‘d efficiently and completely efficient change capture during copy phase • AMT is using triggers and staging tables to keep key values can use load with options, hadr-aware • Achieve loadcopy backup and loadcopy restore on Hadr standby transfers statistic data and statistics profile check and relational constraints • check validation not needed • RI transferred from V 10 on, fast • On V<10, error is SQL 2103 N, rc 6 regenerates dependent objects • views, triggers
#IDUG 7 AMT usage Sample statement -- for a small table, target not predefined CALL SYSPROC. ADMIN_MOVE_TABLE( 'DB 2 INST 3', -- tabschema 'BIN_MF_PROMET', --tabname 'USERSPACE 1', -- data tablespace 'USERSPACE 1', -- index tablespace 'USERSPACE 1', -- LOB tablespace '', '', '', -- options to change mdc_cols, part_key and other 'MOVE') ; -- operation
#IDUG 8 AMT usage Sample statement #2 -- target predefined CALL SYSPROC. ADMIN_MOVE_TABLE( 'DB 2 INST 3', 'BIN_MF_PROMET', 'NEW_BIN_MF_PROMET_PREDEFINED', '', -- no options set 'MOVE') ;
#IDUG 9 AMT usage Sample statement #3 -- target predefined -- set VENDOROPT db cfg setting properly with hadr CALL SYSPROC. ADMIN_MOVE_TABLE( 'DB 2 INST 3', 'BIN_MF_PROMET', 'NEW_BIN_MF_PROMET_PREDEFINED', 'COPY_USE_LOAD COPY YES USE TSM', -- options set 'MOVE') ;
#IDUG 10 AMT usage Sample generator statements select '--CALL SYSPROC. ADMIN_MOVE_TABLE(' ''||tabschema||'' ', ' ''||tabname||' '', ''USERSPACE 1'', '''', ''MOVE'') ; ' from syscat. tables where tbspace='U 5 ERSPACE 1' and card<? and type='T' order by card asc ;
#IDUG 11 AMT usage, cont‘d Sample generator statements -- involving load into IBM Tivoli Storage Manager Select '-- CALL SYSPROC. ADMIN_MOVE_TABLE(' ''||tabschema||'' ', ' ''||tabname||'' ', ''USERSPACE 1'', '''', '''', “COPY_USE_LOAD COPY YES USE TSM” ''MOVE'') ; ' where. . . ; -- involving load into EMC Networker backup SW on Linux … ''COPY_USE_LOAD COPY YES LOAD /usr/libnsrdb 2. so'',
#IDUG 12 Objectives, #2 Review admin_move_table (AMT) procedure’s purpose and usage. Explain my clients database service level requirements and setups and describe the strengths of AMT procedure in this regard. Describe the database manager versions implementation limits, whose relief requires less-than-online operation with regular utilities. Explain how to achieve same goals with AMT in an efficient way. Understand evolution of AMT itself, show hints and quirks.
#IDUG 13 My client’s service level requirements and environment. . . Banks, payment card processors Core banking, internet banking ATM back-end, Authorization systems, Card Iss & Acq Continuity of business concern, regulatory compliance Strict change-management, good back-out plans Online all the time - no regular reorg usage in production • Test and UAT environments – production like • So good testing is possible
#IDUG 14 My client’s service level requirements and environment. . . , cont‘d Not all databases have really high workload Development in-house Some „Black-box“ applications • Without influence to developers • Just perf. measurement and index optimization Less resource efficient, but online operation, is acceptable
#IDUG 15 My client’s service and their database setups • • • Mature db 2 versions (fp 5+ till V 8) Fixpack implementation planning and approval Narrow change management windows, freeze periods Prudent changes, one change at a time (OS or storage or database version or patch) In november 2013 - first V 10. 1. 2 upgrade from V 9 Planned in autumn 2014 – first V 10. 5. 3 a upgrades
#IDUG 16 My client’s service and their database setups, cont'd • • WSE or AWSE or Infosphere editions HADR pairs or triplets with V 10 (for DR) db cfg logindexbuild=on SAN storage with Flashcopy/Disk. Mirror/Clone features IBM TSM or EMC Networker backup software TSA/MP or custom Linux HA scripts db 2 audit active still some regular tablespaces, their size under monitoring
#IDUG 17 Objectives, #3 Review admin_move_table (AMT) procedure’s purpose and usage. Explain my clients database service level requirements and setups and describe the strengths of AMT procedure in this regard. Describe the database manager versions implementation limits, whose relief requires less-than-online operation with regular utilities. Explain how to achieve same goals with AMT in an efficient way. Understand evolution of AMT itself, show hints and quirks.
#IDUG 18 Database manager version’s implementation limits Size limits of regular tablespaces Not storage-reclaimable in earlier versions XML_RECORD_TYPE • V 9. 7+ ->V 2 Reorg dependent schema evolution • Reorg-required changes
#IDUG 19 Database manager version’s implementation limits, cont‘d Transaction log size a concern where LOGINDEXBUILD=ON Fragmentation (overflows, pctfree change) requires reorg „Alter table attach partition“ and global indexes unacceptable Others, not covered by AMT: • Conversion to Unicode • Backup image transferability between platforms
#IDUG 20 Regular tablespace size limits 64 - 512 GB, depending on pagesize Convert to large: • “alter tablespace convert to large” is online • need to reorg tables and indexes, • not an option for large tables (read-only time) • mandatory also for small tables Conversion into automatic storage doesn’t change tablespace type (from regular to large) • Was considered as an online option • Can only eliminate stripe sets
#IDUG 21 Tablespaces not storage-reclaimable in earlier versions before V 9. 7 created tablespaces • must be recreated and data copied storage implications if still in use • Need for better planning of growth • Usually cannot lower HWM a lot • Free space holes in tablespace if not all tables moved away • My typical usage before storage reclaimable era: • For growing tables custom tablespaces • For others, common ones. • With single storage, sorted on access type
#IDUG 22 XML_RECORD_TYPE internal format on tables with XML data type • IBM recommends converting in “after migration” activities • V 1 before db 2 v 9. 7 • What about SYSIBM. SYSXSROBJECTS in SYSCAT tablespace (still V 1) reorgs really long running on XML tables • XML region indexes are implicitly created • Great log consumer and must move tables to v 9. 7+ created tablespace to relief
#IDUG 23 Reorg dependent schema evolution, Transaction log size limit Most column data-type changes require it • Little exceptions of the rule • Must use classic reorg Log size limit with „logindexbuild on“ may be hit • • Most HADR scenario require logindexbuild=on even when creating new indexes on large tables Small max log size may be good to prevent user errors, but not for reorg Log file size limit can't be changed online
#IDUG 24 Transaction log size limit, Index rebuild Not easy to back-out from “index rebuild pending” ! • Continuity of business threat • Index rebuild mess • • DB CFG INDEXREC RESTART value is default • Gets cancelled, if application that triggered it, disconnects • Impossible to follow progress “Solution” • • LOGINDEXBUILD=OFF configurable online Wait for rebuilds Hadr reinit needed Application performance questionable while running
#IDUG 25 Transaction log size limit, Fragmentation (overflows, pctfree change) Fragmentation minor issue in my environments • No delete operations • Without heavy load (performance of OV access) Can avoid reorgs • V 10. 5 – reorg. . . cleanup is easy on log.
#IDUG 26 Alter table attach partition and global indexes index rebuild, again tx log produced partitioned indexes are better (from 9. 7. 1+) safety clause “alter table … attach partition … require matching indexes”
#IDUG 27 AMT strengths online operation can move tables to V 9. 7+ created tablespaces crucial phases under control can back-out in any phase using load, retains HADR standby condition • loadcopy backup into backup software and loadcopy restore on hadr standby • Tested with IBM TSM or EMC Networker • Still offline operation: • moving dbpath like with db 2 relocatedb • (I have not used it under hadr setups)
#IDUG 28 Objectives, #4 • • • Review admin_move_table (AMT) procedure’s purpose and usage. Explain my clients database service level requirements and setups and describe the strengths of AMT procedure in this regard. Describe the database manager versions implementation limits, whose relief requires less-than-online operation with regular utilities. Explain how to achieve same goals with AMT in an efficient way. Understand evolution of AMT itself, show hints and quirks.
#IDUG 29 Experience, hints, pitfalls How to organize work ? Plan to change all non-system tablespaces, moving all data Prepare tablespace replacement list and order Intermediate disk usage may be three-times table size Priorities based on this rules • • • Regular tablespaces, most growing first xml version 1 from small to large tablespaces to gain experience Consider storage free space Compressed data will be 1/3 to 1/5 of original. Wait till V 10 if RI recreation is not an option • AMT not supported for tables with RI in V 9. 7
#IDUG 30 Experience, hints, pitfalls, cont‘d Tablespace strategy consider new extent sizes and automatic prefetch if storage change was done or is planned • Consult storage documentation or configuration for stripe size Year 2010 – 256 kb, Year 2013 – 512 kb minimum • Don‘t miss this chance, it could takes years to have another ! make tablespaces large automatic storage or DMS no more a question • common physical properties‘ layer moved to storage group • tablespaces can inherit them • automatic storage strategy gain was not clear until storage groups in V 10. 1 • In V 10. 5 fp 1, SMS and DMS is deprecated !
#IDUG 31 Experience, hints, pitfalls, cont‘d for tables in tablespace: execute reorgchk and decide pctfree values CALL REORGCHK_TB_STATS('S', 'yourschema'); partitioning and compression applicable ? • In V 10. 5, partitioning is “free” in Workgroup edition • “Alter table compress yes” on source when not using predefined target table • Evaluate adaptive compression if on V 10. 5 identity, generated columns ? • "generated as row change timestamp" values are changed • identity column – next uncached value used on target after AMT
#IDUG 32 Experience, hints, pitfalls, cont‘d for tables in tablespace: Evaluate index improvement • On unique indexes, add include columns • Find common joins on table and recurring predicates • Append new columns to existing indexes definitions • achieve better query cost, tables my have grown Data capture changes active (replication) ? • Find inactive periods for the table involved • Use phase scripts and combine with asnclp scripts to be effective • Use asnclp „offline load before / after“ statements to eliminate refresh of replication targets
#IDUG 33 Experience, hints, pitfalls, cont‘d for tables in tablespace: Choose best AMT copy method • Small tables first • With insert copy method, it commits periodically • Keep an eye on log usage while index creation • Or specify COPY_WITH_INDEXES option • For bigger tables, use load • Review AMT limitations beforehand • Even in 10. 5, there is a long list
#IDUG 34 Experience, hints, pitfalls, cont‘d Execution rename old tablespace, create new prepare target tables • I use data modeling tools and version control tools • All planned changes on new tables documented • ensure that indexes on partitioned tables are partitioned: • try „attach partition… require matching indexes“, to be sure • evaluate compression dictionaries build mode • Dictionary on every partition • Follow dictionary creation when new partition is populated • a new partition dictionary was build soon (on evening of January 1 st)
#IDUG 35 Experience, hints, pitfalls, cont‘d execution move table with AMT • Do it locally, not from sql tools, for large tables comm-timeouts exist on every layer • on very active tables prepare phase scripts for init, copy, swap phase and to cancel You can react quickly • Choose AMT's KEEP option to keep old table for confidence • check loadcopy restore success on hadr standby You could be in hadr disconnected state while loadcopy restore is in progress
#IDUG 36 Experience, hints, pitfalls, cont‘d diag log on standby • Test DB CFG VENDOROPT setting with AMT on dummy table • db 2 diag. log sample: Starting to restore a load copy. PROD . AR_AVTORIZACIJEANjq 8 Ft. 20130717142713 Vendor RC=0 x 00000017=23 -- see DB 2 API Guide for meaning. DATA #2 : signed integer, 4 bytes 4096 MESSAGE : Load copy restore completed successfully.
#IDUG 37 Experience, hints, pitfalls, cont‘d execution, after success recreate possibly invalid objects (indirect views, instead-oftriggers, do rebinds) as soon as possible • currently I am using AUTO_REVAL setting value DISABLED • A procedure ADMIN_REVALIDATE_DB_OBJECTS() fits the purpose postpone RI creation if still on V <10 drop old tablespaces don't forget to backup
#IDUG 38 Experience, hints, pitfalls, cont‘d execution, after success • Measure overall benefits from the change Partitions implemented ? Steady future. • Evaluate compression benefit after a period • Less overflow, page reorgs • Better query plans on xml index achieved immediately • Compare tablespace performance data if apropriate • • When all tables moved to a new tablespace, • Without other changes
#IDUG 39 Experience, hints, pitfalls, cont‘d if not successful at init phase? • • • Drop foreign keys, if on V 9. 7 Add statistics profile Cancel operation not successful always? You get SQL 2104 N The ADMIN_MOVE_TABLE procedure could not be completed at this time by this user. Reason code: "4". • Same user, but different connection • V 10. 5 - improved behaviour through UTILITY_INVOCATION_ID
#IDUG 40 Experience, hints, pitfalls, cont‘d How to cleanup manualy 1/2? Ensure AMT is not running • If comm lost, check applications connected, diag. log Drop 4 triggers and 2 staging tables drop trigger "My. Schema". "Table. AHwjp. Ld"; drop trigger "My. Schema". "Table. AHwjp. Lu"; drop trigger "My. Schema". "Table. AHwjp. Lv"; drop trigger "My. Schema". "Table. AHwjp. Li" ; drop table "My. Schema". "Table. AHwjp. Ls"; drop table "My. Schema". "Table. AHwjp. Lt";
#IDUG 41 Experience, hints, pitfalls, cont‘d How to cleanup manualy 2/2? Delete AMT “state”: Delete from SYSTOOLS. ADMIN_MOVE_TABLE WHERE (tabschema, tabname) = ('My. Schema', 'Table') ; • Drop “pseudo-lock” table if exists: Drop table SYSTOOLS. OTM_SEMAPHORE_TABLE; • Will be recreated on next AMT execution • Ensure one AMT session per database with other DBAs
#IDUG 42 Experience, hints, pitfalls, cont‘d if not successful at copy phase? Using load ? • Just retry COPY, could be storage SW transient error • TSM or Networker temporary not available • Out of tapes, TSM tape reclamation in progress ? Not using load ? • TX log disk full ? • HADR Standby lag ?
#IDUG 43 Experience, hints, pitfalls, cont‘d if not successful at swap phase? Could take 10+ calls to succeed the swap phase • 2 -10 seconds lock on catalog • Our usual locktimeout value 20 seconds • • On card authorization system only 8 seconds Set REPLAY_THRESHOLD and SWAP_MAX_RETRIES before REPLAY phase Open cursors on source ? • Wait, retry later • Cancel AMT • Force application, if you know reaction
#IDUG 44 Experience, hints, pitfalls, cont‘d Quirks in V 9. 7 • After successfull movement on V 9. 7. 7, • users got occasional SQL 0727, rc 2 : An error occurred during implicit system action type, implicit prepare of a cached dynamic SQL statement • I flushed package cache • Did not use ADMIN_REVALIDATE_DB_OBJECTS at the time • Had unsuccessfull call to AMT when • Had predefined target table, two unique indexes: U 1 (col 1, col 2) and U 2 (col 2, col 1), but primary key using different u. index on source than on target table • Missing stats profile on source, defined on target, fails with: • SQL 0104 N An unexpected token "END-OF-STATEMENT" was found following "UNSET". Expected tokens may include: "PROFILE". SQLSTATE=42601
#IDUG 45 Experience, hints, pitfalls, cont‘d Other quirks in V 10. 5 FP 3 a • CALL SYSPROC. ADMIN_MOVE_TABLE('PROD', 'AV_KARTICA', 'TSAVX 1', '', '', 'COPY_USE_LOAD nonrecoverable', 'SWAP') Could return „SQL 2102 N The ADMIN_MOVE_TABLE procedure could not be completed because of an internal failure during the execution of the procedure. Reason code: "". SQLSTATE=5 UA 0 M“ • Diag. log shows a normal lock-timeout : EDUID : 121 EDUNAME: db 2 agent (DBTE 1) 0 FUNCTION: DB 2 UDB, access plan manager, sqlra_inval_vars_hard, probe: 100 RETCODE : ZRC=0 x 80100044=-2146435004=SQLP_LTIMEOUT "Lock. Time. Out - tran rollback Reason code 68"
#IDUG 46 Experience, hints, pitfalls, cont‘d PMRs, APARs Actual PMR 05534, SGC, 708, fixed in special build, not yet fixed in V 10 Amt() call, using load copy to EMC Networker, resulting in SQL 3025 N A parameter specifying a filename or path is not valid. Detected in FP 9. 7. 9, it worked in FP 9. 7. 7 Unofficial support statements from May 2014: • V 9. 7: APAR IT 02437 fix currently planed for inclusion in FP 10 • V 10. 1: APAR IT 02497 fix currently planed for inclusion in FP 5 • V 10. 5: APAR IT 02498 fix currently planned for inclusion in FP 5 • But we‘ve got special build for 9. 7. 9 a, thank you IBM !
#IDUG 47 Experience, hints, pitfalls, cont‘d PMRs, APARs, 2/3 Until special build, full restore was needed on HADR standby V 10. 1. FP 4 – Around 20 APARs documented • presume a major rework has been done • AMT not tested in my databases • Planning to skip V 10. 1 and upgrade to 10. 5 V 10. 5 FP 3 a – Only 4 APARs documented • No major errors found in test environments • V 10. 5 FP 4 (Cancun) - 12 APARS, 7 same as in FP 10. 1. 4
#IDUG 48 Experience, hints, pitfalls, cont‘d PMRs, APARs, 3/3 Older PMR, fixed, just warning: MESSAGE: "POSSIBLE MEMORY CORRUPTION" IN DB 2 DIAG. LOG WHEN USING ADMIN_MOVE_TABLE() • IBM Support determined, that is was not dangerous • Fixed in 9. 7. 9, APAR IC 91694 • Fixed in 10. 5. 3, APAR IC 95513
#IDUG 49 From user to system integrated function Started entirely as user procedure • First name “otm”: online_move_table(), not using objects in system namespace • More and more integrated, a new table-level state flag to prevent concurrent utilities (load, truncate, import replace, reorg, … get SQL 0668 rc 10) • Clearing a wrong table-level flag, due to canceled AMT(): Search for IBM technote „The steps to clear the "ongoing ADMIN_MOVE_TABLE" status for a table“ • RI „transfer“ support in V 10+ • Today it is recommended way to abandone the deprecated SMS tablespaces and adopt automatic storage
#IDUG 50 Details of implementation At init phase: • truncate destination table Internaly using, when „transferring“ constraints: • Before renaming source table: • set integrity for … off cascade deferred, • drop foregin keys, checks • After renaming the target table: • alter table … add check / add foreign keys • set integrity … immediate unchecked • Has effect on following manual (DBA) „set integrity“ execution, see docs • SYSCAT. TABLES. CONST_CHECKED values change from ‘Y‘ to 'U'
#IDUG 51 Thank you for listening ! Glad to hear questions ! … or ask later through mail.
#IDUG Andrej Furlanič Interes d. o. o. Andrej. Furlanic@gmail. com C 06 ADMIN_MOVE_TABLE(): Why would a DBA move a table ? (Waiter as a second job ? ) Please fill out your session evaluation before leaving!
- Slides: 52