Export data as well as the corresponding column names?

**shenanwei@gmail.com** · Feb 3 '06, 03:05 PM

Re: Export data as well as the corresponding column names?

Use ixf format will have table DDL, include the column name. But not
readable directly.
The easy way is like this
1. db2 export to tablename.del
2. db2 select from tablename > column.del
3. copy column.del + tablename.del

mjf wrote:[color=blue]
> Hello,
> Is there a way to export the data from some tables, along with their
> corresponding column names? I know I can use "export" command to export
> the data from a table to a DEL file (the users need to be able to read
> the file directly, which leaves the choice of a DEL file), but only the
> data will be in the file. The users would like to see the column names
> side by side with the data as well. I wonder if there is an easy way to
> do so without too much coding.
> Thanks.[/color]

**Brian Tkatch** · Feb 3 '06, 03:15 PM

Re: Export data as well as the corresponding column names?

You may be able to use a UNION query that SELECTs the COLUMN names on
top.

SELECT 'Column1', 'Column 2', Column 3'....
UNION ALL
SELECT ......

B.

**mjf** · Feb 3 '06, 07:55 PM

Re: Export data as well as the corresponding column names?

Hello Brian, the method you suggested is very promising. Thanks a lot!
I did it to a test table that has the following DDL:

db2 "describe table test"

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
COL1 SYSIBM SMALLINT 2
0 Yes
COL2 SYSIBM VARCHAR 5
0 Yes
COL3 SYSIBM VARCHAR 5
0 Yes

db2 "export to test.del of del modified by chardel'' coldel; decpt,
select col1, 'col1 ', col2, 'col2 ', col3, 'col3 ' from test"

The export was successful, and here is the content in test.del:
1;'col1 ';'test1';'col2 ';'val1';'col3 '
2;'col1 ';'test2';'col2 ';'val2';'col3 '
3;'col1 ';'test3';'col2 ';'val3';'col3 '

I added the blanks in the column names so the users can spot each pair
of value/column-name easily. When there are lots of columns in the
table, the above would wrap around to next lines and made it hard to
read, though. Do you know any way of formatting it like the following?

1;'col1 ';
'test1';'col2 ';
'val1';'col3 '

2;'col1 ';
'test2';'col2 ';
'val2';'col3 '

3;'col1 ';
'test3';
'col2 ';
'val3';'col3 '
I need to set up a daily cron job to do such a file, so I need to do
everything automatically. I guess I can always write a PERL script to
separate the pairs, but wonder if there is yet another easy way to do
it.
Thanks again.

**Brian Tkatch** · Feb 6 '06, 03:15 PM

Re: Export data as well as the corresponding column names?

I don't think so. It's a bit beyond my knowledge of db2.

If you are using a cron job, perl seems easy enough, or any of those
nifty tools that filter columns in order.

B.

**Knut Stolze** · Feb 6 '06, 03:25 PM

Re: Export data as well as the corresponding column names?

mjf wrote:
[color=blue]
> Hello Brian, the method you suggested is very promising. Thanks a lot!
> I did it to a test table that has the following DDL:
>
> db2 "describe table test"
>
> Column Type Type
> name schema name Length
> Scale Nulls
> ------------------------------ --------- ------------------ --------
> ----- ------
> COL1 SYSIBM SMALLINT 2
> 0 Yes
> COL2 SYSIBM VARCHAR 5
> 0 Yes
> COL3 SYSIBM VARCHAR 5
> 0 Yes
>
> db2 "export to test.del of del modified by chardel'' coldel; decpt,
> select col1, 'col1 ', col2, 'col2 ', col3, 'col3 ' from test"
>
> The export was successful, and here is the content in test.del:
> 1;'col1 ';'test1';'col2 ';'val1';'col3 '
> 2;'col1 ';'test2';'col2 ';'val2';'col3 '
> 3;'col1 ';'test3';'col2 ';'val3';'col3 '
>
> I added the blanks in the column names so the users can spot each pair
> of value/column-name easily. When there are lots of columns in the
> table, the above would wrap around to next lines and made it hard to
> read, though. Do you know any way of formatting it like the following?
>
> 1;'col1 ';
> 'test1';'col2 ';
> 'val1';'col3 '
>
> 2;'col1 ';
> 'test2';'col2 ';
> 'val2';'col3 '
>
> 3;'col1 ';
> 'test3';
> 'col2 ';
> 'val3';'col3 '[/color]

You could simply concatenate all values into a single, long string and then
use some logic to insert the line breaks. A dedicated UDF comes to mind.
I would probably code it in Java or C/C++ as it is a bit easier to iterate
over strings that way.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

**Brian Tkatch** · Feb 6 '06, 03:45 PM

Re: Export data as well as the corresponding column names?

If he's going to use a UDF, wouldn't a FOR loop do it?

Someting like:

FOR Loop AS SELECT col1, col2, ...
Long_String = Long_String || Loop.Col1 || Loop.Col2...
END

Or would that take too long?

B.

**Gert van der Kooij** · Feb 6 '06, 04:15 PM

Re: Export data as well as the corresponding column names?

In article <1138995758.629 880.222600@o13g 2000cwo.googleg roups.com>,
mjf (minjie@excite. com) says...[color=blue]
> db2 "export to test.del of del modified by chardel'' coldel; decpt,
> select col1, 'col1 ', col2, 'col2 ', col3, 'col3 ' from test"
>[/color]

On windows I would add a column with x'0D0A' to get what you want, on
Unix X'0A' should work.

This should work (not tested):

db2 "export to test.del of del
modified by chardel'' coldel; decpt,
select col1, 'col1 ', x'0A',
col2, 'col2 ', x'0A',
col3, 'col3 '
from test"

**njzy333@gmail.com** · Feb 7 '06, 07:35 PM

Re: Export data as well as the corresponding column names?

It's almost there!
I ran the following statement:

db2 "export to test.del of del modified by chardel'' coldel; decpt,
select 'col1', col1, x'0A', 'col2', col2, x'0A', 'col3', col3 from
test"

And the result is:

'col1';1;'
';'col2';'test1 ';'
';'col3';'val1'
'col1';2;'
';'col2';'test2 ';'
';'col3';'val2'
'col1';3;'
';'col2';'test3 ';'
';'col3';'val3'

I guess that's the best we can do without using Java or C/C++?
Thank all of you very much for your help!

**mjf** · Feb 7 '06, 07:55 PM

Re: Export data as well as the corresponding column names?

I posted the above reply but didn't realize someone else has logged on
to gmail on the same machine and the email and nickname of the message
became his.
By the way, does anyone know how to delete a posted message? I'd like
to delete the above message and re-post it using my own id.
Thanks.
mjf

**Serge Rielau** · Feb 7 '06, 09:45 PM

Re: Export data as well as the corresponding column names?

mjf wrote:[color=blue]
> I posted the above reply but didn't realize someone else has logged on
> to gmail on the same machine and the email and nickname of the message
> became his.
> By the way, does anyone know how to delete a posted message? I'd like
> to delete the above message and re-post it using my own id.
> Thanks.
> mjf
>[/color]
In Thunderbird, right click mouse and pick Cancel Message.

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

**mjf** · Feb 8 '06, 02:15 PM

Re: Export data as well as the corresponding column names?

Serge, thanks!
I removed the message I posted yesterday with someone else's id
(accidentally), and am re-posting it here with my own id:
~~~~~~~
t's almost there!
I ran the following statement:

db2 "export to test.del of del modified by chardel'' coldel; decpt,
select 'col1', col1, x'0A', 'col2', col2, x'0A', 'col3', col3 from
test"

And the result is:

'col1';1;'
';'col2';'test1 ';'
';'col3';'val1'
'col1';2;'
';'col2';'test2 ';'
';'col3';'val2'
'col1';3;'
';'col2';'test3 ';'
';'col3';'val3'

I guess that's the best we can do without using Java or C/C++?
Thank all of you very much for your help!
~~~~~~~~~~~~~

**Knut Stolze** · Feb 8 '06, 02:25 PM

Re: Export data as well as the corresponding column names?

mjf wrote:
[color=blue]
> Serge, thanks!
> I removed the message I posted yesterday with someone else's id
> (accidentally), and am re-posting it here with my own id:
> ~~~~~~~
> t's almost there!
> I ran the following statement:
>
> db2 "export to test.del of del modified by chardel'' coldel; decpt,
> select 'col1', col1, x'0A', 'col2', col2, x'0A', 'col3', col3 from
> test"[/color]

How about this:

SELECT 'col1;' || RTRIM(CHAR(col1 )) || ';' || x'0A' || 'col2;''' || col2 ||
''';' || x'0A' || 'col3;''' || col3 || '''' FROM test

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

**mjf** · Feb 8 '06, 09:05 PM

Re: Export data as well as the corresponding column names?

Knut Stolze wrote:[color=blue]
> How about this:
>
> SELECT 'col1;' || RTRIM(CHAR(col1 )) || ';' || x'0A' || 'col2;''' || col2 ||
> ''';' || x'0A' || 'col3;''' || col3 || '''' FROM test
>
> --
> Knut Stolze
> DB2 Information Integration Development
> IBM Germany[/color]

Wow! That's great! It worked perfectly! Here is the result after
running your statement:
1
---------------------------------------
col1;1;
col2;'test1';
col3;'val1'
col1;2;
col2;'test2';
col3;'val2'
col1;3;
col2;'test3';
col3;'val3'

3 record(s) selected.

Thank you very much, Knut!

Export data as well as the corresponding column names?

Export data as well as the corresponding column names?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment