BEGIN{
OS          = "AIX";
QUERIES    = ENVIRON[ "QUERIES"     ];
DISPLAY_SQL = ENVIRON[ "DISPLAY_SQL" ];
SAVE_SQL    = ENVIRON[ "SAVE_SQL"    ];
SQL_ONLY    = ENVIRON[ "SQL_ONLY"    ];
LAST_QUERY  = ENVIRON[ "LAST_QUERY"  ];
TMP_SQL     = ENVIRON[ "TMP_SQL"     ];
INCLUDE_OPT = ENVIRON[ "INCLUDE_OPT" ];

#       S=Start-Time ; O=Operation ; T=Timing ; R=Row-Counts; X=Sort-Info;N=None
if (INCLUDE_OPT != "N")
{
if (INCLUDE_OPT ~ /S/) OPT_ST_TIME = 1;
if (INCLUDE_OPT ~ /O/) OPT_OPER    = 1;
if (INCLUDE_OPT ~ /T/) OPT_TIMES   = 1;
if (INCLUDE_OPT ~ /R/) OPT_ROWS    = 1;
if (INCLUDE_OPT ~ /X/) OPT_SORTS   = 1;
}

STMT_NUM    = 0;
MIL         = 1000000;
CON_REC     = 0;
header      = 0;
h=0;  # Counter of Handles
}

/) Connection Header Event .../{ CON_REC=1;            }
/  Appl Handle: / && CON_REC{ HAND[      ++h] = $NF ; H=$NF ;
  CA_HAND[    H ] = $NF}
/  Appl Id: /&& CON_REC{ CA_ID[      H ] = $NF}
/  Appl Seq number: /&& CON_REC{ CA_SEQ[     H ] = $NF}
/  Program Name    : /{ C_PROG[     H ] = $NF}
/  Authorization Id: /{ C_DBUSER[   H ] = $NF}
/  Execution Id    : /{ C_CLUSER[   H ] = $NF}
/  Client Process Id: /{ C_CLPROC[   H ] = $NF}
/  Client Database Alias: /{ C_DBNAME[   H ] = $NF}
/  Client Communication Protocol: /{ C_PROTOCOL[ H ] = $NF}
/  Client Network Name: /{ C_NET[      H ] = $NF}
/  Connect timestamp: /{ C_TIME[     H ] = $(NF-1) $NF }
####################################################################
/) Statement Event .../{ CON_REC       = 0       ;
  A_HANDLE= ""  ;
                                          A_ID= ""  ;
                                          A_SEQ= ""  ;
                                          S_TYPE= ""  ;
                                          S_OPER= ""  ;
                                          P_SECTION= ""  ;
                                          P_CREATOR= ""  ;
                                          P_PACKAGE= ""  ;
                                          S_CURSOR= ""  ;
                                          S_BLOCKED= ""  ;
                                          S_TEXT = ""  ;
                                          S_TYPE = ""  ;
                                          S_START_T= ""  ;
                                          S_EXEC_T= ""  ;
                                          S_AGENTS= ""      ;
                                          S_U_CPU_T= ""      ;
                                          S_S_CPU_T= ""      ;
                                          S_FETCHES= ""      ;
                                          S_SORTS= ""      ;
                                          S_SORT_T= ""      ;
                                          S_SORT_OFLOWS = ""      ;
                                          S_ROWS_READ= ""      ;
                                          S_ROWS_WRITTEN= ""      ;
                                          S_SQLCODE= ""      ;
}
#) Statement Event ...
/  Appl Handle: /&& ! CON_REC{ A_HANDLE= $NF  }
/  Appl Id: /&& ! CON_REC{ A_ID = substr( $NF, length($NF)-4) }
/  Appl Seq number: /&& ! CON_REC{ A_SEQ= $NF  }
#  -------------------------------------------
/  Type     : /{ S_TYPE= $NF  }
/  Operation: /{ S_OPER = substr( $0, 14)}
/  Section  : /{ P_SECTION= $NF  }
/  Creator  : /{ P_CREATOR= $NF  }
/  Package  : /{ P_PACKAGE= $NF  }
/  Cursor   : /{ S_CURSOR= $NF  }
/  Cursor was blocking: /{ S_BLOCKED= $NF  }
/  Text     : /{ S_TEXT = substr( $0, 14)}
#  -------------------------------------------
/  Operation: Static Commit/{ S_TYPE = "Static";
  S_OPER= "Commit"}
/  Operation: Execute Immediate/{ S_OPER= "ExecImmed"}
#  -------------------------------------------
/  Start Time: /{ S_START_T= $NF  }
#  Stop Time:  05-16-2000 18:25:00.650866 - for datetime: $(NF-1) $NF }
/  Exec Time: /{ S_EXEC_T= $(NF-1) } #Sec
/  Number of Agents created: /{ S_AGENTS= $NF     }
/  User CPU: /{ S_U_CPU_T= $(NF-1) } #Sec
/  System CPU: /{ S_S_CPU_T= $(NF-1) } #Sec
/  Fetch Count: /{ S_FETCHES= $NF  }
/  Sorts: /{ S_SORTS= $NF  }
/  Total sort time: /{ S_SORT_T= $NF  } #Msec
/  Sort overflows: /{ S_SORT_OFLOWS = $NF  }
/  Rows read: /{ S_ROWS_READ= $NF  }
/  Rows written: /{ S_ROWS_WRITTEN= $NF  }
#  Internal rows deleted: 0
#  Internal rows updated: 0
#  Internal rows inserted: 0
#  SQLCA:
/   sqlcode: /{ S_SQLCODE= $NF  }
#   sqlstate: 00000

/   sqlstate: /{  # Begin Display

STMT_NUM=STMT_NUM + 1;
SQL_OUT="";

if (DISPLAY_SQL == -1)
SQL_LEN = length( S_TEXT);
else
SQL_LEN = DISPLAY_SQL;

# Save SQL Text to a file, or get name of existing (duplicate) file
if ( S_TEXT && SAVE_SQL )  Save_Sql();

headline = "";
dataline = "";

if ( SQL_ONLY )
{
if (SAVE_SQL)S_Save_Info();
}
else
{
S_App_Info();
if (SAVE_SQL)S_Save_Info();
if (OPT_OPER) S_Oper_Info();
if (OPT_TIMES) S_Timing_Info();
if (OPT_ROWS)  S_Rows_Info();

if (OPT_SORTS)
{
# When all options displayed, remove '|' to fit in 132
if (INCLUDE_OPT ~ /[SORTX]{5}/)
{
rm_Trailing();
headline = headline " ";
dataline = dataline " ";
}
S_Sort_Info();
}
}

S_SQL_Text();

rm_Trailing();

if ( headline && ! header)
{
for (i=1; i<=length( headline); i++)
underline = underline "-";

print headline;
print underline;
header = 1;
}

if (dataline) print dataline;
}

################################################################################
function S_App_Info() {

headline = headline sprintf( \
"%4.4s %5.5s %-4.4s",
"Hand",
"AppID",
"Seq");
dataline = dataline sprintf( \
"%4s %5s %4s",
A_HANDLE,
A_ID,
A_SEQ);

if (OPT_ST_TIME)
{
headline = headline sprintf( \
" %-15.15s",
"Start-Time");
dataline = dataline sprintf( \
" %15s",
S_START_T);
}

headline = headline " | ";
dataline = dataline " | ";
}

################################################################################
function S_Save_Info() {

headline = headline sprintf( \
"%8.8s | ",
"SQL-File");
if ( ( ! SQL_ONLY ) || ( SQL_ONLY && S_TEXT ) )
dataline = dataline sprintf( \
"%8s | ",
SQL_OUT);
}

################################################################################
function  S_Oper_Info() {

headline = headline sprintf( \
"%-2.2s %-9.9s %5.5s | ",
"Type",
"Oper",
"Code");
dataline = dataline sprintf( \
"%-2.2s %-9s %5s | ",
S_TYPE,
S_OPER,
S_SQLCODE );
}

################################################################################
function S_Timing_Info() {

headline = headline sprintf( \
"%8.8s %8.8s %8.8s | ",
"Exec(s)",
"UCPU(s)",
"SCPU(s)");
dataline = dataline sprintf( \
"%8s %8s %8s | ",
substr( S_EXEC_T, 1, 8),
substr( S_U_CPU_T, 1, 8),
substr( S_S_CPU_T, 1, 8));
}

################################################################################
function S_Rows_Info() {

headline = headline sprintf( \
"%9.9s %9.9s %5.5s | ",
"Read",
"Written",
"Fetch");
dataline = dataline sprintf( \
"%9s %9s %5s | ",
S_ROWS_READ,
S_ROWS_WRITTEN,
S_FETCHES);
}

################################################################################
function S_Sort_Info() {

headline = headline sprintf( \
"%5.5s %5.5s %9.9s | ",
"Sorts",
"SOVFL",
"Sort(ms)");
dataline = dataline sprintf( \
"%5s %5s %9s | ",
S_SORTS,
S_SORT_OFLOWS,
S_SORT_T);
}

################################################################################
function S_SQL_Text() {

if ( DISPLAY_SQL )
headline = headline sprintf( \
"%-*s | ",
SQL_LEN, "SQL-Text");
if (S_TEXT && SQL_LEN)
dataline = dataline sprintf( \
"%-*.*s | ",
SQL_LEN, SQL_LEN, S_TEXT);

# exception to rm_Trailing, force removal of seperator when no SQL text
# since this is always the last entry on the line
if (S_TEXT == "")
dataline = substr( dataline, 1, (len - 3) );
}

################################################################################
function rm_Trailing() {
# Get rid of trailing separator

if ( substr( headline, (len=length( headline))-2) == " | " )
{
headline = substr( headline, 1, (len - 3) );
dataline = substr( dataline, 1, (len - 3) );
}
}

################################################################################
function Save_Sql() {
# Save SQL Text to a file, or get name of existing (duplicate) file

DUPFILE="";
print S_TEXT > TMP_SQL;
close( TMP_SQL);

# Check if a duplicate SQL file exists
if ( OS == "AIX" )
{
# Get filesize of TMP_SQL
"ls -l " TMP_SQL | getline DIRLINE;
close( "ls -l " TMP_SQL );
DFN=split( DIRLINE, DIRARRAY);
FILESIZE=DIRARRAY[5];

# Loop thru sql files in reverse creation order
# Compare only if same filesize
while ( (DUPFILE == "") &&
("ls -lt " QUERIES "/[0-9]*.sql" | getline DIRLINE ) )
{
DFN=split( DIRLINE, DIRARRAY);
if ((DIRARRAY[5] == FILESIZE) &&
    (TMP_SQL != DIRARRAY[DFN])  )
{
"diff " TMP_SQL " " DIRARRAY[DFN]   \
" >/dev/null 2>&1 && "              \
"basename "  DIRARRAY[DFN] | \
getline DUPFILE;
close(  "diff " TMP_SQL " " DIRARRAY[DFN]   \
" >/dev/null 2>&1 && "              \
"basename "  DIRARRAY[DFN] );
}
}
close( "ls -lt " QUERIES "/[0-9]*.sql" );

system( "rm " TMP_SQL);
}
else# Untested
{
# delete TMP_SQL
}

if ( DUPFILE != "" )
{  # A duplicate SQL file was found
SQL_OUT = DUPFILE;
}
else
{  # Create SQL file
LAST_QUERY=LAST_QUERY + 1;
SQL_OUT=LAST_QUERY ".sql";
print S_TEXT > QUERIES "/" SQL_OUT;
}
}