AIX – OS
General stuff
http://www.scribd.com/doc/2309604/AIX-Disk-Tuning-Guidelines
1. Avoid disk I/O where possible. Turn off un-necessary logging.
2. Spread I/O over as many disks and adapters as economically feasible
3. Locate most active partitions at optimum spot on the disk – How?
4. Isolate most active partitions on their own disk
5. Randomize the sequencing of LV partitions across disks
6. Rule of thumb – There is a problem if
a. vmstat shows “wa” column averaging over 20-30% (wa = processor idle time due to disk / NFS I/O requests)
b. iostat shows %tm_act column averaging over 50-60% (tm_act = % of time the physical disk/tape was active. (bandwidth utilization for the drive) )
c. from iostat results: dividing Kbps by the tps gives the average disk transfer size(adts, not an official ETLA). This can be used to check if I/O is random ( adts 4K)
d. Formula to calculate Nbr of Disks for No Raid config – N = TPS / IOS (IOS=60 for random access and 1400 for serial access). For Raid see url..
Tools
nmon

DB2
General
Using BUFFPAGE parameter is a good thing. (See Details)
Tools
1. db2look – used to look at the schema. Can generate the DDL for selected objects.
db2look -d $DBLOC -i $DBUID -w $DBUID -t
-e > ddl_for_table.txt
db2look -d $DBLOC -i $DBUID -w $DBUID -e > ddl_for_schema.txt
2. db2 – This can launch an interactive shell or can be used to fire sqls directly or special commands. Depending on the node you are logged on some commands will not work
db2 “select * from item_master” > all_items.txt
db2 -x list applications (lists all the active connections, requires to be executed on the DB server)
db2 get snapshot for locks on (get current locks on the DB, DB Server only)
db2 get snapshot for all on (get all the snapshots, results can get huge!!, DB Server only)
db2 “alter table trans_invn alter column trans_invn_type_char drop expression”
db2 attach to user using (to change default node associated with a login into the DB Server)
3. db2advis – Tuning advisor (DB Server only). Makes recommendations for sql performance tuning. Note, this most likely _always_ recommends something. So we do need to cross check whether we need to do everything this tool advises. But it is a good starting point\
db2advis -d $DBLOC -i -t
4. db2exfmt – Formatter (DB Server only)5. db2expln – Explains plan.
db2expln -d $DBLOC -u $DBUID $DBPWD -o -f [-z delimiter -g Visual Graph]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.cmd.doc/doc/r0005736.html

6. Catalog commands
a. Db2 catalog tcpip node remote server
b. Db2 catalog db as at node

Details
http://www-128.ibm.com/developerworks/db2/library/techarticle/anshum/0107anshum.html
Running stats on all the table and indexes
db2 -v connect to DB_NAME
db2 -v “select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes”
db2 -v reorgchk update statistics on table all
db2 -v “select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes order by stats_time”
db2 -v terminate

Running stats for one table
db2 -v runstats on table TAB_NAME and indexes all

Checking if stats have been run recently
db2 -v “select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes”

Determine whether BUFFPAGE is being used
db2 -v connect to DB_NAME
db2 -v SELECT * from SYSCAT.BUFFERPOOLS
db2 -v connect reset
db2 -v terminate

Edit settings for BUFFPAGE
db2 -v connect to DB_NAME
db2 -v select * from syscat.bufferpools
db2 -v alter bufferpool IBMDEFAULTBP size -1
db2 -v connect reset
db2 -v update db cfg for dbname using BUFFPAGE bigger_value
db2 -v terminate

Verifying Buffer Pool is sufficient
db2 -v update monitor switches using bufferpool on
db2 -v get monitor switches db2 -v reset monitor all
– run your application –
db2 -v get snapshot for all databases > snap.out
db2 -v get snapshot for dbm >> snap.out
db2 -v get snapshot for all bufferpools >> snap.out
db2 -v reset monitor all db2 -v terminate

Buffer Hit Ratio (BHR) = (1 – ((buffer pool data physical reads + buffer pool index physical reads) / (buffer pool data logical reads + pool index logical reads) )) * 100%
Ideally BHR should be greater than 95%

LOGBUFSZ parameter….

db2 connect to user using

http://www.cse.yorku.ca/course_archive/2006-07/W/6421/db2notes/import.html