#!/usr/local/bin/perl # # -------------------------------------------------------------------------------------- # Macro script to run the EOB daily scripts upon # notification that a new file(s) has arrived # # RESTART NOTE: If this script stops abnormally all committed records and *.asc files must # be deleted before the job is rerun. # # Modification Log # # 02/03/2008 - Add a timestamp value to be used for a common creation # timestamp on all records # 04/11/2008 - Update to check for an empty file # 07/09/2009 - remove set integrity # 03/03/2013 - Cloned to create Massachusetts specific version. # # --------------------------------------------------------------------------------------- use strict; use POSIX qw(strftime); # ~ Variables ---------------------------------------------------- my $base_dir = "/nmas_eob"; #my $data_dir = $base_dir."/data"; my $data_dir = $base_dir."/data_mass"; my $script_dir = $base_dir."/scripts"; my %properties = readproperties("$script_dir/nmase.properties"); my $db2_user = $properties{db2_user}; my $db2_password = $properties{db2_password}; my $db2_db = $properties{db2_db}; my $files_to_process = $properties{files_to_process}; my $files_processed = 0; #my $db2_load_mode = "REPLACE"; my $db2_load_mode = "INSERT"; my $rc = ""; my $running_marker_file = "$script_dir/daily_batch_running.txt"; my $load_eob_message = " load client FROM %s.eob-message.asc OF ASC modified BY striptblanks dateformat=\"YYYYMMDD\" timestampformat=\"YYYY-MM-DD-HH.MM.SS.UUUUUU\" method l (1 3,4 11,12 20,21 24,25 25,26 29,31 80,81 83,85 98,99 124,126 15000) NULL indicators (0,0,0,0,0,0,30,0,84,0,125) $db2_load_mode INTO NMASE.EOB_MESSAGE statistics no nonrecoverable without prompting; "; my $load_eob_claim = " load client FROM %s.eob-claim.asc OF ASC modified BY striptblanks dateformat=\"YYYYMMDD\" timestampformat=\"YYYY-MM-DD-HH.MM.SS.UUUUUU\" method l (1 3,4 11,12 20,21 34,35 37,38 40,41 52,53 64,65 76,77 88,89 100, 101 112,113 124,125 136,137 140,141 154,156 169,171 220,222 271, 273 322,323 325,327 376,377 402,403 418) NULL indicators (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,155,170,221,272,0,326,0,0) $db2_load_mode INTO NMASE.EOB_CLAIM statistics no nonrecoverable without prompting; "; my $load_eob_detail_service = " load client FROM %s.eob-detail-service.asc OF ASC modified BY striptblanks dateformat=\"YYYYMMDD\" timestampformat=\"YYYY-MM-DD-HH.MM.SS.UUUUUU\" method l (1 3,4 11,12 20,21 34,35 38,39 46,47 54,55 66,67 78,79 90, 91 102,103 114,115 126,127 138,139 150,151 154,155 155,156 176,177 202) $db2_load_mode INTO NMASE.EOB_DETAIL_SERVICE statistics no nonrecoverable without prompting; "; my $load_eob = " load client FROM %s.eob.asc OF ASC modified BY striptblanks dateformat=\"YYYYMMDD\" timestampformat=\"YYYY-MM-DD-HH.MM.SS.UUUUUU\" method l (1 3,4 11,12 20,21 36,37 50,52 59,60 64,65 69,71 78,79 83, 84 88,90 97,98 102,103 107,109 116,117 121,122 126,128 135, 136 140,141 145,146 147,148 149,150 151,152 154,155 157, 158 169,170 181,182 193,194 205,206 217,218 229,230 241, 242 253,254 254,255 255,256 264,266 275,277 290,291 306, 307 319,320 345,347 386,388 427,429 468,470 509,511 550, 552 591,593 632,634 673,675 714,716 755,757 796,798 837, 839 878,880 919,921 960,962 1001,1003 1042,1044 1083,1085 1124, 1126 1155,1156 1156,1157 1157,1158 1159) NULL indicators (0,0,0,0,0,51,0,0,70,0,0,89,0,0,108,0,0,127,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,265,276,0,0,0,346,387,428,469,510,551, 592,633,674,715,756,797,838,879,920,974,1002,1043,1084,1125,1156,1157,1158) $db2_load_mode INTO NMASE.EOB statistics no nonrecoverable without prompting; "; # -- CREATION_TIMESTAMP defaults to CURRENT_TIMESTAMP ## WTC - Change to call the Massachusetts version of this pre-processor ##require "$script_dir/pre_process_eob.pl"; require "$script_dir/pre_process_eob_mass.pl"; # --------------------------------------------------------------------- # ~ Main Procedure ---------------------------------------------------- # --------------------------------------------------------------------- # start the stopwatch and set variable for timestamp in file names my $start_time = time; # Build log file name ## WTC - changed to ass mass to the file name my $log = "$script_dir/daily_batch_run_mass.".&prettyTimestamp($start_time).".log"; # redirect STDOUT to the log file open STDOUT, ">$log" or die "open STDOUT, >$log ) failed with OS_ERROR = $!"; chmod 0777, $log; # Ensure the last run completed successfully if (-e $running_marker_file) { writeLog("This job didn't finish normally the last time it ran, Need to research previous"); writeLog("logs and see what happened. Will need to remove marker file <$running_marker_file>"); writeLog("after root cause of the problem has been resolved and then rerun the job"); exit 1; } open RUNNING_MARKER, ">$running_marker_file" or die "Couldn't open running marker file"; printf RUNNING_MARKER "$log\n"; close RUNNING_MARKER; writeLog("-------------------Start-------------------------"); writeLog("daily_batch_run.pl script starting"); # loop through and process each input file my @files = glob("$data_dir/$files_to_process"); if (scalar(@files) > 0) { writeLog(""); writeLog("RESTART NOTE: If this script stops abnormally after any of *.asc files"); writeLog("have been processed and rows have been inserted and commited into DB"); writeLog("Those rows that have been committed must be deleted before rerunning"); writeLog("this job or the committed records will error on the load insert of the rerun. "); writeLog("The /nmas_eob_/data/*.asc files will need to be deleted. The marker file"); writeLog("<$running_marker_file> will need to be deleted."); writeLog("The file <$_> is the file that is currently being processed."); writeLog(" "); writeLog("You will have to use a delete SQL statement to clean off all rows with the"); writeLog("timestamp of this run. The EOB EOB_CLAIM EOB_DETAIL_SERVICE EOB_MESSAGE could potentially "); writeLog("need to be cleaned up depending on where the error occurred."); writeLog(" "); writeLog(" "); writeLog(" "); # connect to the DB $rc = system "db2 -v CONNECT TO $db2_db USER $db2_user USING $db2_password "; die "Error signaled when attempting to connect to the database" if ($rc); foreach (@files) { # bypass the file if the execute bit isn't on # (The execute permission is used to tell us this file is ready to process) unless (-x $_) { writeLog("Bypassing $_ because the executible permission is not on"); next; } if ( -s $_ == 0 ) { writeLog("The Following is an EMPTY FILE: $_"); writeLog(" "); $rc=system("mv $_ $_.EMPTY_FILE"); writeLog("MV RC: $rc"); next; } writeLog(" "); writeLog("# of files processed so far is <$files_processed>"); writeLog("About to issue the following command to the pre_process to split file"); writeLog("which will call the pre_process_main function in the pre_process_eob.pl script"); writeLog("pre_process_main($_, $data_dir)"); writeLog(" "); # pre process the input file &pre_process_main($_, $data_dir); $files_processed++; # set the permissions so the db2 instance will permit load chmod 0777, glob("$data_dir/*.asc"); # load the data to the DB writeLog(" "); writeLog("About to run build db2 script to run the load command. for file <$_>"); writeLog(" "); writeLog("Here is the load command about to be run for EOB file <$load_eob>"); writeLog(" "); writeLog(" "); db2f("$load_eob", $_); writeLog("Here is the load command about to be run for EOB_CLAIM file <$load_eob_claim>"); writeLog(" "); writeLog(" "); db2f("$load_eob_claim", $_); writeLog("Here is the load command about to be run for EOB_DETAIL_SERVICE file <$load_eob_detail_service>"); writeLog(" "); writeLog(" "); db2f("$load_eob_detail_service", $_); writeLog("Here is the load command about to be run for EOB_MESSAGE file <$load_eob_message>"); writeLog(" "); writeLog(" "); db2f("$load_eob_message", $_); # zip up the input files writeLog("Cleaning up temp files with this command:"); $rc = system "rm $_*.asc"; die "Error ($rc) attempting to remove $_*.asc" if ($rc); writeLog("Compressing the file with the following command "); ###DRM $rc = system "gzip $_"; ###DRM die "Error ($rc) attempting to gzip $_" if ($rc); $rc = system "compress $_"; die "Error ($rc) attempting to compress $_" if ($rc); } # if ($files_processed) # { ###DRM # set the referential integrity back on ###DRM if ($db2_load_mode eq "REPLACE") { ###DRM db2("set integrity for nmase.eob immediate checked"); ###DRM } ###DRM writeLog(" "); ###DRM writeLog("run "); ###DRM db2("set integrity for nmase.eob_claim immediate checked"); ###DRM ###DRM writeLog(" "); ###DRM writeLog("run "); ###DRM db2("set integrity for nmase.eob_detail_service immediate checked"); ###DRM ###DRM writeLog(" "); ###DRM writeLog("run "); ###DRM db2("set integrity for nmase.eob_message immediate checked"); # # Do runstats # writeLog(" "); # writeLog("run "); # db2('runstats on table nmase.eob with distribution and sampled detailed indexes all tablesample bernoulli\(30\)'); # # writeLog(" "); # writeLog("run "); # db2('runstats on table nmase.eob_claim with distribution and sampled detailed indexes all tablesample bernoulli\(30\)'); # # writeLog(" "); # writeLog("run "); # db2('runstats on table nmase.eob_detail_service with distribution and sampled detailed indexes all tablesample bernoulli\(30\)'); # # writeLog(" "); # writeLog("run "); # db2('runstats on table nmase.eob_message with distribution and sampled detailed indexes all tablesample bernoulli\(30\)'); # } # disconnect db2("disconnect ALL"); } else { print "No files found to process in this run\n"; exit 1; } my $finish_time = time; writeLog(" "); writeLog(" "); print "Daily batch run ".&prettyTimestamp($start_time)." completed at ".&prettyTimestamp($finish_time)."\n"; print "Elapsed time for daily batch run: " . (int($finish_time - $start_time)) . " seconds\n"; writeLog(" "); writeLog("---------------End-------------------------"); # remove the running marker file unlink $running_marker_file or die "error deleteing $running_marker_file"; # -------------------------------------------------------- # Execute the parameter as a DB2 command # -------------------------------------------------------- sub db2($) { my $command = "db2 -vo @_"; my $rc = system $command; die "\nError attempting to execute [$command]\n" if ($rc); return 0; } # -------------------------------------------------------- # Execute the DB2 command(s) within a file # -------------------------------------------------------- sub db2f($$) { my ($command, $file_name) = @_; # create the external file open TEMP_FILE, ">$script_dir/temp.sql" or die "Couldn't open temporary file"; chmod 0777, "$script_dir/temp.sql"; printf TEMP_FILE $command, $file_name; close TEMP_FILE; # execute the DB2 commands my $command = "db2 -tsvof $script_dir/temp.sql"; my $rc = system $command; writeLog(" "); writeLog(" "); writeLog("Processing File $file_name - RC=<$rc>"); writeLog(" "); writeLog(" "); die "\nError attempting to execute [$command] RC=$rc\n" if ($rc); # clean up and delete the file unlink "$script_dir/temp.sql" or die "error deleteing $script_dir/temp.sql"; return 0; } # -------------------------------------------------------- # Format a time stamp # -------------------------------------------------------- sub prettyTimestamp($) { my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(@_[0]); sprintf "%4d-%02d-%02d-%02d.%02d.%02d", $year+1900,$mon+1,$mday,$hour,$min,$sec; } # -------------------------------------------------------- # Read a property file and create a hash of the values # -------------------------------------------------------- sub readproperties($) { my ($propfile) = @_; my (%props); # Return value: a hash of property values. my ($ln,$name,$value,@nv); open(PROPFILE,"$propfile") or die "Unable to open $propfile"; while ($ln = ) { chomp $ln; $ln =~ s/#(.*$)//; # Remove line comments in the properties file. # if ($1 ne "") { print "# $1\n"; } ### DEBUG ### @nv = split /\s*=/,$ln,2; $value = pop @nv; $value =~ s/^\s*=?\s*//; $value =~ s/\s*$//; $name = pop @nv; $name =~ s/^\s*//; $name =~ s/\s*$//; if ($name ne "") { $props{$name} = $value; # print "$name=$value\n"; ### DEBUG ### } } close(PROPFILE); return %props; } #---------------------------------------------------------------------------------- # writeLog Subroutine # # This subroutine writes messages to the log file # #---------------------------------------------------------------------------------- sub writeLog { my $line_head = strftime "%m/%d/%Y %H:%M:%S - ", localtime; my $print_line = shift; print STDOUT "$line_head$print_line\n" or die "print STDOUT failed with OS_ERROR = $!"; }