SAPTechArena

DB2 Utility Commands 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 <util_id> TO ...

DB2 Tuning 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" ...

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...

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 <colname> 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...

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 <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...

DB2 Backup and Restore Commands Take an OFFLINE backup db2 connect to <dbname> db2 quiesce database immediate force connections db2 connect reset db2 backup database <dbname> to <path> compress without prompting after backup completes: db2 connect to <dbname> db2 unquiesce database db2 connect reset Take an ONLINE Backup db2 backup db <dbname>...

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 <dbname> db2 terminate db2stop force ipclean Start the db2 instance: db2start Kill a hung instance (last resort): db2_kill -all List the...