How to load a DB2 database using PERL

Bsm30 has asked for the wisdom of the Perl Monks concerning the following question:

Oh wise ones...
I have been tasked with rewriting a UNIX script in PERL, so that it will run on an NT box. I am very new to PERL and even newer to DB2. The following code (which works from the db2 cmd line) is what I am basically trying to do:

 db2 "LOAD from $INPUT_DIR/product.input \
   OF DEL  \
   MODIFIED BY TIMESTAMPFORMAT=\"YYYY-MM-DD-HH:MM:SS.UUUUUU\" \
   SAVECOUNT 1000  \
   MESSAGES $OUTPUT_DIR/product.msg  \
   REPLACE INTO DB2INST1.PRODUCT  \
   COPY YES TO /dev/null \
   WITHOUT PROMPTING \
   DATA BUFFER 2000" >> $SQL_LOG

 db2 "SET INTEGRITY FOR DB2INST1.PRODUCT IMMEDIATE CHECKED" >> $SQL_LO
+G
[download]

What I have so far is...

use DBI;
use Tie::File;
use File::Copy;

my $database='DBI:DB2:game';
my $user='db2admin';
my $password='db2admin';
my $dbh = DBI->connect($database,$user,$password) or die "Can't connec
+t to $database: $DBI::errstr";
my $sth = $dbh->prepare(
    q{ load from product.input of DEL modified by timestampformat="YYY
+Y-MM-DD-HH:MM:SS.uuuuuu" 
    savecount 1000 messages rob.msg replace into db2inst1.product copy
+ yes to d:\data\perl without prompting}) 
    or die "Can't prepare statement: $DBI::errstr";
my $rc = $sth->execute or die "Can't execute statement: $DBI::errstr";
[download]

It doesn work....
I get the following message:
DBD::DB2::st execute failed: IBM CLI Driver DB2/NT SQL0007N The character "\" following "duct copy yes to d:/" is not valid. SQLSTATE=42601 Can't execute statement: IBM CLI Driver DB2/NT SQL0007N The character "\" following "duct copy yes to d:/" is not valid. SQL STATE=42601 Database handle destroyed without explicit disconnect.

I changed my backslashed to / in the code, then I get:

DBD::DB2::st execute failed: IBM CLI Driver DB2/NT SQL0104N An unexpected token "product" was found following " load from ". Expected tokens may include: "JOIN". SQLSTATE=42601 Can't execute statement: IBM CLI Driver DB2/NT SQL0104N An unexpected token "product" was found following " load from ". Exp ected tokens may include: "JOIN". SQLSTATE=42601 Database handle destroyed without explicit disconnect.
Please Help. I'm lost

Comment on How to load a DB2 database using PERL Select or Download Code

Replies are listed 'Best First'.
Re: How to load a DB2 database using PERL by theguvnor (Chaplain) on Apr 15, 2004 at 19:37 UTC
As long as you can download and install the "DB2 Connect" package for Windows (which manages the ODBC connection to DB2), you will be able to do this easily using DBI and the DBD::ODBC driver. You create a DSN using DB2 Connect that contains the info for your DB2 database, and point the DBI at that DSN. Not sure if DB2 Connect can be downloaded for free anymore. [Jon]	[reply]
Re: Re: How to load a DB2 database using PERL by Beechbone (Friar) on Apr 16, 2004 at 20:53 UTC
FYI: A DB2 Connect installation is needed to use the DBD::DB2 drivers, too. BTW, why do you recommend to use ODBC when the native interface is up and running already? _Search, Ask, ^Know	[reply]
Re^3: How to load a DB2 database using PERL by dazzle (Sexton) on Jul 13, 2005 at 19:18 UTC
Actually, a DB2 Connect installation (not free) is only required for access to DB2 on iSeries or zSeries machines from Linux, UNIX, or Windows. To access DB2 on Linux, UNIX, or Windows, you just need to download a free DB2 client (Runtime, Administration, or Application Development) from DB2 Support. If you're compiling DBD::DB2, get the DB2 Application Development Client and ensure you select the "Install Headers" option.	[reply]
Re: How to load a DB2 database using PERL by runrig (Abbot) on Apr 15, 2004 at 19:12 UTC
Google is your friend. 'load' is not really valid sql, it is a special command. If you just want to load data, keep it as a shell script, or if you need to have a perl program to do other things, use system or open a pipe to the db2 command. Update: just noticed that you need this on NT. If you can't install the DB2 tools on NT, and need to use ODBC or something, then you'll have to read from the file and insert one record at a time, which will probably be slower. Better to transfer the file to a system with the load facility (i.e. the db2 command), and let it do the work. And just a warning, if you use 'PERL', you will be mocked by a segment of the community. It's 'Perl' or 'perl'.	[reply]
Re: How to load a DB2 database using PERL by JSchmitz (Canon) on Apr 15, 2004 at 19:21 UTC
LOAD is not an SQL statement, it is a command. Therefore it can't be executed via DBI (or JDBC, ODBC, etc). You need to execute it using either the system() call in perl or by opening the command and reading from the pipe, e.g., open(LOAD, "db2 load from ... \|"); Or write a perl XS module that calls the sqluload() API! ;-) Good luck,	[reply]
Re: How to load a DB2 database using PEARL by Beechbone (Friar) on Apr 16, 2004 at 21:26 UTC
LOAD cannot executed via CLI because it's a "Command Line Processor Command". See http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/core/r0010410.htm for a list. These commands are resolved by the command line processor ("db2" or "db2.exe"), not the database engine. You may get some of the functionality on C level, but not with CLI SQL. Do a system() instead. `system('db2', 'LOAD from ...'); if ($? == 0) { # all ok } if ($? & 1) { # oops, we didn't select at all? } if ($? & 2) { # just a warning } if ($? & 4) { # execution failed } if ($? & 8) { # command line processor failed } if ($? & ! 15) { # what? }` [download] _Search, Ask, ^Know	[reply] [d/l]