On Fri, Apr 30, 2010 at 4:36 PM, Ovid <publiustemp-d...@yahoo.com> wrote:
> ----- Original Message ----
>> From: "Song, Melinda" <qing.s...@morganstanley.com>
>
>
>> I am using DBI to connect to a DB2 LUW database. It seems I cannot use DBI to
>> issue the IMPORT command.
>
>> my $dbh = DBI->connect(
>> "dbi:MSDB2:$DB_NAME", '', '', { 'RaiseError' => 1 } );
>> dbh->do("import
>> from $filename of del insert into $tabname");
>
>> DBD::MSDB2::db do failed:
>> [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "import"
>> was found following "BEGIN-OF-STATEMENT". Expected tokens may
>> include: "<values>". SQLSTATE=42601
>
> Hi Melinda,
>
> I was surprised to see that there was an MSDB2 driver, but Google suggests
> that this is a Morgan Stanley subclass of the DB2 driver. That makes this
> harder to debug. You may have to contact whoever wrote DBD::MSDB2, but I
> can't say I know DBI internals well enough to know if that's a reasonable
> route.
>
> Googling for your exact error returns limited results, but I see that others
> get this result without DBI:
>
> http://www.dbforums.com/db2/1618417-truncate-table-db2.html
>
> That suggests that an older version of DB2 might be involved, but it's a
> different route taken to reach that error, so again, I've no idea if that's
> relevant.
>
> Sorry I can't be of more help.
>
> Cheers,
> Ovid
> --
> Buy the book - http://www.oreilly.com/catalog/perlhks/
> Tech blog - http://blogs.perl.org/users/ovid/
> Twitter - http://twitter.com/OvidPerl
> Official Perl 6 Wiki - http://www.perlfoundation.org/perl6
>
>
>
I was wondering if DB2's import might be a command line executable as
it is with Oracle, so I googled DB2 Import Executable and found the
following. This was posted about Java, but I believe the part about
what you can call from your program is applicable:
Credit goes to someone named Dave Hughes
########################################
The IMPORT command isn't SQL; it's a "CLP command" (which is why it
doesn't appear under the SQL reference in the InfoCenter, but in a
separate section along with other commands like CREATE DATABASE, LOAD,
etc.)
Before DB2 9, you could only execute CLP commands from the CLP (the db2
command line executable), with the exception of one or two commands.
Starting with DB2 9, you can execute many (but not all) CLP commands
via the ADMIN_CMD() [1] stored procedure, including IMPORT [2], EXPORT
[3] and LOAD [4].
However, one important thing to be aware of with ADMIN_CMD(). The
procedure runs the command *on the server*. Hence, for IMPORT, the
input data file must reside on the server (see [2]).
There's probably some "proper" JDBC method for calling stored
procedures, or I suspect you could just do:
statement.execute("CALL SYSPROC.ADMIN_CMD('IMPORT FROM
''C:/staff.xml.xsd'' OF DEL METHOD P(1) MESSAGES ''C:/messages.txt''
INSERT INTO STAFF_AS_XML (STAFF_AS_XML_COL)')");
########################################
So you have some options that I can think of off the top of my head:
1) use the ADMIN_CMD() SP and make sure your data is on the server.
2) Call the CLP and pipe commands to it.
3) Loop trough the data and do inserts.
4) If DBD::DB2 supports array inserts (I'd assume it does, but I hate
assuming), use that and do a mass insert. You'll need to read the
perldocs on DBD::DB2 to see if it's available and if so, how to use
it.
And of course, there are likely other ways. That's why we use Perl, right?
--
"Champions do not become champions when they win the event, but in the
hours, weeks, months and years they spend preparing for it. The
victorious performance itself is merely the demonstration of their
championship character." -T. Alan Armstrong
"The Ow that can be expressed is not the true Ow." - Ao Tzu
