Export as a CSV, from DB2 8.1 OS/390

felx · #1 (**permalink**) 03-07-07, 09:41

Unforunately DB2 for OS/390 can't EXPORT data as DEL, only IFX.
I need the CSV as external table in Oracle. I assume, that oracle will be not able to read IXF.

Do you have any idea how to create a CSV from a query or table in DB2 for OS/390? What are the other posibilities for linking DB2 tables to Oracle? We don't have transparent gateways here...

Is there an Perl interface for DB2? Is there an unix-based converter for IXFs?

sathyaram_s · #2 (**permalink**) 03-07-07, 10:31

select col2,',',col3,','col4 from table1 is one option.
Another is to export the data, load into a db2 luw database (you may use the no charge - db2 express edition), reexport and load to oracle

Sathyaram

felx · #3 (**permalink**) 03-07-07, 10:33

Quote:

Originally Posted by sathyaram_s

select col2,',',col3,','col4 from table1 is one option.

How to write 'select col2,',',col3,','col4 from table1 ' to a csv? From CLP?

sathyaram_s · #4 (**permalink**) 03-07-07, 10:37

CLP - yes
or
DSNTEP2/DSNTEP4

Sathyaram

grofaty · #5 (**permalink**) 03-08-07, 00:56

Quote:

Originally Posted by felx

How to write 'select col2,',',col3,','col4 from table1 ' to a csv? From CLP?

Hi,
I don't like CLP because there is not possible of using operating system commands (or is more difficult). So I use Command Window.
You can use:
db2 -r output_file.txt -tf sql_file.sql

or you can also use operating system command "redirect to file" which is letter >.

db2 -tf sql_file.sql > output_file.txt

Note: On Windows "redirect to file" returns one additional character at the end of file. I suggest you to look into output_file with some hex-text tool to see the output.

-r option appends data to output_file so you need to delete the output if you like to rerun the sql_file.

Hope this helps,
Grofaty

felx · #6 (**permalink**) 03-08-07, 04:46

Yes, it's looks nice.

As a first line in my sql file I have a connect statement. How to suppress its output to flatfile? I mean the

Quote:

Database Connection Information

Database server = DB2 OS/390 8.1.5
SQL authorization ID = user
Local database alias = DB2TRALALA

at the start of file? And how to suppress the column names and separation line in the result output part of file?

sathyaram_s · #7 (**permalink**) 03-08-07, 06:06

Try this one ...

Create a file with the following commands :

Code:

update command options using o off   ;
connect to eudw  ;
update command options using o on x off  ;
 

select * from sysibm.sysdummy1 ;

Then use grofaty's suggestions

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech