With this particular episode, we've also broken precedent and made Ergin's slide deck available as a PDF. He provided excellent examples of how to use DB2 ADMIN_TABLE_MOVE during his talk. You'll find the PDF link on the show replay page.
Have you registered for IDUG North America in Tampa Florida yet? There are still a few good seats left in my full day seminar Z06: DB2 for LUW Performance Analysis and Tuning Workshop. Attendees will receive over three dozen amazingly helpful SQL Snapshot commands...
These SQL Snapshot commands are not SELECT * from SYSIBMADM.SNAPVIEW. I would not insult your intelligence like that!
Example: Database Memory Pool Analysis
SELECT SUBSTR(A.DB_NAME,1,10) as DBNAME,A.POOL_ID AS MEMORY_NAME,
SUBSTR(CASE WHEN ( SUBSTR(A.POOL_SECONDARY_ID,1,1) IS NULL)
THEN 'HEAP'
WHEN ( SUBSTR(A.POOL_SECONDARY_ID,1,1) = 'S' )
THEN A.POOL_SECONDARY_ID
ELSE ( SELECT SUBSTR(BP.BPNAME,1,32)
FROM IDUGCONF.SYSCAT_BUFFERPOOLS BP
WHERE CHAR(BP.BUFFERPOOLID) = A.POOL_SECONDARY_ID
)
END,1,22) AS MEMORY_DESCRIPTION,
CAST( (A.POOL_CUR_SIZE / 1048576.0) AS DECIMAL(11,2)) AS CURRSIZE_MB,
A.POOL_CUR_SIZE AS CURRENT_SIZE_BYTES,
CAST( ((A.POOL_CUR_SIZE * 100.0) / A.POOL_CONFIG_SIZE) AS DECIMAL(5,2)) AS CURR_CFG_SIZE_PCT,
CASE WHEN ( ((A.POOL_CUR_SIZE * 100.0) / A.POOL_CONFIG_SIZE) ) > 90
THEN 'INCR POOL!'
ELSE '*POOL OK* '
END AS POOL_EVAL,
A.POOL_CONFIG_SIZE,
A.POOL_WATERMARK AS POOL_HWM_BYTES,
A.DBPARTITIONNUM AS PART
FROM IDUGCONF.SNAPDB_MEMORY_POOL A
ORDER BY A.DBPARTITIONNUM;
You will notice these SQL Snapshots refer to the IDUGCONF schema. You will receive the source code for these SQL Snapshots electronically. To prepare for the IDUG Ed Seminar, check out the class preparation instructions. It should only take you 15-30 minutes to prepare and you will learn a lot about the performance of a database in your organization - AND HOW TO TUNE IT!
After the class, all you'll need to do is change IDUGCONF to SYSIBMADM (in most cases) and then you can run the SQL Snapshots directly against your organization's databases. For some of the commands, you will also need to change "IDUGCONF.SYSCAT_" to "SYSCAT." when catalog tables are being referenced.
Hope to see you in class!
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM DB2 Gold Consultant
IBM Information Management Champion
Host of The DB2Night Show™