Home » Databases » Sybase » ASE » Ceiling() and Floor() functions for Perl?

Ceiling() and Floor() functions for Perl?

I was writing a routine to determine if we had enough space in our Sybase ASE server to perform a full reorg rebuild for each of our tables in each database and realized that I wanted to use the ceiling() function as Perl doesn’t have a round() function.

Unless you’re familiar with ceiling and floor from Excel, Java, whatever, you might wonder what they do. Let me show you:

Let’s use the number 23.2

  • ceiling(23.2) will produce 24
  • floor(23.2) will produce 23

How did this help me in my task? Well, the criteria I wanted was that if table_A needed 15mb more transaction log space in order to do a reorg rebuild, then I wanted the script to say “Please add 100mb more to the transaction log”. This will not only cover the current requirement, but it will give us a bit of room for table growth.

In Perl, the ceiling() function is actually named ceil() for some reason. So, let’s show a bit of code:

use POSIX qw(ceil floor);
   my $dbh = sybConnect($server, $login, $pass, { 'tds_keepalive' => 1, 'timeout' => 14400, 'syb_show_eed' => 1, 'syb_no_child_con' => 1 } )
        or die "RED ALERT (UNABLE TO CONNECT TO $server!):  $!\n";

    print "="x60 . "\n";
    print "   Will full reorg rebuild run on $server okay?\n";
    print "="x60 . "\n";

    foreach my $database ( dbList($dbh) ) {
        next if _ignore_database($database);

        if ( change_db($dbh, $database) ) {
            print "Database: $database\n";
            my $db_space_ref = dbSpace($dbh, $database);
            my $logFree = ($db_space_ref->{'log'} - $db_space_ref->{'log used'}) * 1024;
            my $dataFree = ($db_space_ref->{'data'} - $db_space_ref->{'used'}) * 1024;
            my $max_data_need = 0;
            my $max_log_need = 0;

            foreach my $table ( getTableList($dbh) ) {
                my $table_ref = getTableInfo($dbh, $table);
                my $table_est_reserved = $table_ref->{'est reserved'} * 1.25;

                if ( $table_est_reserved > $logFree) {
                    printf "\t$table : Requires %d MBytes free log space.  Log has %d MBytes.\n",
                        $table_est_reserved / 1024, $logFree / 1024;

                    if ($max_log_need < ($table_est_reserved - $logFree) / 1024) {
                        $max_log_need =  ($table_est_reserved - $logFree) / 1024;
                    }

                    $server_ok = undef;
                } elsif ( $table_est_reserved > $dataFree) {
                    printf "\t$table : Requires %d MBytes free data space.  Data has %d MBytes.\n",
                        $table_est_reserved / 1024, $dataFree / 1024;

                    if ($max_data_need < ($table_est_reserved - $dataFree) / 1024) {
                        $max_data_need =  ($table_est_reserved - $dataFree) / 1024;
                    }

                    $server_ok = undef;
                }
            }

            if ($max_log_need > 0) {
                printf " Please increase the log segment by %d MBytes\n", ceil($max_log_need / 100) * 100;
            }

            if ($max_data_need > 0) {
                printf " Please increase the data segments by %d MBytes\n", ceil($max_data_need / 100) * 100;
            }
        }
    }

    print "-"x60 . "\n";
    printf " Server %s will%s run full reorg rebuild ok at time of run\n", $server, $server_ok ? "" : " *NOT*";
    print "="x60 . "\n";

Note that I’m dividing the needed log and data space by 100, doing the ceil() function and then multiplying it by 100. What does this do?

Say the data needed is 45mb

  • 45 / 100 = 0.45
  • ceil(0.45) = 1
  • 1 * 100 = 100mb

Oh, you wanted to see a bit of the output for the script?

$ tail enough_space_for_reorg.test_server.out
 Please increase the log segment by 100 MBytes
Database: Potter_034
        Harry_Pottery_stat_hist : Requires 1346 MBytes free log space.  Log has 1337 MBytes.
 Please increase the log segment by 100 MBytes
Share Button

Comments

  1. Wehbe Ali says:

    Hi, am interested by your code to reorg rebuild sybase tables! will you send me the code? i took a hard copy of the screen but it didn’t work Thanks!

  2. Soup d'Campbells says:

    You should not use the CSS glow effect on source code. Your readers may be interested in actually reading the source; as it stands, most of your keywords are formless blobs.

    1. I’ll modify it shortly. thanks! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect