Here is my shell/SQL execution script. It takes an input file that is a mix of DOS commands, SQL statements, and shell commands, generates a shell script to run them, sends it to a Linux server, opens a PuTTY session, runs the script, and retrieves the results. I run it from a Windows PC, and it uses pscp and plink to drive the remote session.
@rem = '--*-Perl-*-- @echo off perl "%~dpnx0" %1 %2 %3 %4 %5 %6 %7 %8 %9 goto endofperl @rem '; #!perl #line 8 use strict; use warnings; use Win32::Clipboard; my $CLIP = Win32::Clipboard(); my $userpath = $ENV{"HOMEDRIVE"}.$ENV{"HOMEPATH"}; mkdir "$userpath\\sqlcmd"; my @date = localtime(); my $date = sprintf("%04d-%02d-%02d",$date[5]+1900,$date[4]+1,$date[3]) +; my $time = sprintf("%02d-%02d-%02d",$date[2],$date[1],$date[0]); my $ts = "${date}_${time}"; my $input = shift; open IN, "<$input" or die "Can't open input $input\n"; my $output = "sqlout_${input}_$ts.txt"; my $outerr = "plinkerr_${input}_$ts.txt"; my $output1 = "psqlout.txt"; my $shell = "$userpath\\sqlcmd\\shell_$ts.txt"; open SQL, ">$shell" or die "Can't open output $shell\n"; binmode(SQL); my $sh = "$userpath\\sqlcmd\\sh_$ts.txt"; open SH, ">$sh" or die "Can't open output $sh\n"; my @cmd = (); print SQL ". `cat /.dshome`/dsenv\n"; # Special vars my %svar = (); $svar{"ts"}=$ts; $svar{"output"}=$output; # Shell vars my %shellvar = (); my $sqlline = ""; my $server; my $user; my $passwd; my $dbconn; my $dbuser; my $dbpasswd; my $commit = "-c"; my $fail = 0; my $echo = 1; while (<IN>) { next if /^#/; s/\s+$//; if ($. == 1) { my $ufile; if (/^\@(.+)/) { $ufile = $1; } else { $ufile = "user.txt"; } print "Using $ufile\n"; open USR, "<$userpath/$ufile" or die "Can't open user file $userpa +th/$ufile"; my $server = <USR>; my $user = <USR>; my $passwd = <USR>; my $dbconn = <USR>; my $dbuser = <USR>; my $dbpasswd = <USR>; my $shpath = <USR>; $server =~ s/\s+.*//; $user =~ s/\s+.*//; $passwd =~ s/\s+.*//; $dbconn =~ s/\s+.*//; $dbuser =~ s/\s+.*//; $dbpasswd =~ s/\s+.*//; $shpath =~ s/\s+.*// if $shpath; $svar{"server"}=$server; $svar{"user"}=$user; $svar{"passwd"}=$passwd; $svar{"shpath"}=$shpath if $shpath; print "Unix box = $server\n"; print "Unix user = $user\n"; print "DB conn = $dbconn\n"; print "DB user = $dbuser\n"; print "Shell path = $shpath\n" if $shpath; if ($shpath) { print SH "chmod 700 $shpath/shell_$ts.sh\n"; print SH "$shpath/shell_$ts.sh\n"; print SH "rm $shpath/shell_$ts.sh\n"; push @cmd, "pscp -pw \"${passwd}\" \"${shell}\" ${user}\@${serve +r}:${shpath}/shell_$ts.sh\n"; push @cmd, "plink -batch -pw \"${passwd}\" ${user}\@${server} -m + \"${sh}\" >> \"${output}\" 2>${outerr}\n"; } else { push @cmd, "plink -batch -pw \"${passwd}\" ${user}\@${server} -m + \"${shell}\" >> \"${output}\" 2>${outerr}\n"; } print SQL "db2 connect to $dbconn USER $dbuser USING $dbpasswd\n"; } next if /^\@/; # Interpolate special vars into all commands foreach my $key (keys %svar) { s/\$\{$key\}/${svar{$key}}/g; } s/ +$//; # s/[^\x20-\x7f£]//; # remove any non-ASCII characters if (/^\*/) { # If it begins with a *... s/^\*//; # trim off the * print SQL "$_\n"; # ...and add it to the shell script if (/^(\w+)=(.+)/) { # If it's a shell variable... $shellvar{$1}=$2; # ...add it to a hash } next; } elsif (/^!/) { s/^!//; # Interpolate Unix shell vars into Command Prompt commands while (/\$\{(\w+)\}/ and defined $shellvar{$1}) { my $sv = $1; s/\$\{$sv\}/${shellvar{$sv}}/g; } s/\$\{output\}/${output}/g; if (/>/) { push @cmd, "$_\n"; } else { push @cmd, "$_ >> ${output}\n"; } next; } elsif (/^%/) { if (/%COMMIT ON/i) { $commit = "-c"; } elsif (/%COMMIT OFF/i) { $commit = "+c"; } elsif (/%FAIL ON/i) { $fail = 1; } elsif (/%ECHO ON/i) { $echo = 1; } elsif (/%ECHO OFF/i) { $echo = 0; } next; } $sqlline .= " " . $_; if (substr($sqlline,-1) eq ";") { unless ($sqlline =~ /^\s*--/) { chop $sqlline; $sqlline =~ s/^\s+//; $sqlline =~ s/\s+/ /g; my $rsql = 0; if ($sqlline =~ /^=/) { # Convert the output to record vi +ew if prefixed by = $sqlline =~ s/^=//; # trim off the = $rsql = 1; } my $ssql = 0; if ($sqlline =~ /^\+/) { # Store the result in ${result} +if prefixed by + $sqlline =~ s/^\+//; # trim off the + $ssql = 1; } my $csql = 0; if ($sqlline =~ /^\,/) { # Store the result comma-separat +ed in ${result} if prefixed by , $sqlline =~ s/^\,//; # trim off the , $ssql = 1; $csql = 1; } if ($rsql) { print SQL "db2 $commit -v \"$sqlline\"|perl -e 'while (\<\>) { + if (\$colseps eq \"\" and \/\^\-\/) { \$colseps = \$_; \$colnames = +\$prev; my \$col; my \$off = 0; foreach (split \/ \/,\$colseps) { my +\$len = length; push \@col_offs, \$off; push \@col_lens, \$len; my \$ +colname = substr(\$colnames, \$off, \$len); \$colname =\~ s\/ +\$\/\/ +; \$longname = length(\$colname) if length(\$colname) \> \$longname; +\$off += \$len + 1; } print; next; } if (\/\^\$\/) { \$colseps = \"\" +; } if (\$colseps ne \"\") { chomp; ++\$rec; print \"Record \$rec\\n\ +"; for (my \$i=0; \$i\<\@col_offs; ++\$i) { \$val=substr(\$_, \$col_o +ffs[\$i], \$col_lens[\$i]); \$val=substr((\" \"x20).\$val,-20) if len +gth(\$val)\<20 and \$val=~/\^ /; printf \"\%\-\${longname}.\${longnam +e}s = \%s\\n\", substr(\$colnames, \$col_offs[\$i], \$col_lens[\$i]), + \$val; } print \"\\n\"; } else { print; } } continue { chomp; \$prev + = \$_; }'\n"; } elsif ($ssql) { print SQL "result=`db2 $commit -x \"$sqlline\"`\n"; if ($csql) { print SQL "result=`echo \${result}|sed 's/ /,/g'`\n"; } } else { print SQL "psqlout=`db2 $commit -v \"$sqlline\"`\n"; if ($echo) { print SQL "echo \"\${psqlout}\"\n"; } # if ($fail) { # print SQL "psqlcheck=\${psqlout}\n"; # print SQL "if [ \"`expr match \\\"\${psqlcheck}\\\" '.*\\( +selected\\).*'`\" -ne \" selected\"]; then echo ERROR; exit 1; fi\n"; # } } } $sqlline = ""; } } print SQL "db2 connect reset\n"; close SQL; close SH; push @cmd, "cp -m \"${output}\" \"${output1}\"\n"; my $die; foreach (@cmd) { #print "Executing $_\n"; print "Executing $1\n" if /^(\S+)/; $die = 0; last if /^exit$/i; system($_); if (-e ${outerr}) { open ERR, "<${outerr}"; while (<ERR>) { print; if (/^Access denied$/) { $die = 1; } } die if $die; } } unlink $outerr; __END__ :endofperl
Here is an example script:
@uatuser.txt set schema da03; *data_src_id=40 SELECT * FROM BATCH_AUDIT WHERE data_src_id = ${data_src_id} order by BATCH_ID with ur; *regions=1,2,3 ,select region_name from regions where region_id in ( ${regions} ); *echo ${result}
* in front of a line designates a shell command. The * is removed and the remainder is executed.
, in front of an SQL command (terminated with a ;) instructs the script to convert the SQL results into a comma-separated list for use in a future IN list. Results are stored in the $results shell variable.
There is a risk of password leakage as it needs to write the DB2 user name and password into the script that is executed on the Linux machine, and it writes the Linux user name and password to a temporary file on the local PC (it writes it to the user's Documents and Settings so other users can't see it). It also picks up these user names and passwords from a config file, as I couldn't be bothered typing them in every time I want to run a script. This file is also in the user's Documents and Settings directory, here is an example:
cs-uat-etl02 <= Unix server name username <= put your user name here password <= put your password here ZP_D2TB <= DB2 connection name DSXMUAT <= put the database user name here dbpassword <= put the database password here /data/scratch/phil <= this is where the temporary shell script is w +ritten
Critique and suggestions are welcome. The line after "if ($rsql) {" is pretty hideous, it pushes the results through a Perl one-liner that I reslly should push out to an external Perl script file, but I want the whole thing to be self-contained and be run from a Windows PC with no external dependencies.
To Do: Failure detection, the "%fail on" command is intended to abort if any SQL errors occur, perhaps with the exception of the "No rows to delete" error.