In 2010, the "DB2 LUW Performance Analysis and Tuning Workshop" drew top attendance numbers in North America, Australia, and EMEA. In 2011, this IDUG Ed Seminar has been updated and is being offered again as session
1900.
Click Here for Details.

In this full day class, you will receive over three dozen SQL Snapshot commands to help analyze YOUR database. Together, we will learn about what to measure, we will discover the problems and "opportunities for improvement" in your database, we will discuss solutions, and you will return to your office fully equipped to make measurable performance improvements.

This blog post contains the specific instructions that you should use to prepare for this workshop...

In terms of preparation for this class, here's what you will want to do to optimally prepare:

Make copy tables of SYSIBMADM snapshot views and some catalog tables (CREATE LIKE)
INSERT INTO copy table SELECT * FROM original table or view
EXPORT TO IXF file SELECT * FROM copy tables
Binary transfer the IXF files to a laptop running DB2 LUW (Express-C ( free DB2 ) will suffice)
IMPORT FROM IXF file REPLACE_CREATE INTO local copy of tables on laptop

All of the SQL commands provided to you during the class will reference the schema name IDUGCONF. The rest of this blog post contains the specific commands that you can use to prepare for the class. When you return back to your office with electronic copies of the SQL Snapshots, all you will need to do is change IDUGCONF to SYSIBMADM and you'll be ready to analyze your databases locally. Actually, as an added bonus, I'll also send you home with a second set of the SQL that is pre-edited and ready to run without the need for editing!

This should be a $5,000 course. IDUG is making this class available for only $594 EUR or just $540 EUR if you attend the full conference.
Register Soon!

As per the outline above, here are the specific commands that you should run to best prepare for this class. If you cannot bring a laptop running DB2, attend the class anyways so that you can learn all of the tricks and take home the SQL Snapshot commands.

For this class, please use a REAL production database if you can - and hopefully a database that is in need of performance improvement!!! When you return back to your office equipped with a toolbox of analysis commands and performance solutions, we'd sure like your organization to reap the full benefit of your attendance at IDUG! Let's have your management saying "Wow, that was the best education money we ever spent!" icon_biggrin

Step 1: Make a Copy of SYSIBMADM views and a few catalog tables

If you would like to download these commands to a text file, simply right-click save-as the following link: create-idugconf-snaptabs.txt Execute the downloaded commands with "db2 -tvf filename".

Commands to run:

create table IDUGCONF.ADMINTABINFO LIKE SYSIBMADM.ADMINTABINFO;

create table IDUGCONF.APPL_PERFORMANCE LIKE SYSIBMADM.APPL_PERFORMANCE;

create table IDUGCONF.APPLICATIONS LIKE SYSIBMADM.APPLICATIONS;

create table IDUGCONF.AUTHORIZATIONIDS LIKE SYSIBMADM.AUTHORIZATIONIDS;

create table IDUGCONF.BP_HITRATIO LIKE SYSIBMADM.BP_HITRATIO;

create table IDUGCONF.BP_READ_IO LIKE SYSIBMADM.BP_READ_IO;

create table IDUGCONF.BP_WRITE_IO LIKE SYSIBMADM.BP_WRITE_IO;

create table IDUGCONF.CONTACTGROUPS LIKE SYSIBMADM.CONTACTGROUPS;

create table IDUGCONF.CONTACTS LIKE SYSIBMADM.CONTACTS;

create table IDUGCONF.CONTAINER_UTILIZATION LIKE SYSIBMADM.CONTAINER_UTILIZATION;

create table IDUGCONF.DB_HISTORY LIKE SYSIBMADM.DB_HISTORY;

create table IDUGCONF.DBCFG LIKE SYSIBMADM.DBCFG;

create table IDUGCONF.DBMCFG LIKE SYSIBMADM.DBMCFG;

create table IDUGCONF.ENV_INST_INFO LIKE SYSIBMADM.ENV_INST_INFO;

create table IDUGCONF.ENV_PROD_INFO LIKE SYSIBMADM.ENV_PROD_INFO;

create table IDUGCONF.ENV_SYS_INFO LIKE SYSIBMADM.ENV_SYS_INFO;

create table IDUGCONF.LOCKS_HELD LIKE SYSIBMADM.LOCKS_HELD;

create table IDUGCONF.LOCKWAITS LIKE SYSIBMADM.LOCKWAITS;

create table IDUGCONF.LOG_UTILIZATION LIKE SYSIBMADM.LOG_UTILIZATION;

create table IDUGCONF.LONG_RUNNING_SQL LIKE SYSIBMADM.LONG_RUNNING_SQL;

create table IDUGCONF.NOTIFICATIONLIST LIKE SYSIBMADM.NOTIFICATIONLIST;

create table IDUGCONF.OBJECTOWNERS LIKE SYSIBMADM.OBJECTOWNERS;

create table IDUGCONF.PDLOGMSGS_LAST24HOURS LIKE SYSIBMADM.PDLOGMSGS_LAST24HOURS;

create table IDUGCONF.PRIVILEGES LIKE SYSIBMADM.PRIVILEGES;

create table IDUGCONF.QUERY_PREP_COST LIKE SYSIBMADM.QUERY_PREP_COST;

create table IDUGCONF.REG_VARIABLES LIKE SYSIBMADM.REG_VARIABLES;

create table IDUGCONF.SNAPAGENT LIKE SYSIBMADM.SNAPAGENT;

create table IDUGCONF.SNAPAGENT_MEMORY_POOL LIKE SYSIBMADM.SNAPAGENT_MEMORY_POOL;

create table IDUGCONF.SNAPAPPL LIKE SYSIBMADM.SNAPAPPL;

create table IDUGCONF.SNAPAPPL_INFO LIKE SYSIBMADM.SNAPAPPL_INFO;

create table IDUGCONF.SNAPBP LIKE SYSIBMADM.SNAPBP;

create table IDUGCONF.SNAPBP_PART LIKE SYSIBMADM.SNAPBP_PART;

create table IDUGCONF.SNAPCONTAINER LIKE SYSIBMADM.SNAPCONTAINER;

create table IDUGCONF.SNAPDB LIKE SYSIBMADM.SNAPDB;

create table IDUGCONF.SNAPDB_MEMORY_POOL LIKE SYSIBMADM.SNAPDB_MEMORY_POOL;

create table IDUGCONF.SNAPDBM LIKE SYSIBMADM.SNAPDBM;

create table IDUGCONF.SNAPDBM_MEMORY_POOL LIKE SYSIBMADM.SNAPDBM_MEMORY_POOL;

create table IDUGCONF.SNAPDETAILLOG LIKE SYSIBMADM.SNAPDETAILLOG;

create table IDUGCONF.SNAPDYN_SQL LIKE SYSIBMADM.SNAPDYN_SQL;

create table IDUGCONF.SNAPFCM LIKE SYSIBMADM.SNAPFCM;

create table IDUGCONF.SNAPFCM_PART LIKE SYSIBMADM.SNAPFCM_PART;

create table IDUGCONF.SNAPHADR LIKE SYSIBMADM.SNAPHADR;

create table IDUGCONF.SNAPLOCK LIKE SYSIBMADM.SNAPLOCK;

create table IDUGCONF.SNAPLOCKWAIT LIKE SYSIBMADM.SNAPLOCKWAIT;

create table IDUGCONF.SNAPSTMT LIKE SYSIBMADM.SNAPSTMT;

create table IDUGCONF.SNAPSTORAGE_PATHS LIKE SYSIBMADM.SNAPSTORAGE_PATHS;

create table IDUGCONF.SNAPSUBSECTION LIKE SYSIBMADM.SNAPSUBSECTION;

create table IDUGCONF.SNAPSWITCHES LIKE SYSIBMADM.SNAPSWITCHES;

create table IDUGCONF.SNAPTAB LIKE SYSIBMADM.SNAPTAB;

create table IDUGCONF.SNAPTAB_REORG LIKE SYSIBMADM.SNAPTAB_REORG;

create table IDUGCONF.SNAPTBSP LIKE SYSIBMADM.SNAPTBSP;

create table IDUGCONF.SNAPTBSP_PART LIKE SYSIBMADM.SNAPTBSP_PART;

create table IDUGCONF.SNAPTBSP_QUIESCER LIKE SYSIBMADM.SNAPTBSP_QUIESCER;

create table IDUGCONF.SNAPTBSP_RANGE LIKE SYSIBMADM.SNAPTBSP_RANGE;

create table IDUGCONF.SNAPUTIL LIKE SYSIBMADM.SNAPUTIL;

create table IDUGCONF.SNAPUTIL_PROGRESS LIKE SYSIBMADM.SNAPUTIL_PROGRESS;

create table IDUGCONF.TBSP_UTILIZATION LIKE SYSIBMADM.TBSP_UTILIZATION;

create table IDUGCONF.TOP_DYNAMIC_SQL LIKE SYSIBMADM.TOP_DYNAMIC_SQL;

create table IDUGCONF.SYSCAT_TABLES LIKE SYSCAT.TABLES;

create table IDUGCONF.SYSCAT_INDEXES LIKE SYSCAT.INDEXES;

create table IDUGCONF.SYSCAT_BUFFERPOOLS LIKE SYSCAT.BUFFERPOOLS;

create table IDUGCONF.SYSCAT_TABLESPACES LIKE SYSCAT.TABLESPACES;

Step 2: Insert Data Into the Copy Tables as Select * From

If you would like to download these commands to a text file, simply right-click save-as the following link: href="http://www.dbisoftware.com/media/insert-into-snaptabs.txt">insert-into-snaptabs.txt Execute the downloaded commands with "db2 -tvf filename".

Commands to run:

insert into IDUGCONF.ADMINTABINFO SELECT * FROM SYSIBMADM.ADMINTABINFO;

insert into IDUGCONF.APPL_PERFORMANCE SELECT * FROM SYSIBMADM.APPL_PERFORMANCE;

insert into IDUGCONF.APPLICATIONS SELECT * FROM SYSIBMADM.APPLICATIONS;

insert into IDUGCONF.AUTHORIZATIONIDS SELECT * FROM SYSIBMADM.AUTHORIZATIONIDS;

insert into IDUGCONF.BP_HITRATIO SELECT * FROM SYSIBMADM.BP_HITRATIO;

insert into IDUGCONF.BP_READ_IO SELECT * FROM SYSIBMADM.BP_READ_IO;

insert into IDUGCONF.BP_WRITE_IO SELECT * FROM SYSIBMADM.BP_WRITE_IO;

insert into IDUGCONF.CONTACTGROUPS SELECT * FROM SYSIBMADM.CONTACTGROUPS;

insert into IDUGCONF.CONTACTS SELECT * FROM SYSIBMADM.CONTACTS;

insert into IDUGCONF.CONTAINER_UTILIZATION SELECT * FROM SYSIBMADM.CONTAINER_UTILIZATION;

insert into IDUGCONF.DB_HISTORY SELECT * FROM SYSIBMADM.DB_HISTORY;

insert into IDUGCONF.DBCFG SELECT * FROM SYSIBMADM.DBCFG;

insert into IDUGCONF.DBMCFG SELECT * FROM SYSIBMADM.DBMCFG;

insert into IDUGCONF.ENV_INST_INFO SELECT * FROM SYSIBMADM.ENV_INST_INFO;

insert into IDUGCONF.ENV_PROD_INFO SELECT * FROM SYSIBMADM.ENV_PROD_INFO;

insert into IDUGCONF.ENV_SYS_INFO SELECT * FROM SYSIBMADM.ENV_SYS_INFO;

insert into IDUGCONF.LOCKS_HELD SELECT * FROM SYSIBMADM.LOCKS_HELD;

insert into IDUGCONF.LOCKWAITS SELECT * FROM SYSIBMADM.LOCKWAITS;

insert into IDUGCONF.LOG_UTILIZATION SELECT * FROM SYSIBMADM.LOG_UTILIZATION;

insert into IDUGCONF.LONG_RUNNING_SQL SELECT * FROM SYSIBMADM.LONG_RUNNING_SQL;

insert into IDUGCONF.NOTIFICATIONLIST SELECT * FROM SYSIBMADM.NOTIFICATIONLIST;

insert into IDUGCONF.OBJECTOWNERS SELECT * FROM SYSIBMADM.OBJECTOWNERS;

insert into IDUGCONF.PDLOGMSGS_LAST24HOURS SELECT * FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS;

insert into IDUGCONF.PRIVILEGES SELECT * FROM SYSIBMADM.PRIVILEGES;

insert into IDUGCONF.QUERY_PREP_COST SELECT * FROM SYSIBMADM.QUERY_PREP_COST;

insert into IDUGCONF.REG_VARIABLES SELECT * FROM SYSIBMADM.REG_VARIABLES;

insert into IDUGCONF.SNAPAGENT SELECT * FROM SYSIBMADM.SNAPAGENT;

insert into IDUGCONF.SNAPAGENT_MEMORY_POOL SELECT * FROM SYSIBMADM.SNAPAGENT_MEMORY_POOL;

insert into IDUGCONF.SNAPAPPL SELECT * FROM SYSIBMADM.SNAPAPPL;

insert into IDUGCONF.SNAPAPPL_INFO SELECT * FROM SYSIBMADM.SNAPAPPL_INFO;

insert into IDUGCONF.SNAPBP SELECT * FROM SYSIBMADM.SNAPBP;

insert into IDUGCONF.SNAPBP_PART SELECT * FROM SYSIBMADM.SNAPBP_PART;

insert into IDUGCONF.SNAPCONTAINER SELECT * FROM SYSIBMADM.SNAPCONTAINER;

insert into IDUGCONF.SNAPDB SELECT * FROM SYSIBMADM.SNAPDB;

insert into IDUGCONF.SNAPDB_MEMORY_POOL SELECT * FROM SYSIBMADM.SNAPDB_MEMORY_POOL;

insert into IDUGCONF.SNAPDBM SELECT * FROM SYSIBMADM.SNAPDBM;

insert into IDUGCONF.SNAPDBM_MEMORY_POOL SELECT * FROM SYSIBMADM.SNAPDBM_MEMORY_POOL;

insert into IDUGCONF.SNAPDETAILLOG SELECT * FROM SYSIBMADM.SNAPDETAILLOG;

insert into IDUGCONF.SNAPDYN_SQL SELECT * FROM SYSIBMADM.SNAPDYN_SQL;

insert into IDUGCONF.SNAPFCM SELECT * FROM SYSIBMADM.SNAPFCM;

insert into IDUGCONF.SNAPFCM_PART SELECT * FROM SYSIBMADM.SNAPFCM_PART;

insert into IDUGCONF.SNAPHADR SELECT * FROM SYSIBMADM.SNAPHADR;

insert into IDUGCONF.SNAPLOCK SELECT * FROM SYSIBMADM.SNAPLOCK;

insert into IDUGCONF.SNAPLOCKWAIT SELECT * FROM SYSIBMADM.SNAPLOCKWAIT;

insert into IDUGCONF.SNAPSTMT SELECT * FROM SYSIBMADM.SNAPSTMT;

insert into IDUGCONF.SNAPSTORAGE_PATHS SELECT * FROM SYSIBMADM.SNAPSTORAGE_PATHS;

insert into IDUGCONF.SNAPSUBSECTION SELECT * FROM SYSIBMADM.SNAPSUBSECTION;

insert into IDUGCONF.SNAPSWITCHES SELECT * FROM SYSIBMADM.SNAPSWITCHES;

insert into IDUGCONF.SNAPTAB SELECT * FROM SYSIBMADM.SNAPTAB;

insert into IDUGCONF.SNAPTAB_REORG SELECT * FROM SYSIBMADM.SNAPTAB_REORG;

insert into IDUGCONF.SNAPTBSP SELECT * FROM SYSIBMADM.SNAPTBSP;

insert into IDUGCONF.SNAPTBSP_PART SELECT * FROM SYSIBMADM.SNAPTBSP_PART;

insert into IDUGCONF.SNAPTBSP_QUIESCER SELECT * FROM SYSIBMADM.SNAPTBSP_QUIESCER;

insert into IDUGCONF.SNAPTBSP_RANGE SELECT * FROM SYSIBMADM.SNAPTBSP_RANGE;

insert into IDUGCONF.SNAPUTIL SELECT * FROM SYSIBMADM.SNAPUTIL;

insert into IDUGCONF.SNAPUTIL_PROGRESS SELECT * FROM SYSIBMADM.SNAPUTIL_PROGRESS;

insert into IDUGCONF.TBSP_UTILIZATION SELECT * FROM SYSIBMADM.TBSP_UTILIZATION;

insert into IDUGCONF.TOP_DYNAMIC_SQL SELECT * FROM SYSIBMADM.TOP_DYNAMIC_SQL;

insert into IDUGCONF.SYSCAT_TABLES SELECT * FROM SYSCAT.TABLES;

insert into IDUGCONF.SYSCAT_INDEXES SELECT * FROM SYSCAT.INDEXES;

insert into IDUGCONF.SYSCAT_BUFFERPOOLS SELECT * FROM SYSCAT.BUFFERPOOLS;

insert into IDUGCONF.SYSCAT_TABLESPACES SELECT * FROM SYSCAT.TABLESPACES;

Step 3: Export Copy Tables to IXF Files

If you would like to download these commands to a text file, simply right-click save-as the following link: href="http://www.dbisoftware.com/media/export-snaptabs-to-ixf.txt">export-snaptabs-to-ixf.txt Execute the downloaded commands with "db2 -tvf filename".

Commands to run:

export to ADMINTABINFO.ixf of IXF SELECT * FROM IDUGCONF.ADMINTABINFO;

export to APPL_PERFORMANCE.ixf of IXF SELECT * FROM IDUGCONF.APPL_PERFORMANCE;

export to APPLICATIONS.ixf of IXF SELECT * FROM IDUGCONF.APPLICATIONS;

export to AUTHORIZATIONIDS.ixf of IXF SELECT * FROM IDUGCONF.AUTHORIZATIONIDS;

export to BP_HITRATIO.ixf of IXF SELECT * FROM IDUGCONF.BP_HITRATIO;

export to BP_READ_IO.ixf of IXF SELECT * FROM IDUGCONF.BP_READ_IO;

export to BP_WRITE_IO.ixf of IXF SELECT * FROM IDUGCONF.BP_WRITE_IO;

export to CONTACTGROUPS.ixf of IXF SELECT * FROM IDUGCONF.CONTACTGROUPS;

export to CONTACTS.ixf of IXF SELECT * FROM IDUGCONF.CONTACTS;

export to CONTAINER_UTILIZATION.ixf of IXF SELECT * FROM IDUGCONF.CONTAINER_UTILIZATION;

export to DB_HISTORY.ixf of IXF SELECT * FROM IDUGCONF.DB_HISTORY;

export to DBCFG.ixf of IXF SELECT * FROM IDUGCONF.DBCFG;

export to DBMCFG.ixf of IXF SELECT * FROM IDUGCONF.DBMCFG;

export to ENV_INST_INFO.ixf of IXF SELECT * FROM IDUGCONF.ENV_INST_INFO;

export to ENV_PROD_INFO.ixf of IXF SELECT * FROM IDUGCONF.ENV_PROD_INFO;

export to ENV_SYS_INFO.ixf of IXF SELECT * FROM IDUGCONF.ENV_SYS_INFO;

export to LOCKS_HELD.ixf of IXF SELECT * FROM IDUGCONF.LOCKS_HELD;

export to LOCKWAITS.ixf of IXF SELECT * FROM IDUGCONF.LOCKWAITS;

export to LOG_UTILIZATION.ixf of IXF SELECT * FROM IDUGCONF.LOG_UTILIZATION;

export to LONG_RUNNING_SQL.ixf of IXF SELECT * FROM IDUGCONF.LONG_RUNNING_SQL;

export to NOTIFICATIONLIST.ixf of IXF SELECT * FROM IDUGCONF.NOTIFICATIONLIST;

export to OBJECTOWNERS.ixf of IXF SELECT * FROM IDUGCONF.OBJECTOWNERS;

export to PDLOGMSGS_LAST24HOURS.ixf of IXF SELECT * FROM IDUGCONF.PDLOGMSGS_LAST24HOURS;

export to PRIVILEGES.ixf of IXF SELECT * FROM IDUGCONF.PRIVILEGES;

export to QUERY_PREP_COST.ixf of IXF SELECT * FROM IDUGCONF.QUERY_PREP_COST;

export to REG_VARIABLES.ixf of IXF SELECT * FROM IDUGCONF.REG_VARIABLES;

export to SNAPAGENT.ixf of IXF SELECT * FROM IDUGCONF.SNAPAGENT;

export to SNAPAGENT_MEMORY_POOL.ixf of IXF SELECT * FROM IDUGCONF.SNAPAGENT_MEMORY_POOL;

export to SNAPAPPL.ixf of IXF SELECT * FROM IDUGCONF.SNAPAPPL;

export to SNAPAPPL_INFO.ixf of IXF SELECT * FROM IDUGCONF.SNAPAPPL_INFO;

export to SNAPBP.ixf of IXF SELECT * FROM IDUGCONF.SNAPBP;

export to SNAPBP_PART.ixf of IXF SELECT * FROM IDUGCONF.SNAPBP_PART;

export to SNAPCONTAINER.ixf of IXF SELECT * FROM IDUGCONF.SNAPCONTAINER;

export to SNAPDB.ixf of IXF SELECT * FROM IDUGCONF.SNAPDB;

export to SNAPDB_MEMORY_POOL.ixf of IXF SELECT * FROM IDUGCONF.SNAPDB_MEMORY_POOL;

export to SNAPDBM.ixf of IXF SELECT * FROM IDUGCONF.SNAPDBM;

export to SNAPDBM_MEMORY_POOL.ixf of IXF SELECT * FROM IDUGCONF.SNAPDBM_MEMORY_POOL;

export to SNAPDETAILLOG.ixf of IXF SELECT * FROM IDUGCONF.SNAPDETAILLOG;

export to SNAPDYN_SQL.ixf of IXF SELECT * FROM IDUGCONF.SNAPDYN_SQL;

export to SNAPFCM.ixf of IXF SELECT * FROM IDUGCONF.SNAPFCM;

export to SNAPFCM_PART.ixf of IXF SELECT * FROM IDUGCONF.SNAPFCM_PART;

export to SNAPHADR.ixf of IXF SELECT * FROM IDUGCONF.SNAPHADR;

export to SNAPLOCK.ixf of IXF SELECT * FROM IDUGCONF.SNAPLOCK;

export to SNAPLOCKWAIT.ixf of IXF SELECT * FROM IDUGCONF.SNAPLOCKWAIT;

export to SNAPSTMT.ixf of IXF SELECT * FROM IDUGCONF.SNAPSTMT;

export to SNAPSTORAGE_PATHS.ixf of IXF SELECT * FROM IDUGCONF.SNAPSTORAGE_PATHS;

export to SNAPSUBSECTION.ixf of IXF SELECT * FROM IDUGCONF.SNAPSUBSECTION;

export to SNAPSWITCHES.ixf of IXF SELECT * FROM IDUGCONF.SNAPSWITCHES;

export to SNAPTAB.ixf of IXF SELECT * FROM IDUGCONF.SNAPTAB;

export to SNAPTAB_REORG.ixf of IXF SELECT * FROM IDUGCONF.SNAPTAB_REORG;

export to SNAPTBSP.ixf of IXF SELECT * FROM IDUGCONF.SNAPTBSP;

export to SNAPTBSP_PART.ixf of IXF SELECT * FROM IDUGCONF.SNAPTBSP_PART;

export to SNAPTBSP_QUIESCER.ixf of IXF SELECT * FROM IDUGCONF.SNAPTBSP_QUIESCER;

export to SNAPTBSP_RANGE.ixf of IXF SELECT * FROM IDUGCONF.SNAPTBSP_RANGE;

export to SNAPUTIL.ixf of IXF SELECT * FROM IDUGCONF.SNAPUTIL;

export to SNAPUTIL_PROGRESS.ixf of IXF SELECT * FROM IDUGCONF.SNAPUTIL_PROGRESS;

export to TBSP_UTILIZATION.ixf of IXF SELECT * FROM IDUGCONF.TBSP_UTILIZATION;

export to TOP_DYNAMIC_SQL.ixf of IXF SELECT * FROM IDUGCONF.TOP_DYNAMIC_SQL;

export to SYSCAT_TABLES.ixf of IXF SELECT * FROM IDUGCONF.SYSCAT_TABLES;

export to SYSCAT_INDEXES.ixf of IXF SELECT * FROM IDUGCONF.SYSCAT_INDEXES;

export to SYSCAT_BUFFERPOOLS.ixf of IXF SELECT * FROM IDUGCONF.SYSCAT_BUFFERPOOLS;

export to SYSCAT_TABLESPACES.ixf of IXF SELECT * FROM IDUGCONF.SYSCAT_TABLESPACES;

Step 4: Do a BINARY Transfer of the IXF files to Your Laptop

FTP (or other transfer tool) the IXF files to an IDUGCONF directory on your laptop. Remember to use BINARY mode. In the next step, the IMPORT commands will assume/require that the current working directory is the local IDUGCONF directory.

Step 5: Import the IXF files into DB2 tables on Your Laptop

Use DB2 CLP window and change directory to the IDUGCONF directory containing the IXF files.

If you would like to download these commands to a text file, simply right-click save-as the following link: href="http://www.dbisoftware.com/media/import-snaptabs-from-ixf.txt">import-snaptabs-from-ixf.txt Execute the downloaded commands with "db2 -tvf filename".

Commands to run:

import from ADMINTABINFO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.ADMINTABINFO;

import from APPL_PERFORMANCE.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.APPL_PERFORMANCE;

import from APPLICATIONS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.APPLICATIONS;

import from AUTHORIZATIONIDS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.AUTHORIZATIONIDS;

import from BP_HITRATIO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.BP_HITRATIO;

import from BP_READ_IO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.BP_READ_IO;

import from BP_WRITE_IO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.BP_WRITE_IO;

import from CONTACTGROUPS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.CONTACTGROUPS;

import from CONTACTS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.CONTACTS;

import from CONTAINER_UTILIZATION.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.CONTAINER_UTILIZATION;

import from DB_HISTORY.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.DB_HISTORY;

import from DBCFG.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.DBCFG;

import from DBMCFG.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.DBMCFG;

import from ENV_INST_INFO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.ENV_INST_INFO;

import from ENV_PROD_INFO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.ENV_PROD_INFO;

import from ENV_SYS_INFO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.ENV_SYS_INFO;

import from LOCKS_HELD.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.LOCKS_HELD;

import from LOCKWAITS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.LOCKWAITS;

import from LOG_UTILIZATION.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.LOG_UTILIZATION;

import from LONG_RUNNING_SQL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.LONG_RUNNING_SQL;

import from NOTIFICATIONLIST.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.NOTIFICATIONLIST;

import from OBJECTOWNERS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.OBJECTOWNERS;

import from PDLOGMSGS_LAST24HOURS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.PDLOGMSGS_LAST24HOURS;

import from PRIVILEGES.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.PRIVILEGES;

import from QUERY_PREP_COST.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.QUERY_PREP_COST;

import from REG_VARIABLES.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.REG_VARIABLES;

import from SNAPAGENT.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPAGENT;

import from SNAPAGENT_MEMORY_POOL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPAGENT_MEMORY_POOL;

import from SNAPAPPL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPAPPL;

import from SNAPAPPL_INFO.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPAPPL_INFO;

import from SNAPBP.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPBP;

import from SNAPBP_PART.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPBP_PART;

import from SNAPCONTAINER.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPCONTAINER;

import from SNAPDB.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPDB;

import from SNAPDB_MEMORY_POOL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPDB_MEMORY_POOL;

import from SNAPDBM.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPDBM;

import from SNAPDBM_MEMORY_POOL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPDBM_MEMORY_POOL;

import from SNAPDETAILLOG.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPDETAILLOG;

import from SNAPDYN_SQL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPDYN_SQL;

import from SNAPFCM.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPFCM;

import from SNAPFCM_PART.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPFCM_PART;

import from SNAPHADR.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPHADR;

import from SNAPLOCK.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPLOCK;

import from SNAPLOCKWAIT.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPLOCKWAIT;

import from SNAPSTMT.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPSTMT;

import from SNAPSTORAGE_PATHS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPSTORAGE_PATHS;

import from SNAPSUBSECTION.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPSUBSECTION;

import from SNAPSWITCHES.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPSWITCHES;

import from SNAPTAB.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPTAB;

import from SNAPTAB_REORG.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPTAB_REORG;

import from SNAPTBSP.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPTBSP;

import from SNAPTBSP_PART.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPTBSP_PART;

import from SNAPTBSP_QUIESCER.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPTBSP_QUIESCER;

import from SNAPTBSP_RANGE.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPTBSP_RANGE;

import from SNAPUTIL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPUTIL;

import from SNAPUTIL_PROGRESS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SNAPUTIL_PROGRESS;

import from TBSP_UTILIZATION.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.TBSP_UTILIZATION;

import from TOP_DYNAMIC_SQL.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.TOP_DYNAMIC_SQL;

import from SYSCAT_TABLES.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SYSCAT_TABLES;

import from SYSCAT_INDEXES.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SYSCAT_INDEXES;

import from SYSCAT_BUFFERPOOLS.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SYSCAT_BUFFERPOOLS;

import from SYSCAT_TABLESPACES.ixf of IXF commitcount 100 REPLACE_CREATE INTO IDUGCONF.SYSCAT_TABLESPACES;

Step 6: Optional Clean Up - Drop IDUGCONF objects from monitored database

After you have copied the snapshot and catalog data to your laptop, you can return to the original DB2 server that you captured your data from and drop the IDUGCONF objects.

If you would like to download these commands to a text file, simply right-click save-as the following link: href="http://www.dbisoftware.com/media/drop-snaptabs.txt">drop-snaptabs.txt Execute the downloaded commands with "db2 -tvf filename".

Commands to run on original monitored database server ( not your laptop! ) :

drop table IDUGCONF.ADMINTABINFO;

drop table IDUGCONF.APPL_PERFORMANCE;

drop table IDUGCONF.APPLICATIONS;

drop table IDUGCONF.AUTHORIZATIONIDS;

drop table IDUGCONF.BP_HITRATIO;

drop table IDUGCONF.BP_READ_IO;

drop table IDUGCONF.BP_WRITE_IO;

drop table IDUGCONF.CONTACTGROUPS;

drop table IDUGCONF.CONTACTS;

drop table IDUGCONF.CONTAINER_UTILIZATION;

drop table IDUGCONF.DB_HISTORY;

drop table IDUGCONF.DBCFG;

drop table IDUGCONF.DBMCFG;

drop table IDUGCONF.ENV_INST_INFO;

drop table IDUGCONF.ENV_PROD_INFO;

drop table IDUGCONF.ENV_SYS_INFO;

drop table IDUGCONF.LOCKS_HELD;

drop table IDUGCONF.LOCKWAITS;

drop table IDUGCONF.LOG_UTILIZATION;

drop table IDUGCONF.LONG_RUNNING_SQL;

drop table IDUGCONF.NOTIFICATIONLIST;

drop table IDUGCONF.OBJECTOWNERS;

drop table IDUGCONF.PDLOGMSGS_LAST24HOURS;

drop table IDUGCONF.PRIVILEGES;

drop table IDUGCONF.QUERY_PREP_COST;

drop table IDUGCONF.REG_VARIABLES;

drop table IDUGCONF.SNAPAGENT;

drop table IDUGCONF.SNAPAGENT_MEMORY_POOL;

drop table IDUGCONF.SNAPAPPL;

drop table IDUGCONF.SNAPAPPL_INFO;

drop table IDUGCONF.SNAPBP;

drop table IDUGCONF.SNAPBP_PART;

drop table IDUGCONF.SNAPCONTAINER;

drop table IDUGCONF.SNAPDB;

drop table IDUGCONF.SNAPDB_MEMORY_POOL;

drop table IDUGCONF.SNAPDBM;

drop table IDUGCONF.SNAPDBM_MEMORY_POOL;

drop table IDUGCONF.SNAPDETAILLOG;

drop table IDUGCONF.SNAPDYN_SQL;

drop table IDUGCONF.SNAPFCM;

drop table IDUGCONF.SNAPFCM_PART;

drop table IDUGCONF.SNAPHADR;

drop table IDUGCONF.SNAPLOCK;

drop table IDUGCONF.SNAPLOCKWAIT;

drop table IDUGCONF.SNAPSTMT;

drop table IDUGCONF.SNAPSTORAGE_PATHS;

drop table IDUGCONF.SNAPSUBSECTION;

drop table IDUGCONF.SNAPSWITCHES;

drop table IDUGCONF.SNAPTAB;

drop table IDUGCONF.SNAPTAB_REORG;

drop table IDUGCONF.SNAPTBSP;

drop table IDUGCONF.SNAPTBSP_PART;

drop table IDUGCONF.SNAPTBSP_QUIESCER;

drop table IDUGCONF.SNAPTBSP_RANGE;

drop table IDUGCONF.SNAPUTIL;

drop table IDUGCONF.SNAPUTIL_PROGRESS;

drop table IDUGCONF.TBSP_UTILIZATION;

drop table IDUGCONF.TOP_DYNAMIC_SQL;

drop table IDUGCONF.SYSCAT_TABLES;

drop table IDUGCONF.SYSCAT_INDEXES;

drop table IDUGCONF.SYSCAT_BUFFERPOOLS;

drop table IDUGCONF.SYSCAT_TABLESPACES;

That's it! You should be ready for the IDUG EMEA 2011 Ed Seminar Session 1900 now! I will see you in class!

Kind regards,

Scott

President & CEO, DBI

IBM DB2 GOLD Consultant & Information Management Champion

Your Host of The DB2Night Show�