Archives of the TeradataForum

Message Posted: Mon, 15 Apr 2003 @ 01:23:21 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This could be done with one-to-one views of the tables. Create the views with the lock modifier and change their access so they have to go through the views. But if allowed to query during the loads they may get inaccurate, or incomplete results.

The best thing to do is have jobname, or accountname standard. Then when it's time to clobber the users you can sel username from dbc.sessionino where ....., and put the information back into xgtwglobal -nw to abort them. You can even select the exact gtw command before this step. Add an additional table that allows you to make exceptions and join against it:

sel 'kill session '||trim(sessionno (format '---------9'))
from dbc.sessioninfo
where accountname not like '%batch%'
  and accountname not like '%prod%'
  and accountname not like '%operator%'
  and accountname not like '%Crashdumps%'
  and accountname not like '%Smart%'
  and logonsource not like '%133.214.173.999%'
  and logonsource not like '%133.214.171.996%'
  and logonsource not like '%134.214.171.667%'
  and username not in (
      sel * from sysadmin.kill_user_exclude);

Wrap it in script and schedule it. Here's a perl snippet:

$step_info = "Preparing SQL for kill";
$get_sessions = $dbh->prepare(
        "sel 'kill session '||trim(sessionno (format '---------9'))
        from dbc.sessioninfo
        where accountname not like '%batch%'
          and accountname not like '%prod%'
        and accountname not like '%operator%'
        and accountname not like '%Crashdumps%'
        and accountname not like '%Smart%'
        and logonsource not like '%133.214.173.999%'
        and logonsource not like '%133.214.171.996%'
        and logonsource not like '%134.214.171.667%'
        and username not in (
            sel * from sysadmin.kill_user_exclude);");

until ($hour == $until_hour) {
   $step_info = "Selecting users to kill";
   $hour = `date '+%H'`;
   chomp $hour;
   $get_sessions->execute();
   $get_sessions->bind_columns(\$rows);

   while ( $get_sessions->fetch() ) {
      push @kill_sessions, $rows;
   }

   if (@kill_sessions) {

      $step_info = "Executing /tgtw/bin/xgtwglobal -nw";
      open(GATEWAY, "|/tgtw/bin/xgtwglobal -nw >>
$ENV{LOGDIR}/$process/$prog.$date 2>&1");
      print GATEWAY "sel host 52\n";

      foreach $command (@kill_sessions) {
         print GATEWAY "$command\n";
      }

      $get_sessions->finish();
      $stmt_info = $get_sessions->{'tdat_stmt_info'};
      $currstmt  = $get_sessions->{'tdat_stmt_num'};
      $stmthash  = $$stmt_info[$currstmt];
      $activity  = $$stmthash{'ActivityType'};
      $stmtrows  = $$stmthash{'ActivityCount'};
      $warn      = $$stmthash{'Warning'};
      $warn_stmt = "Statement $currstmt: $warn\n" if $warn;

      @date = `date`;

      $message = "
      $warn_stmt
      **** Statement Complete.
      **** $stmtrows sessions selected for death on @date
      $warn\n";

      $total_kills = $total_kills + $stmtrows;

      print "$message\n";

      print GATEWAY "quit\n";
      close(GATEWAY);

      undef @kill_sessions; # Un-initialize the array for the next batch of sessions
   }
   sleep($every); # define a sleep time to wake up and get new logons or persistent users
}

Better than this is to write a PM/API based program do manage this.

	<Prev	Next>		<<First	<Prev	Next>	Last>>