| 1 |
eval 'exec /home/jsoc/bin/linux_ia64/perl -S $0 "$@"' |
| 2 |
if 0; |
| 3 |
#archprobe.pl |
| 4 |
#Originally /home/production/cvs/JSOC/proj/util/scripts/sumstapestat.pl |
| 5 |
#Art's script to get the tape arch pend by group, ds, sorted sunum, etc. |
| 6 |
#Modified to take a group# arg as ARGV[8] only. |
| 7 |
|
| 8 |
#NOTE!!! This has been modified to only works for the calls (0 is group# here): |
| 9 |
#archprobe.pl jsoc_sums hmidb 5434 production raw group ap ' ' 0 |
| 10 |
#archprobe.pl jsoc_sums hmidb 5434 production agg group ap ' ' 0 |
| 11 |
|
| 12 |
use DBI; |
| 13 |
use DBD::Pg; |
| 14 |
use Time::localtime; |
| 15 |
# ART - This module may not be installed. DO NOT USE CPAN to install modules. Use the ActiveState ppm. Unfortunately, |
| 16 |
# ActiveState does not provide this module. |
| 17 |
use Switch; |
| 18 |
|
| 19 |
use constant kDEBUG => 1; |
| 20 |
#use constant kDEBUG => 0; |
| 21 |
|
| 22 |
use constant kStatDADP => "2"; |
| 23 |
use constant kStatDAAP => "4"; # archive pending |
| 24 |
use constant kSubStatDAADP => "128"; # after archive completes, mark delete pending |
| 25 |
use constant kGig => 1073741824; |
| 26 |
|
| 27 |
use constant kTypeQueryAgg => "agg"; |
| 28 |
use constant kTypeQueryRaw => "raw"; |
| 29 |
use constant kTypeOrderSeries => "series"; |
| 30 |
use constant kTypeOrderGroup => "group"; |
| 31 |
|
| 32 |
use constant kMetricAll => "all"; |
| 33 |
use constant kMetricDPS => "dps"; # delete pending now (short) |
| 34 |
use constant kMetricDPM => "dpm"; # delete pending in 100 days (medium) |
| 35 |
use constant kMetricDPL => "dpl"; # delete pending >= 100 days (long) |
| 36 |
use constant kMetricAP => "ap"; # archive pending |
| 37 |
|
| 38 |
use constant kTempTable => "sumsstat"; |
| 39 |
|
| 40 |
# In raw mode, you cannot collect data on dpshort, dpmid, dplong, ap all at the same time. |
| 41 |
# This will exhaust machine memory. Need to provide yet another flag to select which ONE |
| 42 |
# of these metrics to obtain - only collect data on a single metric. |
| 43 |
|
| 44 |
my($err); |
| 45 |
|
| 46 |
my($dbname); # name of the db instance to connect to |
| 47 |
my($dbhost); # name of the db host on which the db instance resides |
| 48 |
my($dbport); # port on $dbhost through which connections are made |
| 49 |
my($dbuser); # database user name (to connect with) |
| 50 |
my($typequery); # type of query to perform (aggregate bytes over series, groups, or |
| 51 |
# don't aggregate) |
| 52 |
my($order); # series - order by series, then group, or group, order by group, then series |
| 53 |
my($metric); # while column of data to produce (delete pending short, delete pending medium, delete pending long, |
| 54 |
# archive pending) |
| 55 |
my($delim); # separator between output columns (fixed-width columns if not supplied) |
| 56 |
|
| 57 |
my($dsn); # database connection arguments |
| 58 |
my($dbh); # database handle |
| 59 |
my($stmnt); |
| 60 |
my($row); |
| 61 |
my($rowb); |
| 62 |
my($rrows); |
| 63 |
my($rrowsb); |
| 64 |
|
| 65 |
my($queryfunc); |
| 66 |
|
| 67 |
my($now); |
| 68 |
my($nowplus100d); |
| 69 |
|
| 70 |
my(%delnow); |
| 71 |
my(%delwi100d); |
| 72 |
my(%dellater); |
| 73 |
my(%archivepend); |
| 74 |
|
| 75 |
if ($#ARGV < 3) |
| 76 |
{ |
| 77 |
print "Improper argument list.\n"; |
| 78 |
exit(1); |
| 79 |
} |
| 80 |
|
| 81 |
$dbname = $ARGV[0]; |
| 82 |
$dbhost = $ARGV[1]; |
| 83 |
$dbport = $ARGV[2]; |
| 84 |
$dbuser = $ARGV[3]; |
| 85 |
|
| 86 |
if ($#ARGV >= 4) |
| 87 |
{ |
| 88 |
switch (lc($ARGV[4])) |
| 89 |
{ |
| 90 |
case kTypeQueryAgg {$typequery = kTypeQueryAgg; $queryfunc = \&GenQueryA;} |
| 91 |
case kTypeQueryRaw {$typequery = kTypeQueryRaw; $queryfunc = \&GenQueryB;} |
| 92 |
else {print "Invalid query type $ARGV[4].\n"; exit(1);} |
| 93 |
} |
| 94 |
} |
| 95 |
else |
| 96 |
{ |
| 97 |
$typequery = kTypeQueryAgg; |
| 98 |
$queryfunc = \&GenQueryA; |
| 99 |
$order = kTypeOrderSeries; |
| 100 |
} |
| 101 |
|
| 102 |
if ($#ARGV >= 5) |
| 103 |
{ |
| 104 |
switch (lc($ARGV[5])) |
| 105 |
{ |
| 106 |
case kTypeOrderSeries {$order = kTypeOrderSeries;} |
| 107 |
case kTypeOrderGroup {$order = kTypeOrderGroup;} |
| 108 |
else {print "Invalid order specified $ARGV[5].\n"; exit(1);} |
| 109 |
} |
| 110 |
} |
| 111 |
else |
| 112 |
{ |
| 113 |
$order = kTypeOrderSeries; |
| 114 |
} |
| 115 |
|
| 116 |
if ($#ARGV >= 6) |
| 117 |
{ |
| 118 |
switch (lc($ARGV[6])) |
| 119 |
{ |
| 120 |
case kMetricAll |
| 121 |
{ |
| 122 |
if ($typequery eq kTypeQueryRaw) |
| 123 |
{ |
| 124 |
print "Metric all cannot be used with an un-aggregated query.\n"; |
| 125 |
exit(1); |
| 126 |
} |
| 127 |
|
| 128 |
$metric = kMetricAll; |
| 129 |
} |
| 130 |
case kMetricDPS {$metric = kMetricDPS;} |
| 131 |
case kMetricDPM {$metric = kMetricDPM;} |
| 132 |
case kMetricDPL {$metric = kMetricDPL;} |
| 133 |
case kMetricAP {$metric = kMetricAP;} |
| 134 |
else {print "Invalid metric specified $ARGV[6].\n"; exit(1);} |
| 135 |
} |
| 136 |
} |
| 137 |
else |
| 138 |
{ |
| 139 |
if ($typequery eq kTypeQueryRaw) |
| 140 |
{ |
| 141 |
# For non-aggregated queries, do not attempt to perform multiple queries for multiple metrics |
| 142 |
$metric = kMetricAP; |
| 143 |
} |
| 144 |
else |
| 145 |
{ |
| 146 |
$metric = kMetricAll; |
| 147 |
} |
| 148 |
} |
| 149 |
|
| 150 |
if ($#ARGV >= 7) |
| 151 |
{ |
| 152 |
$delim = substr($ARGV[7], 0, 1); |
| 153 |
} |
| 154 |
if ($#ARGV >= 8) |
| 155 |
{ |
| 156 |
$grouparg = $ARGV[8]; |
| 157 |
} |
| 158 |
$quiet = 0; |
| 159 |
if ($#ARGV >= 9) |
| 160 |
{ |
| 161 |
$quiet = $ARGV[9]; #dont print out headers |
| 162 |
} |
| 163 |
|
| 164 |
$err = 0; |
| 165 |
|
| 166 |
# connect to the database |
| 167 |
$dsn = "dbi:Pg:dbname=$dbname;host=$dbhost;port=$dbport"; |
| 168 |
if(!$quiet) { |
| 169 |
print "Connection to database with '$dsn' as user '$dbuser' ... "; |
| 170 |
} |
| 171 |
|
| 172 |
# Despite ALL documentation saying otherwise, it looks like the error codes/string |
| 173 |
# provided by DBI are all UNDEFINED, unless there is some kind of failure. So, |
| 174 |
# never try to look at $dbh->err or $dbh->errstr if the call succeeded. |
| 175 |
$dbh = DBI->connect($dsn, $dbuser, ''); # will need to put pass in .pg_pass |
| 176 |
|
| 177 |
if (defined($dbh)) |
| 178 |
{ |
| 179 |
if(!$quiet) { print "success!\n"; } |
| 180 |
|
| 181 |
# Loop over storage groups |
| 182 |
$stmnt = "SELECT group_id FROM sum_partn_alloc GROUP BY group_id ORDER BY group_id"; |
| 183 |
$rrows = $dbh->selectall_arrayref($stmnt, undef); |
| 184 |
$err = !(NoErr($rrows, \$dbh, $stmnt)); |
| 185 |
|
| 186 |
if (!$err) |
| 187 |
{ |
| 188 |
my($timenow) = time(); |
| 189 |
my($timeo) = localtime($timenow); |
| 190 |
my($timeltro) = localtime($timenow + 100 * 24 * 60 * 60); |
| 191 |
|
| 192 |
$now = sprintf("%04d%02d%02d%02d%02d", $timeo->year() + 1900, $timeo->mon() + 1, $timeo->mday(), $timeo->hour(), $timeo->min()); |
| 193 |
$nowplus100d = sprintf("%04d%02d%02d%02d%02d", $timeltro->year() + 1900, $timeltro->mon() + 1, $timeltro->mday(), $timeltro->hour(), $timeltro->min()); |
| 194 |
|
| 195 |
|
| 196 |
if ($typequery) |
| 197 |
{ |
| 198 |
# Create a temporary table to hold pre-sorted results. |
| 199 |
$stmnt = "CREATE TEMPORARY TABLE " . kTempTable . "(tgroup integer not null, series varchar(64) not null, metric varchar(8), aggbytes bigint default 0)"; |
| 200 |
ExecStatement(\$dbh, $stmnt, 1, "Unable to create temporary table " . "'kTempTable'" . ".\n"); |
| 201 |
|
| 202 |
$stmnt = "CREATE INDEX " . kTempTable . "_group_idx on " . kTempTable . "(tgroup, lower(series))"; |
| 203 |
ExecStatement(\$dbh, $stmnt, 1, "Unable to create index on temporary table " . "'kTempTable'" . ".\n"); |
| 204 |
|
| 205 |
$stmnt = "CREATE INDEX " . kTempTable . "_series_idx on " . kTempTable . "(lower(series), tgroup)"; |
| 206 |
ExecStatement(\$dbh, $stmnt, 1, "Unable to create index on temporary table " . "'kTempTable'" . ".\n"); |
| 207 |
} |
| 208 |
|
| 209 |
# $rrows is a reference to an array; the array is an array of refereces to an array, so $row |
| 210 |
# is a reference to an array that has just one element (since the SELECT statement has just |
| 211 |
# one column). This element is the namespace name. |
| 212 |
foreach $row (@$rrows) |
| 213 |
{ |
| 214 |
$group = $row->[0]; |
| 215 |
|
| 216 |
if (kDEBUG) |
| 217 |
{ |
| 218 |
#$group = 9; |
| 219 |
$group = $grouparg; |
| 220 |
} |
| 221 |
|
| 222 |
# Delete now |
| 223 |
if ($metric eq kMetricAll || $metric eq kMetricDPS) |
| 224 |
{ |
| 225 |
$err = !$queryfunc->(\$dbh, $group, kStatDADP, "effective_date < '$now'", $typequery, $order, \%delnow, kMetricDPS); |
| 226 |
} |
| 227 |
|
| 228 |
# Delete <= 100 days AND > now |
| 229 |
if ($metric eq kMetricAll || $metric eq kMetricDPM) |
| 230 |
{ |
| 231 |
if (!$err) |
| 232 |
{ |
| 233 |
$err = !$queryfunc->(\$dbh, $group, kStatDADP, "effective_date <= '$nowplus100d' AND effective_date >= '$now'", $typequery, $order, \%delwi100d, kMetricDPM); |
| 234 |
} |
| 235 |
} |
| 236 |
|
| 237 |
if ($metric eq kMetricAll || $metric eq kMetricDPL) |
| 238 |
{ |
| 239 |
if (!$err) |
| 240 |
{ |
| 241 |
$err = !$queryfunc->(\$dbh, $group, kStatDADP, "effective_date > '$nowplus100d'", $typequery, $order, \%dellater, kMetricDPL); |
| 242 |
} |
| 243 |
} |
| 244 |
|
| 245 |
# Archive Pending |
| 246 |
if ($metric eq kMetricAll || $metric eq kMetricAP) |
| 247 |
{ |
| 248 |
if (!$err) |
| 249 |
{ |
| 250 |
$err = !$queryfunc->(\$dbh, $group, kStatDAAP . " AND archive_substatus = " . kSubStatDAADP, "", $typequery, $order, \%archivepend, kMetricAP); |
| 251 |
} |
| 252 |
} |
| 253 |
|
| 254 |
if (kDEBUG) |
| 255 |
{ |
| 256 |
last; |
| 257 |
} |
| 258 |
} # loop over storage groups |
| 259 |
|
| 260 |
SortAndPrintResults(\%delnow, \%delwi100d, \%dellater, \%archivepend, $typequery, $order, $metric, $delim, $dbh); |
| 261 |
} |
| 262 |
} |
| 263 |
else |
| 264 |
{ |
| 265 |
print "failure!!!!\n"; |
| 266 |
$err = 1; |
| 267 |
} |
| 268 |
|
| 269 |
# AL FINAL |
| 270 |
exit($err); |
| 271 |
|
| 272 |
# Aggregate |
| 273 |
sub GenQueryA |
| 274 |
{ |
| 275 |
my($dbh) = $_[0]; # reference to a reference |
| 276 |
my($group) = $_[1]; |
| 277 |
my($status) = $_[2]; |
| 278 |
my($datewhere) = $_[3]; |
| 279 |
my($typequery) = $_[4]; |
| 280 |
my($order) = $_[5]; |
| 281 |
my($container) = $_[6]; # reference |
| 282 |
my($metric) = $_[7]; |
| 283 |
|
| 284 |
my($ok) = 1; |
| 285 |
|
| 286 |
my($stmnt); |
| 287 |
|
| 288 |
if (length($datewhere) > 0) |
| 289 |
{ |
| 290 |
$datewhere = " AND $datewhere"; |
| 291 |
} |
| 292 |
|
| 293 |
$stmnt = "INSERT INTO " . kTempTable . " (tgroup, series, metric, aggbytes) (SELECT '$group', main.owning_series, '$metric', sum(bytes) FROM (SELECT ds_index, group_id, bytes FROM sum_partn_alloc WHERE status = $status AND group_id = $group$datewhere) AS partn, (SELECT ds_index, owning_series FROM sum_main WHERE storage_group = $group) AS main WHERE partn.ds_index = main.ds_index GROUP BY partn.group_id, main.owning_series)"; |
| 294 |
|
| 295 |
if (kDEBUG) |
| 296 |
{ |
| 297 |
if(!$quiet) { |
| 298 |
print "Query is:\n$stmnt\n"; |
| 299 |
} |
| 300 |
} |
| 301 |
|
| 302 |
ExecStatement($dbh, $stmnt, 1, "Unable to insert data into temporary table.\n"); |
| 303 |
|
| 304 |
return $ok; |
| 305 |
} |
| 306 |
|
| 307 |
# Don't aggregate |
| 308 |
sub GenQueryB |
| 309 |
{ |
| 310 |
my($dbh) = $_[0]; # reference to a reference |
| 311 |
my($group) = $_[1]; |
| 312 |
my($status) = $_[2]; |
| 313 |
my($datewhere) = $_[3]; |
| 314 |
my($typequery) = $_[4]; |
| 315 |
my($order) = $_[5]; |
| 316 |
my($container) = $_[6]; # reference |
| 317 |
my($metric) = $_[7]; |
| 318 |
|
| 319 |
my($ok) = 1; |
| 320 |
|
| 321 |
my($stmnt); |
| 322 |
my($rrows); |
| 323 |
|
| 324 |
if (length($datewhere) > 0) |
| 325 |
{ |
| 326 |
$datewhere = " AND $datewhere"; |
| 327 |
} |
| 328 |
|
| 329 |
# change sort order to (series, online_loc) kehcheng 2020.08.21 |
| 330 |
# $stmnt = "SELECT main.owning_series, main.ds_index, main.online_loc, partn.bytes, partn.sumid, partn.effective_date FROM (SELECT ds_index, group_id, bytes, sumid, effective_date FROM sum_partn_alloc WHERE status = $status AND group_id = $group$datewhere) AS partn, (SELECT ds_index, owning_series, online_loc FROM sum_main WHERE storage_group = $group) AS main WHERE partn.ds_index = main.ds_index ORDER BY lower(main.owning_series), main.ds_index"; |
| 331 |
|
| 332 |
$stmnt = "SELECT main.owning_series, main.ds_index, main.online_loc, partn.bytes, partn.sumid, partn.effective_date FROM (SELECT ds_index, group_id, bytes, sumid, effective_date FROM sum_partn_alloc WHERE status = $status AND group_id = $group$datewhere) AS partn, (SELECT ds_index, owning_series, online_loc FROM sum_main WHERE storage_group = $group) AS main WHERE partn.ds_index = main.ds_index ORDER BY lower(main.owning_series), main.online_loc"; |
| 333 |
|
| 334 |
if (kDEBUG) |
| 335 |
{ |
| 336 |
if(!$quiet) { |
| 337 |
print "Query is:\n$stmnt\n"; |
| 338 |
} |
| 339 |
} |
| 340 |
|
| 341 |
$rrows = $$dbh->selectall_arrayref($stmnt, undef); |
| 342 |
$ok = NoErr($rrows, $$dbh, $stmnt); |
| 343 |
|
| 344 |
if ($ok) |
| 345 |
{ |
| 346 |
# save results |
| 347 |
$ok = SaveResults($rrows, $group, $typequery, $order, $container); |
| 348 |
} |
| 349 |
|
| 350 |
return $ok; |
| 351 |
} |
| 352 |
|
| 353 |
sub SaveResults |
| 354 |
{ |
| 355 |
my($rrows) = $_[0]; # reference to array |
| 356 |
my($group) = $_[1]; # scalar |
| 357 |
my($typequery) = $_[2]; # scalar |
| 358 |
my($order) = $_[3]; # scalar |
| 359 |
my($container) = $_[4]; # reference to hash |
| 360 |
|
| 361 |
my($row); |
| 362 |
my($ok) = 1; |
| 363 |
|
| 364 |
switch ($typequery) |
| 365 |
{ |
| 366 |
case kTypeQueryAgg |
| 367 |
{ |
| 368 |
# Changed to use a temporary table to hold results. No need to use hash arrays |
| 369 |
# to hold the data. |
| 370 |
} |
| 371 |
case kTypeQueryRaw |
| 372 |
{ |
| 373 |
# row is series, ds_index, sudir, bytes |
| 374 |
switch ($order) |
| 375 |
{ |
| 376 |
case kTypeOrderSeries |
| 377 |
{ |
| 378 |
foreach $row (@$rrows) |
| 379 |
{ |
| 380 |
if (defined($container->{lc($row->[0])})) |
| 381 |
{ |
| 382 |
push(@{$container->{lc($row->[0])}->{$group}}, [$row->[1], $row->[2], $row->[3]]) |
| 383 |
} |
| 384 |
else |
| 385 |
{ |
| 386 |
$container->{lc($row->[0])} = {$group => [[$row->[1], $row->[2], $row->[3]]]}; |
| 387 |
} |
| 388 |
} |
| 389 |
} |
| 390 |
case kTypeOrderGroup |
| 391 |
{ |
| 392 |
$container->{$group} = []; |
| 393 |
|
| 394 |
foreach $row (@$rrows) |
| 395 |
{ |
| 396 |
push(@{$container->{$group}}, [lc($row->[0]), $row->[1], $row->[2], $row->[3], $row->[4], $row->[5]]); |
| 397 |
#print "row>[0]=$row->[0], row>[1]=$row->[1], row>2=$row->[2], row>3=$row->[3], row>4=$row->[4], row>5=$row->[5]\n"; |
| 398 |
} |
| 399 |
} |
| 400 |
else |
| 401 |
{ |
| 402 |
print "Invalid column $order by which to order.\n"; |
| 403 |
$ok = 0; |
| 404 |
} |
| 405 |
} |
| 406 |
} |
| 407 |
else |
| 408 |
{ |
| 409 |
print "Invalid query type $typequery.\n"; |
| 410 |
$ok = 0; |
| 411 |
} |
| 412 |
} # switch type query |
| 413 |
|
| 414 |
return $ok; |
| 415 |
} |
| 416 |
|
| 417 |
sub NoErr |
| 418 |
{ |
| 419 |
my($rv) = $_[0]; |
| 420 |
my($dbh) = $_[1]; |
| 421 |
my($stmnt) = $_[2]; |
| 422 |
my($ok) = 1; |
| 423 |
|
| 424 |
if (!defined($rv) || !$rv) |
| 425 |
{ |
| 426 |
if (defined($$dbh) && defined($$dbh->err)) |
| 427 |
{ |
| 428 |
print STDERR "Error " . $$dbh->errstr . ": Statement '$stmnt' failed.\n"; |
| 429 |
} |
| 430 |
|
| 431 |
$ok = 0; |
| 432 |
} |
| 433 |
|
| 434 |
return $ok; |
| 435 |
} |
| 436 |
|
| 437 |
sub ExecStatement |
| 438 |
{ |
| 439 |
my($dbh, $stmnt, $doit, $msg) = @_; |
| 440 |
my($res); |
| 441 |
|
| 442 |
if(!$quiet) { |
| 443 |
print "executing db statement ==> $stmnt\n"; |
| 444 |
} |
| 445 |
|
| 446 |
if ($doit) |
| 447 |
{ |
| 448 |
$res = $$dbh->do($stmnt); |
| 449 |
NoErr($res, $dbh, $stmnt) || die $msg; |
| 450 |
} |
| 451 |
} |
| 452 |
|
| 453 |
use constant kTypeSortNumrcAsc => 1; |
| 454 |
use constant kTypeSortAlphaAsc => 2; |
| 455 |
|
| 456 |
sub CombineHashKeys |
| 457 |
{ |
| 458 |
my($typesort) = $_[0]; |
| 459 |
my($out) = $_[1]; # reference |
| 460 |
my(@hashes) = @_[2..$#_]; # array of hash references |
| 461 |
my($ahash); |
| 462 |
my(@superduper); |
| 463 |
my(@sorted); |
| 464 |
my(%seen); |
| 465 |
my($elem); |
| 466 |
|
| 467 |
my($ok) = 1; |
| 468 |
|
| 469 |
foreach $ahash (@hashes) |
| 470 |
{ |
| 471 |
if (defined($ahash)) |
| 472 |
{ |
| 473 |
push(@superduper, keys(%$ahash)); |
| 474 |
} |
| 475 |
} |
| 476 |
|
| 477 |
# sort |
| 478 |
switch ($typesort) |
| 479 |
{ |
| 480 |
case kTypeSortNumrcAsc |
| 481 |
{ |
| 482 |
@sorted = sort {$a <=> $b} @superduper; |
| 483 |
} |
| 484 |
case kTypeSortAlphaAsc |
| 485 |
{ |
| 486 |
@sorted = sort {$a cmp $b} @superduper; |
| 487 |
} |
| 488 |
else |
| 489 |
{ |
| 490 |
print "Unsupported sort operation '$typesort'.\n"; |
| 491 |
$ok = 0; |
| 492 |
} |
| 493 |
} |
| 494 |
|
| 495 |
# eliminate duplicates |
| 496 |
foreach $elem (@sorted) |
| 497 |
{ |
| 498 |
push(@$out, $elem) unless $seen{$elem}++; |
| 499 |
} |
| 500 |
|
| 501 |
return $ok; |
| 502 |
} |
| 503 |
|
| 504 |
sub PrintRow |
| 505 |
{ |
| 506 |
my($delim) = $_[0]; |
| 507 |
my($firstarg) = $_[1]; |
| 508 |
my($secondarg) = $_[2]; |
| 509 |
my($hbytes) = $_[3]; # reference |
| 510 |
my($dformat) = $_[4]; # reference to delimted string format |
| 511 |
my($fformat) = $_[5]; # reference to fixed-width string format |
| 512 |
|
| 513 |
my($line); |
| 514 |
my($actualformat); |
| 515 |
|
| 516 |
$actualformat = defined($delim) ? $dformat : $fformat; |
| 517 |
|
| 518 |
$line = sprintf($$actualformat, |
| 519 |
$firstarg, |
| 520 |
$secondarg, |
| 521 |
defined($hbytes->{+kMetricDPS}) ? $hbytes->{+kMetricDPS} / kGig : 0, |
| 522 |
defined($hbytes->{+kMetricDPM}) ? $hbytes->{+kMetricDPM} / kGig : 0, |
| 523 |
defined($hbytes->{+kMetricDPL}) ? $hbytes->{+kMetricDPL} / kGig : 0, |
| 524 |
defined($hbytes->{+kMetricAP}) ? $hbytes->{+kMetricAP} / kGig : 0); |
| 525 |
|
| 526 |
print "$line\n"; |
| 527 |
} |
| 528 |
|
| 529 |
# Orders by series, group first. If caller requests ordering by group, series, the |
| 530 |
# rows are re-ordered. |
| 531 |
sub SortAndPrintResults |
| 532 |
{ |
| 533 |
# Each of the elements in each of these hash arrays is a reference to a hash array. |
| 534 |
# The parent hash array is keyed by series name. Each child hash array is |
| 535 |
# keyed by group with byte count values. The parent hash arrays do not necessarily |
| 536 |
# have the same set of series. |
| 537 |
my($delnow) = $_[0]; |
| 538 |
my($delwi100d) = $_[1]; |
| 539 |
my($dellater) = $_[2]; |
| 540 |
my($archivepend) = $_[3]; |
| 541 |
my($typequery) = $_[4]; |
| 542 |
my($order) = $_[5]; |
| 543 |
my($metric) = $_[6]; |
| 544 |
my($delim) = $_[7]; |
| 545 |
my($dbh) = $_[8]; |
| 546 |
|
| 547 |
my(@serieslist); |
| 548 |
my(@grouplist); |
| 549 |
my($elem); |
| 550 |
my($series); |
| 551 |
my($group); |
| 552 |
my($line); |
| 553 |
|
| 554 |
my(@sorted); |
| 555 |
my($dnow); |
| 556 |
my($d100); |
| 557 |
my($dlater); |
| 558 |
my($ap); |
| 559 |
my($metricval); |
| 560 |
|
| 561 |
my(%metricheaders); |
| 562 |
my(%containers); |
| 563 |
my(@contkeys); |
| 564 |
my($contkey); |
| 565 |
|
| 566 |
my($stmnt); |
| 567 |
my($rrows); |
| 568 |
my($row); |
| 569 |
|
| 570 |
my($ok); |
| 571 |
|
| 572 |
$ok = 1; |
| 573 |
|
| 574 |
%containers = (kMetricDPS, $delnow, kMetricDPM, $delwi100d, kMetricDPL, $dellater, kMetricAP, $archivepend); |
| 575 |
@contkeys = keys(%containers); |
| 576 |
|
| 577 |
switch ($typequery) |
| 578 |
{ |
| 579 |
case kTypeQueryAgg |
| 580 |
{ |
| 581 |
my(%hbytes); # sum(bytes) of the 4 containers for current series. |
| 582 |
|
| 583 |
%metricheaders = (kMetricDPS, "DP Now (GB)", kMetricDPM, "DP <= 100d (GB)", kMetricDPL, "DP > 100d (GB)", kMetricAP, "AP (GB)"); |
| 584 |
|
| 585 |
switch ($order) |
| 586 |
{ |
| 587 |
case kTypeOrderSeries |
| 588 |
{ |
| 589 |
# type - agg; order - series |
| 590 |
if (defined($delim)) |
| 591 |
{ |
| 592 |
$line = sprintf("series${delim}group${delim}$metricheaders{+kMetricDPS}${delim}$metricheaders{+kMetricDPM}${delim}$metricheaders{+kMetricDPL}${delim}$metricheaders{+kMetricAP}"); |
| 593 |
} |
| 594 |
else |
| 595 |
{ |
| 596 |
$line = sprintf("%-48s%-8s%-24s%-24s%-24s%-24s", "series", "group", $metricheaders{+kMetricDPS}, $metricheaders{+kMetricDPM}, $metricheaders{+kMetricDPL}, $metricheaders{+kMetricAP}); |
| 597 |
} |
| 598 |
|
| 599 |
print "$line\n"; |
| 600 |
|
| 601 |
# Just use the db to do the sorting on the temporary table containing the data. |
| 602 |
$stmnt = "SELECT series, tgroup, metric, aggbytes FROM " . kTempTable . " ORDER BY lower(series), tgroup"; |
| 603 |
|
| 604 |
if ($ok) |
| 605 |
{ |
| 606 |
$rrows = $dbh->selectall_arrayref($stmnt, undef); |
| 607 |
$ok = NoErr($rrows, \$dbh, $stmnt); |
| 608 |
} |
| 609 |
|
| 610 |
$group = ""; |
| 611 |
$series = ""; |
| 612 |
|
| 613 |
if ($ok) |
| 614 |
{ |
| 615 |
%hbytes = (); |
| 616 |
if (defined($delim)) |
| 617 |
{ |
| 618 |
$dformat = "%s${delim}%s${delim}%f${delim}%f${delim}%f${delim}%f"; |
| 619 |
} |
| 620 |
|
| 621 |
$fformat = "%-48s%-8d%-24f%-24f%-24f%-24f"; |
| 622 |
|
| 623 |
foreach $row (@$rrows) |
| 624 |
{ |
| 625 |
if ((length($series) > 0 && $series ne $row->[0]) || |
| 626 |
(length($group) > 0 && $group ne $row->[1])) |
| 627 |
{ |
| 628 |
PrintRow($delim, $series, $group, \%hbytes, \$dformat, \$fformat); |
| 629 |
%hbytes = (); |
| 630 |
} |
| 631 |
|
| 632 |
$series = $row->[0]; |
| 633 |
$group = $row->[1]; |
| 634 |
$hbytes{$row->[2]} = $row->[3]; |
| 635 |
} |
| 636 |
|
| 637 |
# Must print last row since only the previous row is printed in the loop above |
| 638 |
PrintRow($delim, $series, $group, \%hbytes, \$dformat, \$fformat); |
| 639 |
} |
| 640 |
} |
| 641 |
case kTypeOrderGroup |
| 642 |
{ |
| 643 |
# type - agg; order - group |
| 644 |
if (defined($delim)) |
| 645 |
{ |
| 646 |
$line = sprintf("#group${delim}series${delim}$metricheaders{+kMetricDPS}${delim}$metricheaders{+kMetricDPM}${delim}$metricheaders{+kMetricDPL}${delim}$metricheaders{+kMetricAP}"); |
| 647 |
} |
| 648 |
else |
| 649 |
{ |
| 650 |
$line = sprintf("%-8s%-48s%-24s%-24s%-24s%-24s", "#group", "series", $metricheaders{+kMetricDPS}, $metricheaders{+kMetricDPM}, $metricheaders{+kMetricDPL}, $metricheaders{+kMetricAP}); |
| 651 |
} |
| 652 |
|
| 653 |
print "$line\n"; |
| 654 |
|
| 655 |
# Just use the db to do the sorting on the temporary table containing the data. |
| 656 |
$stmnt = "SELECT tgroup, series, metric, aggbytes FROM " . kTempTable . " ORDER BY tgroup, lower(series)"; |
| 657 |
|
| 658 |
if ($ok) |
| 659 |
{ |
| 660 |
$rrows = $dbh->selectall_arrayref($stmnt, undef); |
| 661 |
$ok = NoErr($rrows, \$dbh, $stmnt); |
| 662 |
} |
| 663 |
|
| 664 |
$group = ""; |
| 665 |
if (kDEBUG) #change by Jim to print out group if no hits |
| 666 |
{ |
| 667 |
#$group = 9; |
| 668 |
$group = $grouparg; |
| 669 |
} |
| 670 |
|
| 671 |
$series = ""; |
| 672 |
|
| 673 |
if ($ok) |
| 674 |
{ |
| 675 |
%hbytes = (); |
| 676 |
if (defined($delim)) |
| 677 |
{ |
| 678 |
$dformat = "%s${delim}%s${delim}%f${delim}%f${delim}%f${delim}%f"; |
| 679 |
} |
| 680 |
$fformat = "%-8d%-48s%-24f%-24f%-24f%-24f"; |
| 681 |
|
| 682 |
foreach $row (@$rrows) |
| 683 |
{ |
| 684 |
if ((length($group) > 0 && $group ne $row->[0]) || |
| 685 |
(length($series) > 0 && $series ne $row->[1])) |
| 686 |
{ |
| 687 |
PrintRow($delim, $group, $series, \%hbytes, \$dformat, \$fformat); |
| 688 |
%hbytes = (); |
| 689 |
} |
| 690 |
|
| 691 |
$group = $row->[0]; |
| 692 |
$series = $row->[1]; |
| 693 |
$hbytes{$row->[2]} = $row->[3]; |
| 694 |
} |
| 695 |
|
| 696 |
# Must print last row since only the previous row is printed in the loop above |
| 697 |
PrintRow($delim, $group, $series, \%hbytes, \$dformat, \$fformat); |
| 698 |
} |
| 699 |
} |
| 700 |
else |
| 701 |
{ |
| 702 |
print "Invalid column $order by which to order.\n"; |
| 703 |
} |
| 704 |
} # switch $order |
| 705 |
} # case agg |
| 706 |
case kTypeQueryRaw |
| 707 |
{ |
| 708 |
# **** Can only work with a single container at a time when the query type is raw!! Each container uses up a |
| 709 |
# huge amount of memory, so this script must be modified to handle a single one specified on the cmd-line. |
| 710 |
# row is series, ds_index, sudir, bytes |
| 711 |
my(%topheaders); |
| 712 |
|
| 713 |
# Only one of the containers should be non-empty - if that is not the case, that is an error |
| 714 |
if ($metric eq kMetricAll) |
| 715 |
{ |
| 716 |
print "Cannot generate non-aggregate report for more than one metric.\n"; |
| 717 |
$ok = 0; |
| 718 |
} |
| 719 |
else |
| 720 |
{ |
| 721 |
%metricheaders = (kMetricDPS, "DP Now (bytes)", kMetricDPM, "DP <= 100d (bytes)", kMetricDPL, "DP > 100d (bytes)", kMetricAP, "AP (bytes)"); |
| 722 |
%topheaders = (kMetricDPS, "*** DP Now ***", kMetricDPM, "*** DP <= 100d ***", kMetricDPL, "*** DP > 100d ***", kMetricAP, "#*** AP ***"); |
| 723 |
|
| 724 |
my($sunum); |
| 725 |
my($sudir); |
| 726 |
my($rowdata); |
| 727 |
|
| 728 |
switch ($order) |
| 729 |
{ |
| 730 |
case kTypeOrderSeries |
| 731 |
{ |
| 732 |
# type - raw; order - series |
| 733 |
print "$topheaders{$metric}\n"; |
| 734 |
if (defined($delim)) |
| 735 |
{ |
| 736 |
$line = "series${delim}group${delim}sunum${delim}sudir${delim}$metricheaders{$metric}"; |
| 737 |
} |
| 738 |
else |
| 739 |
{ |
| 740 |
$line = sprintf("%-32s%-8s%-16s%-24s%-24s", "series", "group", "sunum", "sudir", $metricheaders{$metric}); |
| 741 |
} |
| 742 |
|
| 743 |
print "$line\n"; |
| 744 |
|
| 745 |
if (CombineHashKeys(kTypeSortAlphaAsc, \@serieslist, $containers{$metric})) |
| 746 |
{ |
| 747 |
foreach $elem (@serieslist) |
| 748 |
{ |
| 749 |
$series = $elem; |
| 750 |
@grouplist = (); |
| 751 |
|
| 752 |
if (CombineHashKeys(kTypeSortNumrcAsc, \@grouplist, $containers{$metric}->{$series})) |
| 753 |
{ |
| 754 |
foreach $group (@grouplist) |
| 755 |
{ |
| 756 |
if (defined($containers{$metric}->{$series}->{$group})) |
| 757 |
{ |
| 758 |
# $containers{$metric}->{$series}->{$group} is reference to an array with array references |
| 759 |
# as elements. The child arrays have sunum, sudir, and bytes |
| 760 |
# as elements. |
| 761 |
foreach $rowdata (@{$containers{$metric}->{$series}->{$group}}) |
| 762 |
{ |
| 763 |
# $rowdata is a reference to an array containing sunum, sudir, and bytes |
| 764 |
# as elements. |
| 765 |
$sunum = $rowdata->[0]; |
| 766 |
$sudir = $rowdata->[1]; |
| 767 |
$metricval = $rowdata->[2]; |
| 768 |
|
| 769 |
if (defined($delim)) |
| 770 |
{ |
| 771 |
$line = sprintf("$series${delim}$group${delim}$sunum${delim}$sudir${delim}%d", $metricval); |
| 772 |
} |
| 773 |
else |
| 774 |
{ |
| 775 |
$line = sprintf("%-32s%-8d%-16s%-24s%-24d", $series, $group, $sunum, $sudir, $metricval); |
| 776 |
} |
| 777 |
|
| 778 |
print "$line\n"; |
| 779 |
} |
| 780 |
} |
| 781 |
} |
| 782 |
} |
| 783 |
else |
| 784 |
{ |
| 785 |
print "Problem creating group list - continuing.\n"; |
| 786 |
} |
| 787 |
} |
| 788 |
} |
| 789 |
else |
| 790 |
{ |
| 791 |
print "Problem creating series list - bailing.\n"; |
| 792 |
} |
| 793 |
} |
| 794 |
case kTypeOrderGroup |
| 795 |
{ |
| 796 |
my($sunum); |
| 797 |
my($sudir); |
| 798 |
my($rowdata); |
| 799 |
|
| 800 |
print "$topheaders{$metric}\n"; |
| 801 |
if (defined($delim)) |
| 802 |
{ |
| 803 |
$line = "#group${delim}series${delim}sunum${delim}sudir${delim}$metricheaders{$metric}${delim}sumid${delim}eff_date"; |
| 804 |
} |
| 805 |
else |
| 806 |
{ |
| 807 |
$line = sprintf("%-8s%-32s%-16s%-24s%-24s", "group", "series", "sunum", "sudir", $metricheaders{$metric}); |
| 808 |
} |
| 809 |
|
| 810 |
print "$line\n"; |
| 811 |
|
| 812 |
# The data in the containers are ordered by group, series. The tuples are |
| 813 |
# (series, ds_index, sudir, bytes). So there is very little work to do here. |
| 814 |
@grouplist = (); |
| 815 |
|
| 816 |
if (CombineHashKeys(kTypeSortNumrcAsc, \@grouplist, $containers{$metric})) |
| 817 |
{ |
| 818 |
foreach $group (@grouplist) |
| 819 |
{ |
| 820 |
foreach $rowdata (@{$containers{$metric}->{$group}}) |
| 821 |
{ |
| 822 |
$series = $rowdata->[0]; |
| 823 |
$sunum = $rowdata->[1]; |
| 824 |
$sudir = $rowdata->[2]; |
| 825 |
$metricval = $rowdata->[3]; |
| 826 |
$sumid = $rowdata->[4]; |
| 827 |
$effdate = $rowdata->[5]; |
| 828 |
|
| 829 |
if (defined($delim)) |
| 830 |
{ |
| 831 |
$line = sprintf("$group${delim}$series${delim}$sunum${delim}$sudir${delim}%d${delim}$sumid${delim}$effdate", $metricval); |
| 832 |
} |
| 833 |
else |
| 834 |
{ |
| 835 |
$line = sprintf("%-8d%-32s%-16s%-24s%-24d", $group, $series, $sunum, $sudir, $metricval); |
| 836 |
} |
| 837 |
|
| 838 |
print "$line\n"; |
| 839 |
} |
| 840 |
} |
| 841 |
} |
| 842 |
} |
| 843 |
else |
| 844 |
{ |
| 845 |
print "Invalid column $order by which to order.\n"; |
| 846 |
$ok = 0; |
| 847 |
} |
| 848 |
} |
| 849 |
} |
| 850 |
} |
| 851 |
else |
| 852 |
{ |
| 853 |
print "Invalid query type $typequery.\n"; |
| 854 |
} |
| 855 |
} # switch query type |
| 856 |
|
| 857 |
# TODO - print out totals by group |
| 858 |
} |
