[Perl/Informix] Automated Data Extraction

[Squirrelington]

I needed a way to automatically extract several tables from a database running Informix so I could DTS them into MS SQL 2k5+. This was an older Informix 7.4 server running Debian 3 sarge and every attempt at connect to this server via ODBC failed so I wrote this script.

#!/usr/bin/perl -X

##################################################################
# Export all tables from an Informix database using iSQL command #
#   by Travis Gutjahr                                            #
#   Version 2.1                                                  #
#   Updated July 14, 2011 11:18 UTC -05:00                       #
##################################################################

### VARIABLES

### Database to extract all tables from
$database = "maindb";

### Folder to extract files to
$backupfolder = "/backup/output";

### Delimiter to use
$delimiter = "?";

### SOURCE CODE - DO NOT EDIT ###

print "Retrieving table list...\n";
$tableinfoexport = `isql $database << EOF
output to pipe "cat" without headings
select tabname from systables where tabid > 99 and tabtype = "T"
EOF`;
print "Table list retrieved!\n\n";

foreach $table (split(/\n/, $tableinfoexport)) {
        if (($table !~ /.*row(s) retrieved\..*/ig) && ($table !~ /^$/)) {
                $table =~ s/^\s+//;
                $table =~ s/\s+$//;
                print "Exporting table: " . $table . "\n";
                $output = `isql $database << EOF
UNLOAD TO '$backupfolder/$table.ifx' DELIMITER '$delimiter' select * from $table
EOF`;
        }
}

close(TABINF);
###

Edited July 15, 2011 by FuhrerDarqueSyde

[Squirrelington]

Change log:

(7/14/2011)

* Fixed a bug where it would open the tables.txt before it was done being written to, resulting in not all tables being exported. The file tables.txt is no longer being generated and the tables list is now piped to 'cat' and stored within a variable for processing in the code, resulting in less folder clutter.

[Xilo]

http://search.cpan.org/~johnl/DBD-Informix-2011.0612/Informix.pm

Did you ever try using that? When connecting to databases with Perl, you want to use DBI modules (which the ODBC is a part of, but why would you?).

[Squirrelington]

I inherited a complete cpio tape backup of a 'working' system but short of using isql and dbaccess, nothing else i used seemed to connect to the instance including ODBC (at least on windows, tried using multiple tools including the Informix SDK as well as connectivity tools i found in a share on this server). Eventually I decided to just export the data using a delimited format and importing to SQL Server using SSIS/DTS.