Thursday, January 30, 2014

DB2 Tuning Commands & DB2 Utility Commands

Quickly look at some basic tuning measurements:
db2 "select db_name,  rows_read, rows_selected, lock_waits, lock_wait_time, deadlocks, lock_escals, total_sorts, total_sort_time from table (snapshot_database (' ', -1) ) as snapshot_database"


List the running utilities and their progress
=>db2 list utilities show detail
Throttle a running utility.
First find the Utility ID.
=>db2 list utilities show detail
Throttle the utility to a value of 1 to 10. 10 being the most throttled.
=>db2 set UTIL_IMPACT_PRIORITY for TO 5

DB2 Tablespace and Bufferpool Commands

View the names, pagesize, and number of pages of the bufferpools in your database:
=>db2 "select bpname,pagesize,npages from syscat.bufferpools"
Make a bufferpool resize automatically.  You must have STMM turned on at the database configuration level
=>db2 alter bufferpool bp1 size AUTOMATIC
Alter a bufferpool size that is a fixed size
=>db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE 15000;
Add space to a DMS tablespace:
=>db2 "alter tablespace  extend (all 8000)"

DB2 Table and Index Commands

Look at columns and data types of a table
=>db2 describe table schema.tabelename
Look at one column's data type.
=>db2 "describe select from schema.tablename"
Look at indexes on a table:
=>db2 describe indexes for table schema.tablename
Export data to a file in ixf format:
  • =>db2 export to tablename.ixf of ixf messages tablename.txt select * from SCHEMA.TABNAME
Import data from an ixf file:
  • =>db2 import from filename.ixf of ixf messages tablename.txt insert into SCHEMA.TABNAME
Import using Load which is faster (doesn't check constraints) and NONRECOVERABLE doesn't log anything.
  • =>db2 load from filename.ixf of ixf insert into SCHEMA.TABNAME nonrecoverable;
Build a file to set integrity on all tables that are in set integrity pending state.
=>db2 "select 'set integrity for '||tabschema||'.'||tabname ||' immediate checked;' from syscat.tables where type = 'T' and status = 'C'" > file.out

DB2 Security Commands

Build a script to grant user execute privilege on all packages.
=>db2 "select 'db2 grant execute on package '||pkgschema||'.'||pkgname||' to user ;' from syscat.packages where pkgschema = 'NULLID'" > output.file

Security Notes
  • dbadm auth must be revoked before any other database privlege.
  • create_not_fenced_routine must be revoked before create_external_routine

DB2 Backup and Restore Commands

Take an OFFLINE backup 
db2 connect to
db2 quiesce database immediate force connections
db2 connect reset
db2 backup database to compress without prompting
after backup completes:
db2 connect to
db2 unquiesce database
db2 connect reset
Take an ONLINE Backup
db2 backup db to online compress
or use a background process on a linux/unix machine
nohup db2 backup db to online compress &
List recent backups and where they are stored
=>db2 list history backup all for
Check the integrity of a backup image
=>db2ckbkp
Restore from Incremental Backup Automatic
=>db2 restore db  incremental automatic taken at
If you need to restore an incremental backup manually this command will give you the required previous backups.
=>db2ckrst -d -t

DB2 HADR and HACMP Commands

xample script for setting up HADR
Update configuration parameters on primary database -
UPDATE DB CFG FOR DB SAMPLE USING LOGINDEXBUILD ON
UPDATE DB CFG FOR DB SAMPLE USING INDEXREC RESTART
Copy backup image from primary to standby system.

Restore database on standby system
RESTORE DATABASE SAMPLE REPLACE HISTORY FILE WITHOUT PROMPTING
Configure databases for client reroute - Primary
UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USING HOSTNAME  PORT

Configure databases for client reroute -Standby
UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USING HOSTNAME PORT
Edit /etc/services file and create an HADR port on both systems.   On Windows:
notepad %SystemRoot%\system32\drivers\etc\services
Add HADR line on both primary and standby system.  Same port number. ie.   DB2_HADR1     55005
Update HADR configuration parameters on primary database -
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC DB2_HADR1
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC DB2_HADR1
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST DB2
UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC
UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120
Enable cfg changes by forcing off all connections
CONNECT TO SAMPLE
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
UNQUIESCE DATABASE
CONNECT RESET
Update HADR configuration parameters on standby database - --
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC DB2_HADR_14
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST COL27VM
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC DB2_HADR_13
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST DB2
UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC
UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120
Start HADR on standby database
DEACTIVATE DATABASE SAMPLE
START HADR ON DATABASE SAMPLE AS STANDBY
Start HADR on primary database

DEACTIVATE DATABASE SAMPLE
START HADR ON DATABASE SAMPLE AS PRIMARY
View status of HADR
=>db2pd -db -hadr
Swap roles of Primary and Standby
=>db2 takeover hadr on db
Takeover Primary operation on the standby db when the original primary is down
=>db2 takeover hadr on db  by force
Steps for setting up HACMP active/passive
HACMP Active server
1. First create the file .db2nodes.cfg. in the /etc directory which is local to each server and will NOT be failed over to another server, on both servers.
2. Use the local hostname in the /etc/.db2nodes.cfg files on both servers.
3. Make sure permissions for file /etc/.db2nodes.cfg are set to 444 on both servers. 
4. As Instance owner, create a link named db2nodes.cfg from the instance sqllib to this new /etc/.db2nodes.cfg file.
=>ln -fs /etc/.db2nodes.cfg /db2home//sqllib/db2nodes.cfg
5. Make sure you create the .db2nodes.cfg on the same path on both servers, so that link will still work after failover.
6. Also make sure same TCP port is being used/reserved on both servers for the instance service port.
7. Remove db2 startup line from inittabs and provide your own db2 startup and stop scripts.......as root =>rmitab db2start
HACMP Standby server
1.  as root.  vi /etc/.db2nodes.cfg .    chmod 444
permissions on /db2home directory should be 775 and owned by db2as:db2asgrp
Copy /etc/services db2 ports from Active server to /etc/services in Standby server.
Make a file called /var/db2/v81/profiles.reg and add instance names on seperate lines
Copy instance id's .profiles over to standby server.

DB2 Basic Commands

  • Size of your database:
    • =>db2 'call get_dbsize_info(?,?,?,0)'
  • Version of DB2 you are running:
    • =>db2level
  • Stop the db2 instance
    • =>db2stop
  • Stop an instance that has current connections
    • =>db2 force applications all
    • =>db2 deactivate db
    • =>db2 terminate
    • =>db2stop force
    • =>ipclean
  • Start the db2 instance
    • =>db2start
  • Kill a hung instance (last resort)
    • =>db2_kill -all
  • List the databases in an instance
    • =>db2 list db directory
  • List the cataloged nodes
    • =>db2 list node directory
  • Show the database manager configuration settings
    • =>db2 get dbm cfg
  • Show the database level configuration settings
    • =>db2 get db cfg for
  • Activate a database
    • =>db2 activate db
  • Deactivate a database
    • =>db2 deactivate db
  • View the DB2 License
    • =>db2licm -l
  • To switch between partitions
    • =>db2 terminate; export DB2NODE=
  • Wednesday, January 8, 2014

    SCCD 7.5 Quick Configuration on Clustered Environment

    Problem(Abstract)

    Use Quick Configuration received error "TFMSDA0363E - Dataloading application is busy at present processing other dataload requests. Please try again later!"

    Cause

    In clustered environment, if the the system is not configured to use the default connection factory, you need to define one using the mxe.jms.factory.name system property. The standard one is "jms/maximo/int/cf/intcf". However mxe.jms.factory.name need to match the value defined in WebSphere -> Resources ->JMS -> Queue connection factories -> JNDI name. Examples as below -
    Single Node, default settings:



    Clustered Env with 2 Nodes:



    System Property mxe.jms.factory.name:


    JMS Messaging - High availability, scalability and Maximo Integration Framework using a single Service Integration Bus

    T he first is configuring the JMS resources for scalability and the second is configuring the messaging engines for highly availability. For...