Hidden Gems The Top 10 Most Underutilized Functions

Hidden Gems: The Top 10 Most Under-utilized Functions in DB 2 Matthias Nicola, IBM Silicon Valley Lab Session Code: C 15 Wednesday, 18 November 2015, 17: 00 – 18: 00 Platform: DB 2 for Linux, UNIX, and Windows

Key Points • Learn about useful DB 2 features & functions that are easily overlooked. • Learn Tips & Tricks that increase your productivity as a DBA or application developer. • Using the right tool for the job requires that you know which tools are available to you. This session adds 10 new tools to your tool box! 2

Agenda Gem 1: The LISTAGG Function Gem 2: Usage Lists Gem 3: Insert time clustered tables (ITCs) Gem 4: RATIO_TO_REPORT analytic function Gem 5: Multiple-event triggers Gem 6: Trigger event predicates Gem 7: Monitor database configuration changes Gem 8: db 2 look new options Gem 9: HADR Log Spooling Gem 10: Explain a Stored Procedure 3

Gem 1: The LISTAGG function Combines multiple string values from separate rows into a single string Status Supplier. Name Active Milk & More Active Pete’s Fruits Unavailable Chocolate Galore Active Meat Masters Unavailable Soda Company STATUS ------Active Unavailable SELECT status, listagg(suppliername, ’, ’) AS suppliers FROM supplier GROUP BY status; SUPPLIERS --------------Mile & More, Pete’s Fruits, Meat Masters Chocolate Galore, Soda Company

Gem 2: Usage Lists Usage list: database object that collects all DML statements that reference a specified table or index, plus statistics per statement Helps you find out which statements have affected a table or index How to use: Usage lists require extended monitoring: update db cfg using mon_obj_metrics extended CREATE USAGE LIST to define a usage list DROP USAGE LIST to remove a usage list SET USAGE LIST to enable/disable a usage list Obtain information from a usage list by querying: MON_GET_TABLE_USAGE_LIST table function MON_GET_INDEX_USAGE_LIST table function

Gem 2: Usage Lists Examples CREATE USAGE LIST listname FOR TABLE mytable LIST SIZE 500 WHEN FULL WRAP ACTIVE ON START DATABASE; CREATE USAGE LIST listname FOR INDEX myidx WHEN FULL DEACTIVATE INACTIVE ON START DATABASE; Must be a regular table, not a view, nickname, catalog tables, temp table, etc. Size etc. can be changed with ALTER USAGE LIST The list size must be between 10 and 5000. Default is 100. Enable/disable a usage list with SET USAGE LIST

Gem 2: Usage Lists How to read information from a usage list SELECT EXECUTABLE_ID, NUM_REFERENCES, ROWS_READ, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED, LOCK_WAITS, LOCK_WAIT_TIME, LOCK_ESCALS, OBJECT_DATA_L_READS, OBJECT_DATA_P_READS, . . . FROM TABLE( MON_GET_TABLE_USAGE_LIST('myschema', 'myusagelist', -2) ) ORDER BY ROWS_READ DESC FETCH FIRST 50 ROWS ONLY • To get the stmt text: use the EXECUTABLE_ID as input parameter for the table function MON_GET_PKG_CACHE_STMT

Gem 3: Insert time clustered tables (ITCs) Common usage pattern: rows that are inserted together are often read together and/or deleted together ITCs cluster rows automatically by time: rows that are inserted at a similar time are stored in the same extent No explicit clustering column! CREATE TABLE activitylog (column 1 INTEGER, column 2. . . , . . . ) ORGANIZE BY INSERT TIME; Good choice for “cyclic tables”, e. g. keep 30 days worth of data and then delete the oldest day every day Time clustering increases the chance that entire extents will free up Free up space with the REORG RECLAIM EXTENTS command No need for a full reorg to reclaim the free space !

Gem 4: RATIO_TO_REPORT analytic function Returns the ratio of one argument to the sum of all arguments in an OLAP partition. In this example, the ratio reflects the percentage of a player’s scoring for a team’s total scoring: select player, ppg, (RATIO_TO_REPORT(ppg) over())* 100 ratio from nba where team = 'Portland'; create table nba ( team VARCHAR(32), player VARCHAR(32), ppg DECIMAL(5, 2), … ); PLAYER -----Aldridge Lillard Batum Matthews Lopez PPG ------26. 20 22. 90 15. 20 14. 50 10. 00 RATIO -----23. 87 20. 83 13. 19 9. 09

Gem 4: RATIO_TO_REPORT analytic function Another example create table census ( state VARCHAR(32) not null, births 2013 DECIMAL(5, 2) not null, … ); select state, births 2013, (RATIO_TO_REPORT(births 2013) over() )*100 ratio from census order by ratio; STATE BIRTHS RATIO ----- -------Illinois 158964 4. 02 Florida 215542 5. 45 New York 240299 6. 07 Texas 381897 9. 66 California 503634 12. 74 …

Gem 5: Multiple-event triggers • Before DB 2 10. 1, a trigger is fired by only one of three possible events: INSERT or UPDATE or DELETE • Sometimes the triggered action is required for two or all three of these events • Before 10. 1 you had to create and maintain multiple separate (redundant) triggers • Since DB 2 10. 1 you can create triggers that are fired by multiple events (not just one) • Multiple-event triggers can act per statement or per row

Gem 5: Multiple-event triggers • Example 1: Trigger that fires on any write operation, regardless of insert, update, or delete. One trigger instead of three! CREATE TRIGGER logdatawrite AFTER INSERT OR DELETE OR UPDATE ON product FOR EACH STATEMENT BEGIN. . . END;

Gem 5: Multiple-event triggers • Example 2: This trigger fires either when a product is deleted or when the price or status columns are modified: CREATE TRIGGER productchange AFTER UPDATE OF price, status OR DELETE ON product REFERENCING NEW AS new OLD AS old FOR EACH ROW BEGIN. . . END;

Gem 6: Trigger event predicates • New in DB 2 10. 1, to identify the event that has fired the trigger • Particularly useful with multiple-event triggers • Three event predicates, that can be used wherever Boolean expressions are allowed: • UPDATING • INSERTING • DELETING • Multi-event triggers with event predicates enable you to maintain all triggered logic for a table in a single place

Gem 6: Trigger event predicates • Example 1: Take one action for inserts, but a different action for updates or deletes – in the same trigger! CREATE TRIGGER tr_product AFTER INSERT OR UPDATE OR DELETE ON product REFERENCING NEW AS new OLD AS old FOR EACH ROW BEGIN IF INSERTING THEN. . . END IF; IF DELETING OR UPDATING THEN. . . END; END IF;

Gem 6: Trigger event predicates • Example 2: Here, event predicates are also used to affect the set of selected rows in the body of the trigger. CREATE TRIGGER product_upd_del AFTER UPDATE OR DELETE ON product REFERENCING NEW AS new OLD AS old FOR EACH ROW WHEN (UPDATING AND new. status = 0) OR (DELETING AND old. status > 0) BEGIN INSERT INTO prod_review SELECT * FROM product p WHERE (UPDATING AND p. status = new. status) OR (DELETING AND p. group = old. group); END;

Gem 7: Monitor database configuration changes • Were there any recent changes to the database configuration? • Use DB 2’s Change History Event Monitoring to find out ! • Monitoring of db cfg and dbm cfg changes Registry variable changes Execution of DDL statements Execution of most DB 2 utilities: load, reorg, runstats, online backup/restore, …

Gem 7: Monitor database configuration changes • Create a change history event monitor for activities of interest • The monitor captures matching events in various logical data groups, with one result table per group CREATE EVENT MONITOR mymon FOR CHANGE HISTORY WHERE EVENT IN (DBCFG, DBMCFG, DBCFGVALUES, DBMCFGVALUES, REGVARVALUES) WRITE TO TABLE; Logical data group Default table name Contains CHANGESUMMARY_MYMON Summary of all events captured by the monitor DBDBMCFG_MYMON CFG parameter changes REGVAR_MYMON Registry variable changes DDLSTMTEXEC_MYMON DDL execution … … …

Gem 7: Monitor database configuration changes Example -- create and activate the monitor: CREATE EVENT MONITOR mymonitor FOR CHANGE HISTORY WHERE EVENT IN (DBCFG, DBMCFG, DBCFGVALUES, DBMCFGVALUES, REGVARVALUES) WRITE TO TABLE; SET EVENT MONITOR mymonitor STATE 1; -- later, query the monitor to check for config changes SELECT EVENT_TIMESTAMP, CFG_NAME, CFG_VALUE, CFG_OLD_VALUE, FROM DBDBMCFG_MYMONITOR; EVENT_TIMESTAMP ---------30/10/2014 08: 41: 39 30/10/2014 08: 42: 35 CFG_NAME CFG_VALUE CFG_OLD_VALUE ------------LOCKLIST 1024 2048 LOCKTIMEOUT 0 -1

Gem 8: db 2 look new options Generate the CREATE DATABASE command that was used to create the source database. db 2 look –d insight –createdb –o db 2 look. out --------------------------- Generate CREATE DATABASE command --------------------------CREATE DATABASE INSIGHT AUTOMATIC STORAGE YES ON '/nfshome/jseeger' DBPATH ON '/nfshome/jseeger/' USING CODESET UTF-8 TERRITORY US COLLATE USING IDENTITY PAGESIZE 32768 DFT_EXTENT_SZ 4

Gem 8: db 2 look new options Generate UPDATE DB CFG commands for the database configuration parameters. db 2 look –d insight –printdbcfg –o db 2 cfg. sql ----------------------------- Generate UPDATE DB CFG commands ------------------------------- The db 2 look command generates the UPDATE DB CFG statements to replicate the database configuration parameters based on the current values in the source database. For the configuration parameters which support AUTOMATIC, you need to add AUTOMATIC to the end if you want the DB 2 database to automatically adjust them. UPDATE DB CFG FOR INSIGHT USING ALT_COLLATE ; UPDATE DB CFG FOR INSIGHT USING STMT_CONC OFF ; UPDATE DB CFG FOR INSIGHT USING DISCOVER_DB ENABLE ; . . .

Gem 9: HADR Log Spooling

Gem 9: HADR Log Spooling • Configuring HADR spooling disk space: update db cfg using hadr_spool_limit 10000 Obtain information for HADR spooling configuration by querying: MON_GET_HADR table function db 2 pd –hadr Database Member 0 -- Database HADRDB -- Active – HADR_ROLE REPLAY_TYPE HADR_SYNCMODE STANDBY_ID = = PRIMARY PHYSICAL SYNC 1 STANDBY_SPOOL_LIMIT(pages) = 10000 PEER_WINDOW(seconds) = 0

Gem 10: Explain a Stored Procedure 1. Create explain tables if they do not already exist 2. Enable the capturing of execution plans when stored procedures are created in the current session: db 2 "CALL SYSPROC. SET_ROUTINE_OPTS('EXPLAIN ALL')" 3. Issue your CREATE PROCEDURE statement, e. g. : db 2 -td# -f create_proc. sql 4. Use db 2 exfmt to write the execution plan to a file: db 2 exfmt -d <dbname> -1 -o myprocplan. txt Output file will contain separate explain information for each statement in the stored procedure. 5. To check whether the explain is enabled for stored procedures: SELECT GET_ROUTINE_OPTS() FROM sysibm. sysdummy 1 6. To disable explain for stored procedures: db 2 "CALL SYSPROC. SET_ROUTINE_OPTS('EXPLAIN NO')"

Gem 11: Row value constructors / Tuple comparisons • • The syntax composing a tuple is called a row value constructor. DB 2 10. 1 allows you to compare rows value constructors. (c 1, c 2, …) = (d 1, d 2, …) if c 1=d 1 and c 2=d 2 and … (c 1, c 2) > (d 1, d 2) if c 1 > d 1, or if c 1 =1 d 1 and c 2 > d 2 SELECT * FROM emp WHERE (name, firstname) = (? , ? ); SELECT * FROM product WHERE (price, weight, size) < (? , ? ); SELECT * FROM mytable WHERE (x, y) BETWEEN (: x 1, : y 1) AND (: x 2, : y 2) 25

Summary… Gem 1: The LISTAGG Function Gem 2: Usage Lists Gem 3: Insert time clustered tables (ITCs) Gem 4: RATIO_TO_REPORT analytic function Gem 5: Multiple-event triggers Gem 6: Trigger event predicates Gem 7: Monitor database configuration changes Gem 8: db 2 look new options Gem 9: HADR Log Spooling Gem 10: Explain a Stored Procedure Gem 11: Tuple comparisons 26

Matthias Nicola IBM Silicon Valley Lab mnicola@us. ibm. com Session: C 15 Hidden Gems: The Top 10 Most Under-utilized Functions in DB 2 Please fill out your session evaluation before leaving!