Trc2Sql.pl  

 Trc2sql.pl       

#!/usr/contrib/bin/perl -na

######################################################
# This script will read an trace produce by PS Cobol, or PS PeopleCode
# and produce SQL output, with bind variables replaced. Theoreticaly,
# this SQL could be run in SQLPLUS,... of course this depends upon
# the process you have traced, and what problem(s) it encountered.
#
# The $type variable could be replaced with a prompt, but for now
# it is hard coded to either COB_TRACE or PCODE_TRACE. Adjust
# accordingly.
#
# Another enhancement would be to make a nice frontend for this,...
# either using VC++, java, or PerlScript. Just a thought.

#   6/30/03  Modified for 8.4 Logs,... not sure if binding was working before.
#######################################################

$type = "PCODE_TRACE";
$cursor = $F[3] if $type eq "COB_TRACE";
$cursor = $F[1] if $type eq "PCODE_TRACE";
if ($. eq 1)
{
print "WHENEVER SQLERROR EXIT SQL.SQLCODE rollback\n";
print "set echo on\n";
print "spool on\n";
}

if (eof() || $_ =~ "CEX Stmt" || $_ =~ "COM Stmt")
{
print $tstmt."/\n" if $. gt 2;
$stmt{$cursor} = substr($_, index($_, "Stmt=") + 5);
$tstmt = $stmt{$cursor};
$tstmt =~ s/ FROM /\nFROM /g;
$tstmt =~ s/ from /\nFROM /g;
$tstmt =~ s/ SET /\nSET /g;
$tstmt =~ s/WHERE/\nWHERE/g;
$tstmt =~ s/where/\nWHERE/g;
$tstmt =~ s/ORDER BY/\nORDER BY/g;
$tstmt =~ s/VALUES /\nVALUES /g;
$tstmt =~ s/ AND /\n AND /g;
$tstmt =~ s/ and /\n AND /g;
$tstmt =~ s/ OR /\n OR /g;
$tstmt =~ s/ or /\n OR /g;
$tstmt =~ s/,/\n,/g;
}

if ( $_ =~ "Bind-")
{
$srch = ":".substr($F[7], 5);
chop($srch) if $type eq "COB_TRACE";
$repl = "'".substr($F[10], 6)."'";
$repl = "' '" if length($repl) eq 2;
$repl = substr($F[9], 6) if $_ =~ "SQLPSPD"; #Precision specified
$repl = substr($F[8], 6) if $_ =~ "SQLPSLO"; #Numeric
$repl = substr($F[8], 6) if $_ =~ "SQLPSSH"; #Numeric
# printf "Searching: %s Replace with: %s\n", $srch, $repl;
$tstmt =~ s/$srch/$repl/;