Export to CSV-File

**Anton Versteeg** · Nov 12 '05, 08:51 AM

Re: Export to CSV-File

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:[color=blue]
> 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
>
>[/color]

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

**Oliver Stratmann** · Nov 12 '05, 08:51 AM

Re: Export to CSV-File

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" <anton_versteeg @nnll.ibm.com> schrieb im Newsbeitrag
news:cbrd00$cnc $1@sp15en20.hur sley.ibm.com...[color=blue]
> 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:[color=green]
> > Hello all,
> >
> > is there another way to export data to a csv-file (comma-separated[/color][/color]
format)?[color=blue][color=green]
> > We tried the EXPORT-command and got the following error
> > "
> > SQL1325N The remote database environment does not support the[/color][/color]
command[color=blue][color=green]
> > or one of the command options.
> >
> > Explanation: An attempt has been made to issue a DB2 workstation[/color][/color]
database[color=blue][color=green]
> > specific command or command option against a host database through DB2
> > Connect or federated server. The following commands generate this error[/color][/color]
when[color=blue][color=green]
> > 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[/color][/color]
options:[color=blue][color=green]
> >
> > a.. IMPORT (Import table) The filetype must be IXF, commitcount must[/color][/color]
be 0,[color=blue][color=green]
> > and the first word in the Action String (e.g. "REPLACE into ...") must[/color][/color]
be[color=blue][color=green]
> > 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[/color][/color]
database[color=blue][color=green]
> > through DB2 Connect or federated server.
> >
> > "
> > Well, is there a workaround or am I hopelessly stuck?
> >
> > Thanks for any help in advance!
> >
> > Bye!
> > Oli
> >
> >[/color]
>
> --
> Anton Versteeg
> IBM Certified DB2 Specialist
> IBM Netherlands
>[/color]

**Jan M. Nelken** · Nov 12 '05, 08:51 AM

Re: Export to CSV-File

Oliver Stratmann wrote:[color=blue]
> 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.
>[/color]

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

**Buck Nuggets** · Nov 12 '05, 08:51 AM

Re: Export to CSV-File

"Jan M. Nelken" <Unknown.User@I nvalid.Domain> wrote in message[color=blue]
> 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?)[/color]

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

**Jan M. Nelken** · Nov 12 '05, 08:52 AM

Re: Export to CSV-File

Buck Nuggets wrote:
[color=blue]
> 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.[/color]

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(tabschem a,1,length(rtri m(tabschema)))
concat
'.'
concat
substr(tabname, 1,length(rtrim( tabname)))
concat
'.IXF of ixf SELECT * FROM '
concat
substr(tabschem a,1,length(rtri m(tabschema)))
concat
'.'
concat
substr(tabname, 1,length(rtrim( tabname)))
from
syscat.tables
where
tabschema not in ('SYSIBM','SYSC AT','SYSSTAT',' SYSTOOLS','SYST EM')
;
------------------- End of script-------------------

Jan M. Nelken

**Anton Versteeg** · Nov 12 '05, 08:52 AM

Re: Export to CSV-File

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:[color=blue]
> 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" <anton_versteeg @nnll.ibm.com> schrieb im Newsbeitrag
> news:cbrd00$cnc $1@sp15en20.hur sley.ibm.com...
>[color=green]
>>You can simulate export to CSV with a Select statement.
>>For instance:
>>
>> select '"""' concat rtrim(charcol1) concat '""",' , '"""' concat
>>rtrim(charcol 2) concat '""",' , numcol3 from table
>>
>>Oliver Stratmann wrote:
>>[color=darkred]
>>>Hello all,
>>>
>>>is there another way to export data to a csv-file (comma-separated[/color][/color]
>
> format)?
>[color=green][color=darkred]
>>>We tried the EXPORT-command and got the following error
>>>"
>>> SQL1325N The remote database environment does not support the[/color][/color]
>
> command
>[color=green][color=darkred]
>>>or one of the command options.
>>>
>>>Explanatio n: An attempt has been made to issue a DB2 workstation[/color][/color]
>
> database
>[color=green][color=darkred]
>>>specific command or command option against a host database through DB2
>>>Connect or federated server. The following commands generate this error[/color][/color]
>
> when
>[color=green][color=darkred]
>>>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[/color][/color]
>
> options:
>[color=green][color=darkred]
>>> a.. IMPORT (Import table) The filetype must be IXF, commitcount must[/color][/color]
>
> be 0,
>[color=green][color=darkred]
>>>and the first word in the Action String (e.g. "REPLACE into ...") must[/color][/color]
>
> be
>[color=green][color=darkred]
>>>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[/color][/color]
>
> database
>[color=green][color=darkred]
>>>through DB2 Connect or federated server.
>>>
>>>"
>>>Well, is there a workaround or am I hopelessly stuck?
>>>
>>>Thanks for any help in advance!
>>>
>>>Bye!
>>>Oli
>>>
>>>[/color]
>>
>>--
>>Anton Versteeg
>>IBM Certified DB2 Specialist
>>IBM Netherlands
>>[/color]
>
>
>[/color]

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Export to CSV-File

Export to CSV-File

Comment

Comment

Comment

Comment

Comment

Comment