Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

How to load a DB2 database using PERL

by Bsm30 (Initiate)
on Apr 15, 2004 at 18:52 UTC ( #345500=perlquestion: print w/ replies, xml ) Need Help??
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
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";
It doesn work....
I get the following message:
DBD::DB2::st execute failed: IBMCLI DriverDB2/NT SQL0007N The character "\" following "duct copy yes to d:/" is not valid. SQLSTATE=42601 Can't execute statement: IBMCLI DriverDB2/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: IBMCLI DriverDB2/NT SQL0104N An unexpected token "product" was found following " load from ". Expected tokens may include: "JOIN". SQLSTATE=42601 Can't execute statement: IBMCLI DriverDB2/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
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'.

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,
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]

      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
        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.

Re: How to load a DB2 database using PEARL
by Beechbone (Pilgrim) 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? }

    Search, Ask, Know

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://345500]
Approved by calin
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2014-12-25 11:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (160 votes), past polls