DB2 LUW Online Table Moves (ADMIN_MOVE

Did you miss The DB2Night Show™ Episode #15 on "DB2 9.7 Online Table Moves" with special guest Ergin Babani from the IBM Toronto Lab? Make sure you check out the recorded replay. This show also set a new record for lively and excellent questions from the virtual studio audience - so be sure to listen through to the end to gain extra insights!

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 (see a sample and learn more) ...

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™

Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=192

DB2 LUW Online Table Moves (ADMIN_MOVE_TABLE)

Example: Database Memory Pool Analysis