script hangs waiting for key stroke

[chris]

I am running the following script on perl, v5.8.4 built for
MSWin32-x86-multi-thread (activestate). The script runs but seems to
hang every now and then and won't continue until I press enter. E.g.:

Processing DE 200407
Processing DE 200408

Hang - Press [enter] <<

Processing DE 200409

Do I need to flush buffers or something?

Many thanks,

Chris


----------


#!/usr/bin/perl -w

use strict;
use DBD::ODBC;

use util::YYYYMM;

my $start = 200402;
my $end = 200410;
my $range_list = range( $start, $end );

foreach my $opco ( qw[ IE UK DE IT GR ES ] ) {
foreach my $yyyymm (@$range_list) {
LOAD: {
print "Processing $opco $yyyymm\n";
my $dbh = DBI->connect('dbi:ODBC:XXXXX', 'XXXXX', 'XXXXX')
or redo LOAD;
my $sth = $dbh->prepare("exec m_run ( '$opco', $yyyymm );")
or redo LOAD;
$sth->execute or redo LOAD;
$dbh->disconnect;
}
}
}
my $yyyymm_plus_one = yyyymm_add( $end );
my $sth = $dbh->prepare("exec m_run_report ( $yyyymm_plus_one );");
$sth->execute;

 

[chris]

You're doing an awful lot of connecting and disconnecting from your

database. You should probably open it once at the beginning and close
it once at the end, for performance if nothing else. But doing this
shouldn't make it hang. However, if it fails to open for some reason,
you're looping back and trying exactly the same thing again. If it
fails to open once, chances are pretty good it will continue to fail to
open, hence an infinite loop. But that wouldn't "continue" on a key press.

Probably a bit more info is required...

Each loop can take about 4hours to run. Whats more, due to technical
constraints, the ODBC connection may die or the script is aborted by
another load job. Previously, the script was manually restarted, that
is why there is now a redo loop.

If the prepare fails, it is probably not appropriate to just go and open
the database over again. I'm hard pressed to imagine how a prepare that
failed would subsequently succeed on the same database. It would be
best to invoke RaiseError and let DBI take care of the checks and error
bombouts.

As above, if the ODBC connection dies, the only way forward is to
re-connect.

So I have no idea why your program is hanging waiting for a keystroke.
But I have no problem seeing why it might hang.

Regarding the question about flushing buffers, the only "buffer" you are
using is for STDOUT, and, I think Perl line-buffers it when it is
connected to a terminal anyway, at least on most OS's. Looks like
you're on Windoze; I don't know if it line buffers STDOUT on a cmd
window or not...yep, it does on XP Home SP2, AS build 810, anyway.

Would it just be safest to remove the print statement? Although, I
would have no idea how far the process has completed except to run a
query on the target database...

One other possibility, I suppose, is whatever the database procedures
you are invoking (like m_run or m_run_report) might do. Could they ever
wait for a keystroke?

The procedures (actually Teradata macros) do not wait for keyboard
input. I suppose the hang could be a bug with the ODBC driver?

Thanks for the reply...

 

[chris]

It runs fine!

The util/YYYYMM.pm ...


-- util/YYYYMM.pm --

#!/usr/bin/perl -w

use strict;

my @return_range;
sub range {
my ($start, $end) = @_;
my $current = $start;
push @return_range, $start;
while ( $current != $end ){
$current = yyyymm_add($current); # i.e. current++
push @return_range, $current; # put current++ into array
}
return \@return_range;
}

sub yyyymm_add {
my ($yyyymm) = shift;

my $YYYY = substr( $yyyymm, 0, 4 );
my $MM = substr( $yyyymm, 4, 2 );

if ( $MM == 12 ) {
$YYYY++; $MM = 1;
} else {
$MM++;
}
my $yyyymm_p1 = sprintf("%04d%02d",$YYYY, $MM);
return $yyyymm_p1;
}


return 1;

 

[chris]

Agreed! Sorry! This is the latest version...

#!/usr/bin/perl -w

use strict;
use DBD::ODBC;

use util::YYYYMM;

my $start = 200402;
my $end = 200408;
my $range_list = range( $start, $end );
my @opcos = qw[ ES ];

print "This will (re)load \n\t@opcos \nfor \n\t@$range_list.\n";
print "Continue [y/N]?";
die ">> Cancelled <<" if (getc !~ /y/i);

my $dbh;
#foreach my $opco ( qw[ IE UK DE IT GR ES ] ) {
foreach my $opco (@opcos) {
foreach my $yyyymm (@$range_list) {
LOAD: {
print "Processing $opco $yyyymm\n";
$dbh = DBI->connect('dbi:ODBC:XXXXXX', 'XXXXXX', 'XXXXXX')
or redo LOAD;
my $sth = $dbh->prepare("exec udb019.m_run ( '$opco', $yyyymm );")
or redo LOAD;
$sth->execute or redo LOAD;
$dbh->disconnect;
}
}
}
my $yyyymm_plus_one = yyyymm_add( $end );
my $dbh2 = DBI->connect('dbi:ODBC:XXXXXX', 'XXXXXX', 'XXXXXX')
or redo LOAD;
my $sth2 = $dbh2->prepare("exec udb019.m_run_report ( $yyyymm_plus_one );");
$sth2->execute;
$dbh2->disconnect;

It runs fine!

Rubbish!
Here, again, is the code you originally posted:

#!/usr/bin/perl -w

use strict;
use DBI;

use util::YYYYMM;

my $start = 200402;
my $end = 200410;
my $range_list = range( $start, $end );

foreach my $opco ( qw[ IE UK DE IT GR ES ] ) {
foreach my $yyyymm (@$range_list) {
LOAD: {
print "Processing $opco $yyyymm\n";
my $dbh = DBI->connect('dbi:ODBC:XXXXX',
'XXXXX', 'XXXXX')
or redo LOAD;
my $sth = $dbh->prepare("exec m_run ( '$opco',
$yyyymm );")
or redo LOAD;
$sth->execute or redo LOAD;
$dbh->disconnect;
}
}
}
my $yyyymm_plus_one = yyyymm_add( $end );
my $sth = $dbh->prepare("exec m_run_report ( $yyyymm_plus_one );");

^^^^
Where is $dbh declared?
Your code cannot possibly compile as shown, and therefore it does not,
as you claim, "run fine".

$sth->execute;