Informix and Perl: they match pretty well!

The unix shell is good, but there are much better and you have already

As an Emeritus Informix DBA, you have certainly developed a number of command shell scripts. This language is very convenient and versatile to ensure your administrative tasks on IBM Informix Dynamic Server. When combined with the usual commands such as onmode, onstat, oncheck, onparams and others, you have set up a range of scripts that allow you to automatically manage your backups, the execution of UPDATE STATISTICS, the management of certain alarms, the start-stop of your instances, as well as the connexion to these instances. You probably added monitoring scripts based on onstat and oncheck.

But to be honest, when we read the scripts for maintenance purpose, or worse when we have to edit a script written by someone else, the code complexity level is often inversely proportional to the ease of reading. It is true that after the seventh level of nesting of “pipe”, we are not sure about what is going to happen.

In fact, if the unix shell includes a large number of useful and relevant orders, the cement use to tie these commands together in order to make applications is not simple to understand and even less easy to debug. Also, if you need to install these scripts on another flavor of Unix, everything will increase in complexity because a lot of commands either do not react the same way, or produce syntax errors straight ahead. I do not even mention the nightmare scenario of deploying an instance on Windows or McIntosh, who are now part of IBM Informix usual platforms , you will probably have to rewrite everything …

It is just in front of your eyes, and it does a lot of things!

This is true, the Unix shell effectively addresses the immediate needs when deadlines are short. However, too many issues, including compatibility , performance and ease of maintenance, will negatively impact the industrialization of our administration processes. I began to measure the true extent of the problem when, in 1997, a friend recommended me to look closely at the language Perl (Practical Extraction Report Language).

It looks tiny, but it isn’t!

Beyond the original role of Perl which is text parsing, I quickly realized that I had in my hands a real development language that combined the attributes of a low-level language like C, awk and sed utilities, including the majority of the Unix shell commands. It handles scalar variables, arrays, hashes,  variables scope, binary and unary operators, logical blocks of statements,  functions with parameters passed by reference or by value, an I/O management very close to the system, and finally the powerful regular expressions. We feel strongly the relationship with the C language, but this is still much easier and faster to code and read (farewell core dumped and sigsegv !)

No fear!

You also have a very good interactive debugger (no more “set-x” forgotten in the scripts!). About performance, Perl does not have the speed of C, since it is an interpreted language, but its performance remains very honorable (Ex 2 sec. to read through a file of 32 Mb/640 000 lines).

But what is the real interest as per compared with the Unix shell?

As to begin, Perl can do everything that the shell can do, but in a much simpler way. No more contortions and somersaults between cat, awk, sort, grep, ls, read, echo etc! With Perl, the code goes directly to the intended purpose. On the other side, Perl can do things that the Unix shell has no clue about, particularly in the field of system programming. For example, it is simple to manage an inter-process connexion via IPC, manage in/out message queues and more goodies like that.

Perl feels at ease everywhere

Another great advantage of Perl is its portability. As long as you do not include platform-specific shell commands in your Perl programs, your Perl program will run identically on different platforms, be it on any Unix, Windows or MacIntosh.

But the strongest advantage is the rich library of modules available for free to the community. These modules cover a large range of functionality, going from extremely useful to more anecdotal. Imagine for instance you can translate about any date string  (eg “Mon, Dec. 26 17: 45: 14 2011 CET”) into an epoch integer variable, thus enabling you to calculate intervals between two timestamps ( the Time::ParseDate package, so useful when you parse a log file for example). As easy is to get all the properties of a file (stat), or read binary file and handle offsets within that file (IO :: handle). Cooler even is Image::ExifTool package to read image files properties, and the powerful Win32::OLE that allows read access or write to almost any object as Microsoft Outlook email box, Word documents, Excel etc …

In all those packages, one is specially interesting for us : DBD::Informix. This package is the module for communication with IBM Informix databases. Developed and maintained by a longtimer Informixian guru, and now venerable bearded IBMer, this module contains all the necessary features to use the SQL language as you would with ESQL/C, dbaccess or Informix-4GL.

Wanna try ?

For reasons you can probably imagine, the DBD: Informix module is ​​not part of basic packages that usually come with Perl (neither does the “Evil Empiretabase” one). You will find it on CPAN, the vast repository of Perl packages. I must admit that the former versions were not that easy to install. Nevertheless, the last version is much easier to install, as long as you carefully follow the instructions.

Required prerequisites:

– Have Perl version greater than or equal to 5.14 to be in full compliance,
– Have esql /C greater than  7.31 or even better Informix CSDK 3..50 or 3.70 according to your Informix version.
– Have an engine or IBM Informix Informix (SE or IDS) “on line”
– Have the stores demo database created
– Have created the user informix
– Have an Internet connection up and running,
– No problem on Linux, and to my knowledge in other Unix platforms, more delicate for the moment on the Windows platform, but stay tuned …

Before starting the installation, you must, logged in as root, be able to connect to the stores database with dbaccess, therefore have the $INFORMIXDIR, $INFORMIXSERVER and $PATH environment variables correctly set. You also must be able to run an esql /c binary, thus have the  $LD_LIBRARY_PATH (Linux, AIX), or $ LIBPATH (solaris etc.) variable set to “$ INFORMIXDIR /lib:$INFORMIXDIR  lib/esql:$INFORMIXDIR/lib/ tools “

Once these preliminary test done, you can type:

perl-MCPAN-e ‘install Bundle::DBD::Informix’

Then let the system (the CPAN tool) manage the case by itself. As long as the prerequisites are met, the installation is done without a hitch. By cons, if you forget, the error messages are somewhat demotivating. It is then sufficient to remedy the missing prerequisites and run the command mentioned above. Perl CPAN installation will resume where it left off.

Some error messages may appear along the way. As the installation script does not stop, so good. The installation ends with “/usr/bin/make install – OK”.

On seeing this message, you are ready to use the DBD::Informix. Do a quick check with a find /usr -name informix.pm, to make sure that you have informix.pm somewhere in  usr … … lib(64)/DBD/informix.pm.

You can also find it in ~root/.cpan/build/DBD-Informix-2011.0612- ~ *, which is the installation directory, but it must be absolutely in / usr / … libXX.

The installation is OK! Let’s take a look at the basics.

To use DBD::Informix, you must include it in the Perl script, noting the top of the module:

use DBD::Informix qw (: ix_types);

To connect to a database:

$dbh = DBI-connect (“dbi: Informix: $ databasename ‘, $ username, $ password);

To prepare a SELECT:

$stmt = sprintf “SELECT col1, col2, col3, col4 FROM mytable ORDER BY col2”;
$sth = $ dbh->prepare ($ stmt);
$sth–>execute ();

To read the cursor, and display the content:

$sqlcode = 0 ;
while ($ sqlcode! = 100) {
($ Var1, $ var2, $ var3, $ var4) = $ sth->fetchrow_array ();
printf “% s% s% s% d \ n”, $ var1, $ var2, $ var3, $ var4;
$sqlcode = $ sth->{ix_sqlcode};
}

But you can also fill an array containing the complete output of the cursor, then handle it in memory as a structure organized as the table image, like the RECORD LIKE Informix 4GL:
$ARRAY_REF = $sth-> fetchall_arrayref ({col1 =>1, col2 =>1, col3=>1, col4 => 1});
foreach $ row (@$ARRAY_REF) {
printf “% s% s% s% d \ n”, $ row->{col1}, $ row->{col2}, $ row->{col3},$row->{col4};
}

In terms of regular SQL statements, i-e INSERT, UPDATE, DELETE, you go find it by yourself:

$stmt = sprintf “INSERT INTO mytable (col1, col2, col3) VALUES (?,?,?)”;
$sth = $ dbh->prepare ($ stmt);
$sth->execute ($ val1, $ val2, $ val3);

By the way, you will see that the use of placeholders is extremely simple, since it is sufficient to put the values ​​in brackets for the execute statement.

To consolidate the use of placeholders, here’s an example with UPDATE:

$stmt = sprintf “UPDATE mytable SET (col2, col3) = (?,?) WHERE col1 =?” ;
$sth = $dbh->prepare ($ stmt);
$sth->execute ($ val2, $ val3, $ val1);

Transactions? Very simple:

$dbh->begin;
$dbh->commit;
$dbh–>rollback;

Unless you wish to simulate the ANSI mode, opening the connection in this way, the behavior of your transaction is identical to the one you used.

$ Dbh = DBI->connect (“dbi:Informix:$ databasename ‘, $ username, $ password,{AutoCommit =>1});

What about my cursor WITH HOLD?

Trivial question, just pass it as an argument during PREPARE, like this:

$sth = $dbh->prepare (“SELECT col1, col2 FROM mytable”, {‘ix_CursorWithHold’ =>1});

Can I handle the BLOBS ?

Telling the truth, the implementation of BLOBS is currently not as rich with ESQL / C, but we are not so far from it.

For the TEXT, BLOBS or other content which may be in a Perl variable, use the bind parameters, as follows:

$textlocation = sprintf “/ home/informix/textfiles/file1.txt”;
fopen TEXT,$textlocation or die “Can not open file”. $texlocation;
@FileContents = <TEXT> ;
$text_val = join (@ fileContents);
$ Pkey = 13452;
$timestamp = “2011-12-28 15:00:00”;
$stmt = sprint “INSERT mytable VALUES (?,?,?)”;
$sth = $ dbh->prepare ($ stmt);
$sth->bind_param (1, $ pkey);
$sth->bind_param (2, $ timestamp);
$sth->bind_param (3, $ text_val, {ix_type =&gt; IX_TEXT});
$sth->execute;

For BLOBS reading, they are mapped into a Perl variable of type string. However, we can use a LOCATE statement, as follows:

$sth->{ix_BlobLocation} = ‘InMemory’;       # Default Behavior
$sth->{ix_BlobLocation} = ‘InFile’;                # in a file whose name is made
$sth->{ix_BlobLocation} = ‘DummyValue’;  # return <text> or <byte>
$sth->{ix_BlobLocation} = ‘NullValue’;         # return undefined value

The SMARTBLOBS are not (yet) supported directly, BUT they can be handled with functions LOTFILE to read, FILETOBLB or FILETOCLOB to write.

To insert:

$stmt = qq!INSERT INTO item2 VALUES (1234, ‘Desk’, FILETOBLOB (‘image.bmp’, ‘client’))!;
$sth = $ dbh->prepare ($ stmt);
$sth->execute ();

To read:

$stmt = qq!SELECT LOTOFILE (myimage,? ‘Customer’) FROM mytable!;
$sth = $ dbh->prepare ($ stmt);
$sth->execute ($ imagefilename);
$sth->bind_columns ($ image));
while ($ sth->fetch) {
print $ picture;
…..
}

So finally, what is the real value of using Perl with DBD::Informix?

In conclusion, we could go on and on, but New Year’s Eve is approaching and concentration is beginning to evaporate.

For an Informix DBA, this combination allows the development of more powerful scripts in terms of functionality, namely for the “affordable” system programming facilities that it offers. Objects such as tables, hash tables, regular expressions and especially the huge library of modules covering virtually everything that can be done, give the developer a power that the unix shell never reached, not to talk about Windows shell…

It also streamlines the administration scripts, making them much more readable and easier to maintain. Finally, considerable argument, Perl offers great portability of your developments close to 100%, provided that one does not use O.S. specific statements. A script running on Linux will work without surprises on AIX, HP-UX, Solaris, Mac OS X and even Windows!

The icing on the cake is the ability to natively access to your Informix databases as does Informix 4GL, ESQL / C or java. From this point open the prospect of even larger developments, such as parse and integrate Microsoft Office documents into an Informix database, typify and classify images in an Informix database, but also to dig in your Mozilla Firefox bookmarks and history , integrating them into an Informix database. More generally you can consider developing very quickly and at a very low cost a Gateway between your favorite database and other databases , for example!

Concrete examples are poste on this site, in the dowload area.

Feel free to download and use immoderately!

In the meantime, while you think about all this, I wish you all the best for 2012!

Bibliography:

  • the reference document of Jonathan Leffler
  • Perl presentation at the IIUG Conference 2001
  • In the O’Reilly’s collection:
  • Programming Perl DBI by Alligator Descartes and Time Bunce,
  • Learning Perl by Tom Phoenix, Randal L. Schwartz
  • Programming Perl, by Larry Wall (the inventor of Perl), Tom Christiansen, Jon Orwant
  • Advanced Perl Programming, By Simon Cozens
  • Perl for System Administration by David Blank-Edelman
  • The Perl doc in French: http://perl.enstimac.fr/DocFr.html
  • Search the library CPAN: http://search.cpan.org/
  • And a lot of perl tutorials on the net

, , , , ,

3 Responses to “Informix and Perl: they match pretty well!”

  1. Sarbjit Singh Gill May 3, 2012 at 4:09 pm # Reply

    Very good pack of info. I will try these soon. Already having a plan to adopt perl path for my scripts !

Trackbacks/Pingbacks

  1. New release of Perl DBD::Informix available on CPAN | Vercelletto.com - February 3, 2013

    […] You will find an introduction/bootstrap tutorial at this place […]

  2. Excute Perl Script - dBforums - May 24, 2013

    […] the Informix Perl DBD driver, which allows you to use SQL statements in your perl scripts? Check here for an […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.