Re: Cobol Trace conversion

Re: Cobol Trace conversion

[ Follow Ups ] [ Post Followup ] [ Message Board ]

Posted by [IP Address: 132.170.238.171] 'Bradley Smith' on September 04, 2001 at 12:30:12 EST:

In Reply to: Cobol Trace conversion posted by [IP Address: 132.170.238.171] 'Ben Barnet' on August 23, 2001 at 09:18:44 EST:

: Hi,

: I have used a utility at a previous site that converts a cobol *.trc file to simple SQL. The file was trc2sql.exe I believe.

: Does anyone know where I can find this file rather urgently ?

: Thanks

: Ben

Here are a couple of different things I found and wrote.

====Perl Program to read trace files=====
#!/usr/local/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.
########################################################################

$type = "PCODE_TRACE";
$line_no = $F[0];
$cursor = $F[3] if $type eq "COB_TRACE";
$cursor = $F[2] 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/ SET /\nSET /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/ OR /\n OR /g;
$tstmt =~ s/,/\n,/g;
}

if ( $_ =~ "Bind-")
{
$srch = ":".substr($F[5], 5);
chop($srch) if $type eq "COB_TRACE";
$repl = "'".substr($F[8], 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/;
}

$last_cursor = $cursor;
=========
========awk program to read trace files========
BEGIN{
SEARCH_EMPLID = ""; #if "" then print all
SEARCH_FIELD = ""; # leave null for all
for(i=0; i < 256; i++) {
Statement[i] = "";
SQL[i] = "";
emplid_bind[i] = 0;
sqltype[i] = "";
}
}
{
printf("\nLINE=%s\n\n", $0);
Cursor = sprintf("%d", substr($4, 2,3) + 0);
printf("%d, Cursor =%d, %s\n", NF, Cursor, $0);
if( ($6 ~ /^Connect=/) && ($6 ~/SYSADM/) ) {
getline;
if ($6 == "GETSTMT") {
Statement[Cursor] = $7;
SQL[Cursor] = "";
emplid_bind[Cursor] = 0;
#printf("Cursor %d, Statment = %s\n", Cursor, Statement[Cursor]);
getline;
for(i = 7; i <= NF; i++ ) {
SQL[Cursor] = sprintf("%s%s ", SQL[Cursor], $i);
}
#printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]);
look_for_field(SQL[Cursor]);
} else if ($6 = "SIL") {
getline; # gets commit
getline; # gets the line before meta-sql expanision
Statement[Cursor] = $7;
getline; # gets the actual line
#printf("SIL: Cursor %d, Statment = %s\n", Cursor, Statement[Cursor]);
getline;
for(i = 7; i <= NF; i++ ) {
SQL[Cursor] = sprintf("%s%s ", SQL[Cursor], $i);
}
#printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]);
look_for_field(SQL[Cursor]);
} else {
printf("didn't find GETSTMT\n");
printf("%s\n", $0);
exit;
}
#if(emplid_bind[Cursor] != 0) {
# printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]);
# printf("EMPLID Bind: %s\n\n\n", emplid_bind[Cursor] );
#}
}else if ($6 == "GETSTMT") { #sometimes just a GETSTMT
Statement[Cursor] = $7;
SQL[Cursor] = "";
emplid_bind[Cursor] = 0;
#printf("Cursor %d, Statment = %s\n", Cursor, Statement[Cursor]);
getline;
for(i = 7; i <= NF; i++ ) {
SQL[Cursor] = sprintf("%s%s ", SQL[Cursor], $i);
}
#printf("%d: SQL[%d]: %s\n", NR, Cursor, SQL[Cursor]);
look_for_field(SQL[Cursor]);
} else if ($6 ~ /^Bind/) { # got us a Bind
#printf("Got Bind on Cursor %d, emplid_bind[%d] = %d\n", Cursor, Cursor, emplid_bind[Cursor]);
#printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]);
#printf("Line: %s\n", $0);
for(i=1;i< 256;i++) {
bind_line[i] = "";
}
bind_cnt = 1;
if (emplid_bind[Cursor] > 0) { # statement has a emplid = in where
lookfor = sprintf("Bind-%d,", emplid_bind[Cursor]);
NewCursor = Cursor;
#printf("lookfor %s, $6 = %s, NewCursor = %d, Cursor = %d\n\n", lookfor, $6, NewCursor, Cursor);
while(NewCursor == Cursor) {
#printf("Line: %s\n", $0);
#printf("lookfor '%s', $6 = '%s', NewCursor = %d\n\n", lookfor, $6, NewCursor);
if($6 == lookfor) {
#printf("Found lookfor\n\n");
i = index($0, "value=");
#printf("value index = %d\n\n", i);
if(i > 0) {
t_emplid = substr($0, (i + 6));
#printf("t_emplid = %s\n\n", t_emplid);
#printf("%d: %s\n", NR, $0);
if ((SEARCH_EMPLID == "") || (SEARCH_EMPLID == t_emplid)) {
#printf("sqltype = '%s'\n", sqltype[Cursor]);
printf("%d: SQL[%d]: %s\n", NR , Cursor, SQL[Cursor]);
bind_line[bind_cnt] = $0;
bind_cnt++;
for(i=1; i < bind_cnt; i++) {
ii = index(bind_line[i], "value=");
i_value = substr(bind_line[i], (ii + 6));
printf("Bind Value: %d - %s\n", i, i_value);
}
getline;
NewCursor = sprintf("%d", substr($4, 2,3) + 0);
while ((NewCursor == Cursor) && ($6 ~ /^Bind/) ) {
ii = index($0, "value=");
i_value = substr($0, (ii + 6));
printf("Bind Value: %d - %s\n", bind_cnt, i_value);
bind_cnt++;

getline;
NewCursor = sprintf("%d", substr($4, 2,3) + 0);
}
printf("\n");

}
}
break;
} else {
bind_line[bind_cnt] = $0;
bind_cnt++;
getline;
NewCursor = sprintf("%d", substr($4, 2,3) + 0);
}
}
}
}

}
function look_for_field (sqlstring) {
sqlstring = toupper(sqlstring); # just make sure we are all uppercase
str_type = substr(sqlstring, 6);
i_str = split(str_type, str_array, " ");
sqltype[Cursor] = str_array[1];
#printf("sqltype[%d] = %s\n\n", Cursor, sqltype[Cursor]);
i_where = index(sqlstring, "WHERE ");
if(i_where > 0) {
where_clause = substr(sqlstring, (i_where + 6) );
if ( (SEARH_FIELD != "" ) && (where_clause ~ SEARCH_FIELD) ) {
i_fields = split(where_clause, where_array," ");
i_bind = 1;
for(i = 1; (i <= i_fields) ; i++) {
#printf("%i: where_array=%s\n", i, where_array[i]);
if(index(where_array[i], SEARCH_FIELD)) {
#printf("HAS EMPLID: %s\n", where_array[i]);
if(substr(where_array[i], 7, 1) == "=") { #as some funky format
x = index(where_array[i],":") + 1;

emplid_bind[Cursor] = substr(where_array[i], x);
#printf("2-emplid_bind[%d] = %d\n", Cursor, emplid_bind[Cursor]);
return;

} else {
emplid_bind[Cursor] = substr(where_array[i+2], 2);
#printf("1-emplid_bind[%d] = %d\n", Cursor, emplid_bind[Cursor]);
return;
}
}
if((where_array[i] = "AND") || (where_array_[i] = "OR")) {
i_bind++;
}
}
}else if (SEARCH_FIELD == "") {
emplid_bind[Cursor] = 1;
}
}
if(index(sqlstring,"INSERT INTO")) {
if ( (SEARCH_FIELD != "" ) && ( sqlstring ~ SEARCH_FIELD ) ) {
#printf("Contains search field: %s\n", sqlstring);
i_paren = index(sqlstring, "(");
i_fields = split(substr(sqlstring, (i_paren +1 )), insert_into," ");
i_bind = 1;
i_stop = 1;
for(i = 1; (i <= i_fields) || (i_stop == 0); i++) {
#printf("%i: insert_into=%s\n", i, insert_into[i]);
if(index(insert_into[i], SEARCH_FIELD)) {
#printf("HAS EMPLID: %s - %d\n", insert_into[i], i);
emplid_bind[Cursor] = i;
return;
}
}
}else if (SEARCH_FIELD == "") {
emplid_bind[Cursor] = 1;
}
}

}
===============================

Follow Ups:

Post a Followup

Name:
E-Mail:
Subject:
Message: : : Hi, : : I have used a utility at a previous site that converts a cobol *.trc file to simple SQL. The file was trc2sql.exe I believe. : : Does anyone know where I can find this file rather urgently ? : : Thanks : : Ben : Here are a couple of different things I found and wrote. : ====Perl Program to read trace files===== : #!/usr/local/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. : ######################################################################## : $type = "PCODE_TRACE"; : $line_no = $F[0]; : $cursor = $F[3] if $type eq "COB_TRACE"; : $cursor = $F[2] 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/ SET /\nSET /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/ OR /\n OR /g; : $tstmt =~ s/,/\n,/g; : } : if ( $_ =~ "Bind-") : { : $srch = ":".substr($F[5], 5); : chop($srch) if $type eq "COB_TRACE"; : $repl = "'".substr($F[8], 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/; : } : $last_cursor = $cursor; : ========= : ========awk program to read trace files======== : BEGIN{ : SEARCH_EMPLID = ""; #if "" then print all : SEARCH_FIELD = ""; # leave null for all : for(i=0; i < 256; i++) { : Statement[i] = ""; : SQL[i] = ""; : emplid_bind[i] = 0; : sqltype[i] = ""; : } : } : { : printf("\nLINE=%s\n\n", $0); : Cursor = sprintf("%d", substr($4, 2,3) + 0); : printf("%d, Cursor =%d, %s\n", NF, Cursor, $0); : if( ($6 ~ /^Connect=/) && ($6 ~/SYSADM/) ) { : getline; : if ($6 == "GETSTMT") { : Statement[Cursor] = $7; : SQL[Cursor] = ""; : emplid_bind[Cursor] = 0; : #printf("Cursor %d, Statment = %s\n", Cursor, Statement[Cursor]); : getline; : for(i = 7; i <= NF; i++ ) { : SQL[Cursor] = sprintf("%s%s ", SQL[Cursor], $i); : } : #printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]); : look_for_field(SQL[Cursor]); : } else if ($6 = "SIL") { : getline; # gets commit : getline; # gets the line before meta-sql expanision : Statement[Cursor] = $7; : getline; # gets the actual line : #printf("SIL: Cursor %d, Statment = %s\n", Cursor, Statement[Cursor]); : getline; : for(i = 7; i <= NF; i++ ) { : SQL[Cursor] = sprintf("%s%s ", SQL[Cursor], $i); : } : #printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]); : look_for_field(SQL[Cursor]); : } else { : printf("didn't find GETSTMT\n"); : printf("%s\n", $0); : exit; : } : #if(emplid_bind[Cursor] != 0) { : # printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]); : # printf("EMPLID Bind: %s\n\n\n", emplid_bind[Cursor] ); : #} : }else if ($6 == "GETSTMT") { #sometimes just a GETSTMT : Statement[Cursor] = $7; : SQL[Cursor] = ""; : emplid_bind[Cursor] = 0; : #printf("Cursor %d, Statment = %s\n", Cursor, Statement[Cursor]); : getline; : for(i = 7; i <= NF; i++ ) { : SQL[Cursor] = sprintf("%s%s ", SQL[Cursor], $i); : } : #printf("%d: SQL[%d]: %s\n", NR, Cursor, SQL[Cursor]); : look_for_field(SQL[Cursor]); : } else if ($6 ~ /^Bind/) { # got us a Bind : #printf("Got Bind on Cursor %d, emplid_bind[%d] = %d\n", Cursor, Cursor, emplid_bind[Cursor]); : #printf("SQL[%d]: %s\n", Cursor, SQL[Cursor]); : #printf("Line: %s\n", $0); : for(i=1;i< 256;i++) { : bind_line[i] = ""; : } : bind_cnt = 1; : if (emplid_bind[Cursor] > 0) { # statement has a emplid = in where : lookfor = sprintf("Bind-%d,", emplid_bind[Cursor]); : NewCursor = Cursor; : #printf("lookfor %s, $6 = %s, NewCursor = %d, Cursor = %d\n\n", lookfor, $6, NewCursor, Cursor); : while(NewCursor == Cursor) { : #printf("Line: %s\n", $0); : #printf("lookfor '%s', $6 = '%s', NewCursor = %d\n\n", lookfor, $6, NewCursor); : if($6 == lookfor) { : #printf("Found lookfor\n\n"); : i = index($0, "value="); : #printf("value index = %d\n\n", i); : if(i > 0) { : t_emplid = substr($0, (i + 6)); : #printf("t_emplid = %s\n\n", t_emplid); : #printf("%d: %s\n", NR, $0); : if ((SEARCH_EMPLID == "") \|\| (SEARCH_EMPLID == t_emplid)) { : #printf("sqltype = '%s'\n", sqltype[Cursor]); : printf("%d: SQL[%d]: %s\n", NR , Cursor, SQL[Cursor]); : bind_line[bind_cnt] = $0; : bind_cnt++; : for(i=1; i < bind_cnt; i++) { : ii = index(bind_line[i], "value="); : i_value = substr(bind_line[i], (ii + 6)); : printf("Bind Value: %d - %s\n", i, i_value); : } : getline; : NewCursor = sprintf("%d", substr($4, 2,3) + 0); : while ((NewCursor == Cursor) && ($6 ~ /^Bind/) ) { : ii = index($0, "value="); : i_value = substr($0, (ii + 6)); : printf("Bind Value: %d - %s\n", bind_cnt, i_value); : bind_cnt++; : : getline; : NewCursor = sprintf("%d", substr($4, 2,3) + 0); : } : printf("\n"); : : } : } : break; : } else { : bind_line[bind_cnt] = $0; : bind_cnt++; : getline; : NewCursor = sprintf("%d", substr($4, 2,3) + 0); : } : } : } : } : } : function look_for_field (sqlstring) { : sqlstring = toupper(sqlstring); # just make sure we are all uppercase : str_type = substr(sqlstring, 6); : i_str = split(str_type, str_array, " "); : sqltype[Cursor] = str_array[1]; : #printf("sqltype[%d] = %s\n\n", Cursor, sqltype[Cursor]); : i_where = index(sqlstring, "WHERE "); : if(i_where > 0) { : where_clause = substr(sqlstring, (i_where + 6) ); : if ( (SEARH_FIELD != "" ) && (where_clause ~ SEARCH_FIELD) ) { : i_fields = split(where_clause, where_array," "); : i_bind = 1; : for(i = 1; (i <= i_fields) ; i++) { : #printf("%i: where_array=%s\n", i, where_array[i]); : if(index(where_array[i], SEARCH_FIELD)) { : #printf("HAS EMPLID: %s\n", where_array[i]); : if(substr(where_array[i], 7, 1) == "=") { #as some funky format : x = index(where_array[i],":") + 1; : : emplid_bind[Cursor] = substr(where_array[i], x); : #printf("2-emplid_bind[%d] = %d\n", Cursor, emplid_bind[Cursor]); : return; : : } else { : emplid_bind[Cursor] = substr(where_array[i+2], 2); : #printf("1-emplid_bind[%d] = %d\n", Cursor, emplid_bind[Cursor]); : return; : } : } : if((where_array[i] = "AND") \|\| (where_array_[i] = "OR")) { : i_bind++; : } : } : }else if (SEARCH_FIELD == "") { : emplid_bind[Cursor] = 1; : } : } : if(index(sqlstring,"INSERT INTO")) { : if ( (SEARCH_FIELD != "" ) && ( sqlstring ~ SEARCH_FIELD ) ) { : #printf("Contains search field: %s\n", sqlstring); : i_paren = index(sqlstring, "("); : i_fields = split(substr(sqlstring, (i_paren +1 )), insert_into," "); : i_bind = 1; : i_stop = 1; : for(i = 1; (i <= i_fields) \|\| (i_stop == 0); i++) { : #printf("%i: insert_into=%s\n", i, insert_into[i]); : if(index(insert_into[i], SEARCH_FIELD)) { : #printf("HAS EMPLID: %s - %d\n", insert_into[i], i); : emplid_bind[Cursor] = i; : return; : } : } : }else if (SEARCH_FIELD == "") { : emplid_bind[Cursor] = 1; : } : } : : } : ===============================

[ Follow Ups ] [ Post Followup ] [ Message Board ]