BEGIN{
NO_APPS  = ENVIRON[ "NO_APPS"   ];
NO_DB    = ENVIRON[ "NO_DB"     ];
headline = "";
dashes   = "------------------------------";
print "";
}

/SQL1611W/{ print }

# Snapshot info at database level
!NO_DB && /Database name /{ print }
!NO_DB && /Database path /{ print }
!NO_DB && /Input database alias /{ print }
!NO_DB && /Locks held / && ! A_HAND{ print }
!NO_DB && /Applications currently connected /{ print }
!NO_DB && /Agents currently waiting on locks /{ print }
!NO_DB && /Snapshot timestamp /{ print ; print "" }

# Application info
/Application handle /{ A_HAND = ""  ;
  A_NAME= ""  ;
  A_USER= ""  ;
  H_HAND= ""  ;
  A_ID= ""  ;
  A_STATUS= ""  ;
  A_LOCKS= ""  ;
  A_WAIT= ""  ;
}
/Application handle /{ A_HAND = $NF }
/Application ID /{ A_ID = $NF ; sub( /\.[0-9]*$/, "", A_ID) }
/Application name /{ A_NAME = $NF }
/Authorization ID /{ A_USER = $NF }
/Application status /{ A_STATUS = substr( $0, index( $0, "=") + 2 )  }
/Status change time /{ A_TIME = $NF }
/Locks held / && A_HAND{ A_LOCKS = $NF }
/Total wait time \(ms\)/{ A_WAIT = $NF }

/Total wait time \(ms\)/ && (! NO_APPS) {

if (A_WAIT == "Collected")  A_WAIT = "";

if (! headline )
{
headline = sprintf( \
"%-10.10s %-8.8s %-6.6s %-20.20s %-15.15s %-5.5s %-7.7s",
"APP.NAME",
"APP.USER",
"HANDLE",
"APP.ID",
"APP.STATUS",
"LOCKS",
"WAIT.ms");

underline = sprintf( \
"%-10.10s %-8.8s %-6.6s %-20.20s %-15.15s %-5.5s %-7.7s",
dashes,
dashes,
dashes,
dashes,
dashes,
dashes,
dashes);

print headline;
print underline;
}
dataline = sprintf( \
"%-10.10s %-8.8s %6.6s %-20.20s %-15.15s %5.5s %s",
A_NAME,
A_USER,
A_HAND,
A_ID,
A_STATUS,
A_LOCKS,
A_WAIT);
print dataline;
}

# Lock info
/ Object Type /{ L_TYPE = ""  ;
  L_SCHEMA = ""  ;
  L_TABLE = ""  ;
  L_MODE = ""  ;
  L_STATUS = ""  ;
  L_ESC = ""  ;
}
/ Object Type /{ L_TYPE = $NF }
/ Table Schema /{ L_SCHEMA = $NF }
/ Table Name /{ L_TABLE = $NF }
/ Mode /{ L_MODE = $NF }
/ Status /{ L_STATUS = $NF }
/ Lock Escalation /{ L_ESC = $NF ;
  LOCKS [ L_SCHEMA ,
L_TABLE  ,
L_TYPE   ,
L_ESC    ,
L_MODE   ,
L_STATUS ] ++;
}

END{
headline = "";
for (ind in LOCKS)
{
if (! headline)
{
headline = sprintf( \
"%-30.30s | %-10.10s | %-3.3s | %-4.4s | %-10.10s | %5.5s",
"TABLE NAME",
"TYPE",
"ESCALATED",
"MODE",
"STATUS",
"COUNT");

underline = sprintf( \
"%-30.30s | %-10.10s | %-3.3s | %-4.4s | %-10.10s | %5.5s",
dashes,
dashes,
dashes,
dashes,
dashes,
dashes);

#underline = headline;
#gsub( /./, "-", underline);

print "" ;
print headline;
print underline;
}

split( ind, LOCK_INFO, SUBSEP);
SCHEMA_TABLE = LOCK_INFO[1] "." LOCK_INFO[2];
dataline = sprintf( \
"%-30.30s | %-10.10s | %-3.3s | %-4.4s | %-10.10s | %5.5s",
SCHEMA_TABLE,
LOCK_INFO[3],
LOCK_INFO[4],
LOCK_INFO[5],
LOCK_INFO[6],
LOCKS[ind]);
print dataline;
}

if (! headline)
{
print "";
print "*** NO LOCKS ***";
}
}

#Database name                              = SAMPLE
#Database path                              = /data/db2inst1/NODE0000/SQL00001/
#Input database alias                       = SAMPLE
#Locks held                                 = 37
#Applications currently connected           = 2
#Agents currently waiting on locks          = 0
#Snapshot timestamp                         = 05-30-2000 22:30:38.093858

#Application handle                         = 1
#Application ID                             = *LOCAL.db2inst1.000531042615
#Sequence number                            = 0001
#Application name                           = db2bp
#Authorization ID                           = DB2INST1
#Application status                         = UOW Waiting
#Status change time                         =
#Application code page                      = 819
#Locks held                                 = 37
#Total wait time (ms)                       = 0

#List Of Locks
# Lock Object Name            = 38
# Node number lock is held at = 0
# Object Type                 = Row
# Tablespace Name             = USERSPACE1
# Table Schema                = DB2INST1
# Table Name                  = STAFF
# Mode                        = X
# Status                      = Granted
# Lock Escalation             = NO