#!/bin/ksh
###
# Written in response to 'transaction log full' SQL0964C:
# Report transaction log utilisation and capacity; 
# select details of the application holding the oldest uncommitted transaction; 
# and ensuing lock chains from the oldest transaction, if any.
# Simply run from the command line as logfull !
# 
# Assumption  : this is run through the instance owner. Place this under $INSTHOME with execute permission. 
# Dependencies: $INSTHOME/sqllib/userprofile export of $DBALIAS.
#             : $INSTHOME/bin/logfull.sql (see setup below)  
# Alex Levy t/a http://sustainablesoftware.net, 20.05.2008
###

. ~$DB2INSTANCE/sqllib/db2profile

# the ~/bin directory is in the Unix PATH variable by default; 
# add a symlink here, so that the command line call is shortened to: logfull
test -d ~$DB2INSTANCE/bin || mkdir -m 755 ~/DB2INSTANCE/bin
test -L ~$DB2INSTANCE/bin/logfull || ln -s ~$DB2INSTANCE/ksh/logfull ~$DB2INSTANCE/bin/logfull

db2 connect to $DBALIAS > /dev/null # the db2profile calls $INSTHOME/sqllib/userprofile which exports $DBALIAS

db2 -o +x -tf $INSTHOME/bin/logfull.sql

v_applid=$(db2 -x select appl_id_oldest_xact from sysibmadm.snapdb)
db2 "call sysproc.am_get_lock_chns($v_applid,?)"

db2 connect reset > /dev/null


# SETUP: cut and paste this into $INSTHOME/bin/logfull.sql
echo     ; 
echo                     TRANSACTION LOG UTILISATION REPORT                             ;
echo  ================================================================================= ; 
echo     ; 

select lu.log_utilization_percent                       as "used %",
       integer(lu.total_log_used_kb / 1024)             as "used (Mb)",
       integer(lu.total_log_used_top_kb / 1024)         as "HWM (Mb)",
       integer(sdb.total_log_available / 1024 / 1024)   as "Available (Mb)",
       integer(sdb.log_held_by_dirty_pages /1024 /1024) as "dirty pages (Mb)",
       smallint(sdb.sec_logs_allocated)                 as "Secondary logs",
       integer(sdl.first_active_log)                    as "First Active Log",
       integer(sdl.current_active_log)                  as "Current Active Log",
       integer(sdl.last_active_log)                     as "Last Active Log", 
       integer(sdb.appl_id_oldest_xact)                 as "Oldest Transaction"
from   sysibmadm.log_utilization lu
                 inner join
       sysibmadm.snapdb sdb
                 on  lu.db_name = sdb.db_name
                 and lu.dbpartitionnum = sdb.dbpartitionnum
                 inner join
       sysibmadm.snapdetaillog sdl
                 on  lu.db_name = sdl.db_name
                 and lu.dbpartitionnum = sdl.dbpartitionnum
;
echo   ; 
echo           APPLICATION HOLDING OLDEST UNCOMMITTED TRANSACTION REPORT       ; 
echo ================================================================================== ; 
echo   ; 

select integer(sdb.appl_id_oldest_xact)        as "Oldest Transaction",
       integer(sa.uow_log_space_used /1024 / 1024)as "Log used(Mb)",
       integer(sa.locks_held)                  as "Locks Held",
       integer(sa.appl_idle_time)              as "Idle (seconds)",
       time(sa.uow_stop_time)                  as "UOW Stop Time",
       sa.rows_selected                        as "Rows Selected",
       sa.rows_read                            as "Rows Read",
       integer(sa.rows_inserted)               as "Rows Inserted",
       integer(sa.rows_updated)                as "Rows Updated",
       integer(sa.rows_deleted)                as "Rows Deleted"
from
       sysibmadm.snapdb   sdb
                 inner join
       sysibmadm.snapappl sa
                 on  sa.db_name = sdb.db_name
                 and sa.dbpartitionnum = sdb.dbpartitionnum
                 and sa.agent_id = sdb.appl_id_oldest_xact
;

echo  ; 
echo  Output from db2 "call sysproc.am_get_lock_chns(agent_id,?)" ;
echo  to see dependencies on the oldest uncommitted transaction.;
echo  =========================================================== ;
echo  ;