youngchang's diary

#!/usr/bin/perl
use Sys::Hostname;
use Getopt::Std;
use Switch;
use POSIX;

$host = hostname;

$TARGETDBNAME = "dbname";
$TARGETUSER   = "username";
$TARGETPASS   = "password";

switch ($host) {
    case "agdb1"    {   $DBNAME =   "NPAGDB"    }
    case "qudb1"    {   $DBNAME =   "NPQTDB"    }
    case "pddb1"    {   $DBNAME =   "NPPDDB"    }
    case "batdrdb"  {   $DBNAME =   "SFMIDB"    }
    case "agdevdb"  {   $DBNAME =   "AGDEVDB"   }
    case "qtdevdb"  {   $DBNAME =   "QTDEVDB"   }
    else            {   $DBNAME =   "Unknown"   }
}

sub process {
    # target loading
    switch ($_[0]) {
        case "file" {
            $filename = $_[2];
            open(MYDATA, $filename) or die ("error occured!");
            @lines = <MYDATA>;
        }

        else {
            while(<STDIN>) {
                @lines = (@lines, $_)
            }
        }
    }

    # to split line with whitespace
    # my $date = POSIX::strftime("%Y-%m-%d-%H:%M:%S", localtime(time()));
    my $input_date = POSIX::strftime("%Y-%m-%d-%H.%M.%S", localtime(time()));
    if ($_[1] ne "del") {
        print "db2 -v \"connect to $TARGETDBNAME user $TARGETUSER using $TARGETPASS\"\n";
    }

    # to find TCB Table Stats part.
    $beg_idx_stats = 0;
    $end_idx_stats = $#lines;
    foreach $stats_line (@lines) {
        if ( $stats_line =~ /TCB Table Stats/ ) {
            $beg_idx_stats++;
            last;
        } else {
            $beg_idx_stats++;
        }
    }
    @tcbstats = @lines[$beg_idx_stats+1..$end_idx_stats];

    # to find TCB Table Infomation.
    $beg_idx_info = 0;
    $end_idx_info = $beg_idx_stats-3;
    foreach $info_line (@lines) {
        if ( $info_line =~ /TCB Table Information/ ) {
            $beg_idx_info++;
            last;
        } else {
            $beg_idx_info++;
        }
    }
    @tcbinfo = @lines[$beg_idx_info+1..$end_idx_info];

    # generate data insertion statements.
    $line_no = 0;
    for $tcbstats_line (@tcbstats) {
        @stats_record = split /\s+/, $tcbstats_line;

        if ($stats_record[1] !~ /^IBM/ && $stats_record[1] !~ /^SYS/ && $stats_record[1] !~ /^TEMP/ && $stats_record[1] !~ /^INTERNAL/ && $#stats_record >= 12) {
            $table_schema = "unknown";

            for $tcbinfo_line (@tcbinfo) {
                @info_record = split /\s+/, $tcbinfo_line;
                if ($info_record[6] !~ /^IBM/ && $info_record[6] !~ /^SYS/ && $info_record[6] !~ /^TEMP/ && $info_record[6] !~ /^INTERNAL/) {
                        if ($stats_record[0] eq $info_record[0] ) {
                            $table_schema = $info_record[7];
                        }
                }
            }

            # db2pd tctstats index information
            # Address     00
            # TableName   01
            # Scans       02
            # UDI         03
            # PgReorgs    04
            # NoChgUpdts  05
            # Reads       06
            # FscrUpdates 07
            # Inserts     08
            # Updates     09
            # Deletes     10
            # OvFlReads   11
            # OvFlCrtes   12

            if ($_[1] eq "del") {
                print "\"$DBNAME\",\"$input_date\",\"$table_schema\",\"$stats_record[1]\",$stats_record[2],$stats_record[6],$stats_record[8],$stats_record[9],$stats_record[10]\n";
            } else {
                print "db2 -v \"insert into adm.tcbstats values ('$DBNAME', '$input_date', '$table_schema', '$stats_record[1]', $stats_record[2], $stats_record[6], $stats_record[8], $stats_record[9], $stats_record[10] )\"\n";
            }
        }

        if ($_[1] ne "del") {
            if ($line_no != 0 && $line_no % 100 == 0) {
                print "db2 -v \"commit work\"\n"
            }
        }
        $line_no++;
    }

    if ($_[1] ne "del") {
        print "db2 -v \"commit work\"\n";
        print "db2 -v \"terminate\"\n";
    }
   
    close MYDATA;
}

sub help {
    print "# ------------------------------------------------------------------  \n";
    print "# This shell is generated by insert_tcbstats.pl                       \n";
    print "# If you have a question, plz contact to youngchang.park@samsung.com  \n";
    print "# ------------------------------------------------------------------  \n";
   
    print "Usage : insert_tcbstats.pl -t [del|sh] -f [filename]                     \n";
    print "        insert_tcbstats.pl -t [del|sh] -s [STDIN]                        \n";
    print "        insert_tcbstats.pl -h                                            \n";
    print "        insert_tcbstats.pl -v                                            \n";
}

sub version {
    print "insert_tcbstats.pl version : 2009 / 06 / 25               \n"
}

%opts = ();
getopts("shvf:t:", \%opts);

if (defined $opts{h}) {
    help();
    exit 0;
}

if (defined $opts{v}) {
    version();
    exit 0;
}

if (defined $opts{t}) {
    if (defined $opts{s}) {
        process("stdin", $opts{t});
        exit 0;
    } else {
        if (defined $opts{f}) {
            process("file", $opts{t}, $opts{f});
            exit 0;
        }
    }
}
help();

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.