Export data as well as the corresponding column names?

[mjf]

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.

[shenanwei]

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:

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.

[Brian Tkatch]

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]

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]

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]

mjf wrote:

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 '

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]

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]

In article <11**********************@o13g2000cwo.googlegroups .com>,
mjf (mi****@excite.com) says...

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

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]

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]

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]

mjf wrote:

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

In Thunderbird, right click mouse and pick Cancel Message.
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[mjf]

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]

mjf wrote:

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"

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]

Knut Stolze wrote:

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

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!