Export to CSV-File

Oliver Stratmann

Hello all,

is there another way to export data to a csv-file (comma-separated format)?
We tried the EXPORT-command and got the following error
"
SQL1325N The remote database environment does not support the command
or one of the command options.

Explanation: An attempt has been made to issue a DB2 workstation database
specific command or command option against a host database through DB2
Connect or federated server. The following commands generate this error when
issued against a DB2 for MVS*, DB2 for OS/400* or SQL/DS* database:

a.. OPSTAT (Collect Operational Status)
b.. DARI (Database Application Remote Interface)
c.. GETAA (Get Administrative Authorizations)
d.. GETTA (Get Table Authorizations)
e.. PREREORG (Prepare to Reorganize Table)
f.. REORG (Call Reorganize Function)
g.. RQSVPT/ENSVPT/RLBSVPT (Subtransaction Requests)
h.. RUNSTATS (Run Statistics).
i.. COMPOUND SQL ATOMIC STATIC (Atomic Compound SQL)
j.. ACTIVATE DATABASE
k.. DEACTIVATE DATABASE
As well, the following commands generate this error with incorrect options:

a.. IMPORT (Import table) The filetype must be IXF, commitcount must be 0,
and the first word in the Action String (e.g. "REPLACE into ...") must be
INSERT.
b.. EXPORT (Export table) The filetype must be IXF.
The command cannot be processed.

User Response: Do not attempt to issue this command against a host database
through DB2 Connect or federated server.

"
Well, is there a workaround or am I hopelessly stuck?

Thanks for any help in advance!

Bye!
Oli

Nov 12 '05 #1

Anton Versteeg

You can simulate export to CSV with a Select statement.
For instance:

select '"""' concat rtrim(charcol1) concat '""",' , '"""' concat
rtrim(charcol2) concat '""",' , numcol3 from table

Oliver Stratmann wrote:

Hello all,

is there another way to export data to a csv-file (comma-separated format)?
We tried the EXPORT-command and got the following error
"
SQL1325N The remote database environment does not support the command
or one of the command options.

Explanation: An attempt has been made to issue a DB2 workstation database
specific command or command option against a host database through DB2
Connect or federated server. The following commands generate this error when
issued against a DB2 for MVS*, DB2 for OS/400* or SQL/DS* database:

a.. OPSTAT (Collect Operational Status)
b.. DARI (Database Application Remote Interface)
c.. GETAA (Get Administrative Authorizations)
d.. GETTA (Get Table Authorizations)
e.. PREREORG (Prepare to Reorganize Table)
f.. REORG (Call Reorganize Function)
g.. RQSVPT/ENSVPT/RLBSVPT (Subtransaction Requests)
h.. RUNSTATS (Run Statistics).
i.. COMPOUND SQL ATOMIC STATIC (Atomic Compound SQL)
j.. ACTIVATE DATABASE
k.. DEACTIVATE DATABASE
As well, the following commands generate this error with incorrect options:

a.. IMPORT (Import table) The filetype must be IXF, commitcount must be 0,
and the first word in the Action String (e.g. "REPLACE into ...") must be
INSERT.
b.. EXPORT (Export table) The filetype must be IXF.
The command cannot be processed.

User Response: Do not attempt to issue this command against a host database
through DB2 Connect or federated server.

"
Well, is there a workaround or am I hopelessly stuck?

Thanks for any help in advance!

Bye!
Oli

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Nov 12 '05 #2

Oliver Stratmann

Hello Anton,

if this is the only way I will implement it. Though it is a bit tedious
because I have to export quite a lot of different tables.
If there is no other way. I will start to write a Select which generates
SELECT-statements as depicted by You below.

Thank You for Your help!

Bye!
Oli

"Anton Versteeg" <an************@nnll.ibm.com> schrieb im Newsbeitrag
news:cb**********@sp15en20.hursley.ibm.com...

You can simulate export to CSV with a Select statement.
For instance:

select '"""' concat rtrim(charcol1) concat '""",' , '"""' concat
rtrim(charcol2) concat '""",' , numcol3 from table

Oliver Stratmann wrote:

Hello all,

is there another way to export data to a csv-file (comma-separated format)? We tried the EXPORT-command and got the following error
"
SQL1325N The remote database environment does not support the command or one of the command options.

Explanation: An attempt has been made to issue a DB2 workstation database specific command or command option against a host database through DB2
Connect or federated server. The following commands generate this error when issued against a DB2 for MVS*, DB2 for OS/400* or SQL/DS* database:

a.. OPSTAT (Collect Operational Status)
b.. DARI (Database Application Remote Interface)
c.. GETAA (Get Administrative Authorizations)
d.. GETTA (Get Table Authorizations)
e.. PREREORG (Prepare to Reorganize Table)
f.. REORG (Call Reorganize Function)
g.. RQSVPT/ENSVPT/RLBSVPT (Subtransaction Requests)
h.. RUNSTATS (Run Statistics).
i.. COMPOUND SQL ATOMIC STATIC (Atomic Compound SQL)
j.. ACTIVATE DATABASE
k.. DEACTIVATE DATABASE
As well, the following commands generate this error with incorrect options:
a.. IMPORT (Import table) The filetype must be IXF, commitcount must be 0, and the first word in the Action String (e.g. "REPLACE into ...") must be INSERT.
b.. EXPORT (Export table) The filetype must be IXF.
The command cannot be processed.

User Response: Do not attempt to issue this command against a host database through DB2 Connect or federated server.

"
Well, is there a workaround or am I hopelessly stuck?

Thanks for any help in advance!

Bye!
Oli

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Nov 12 '05 #3

Jan M. Nelken

Oliver Stratmann wrote:

Hello Anton,

if this is the only way I will implement it. Though it is a bit tedious
because I have to export quite a lot of different tables.
If there is no other way. I will start to write a Select which generates
SELECT-statements as depicted by You below.

1. Export to .IXF file format all your host tables.
2. For each of the table:
a) import into local database (here IXF format comes handy - it
will create table for you;
b) export table in CSV;
c) drop table (but why are you exporting in CSV format when you
already have table in local database from step a?)
3. Have a beer (job finished).

Jan M. Nelken

Nov 12 '05 #4

Buck Nuggets

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message

1. Export to .IXF file format all your host tables.
2. For each of the table:
a) import into local database (here IXF format comes handy - it
will create table for you;
b) export table in CSV;
c) drop table (but why are you exporting in CSV format when you
already have table in local database from step a?)

The above looks like easiest solution for quite a lot of tables.

Here's another just in case - for each table, build your query based
upon system tables. The query builder would be a little bit of work,
but you only need to write that once - and embed it into a function or
whatever. Then just call it for each table you want to export.

buck

Nov 12 '05 #5

Jan M. Nelken

Buck Nuggets wrote:

Here's another just in case - for each table, build your query based
upon system tables. The query builder would be a little bit of work,
but you only need to write that once - and embed it into a function or
whatever. Then just call it for each table you want to export.

Just as a starting point - I would run:

db2 -xtvf export.script -o doexport.script

where export.script would contain followin lines:
------------------- Begin of script-------------------
select
'export to '
concat
substr(tabschema,1,length(rtrim(tabschema)))
concat
'.'
concat
substr(tabname,1,length(rtrim(tabname)))
concat
'.IXF of ixf SELECT * FROM '
concat
substr(tabschema,1,length(rtrim(tabschema)))
concat
'.'
concat
substr(tabname,1,length(rtrim(tabname)))
from
syscat.tables
where
tabschema not in ('SYSIBM','SYSCAT','SYSSTAT','SYSTOOLS','SYSTEM')
;
------------------- End of script-------------------

Jan M. Nelken

Nov 12 '05 #6

Anton Versteeg

DB2 for z/OS V8 supports LOAD/UNLOAD for delimited data.
Not sure if you can wait that long though, since migration to V8 is not
that trivial.

Oliver Stratmann wrote:

Hello Anton,

if this is the only way I will implement it. Though it is a bit tedious
because I have to export quite a lot of different tables.
If there is no other way. I will start to write a Select which generates
SELECT-statements as depicted by You below.

Thank You for Your help!

Bye!
Oli

"Anton Versteeg" <an************@nnll.ibm.com> schrieb im Newsbeitrag
news:cb**********@sp15en20.hursley.ibm.com...

You can simulate export to CSV with a Select statement.
For instance:

select '"""' concat rtrim(charcol1) concat '""",' , '"""' concat
rtrim(charcol2) concat '""",' , numcol3 from table

Oliver Stratmann wrote:

Hello all,

is there another way to export data to a csv-file (comma-separated
format)?
We tried the EXPORT-command and got the following error
"
SQL1325N The remote database environment does not support the
command
or one of the command options.

Explanation: An attempt has been made to issue a DB2 workstation
database
specific command or command option against a host database through DB2
Connect or federated server. The following commands generate this error
when
issued against a DB2 for MVS*, DB2 for OS/400* or SQL/DS* database:

a.. OPSTAT (Collect Operational Status)
b.. DARI (Database Application Remote Interface)
c.. GETAA (Get Administrative Authorizations)
d.. GETTA (Get Table Authorizations)
e.. PREREORG (Prepare to Reorganize Table)
f.. REORG (Call Reorganize Function)
g.. RQSVPT/ENSVPT/RLBSVPT (Subtransaction Requests)
h.. RUNSTATS (Run Statistics).
i.. COMPOUND SQL ATOMIC STATIC (Atomic Compound SQL)
j.. ACTIVATE DATABASE
k.. DEACTIVATE DATABASE
As well, the following commands generate this error with incorrect
options:
a.. IMPORT (Import table) The filetype must be IXF, commitcount must
be 0,
and the first word in the Action String (e.g. "REPLACE into ...") must
be
INSERT.
b.. EXPORT (Export table) The filetype must be IXF.
The command cannot be processed.

User Response: Do not attempt to issue this command against a host
database
through DB2 Connect or federated server.

"
Well, is there a workaround or am I hopelessly stuck?

Thanks for any help in advance!

Bye!
Oli

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Nov 12 '05 #7