OT: Failing to export from DB2

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

I'm trying to use DB2's EXPORT function to dump out a portion of a database table. The command works fine from a DB2 prompt, fine from a shell script, but fails from a Perl script with the vague (to me, anyway) error, Database error: No such file or directory at foo.pl ..

My SQL is along the lines of

export to trans15 of del select trans_id, time from foo.bar fetch firs
+t 15 rows only
[download]

with schema and table names anonymized.

If someone has pointers on what I'm doing wrong, I'd appreciate it.

Update: Sorry, replaced $script with a non-variable script name foo.pl. Just trying (perhaps a little too hard) to anonymize my work.

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Comment on OT: Failing to export from DB2 Select or Download Code

Replies are listed 'Best First'.
Re: OT: Failing to export from DB2 by Anonymous Monk on Mar 30, 2009 at 14:25 UTC
Turn on tracing	[reply]
Re^2: OT: Failing to export from DB2 by talexb (Chancellor) on Mar 30, 2009 at 17:55 UTC
Such an obvious avenue -- and it didn't occur to me. Sort of like forgetting to use `strict` (or maybe `warnings`). Anyway, tried that with my existing code that did an `execute` and got: `SQL0104N An unexpected token "EXPORT" was found following "BEGIN-OF-S +TATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601` [download] I tried just using `do` instead of `execute` and got the same error. Tanktalus suggested using `ADMIN_CMD(my command)`, which got me a different error: `SQL0104N An unexpected token "ADMIN_CMD(EXPORT TO /" was found follo +wing "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". + SQLSTATE=42601` [download] I'm going to try a filename without any slashes to see if that works. If anyone else has any suggestions, shout them out. Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply] [d/l] [select]
Re^3: OT: Failing to export from DB2 by Anonymous Monk on Mar 31, 2009 at 06:27 UTC
That makes it sound like its true that EXPORT is not a SQL statement - it's a CLP command and therefore cannot be used in a stored procedure. Maybe you want to use DB2::Admin, it has an Export method.	[reply]
Re: OT: Failing to export from DB2 by andreas1234567 (Vicar) on Mar 31, 2009 at 07:30 UTC
I am inclined to believe Anonymous Coward in that the `EXPORT` function is not a SQL command, and thus not available through DBI. I can't get it to work (with DBI) either: `$dbh->do ("export to table_foo of del select id, str from table_foo"); DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "export to table_foo of del select id," was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<revoke>". SQLSTATE=42601` [download] However, DB2::Admin works for me (Linux x86_64, DB2 Express-C V.9.5.0, perl v5.10.0): `use strict; use DB2::Admin; # use 'perl -s' our $INST; our $USER; our $PASS; our $TABL; our $SCHM; DB2::Admin::->SetOptions('RaiseError' => 1); DB2::Admin::->Connect( 'Database' => $INST, 'Userid' => $USER, 'Password' => $PASS ); DB2::Admin->Export( 'Database' => $INST, 'Schema' => $SCHM, 'Table' => $TABL, 'OutputFile' => "/tmp/data-$SCHM-$TABL.del", 'FileType' => 'DEL' ); __END__` [download] Running: `$ perl -ws admin.pl -INST=TEST -USER=db2inst1 -PASS=*** -SCHM=db2ins +t1 -TABL=table_foo $ cat /tmp/data-db2inst1-table_foo.del 1,"foo" 2,"bar" 3,"tze"` [download] Update**: Fixed link. -- No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]	[reply] [d/l] [select]
Re^2: OT: Failing to export from DB2 by talexb (Chancellor) on Mar 31, 2009 at 15:23 UTC
Thanks for the pointer to DB2::Admin -- it's appreciated, although it appears that your link doesn't work -- the one to CPAN (DB2::Admin) should work. It's unlikely I'll be able to get this installed on a Production server, but it's great to know for the next generation of scripts. :) For now, I'm using a shell script to get this job done. Updated: To correct link to DB2 Admin. Sigh. Thanks andreas1234567. Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply]
Re^3: OT: Failing to export from DB2 by andreas1234567 (Vicar) on Apr 01, 2009 at 05:27 UTC
It should be DB2::Admin, not `DBI::Admin`. -- No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]	[reply] [d/l]