Code:
$sth = $dbh->prepare( $query ) or die "error with query[$query]\n";
$sth->execute() or die "error executing query ...\n";
while( @row = $sth->fetchrow_array ) {
undef $agent_id ;
undef $execution_id ;
undef $primary_auth_id ;
undef $client_pid ;
undef $client_platform ;
undef $client_dns ;
undef $appl_name ;
undef $user_id ;
undef $last_timestamp ;
undef $elapsed_hours ;
undef $elapsed_minutes ;
undef $elapsed_seconds ;
undef $chg_timestamp ;
undef $start_timestamp ;
undef $stop_timestamp ;
undef $query_cost_estimate ;
undef $rows_read ;
undef $rows_written ;
undef $sql ;
$agent_id = rtrim($row[0]);
$execution_id = rtrim($row[1]);
$primary_auth_id = rtrim($row[2]);
$client_pid = rtrim($row[3]);
$client_platform = rtrim($row[4]);
$client_dns = rtrim($row[5]);
$appl_name = rtrim($row[6]);
$user_id = rtrim($row[7]);
$last_timestamp = $row[8];
$elapsed_hours = $row[9];
$elapsed_minutes = $row[10];
$elapsed_seconds = $row[11];
$chg_timestamp = $row[12];
$start_timestamp = $row[13];
$stop_timestamp = $row[14];
$query_cost_estimate = $row[15];
$rows_read = $row[16];
$rows_written = $row[17];
$sql = $row[18];
@cleandata = ($agent_id,$execution_id,$primary_auth_id,$client_pid,$client_platform,$client_dns,$appl_name,$user_id,$last_timestamp,$elapsed_hours,$elapsed_minutes,$elapsed_seconds,$chg_timestamp,$start_timestamp,$stop_timestamp,$query_cost_estimate,$rows_read,$rows_written,$sql);
# if($sql !~ /SNAP_GET_APPL_INFO/ and $sql =~ /appdm\."?cp\_/i and $sql !~ /with\s+ur/i) {
if($sql !~ /SNAP_GET_APPL_INFO/) {
#
# archive information into SQLite DB
#
$sqlitesql = qq|insert into sql_archive (agent_id,
execution_id,
primary_auth_id,
client_pid,
client_platform,
client_dns,
appl_name,
user_id,
last_timestamp,
elapsed_hours,
elapsed_minutes,
elapsed_seconds,
chg_timestamp,
start_timestamp,
stop_timestamp,
query_cost_estimate,
rows_read,
rows_written,
sql,
datetime,
epoch )
values
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)|;
$insert = $sqlitedbh->prepare($sqlitesql);
@insertrow = @cleandata;
push(@insertrow,$datetime);
push(@insertrow,$epoch);
$insert->execute(@insertrow);
}
}
$sqlitedbh->commit();
#
# terminate db2 connection
#
$sth->finish();
$dbh->disconnect;
