Where we're coming from....

Many Hats -- Adventures in Enterprise Management Systems (ETL)

  

Tweet  

  

  

 delicious  

  

  

Many Hats -- Adventrues in Enterprise Systems Management (Extraction, Transformation & Load)

In the wide, wide world of Enterprise Systems Monitoring and Management, ITSM pros find that we get to wear many hats. 

When given the opportunity to get your hands dirty you can muss them up while sporting the Scripting & Coding hat, often you get to wear the Engineer’s hat, and sometimes you get to wear the Systems Administrator’s hat.  For the last few days, I’ve been having fun times donning my DBA & ETL hat. 

The tussle was over the extraction of system data from an Oracle DB to be loaded into MySQL.  The queries I was running were fairly large (several hundred thousand rows) and my script would run fine for several minutes until Oracle threw off various errors; saying that it was running out of buffer, took too long to run the query, etc.  To feel my pain look here.  ORA-01406 fetched column value was truncated.

After several hours of researching the Oracle forums and websites for remedies to this error, I gently removed my sweat soaked DBA hat and replaced it with my faithful scripting hat -- the script in question pulls data from Oracle using DBI and DBD::Oracle (64 Bit….tune in for the next blog on how to get this installed on Linux). 

The script then pushes the Oracle data into MySQL using DBD::mysql.  It is simple enough to do except that it kept timing out.  The error above led me to think that this was an Oracle issue (not enough memory, time out, etc).  It wasn’t -- the problem was doing the push into MySQL while I was in the loop.  Here was my code before I fixed it:

#!<path to perl>

<declare variables>

use DBI;

use DBD::Oracle;

use DBD::mysql;

# Tell script where oracle lives

$ENV{'ORACLE_HOME'}="<path to oracle> /instantclient_10_2";

$ENV{'TNS_ADMIN'}="<path to oracle>/instantclient_10_2/network/admin";

$ENV{'LD_LIBRARY_PATH'}=$ENV{'LD_LIBRARY_PATH'}.":".$ENV{'ORACLE_HOME'};

$ENV{'PATH'}=$ENV{'PATH'}.":".$ENV{'ORACLE_HOME'};

#open conn to oracle

my $dbhMYSQL=DBI->connect('dbi:mysql:DB','mysqluser','mysqlpass');

my $dbhORACLE=DBI->connect('dbi:Oracle:Oradb','oracleuser','oraclepass');

$MyLongQuerySQL = “select …..”;

my $MyLongQuery=$dbhORACLE->prepare($MyLongQuerySQL);

$MyLongQuery->execute();

while (my @LongQueryData = $MyLongQuery->fetchrow_array())

        {

                # as I go through each Oracle row, insert the row into MySQL

  my $NewInsertSESQL = "insert into MSSwitchEnrichment values ('$LongQueryData[0]','$LongQueryData[1]','$LongQueryData[2]')";

                  my $NewInsertSE = $dbhMYSQL->prepare($NewInsertSESQL);

                  $NewInsertSE->execute();

    }

#<<<<<<<<<<<<END>>>>>>>>>>>>>>>

This worked when the Oracle Query and subsequent insert into MySQL was good if the Oracle query was only a few thousand rows.  Once the query exceeded multiple tens of thousands of rows it would time out.  This puzzled me when I did the Oracle query by the command line.  It had no problem sending the output to the screen….all 400K+ rows.

So this led me to consider the question what if I perform the Oracle query all at once and then store the results in an array?  I could then loop through the array and insert each one of THESE lines into MySQL.  Result?  Works great!  Here is the code fix below:

<everything is the same until you get to the first while loop, then>

while (my @LongQueryData = $MyLongQuery->fetchrow_array())

        {

                 # as I go through each Oracle row, insert the row into MySQL

  #my $NewInsertSESQL = "insert into MSSwitchEnrichment values ('$LongQueryData[0]','$LongQueryData[1]','$LongQueryData[2]')";

                  #my $NewInsertSE = $dbhMYSQL->prepare($NewInsertSESQL);

                  #$NewInsertSE->execute();

        my $entry = "";

                        $entry = "$LongQueryData[0], $LongQueryData[1], $LongQueryData[2]";

                        push (my @LongQueryInfo, $entry);

                        $entry="";

    }

#  Done with the Oracle part here, all Oracle lines are loaded into the array with commas separating each field.

# NOW we load the Mysql database with an array

foreach $LongQueryInfo (@LongQueryInfo)

{

@LongQueryEntry = split(",",$LongQueryInfo);

my $NewInsertSQL = "insert into MySQLTable (Field1, Field2, Field3) values ('$SEEntry[0]','$SEEntry[1]','$SEEntry[2]')";

my $NewInsert = $dbhMYSQL->prepare($NewInsertSQL);

$NewInsert->execute();

}

#<<<<<<<<<<<<<<<END OF FIX>>>>>>>>>>>>>>>>>>>>>>>>>

The script has many other parts to it and it is formatted better than what you see above, but the crux of the problem and solution are outlined above.  I hope this is helpful.  To get more details about this or share stories in Enterprise Systems Management, please reach me here at chris@mkadvantage.com

Christopher Schaft