• 0

[Perl/Informix] Automated Data Extraction


Question

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 by FuhrerDarqueSyde

3 answers to this question

Recommended Posts

  • 0

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.

  • 0

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.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Supply and demand. Competition is a good thing.
    • For the cost of popping Linux on a minipc and attaching it to the back of a monitor, it essentially is a Mac, but 1/10th of the cost. My aunt and uncle were just in town seeing her and they couldn't believe they weren't using Windows. I've ordered them the same setup my mom has and they couldn't be happier. My aunt runs a really old Windows 7 laptop that was upgraded to Windows 10 that is giving out, so a replacement cost of $389 total vs $600 or more and she was sold.
    • BurnAware 18.7 by Razvan Serea Free burning software to create CDs, DVDs, and Blu-ray discs of all types. BurnAware is a full-fledged, easy-to-use burning software which allows users to write all types of files such as digital photos, archives, documents, music and videos to CDs, DVDs and Blu-ray Discs, including BDXL and M-Disc. With BurnAware, you also be able to create boot or multisession discs, high-quality Audio CDs and Video DVDs, make and burn ISO images, copy and backup discs, extract audio tracks, verify and recover data from multisession or unreadable discs. BurnAware is available in three editions - Free, Premium and Professional. Compare and pick edition which is suitable for you. Features Burn files and folders to CD, DVD or Blu-ray Discs. Append or update multisession discs. Burn standard or boot disc images. Burn ISO files to multiple recorders simultaneously. Create boot CDs or DVDs. Create Audio CDs. Create DVD-Video discs. Create MP3 CD / DVD / Blu-ray Discs. Make standard or boot disc images. Copy CD, DVD or Blu-ray Discs to ISO images. Copy from discs to discs. Verify discs byte by byte. Recover files from damaged discs or different sessions. Extract audio tracks from Audio CDs. Erase and format re-writable discs. View detailed disc and drive information. Supports All media types (CD/DVD/Blu-ray Disc) including Double Layer All current hardware interfaces (IDE/SCSI/USB/1394/SATA) including AHCI UDF/ISO9660/Joliet file systems (any combination) On-the-fly writing (no staging to hard drive first) Verification of written files Multisession DVD-RW/DVD+RW Unicode CD-Text (tracks and disc) Compatible with Windows Vista 7, 8, 10, 11 (32-bit or 64-bit) BurnAware 18.7 changelog: Updated translations. Updated disc burning SDK. Improved indirect disc copying (CD <-> DVD). Optimized overall performance on 64-bit systems. Download: BurnAware Free 18.7 | 11.8 MB (Freeware) View: BurnAware Free Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Yeah, as a general rule I prefer standard XBox-like controllers that work like usual out-of-the-box. additional software tends to be overkill. p.s. even my 8BitDo Ultimate 2C wired, while I had to tweak something (setup a udev rule) on Linux for it to work 'out-of-the-box' (which is required for Xinput mode to work in kernels prior to 6.12), I can slightly tweak a small amount of additional buttons/features on the controller itself, no software needed.
  • Recent Achievements

    • Apprentice
      Adrian Williams went up a rank
      Apprentice
    • Reacting Well
      BashOrgRu earned a badge
      Reacting Well
    • Collaborator
      CHUNWEI earned a badge
      Collaborator
    • Apprentice
      Cole Multipass went up a rank
      Apprentice
    • Posting Machine
      David Uzondu earned a badge
      Posting Machine
  • Popular Contributors

    1. 1
      +primortal
      535
    2. 2
      ATLien_0
      265
    3. 3
      +Edouard
      193
    4. 4
      +FloatingFatMan
      182
    5. 5
      snowy owl
      135
  • Tell a friend

    Love Neowin? Tell a friend!