Hi all,

this might sound strange, but I'm desperately seeking for a solution for
this (personal) issue.

Ages ago, I built a DB2 database under OS/2, even with a VX-Rexx
graphical interface. I maintained the OS/2 PC for a long time but
meanwhile I'm considering a migration. :-)

I'm aware, that I can forget all the graphical interfacing that I did,
am just caring for the data. Also I do not intend to run a DB2 again for
maintaining my CD collection.

So I am looking for an easy way to get the tables into Excel under XP.

My dream is a tool that can read the DB2 backup, and copies the tables
in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
are needed, I will try!

Thanks a lot for any hint.


Udo

P.S.: Since years I didn't program anything anymore or worked with SQL
or DB2, please do not consider that I'm an expert.

Udo Kerwath wrote:[color=blue]
> Hi all,
>
> this might sound strange, but I'm desperately seeking for a solution for
> this (personal) issue.
>
> Ages ago, I built a DB2 database under OS/2, even with a VX-Rexx
> graphical interface. I maintained the OS/2 PC for a long time but
> meanwhile I'm considering a migration. :-)
>
> I'm aware, that I can forget all the graphical interfacing that I did,
> am just caring for the data. Also I do not intend to run a DB2 again for
> maintaining my CD collection.
>
> So I am looking for an easy way to get the tables into Excel under XP.[/color]

The EXPORT utility supports delimited files (a.k.a. CSV), which Excel
reads happily.

EXPORT TO TABLE.CSV of DEL SELECT * FROM TABLE

In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
[color=blue]
>
> My dream is a tool that can read the DB2 backup, and copies the tables
> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
> are needed, I will try!
>[/color]

With which version of DB2 was the backup created?

>DB2 database under OS/2, even with a VX-Rexx graphical interface.

Oh, wow, i did that a decade ago? Sheesh! :)

B.

Gert van der Kooij wrote:[color=blue]
> In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
>[color=green]
>> My dream is a tool that can read the DB2 backup, and copies the tables
>> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
>> are needed, I will try!
>>[/color]
>
> With which version of DB2 was the backup created?[/color]

Gert,

it was generated with DB2 V6 (at least that's what the "Event Monitor"
and "Command Center" are saying.

Cheers,
Udo

In article <dv43lv$3rc$1@online.de>, no.spam.udo@online.de says...[color=blue]
> Gert van der Kooij wrote:[color=green]
> > In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
> >[color=darkred]
> >> My dream is a tool that can read the DB2 backup, and copies the tables
> >> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
> >> are needed, I will try!
> >>[/color]
> >
> > With which version of DB2 was the backup created?[/color]
>
> Gert,
>
> it was generated with DB2 V6 (at least that's what the "Event Monitor"
> and "Command Center" are saying.
>
> Cheers,
> Udo
>[/color]

Do you still have a system running with OS/2 and DB2 V6?
If so you can restore the backup and use the export command as have been
sugggested by others. If you don't have a V6 system you need at least V7
system to restore (and automatically migrate) the backup and export the
tables.

Gert van der Kooij wrote:[color=blue]
> In article <dv43lv$3rc$1@online.de>, no.spam.udo@online.de says...[color=green]
>> Gert van der Kooij wrote:[color=darkred]
>>> In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
>>>
>>>> My dream is a tool that can read the DB2 backup, and copies the tables
>>>> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
>>>> are needed, I will try!
>>>>
>>> With which version of DB2 was the backup created?[/color]
>> Gert,
>>
>> it was generated with DB2 V6 (at least that's what the "Event Monitor"
>> and "Command Center" are saying.
>>
>> Cheers,
>> Udo
>>[/color]
>
> Do you still have a system running with OS/2 and DB2 V6?[/color]

Yes - I still use the database.
[color=blue]
> If so you can restore the backup and use the export command as have been
> sugggested by others. If you don't have a V6 system you need at least V7
> system to restore (and automatically migrate) the backup and export the
> tables.[/color]

How can I do the export (ohh gosh, 10 years ago I knew...)? I vaguely
remember that there was an app to see the different tables and so on.
But opening the Folder "DB2 for OS/2" I see a lot of things, but best
looks like a command line interface....:
o HTML Search Server
o Command Center, Command Line Processor
o ODBC Installer & Administrator
o Client Configuration Assistant
o Windows Support
o Problem Determination Tools
o First steps

Sorry to bother you with those stupid beginner questions (incredible
what you can forget within only 10 years...).

Udo

In article <dv45fr$6k2$1@online.de>, no.spam.udo@online.de says...[color=blue]
> Gert van der Kooij wrote:[color=green]
> > In article <dv43lv$3rc$1@online.de>, no.spam.udo@online.de says...[color=darkred]
> >> Gert van der Kooij wrote:
> >>> In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
> >>>
> >>>> My dream is a tool that can read the DB2 backup, and copies the tables
> >>>> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
> >>>> are needed, I will try!
> >>>>
> >>> With which version of DB2 was the backup created?
> >> Gert,
> >>
> >> it was generated with DB2 V6 (at least that's what the "Event Monitor"
> >> and "Command Center" are saying.
> >>
> >> Cheers,
> >> Udo
> >>[/color]
> >
> > Do you still have a system running with OS/2 and DB2 V6?[/color]
>
> Yes - I still use the database.
>[color=green]
> > If so you can restore the backup and use the export command as have been
> > sugggested by others. If you don't have a V6 system you need at least V7
> > system to restore (and automatically migrate) the backup and export the
> > tables.[/color]
>
> How can I do the export (ohh gosh, 10 years ago I knew...)? I vaguely
> remember that there was an app to see the different tables and so on.
> But opening the Folder "DB2 for OS/2" I see a lot of things, but best
> looks like a command line interface....:
> o HTML Search Server
> o Command Center, Command Line Processor
> o ODBC Installer & Administrator
> o Client Configuration Assistant
> o Windows Support
> o Problem Determination Tools
> o First steps
>
> Sorry to bother you with those stupid beginner questions (incredible
> what you can forget within only 10 years...).
>[/color]

If I do remember right the Command Center was a resource hog with V6,
but I could be totally wrong with that. I still prefer the command line,
although I'm on Windows, that's because of the unix background I guess
:)

From the command line you can use the 'db2 list tables for all' to get
all tables (after connecting to the database). This command can also be
used to list all tables belonging to one schema. If you need to know
which schema's are in your db you can run the command
db2 "select distinct tabschema from syscat.tables where type = 'T'".

For each table you need to run the command
db2 "xport to <file.csv> of del select * from <tabschema>.<tabname>".

You can create the export statements with the (wrapped) command
db2 "select 'db2 export to ' || rtrim(tabschema) || '_' || rtrim
(tabname) || '.csv of del select * from ' || rtrim(tabschema) || '.' ||
rtrim(tabname) from syscat.tables where type = 'T' and tabschema =
'<tabschema>'"

Hope this helps.

Gert van der Kooij wrote:[color=blue]
> In article <dv45fr$6k2$1@online.de>, no.spam.udo@online.de says...[color=green]
>> Gert van der Kooij wrote:[color=darkred]
>>> In article <dv43lv$3rc$1@online.de>, no.spam.udo@online.de says...
>>>> Gert van der Kooij wrote:
>>>>> In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
>>>>>
>>>>>> My dream is a tool that can read the DB2 backup, and copies the tables
>>>>>> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
>>>>>> are needed, I will try!
>>>>>>
>>>>> With which version of DB2 was the backup created?
>>>> Gert,
>>>>
>>>> it was generated with DB2 V6 (at least that's what the "Event Monitor"
>>>> and "Command Center" are saying.
>>>>
>>>> Cheers,
>>>> Udo
>>>>
>>> Do you still have a system running with OS/2 and DB2 V6?[/color]
>> Yes - I still use the database.
>>[color=darkred]
>>> If so you can restore the backup and use the export command as have been
>>> sugggested by others. If you don't have a V6 system you need at least V7
>>> system to restore (and automatically migrate) the backup and export the
>>> tables.[/color]
>> How can I do the export (ohh gosh, 10 years ago I knew...)? I vaguely
>> remember that there was an app to see the different tables and so on.
>> But opening the Folder "DB2 for OS/2" I see a lot of things, but best
>> looks like a command line interface....:
>> o HTML Search Server
>> o Command Center, Command Line Processor
>> o ODBC Installer & Administrator
>> o Client Configuration Assistant
>> o Windows Support
>> o Problem Determination Tools
>> o First steps
>>
>> Sorry to bother you with those stupid beginner questions (incredible
>> what you can forget within only 10 years...).
>>[/color]
>
> If I do remember right the Command Center was a resource hog with V6,
> but I could be totally wrong with that. I still prefer the command line,
> although I'm on Windows, that's because of the unix background I guess
> :)
>
> From the command line you can use the 'db2 list tables for all' to get
> all tables (after connecting to the database). This command can also be
> used to list all tables belonging to one schema. If you need to know
> which schema's are in your db you can run the command
> db2 "select distinct tabschema from syscat.tables where type = 'T'".
>
> For each table you need to run the command
> db2 "xport to <file.csv> of del select * from <tabschema>.<tabname>".
>
> You can create the export statements with the (wrapped) command
> db2 "select 'db2 export to ' || rtrim(tabschema) || '_' || rtrim
> (tabname) || '.csv of del select * from ' || rtrim(tabschema) || '.' ||
> rtrim(tabname) from syscat.tables where type = 'T' and tabschema =
> '<tabschema>'"
>
> Hope this helps.
>[/color]

Gert,

this looking great - I will try it asap!

Thanks so much.

Udo

Gert van der Kooij wrote:[color=blue]
> In article <dv45fr$6k2$1@online.de>, no.spam.udo@online.de says...[color=green]
>> Gert van der Kooij wrote:[color=darkred]
>>> In article <dv43lv$3rc$1@online.de>, no.spam.udo@online.de says...
>>>> Gert van der Kooij wrote:
>>>>> In article <dv3req$msc$1@online.de>, no.spam.udo@online.de says...
>>>>>
>>>>>> My dream is a tool that can read the DB2 backup, and copies the tables
>>>>>> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
>>>>>> are needed, I will try!
>>>>>>
>>>>> With which version of DB2 was the backup created?
>>>> Gert,
>>>>
>>>> it was generated with DB2 V6 (at least that's what the "Event Monitor"
>>>> and "Command Center" are saying.
>>>>
>>>> Cheers,
>>>> Udo
>>>>
>>> Do you still have a system running with OS/2 and DB2 V6?[/color]
>> Yes - I still use the database.
>>[color=darkred]
>>> If so you can restore the backup and use the export command as have been
>>> sugggested by others. If you don't have a V6 system you need at least V7
>>> system to restore (and automatically migrate) the backup and export the
>>> tables.[/color]
>> How can I do the export (ohh gosh, 10 years ago I knew...)? I vaguely
>> remember that there was an app to see the different tables and so on.
>> But opening the Folder "DB2 for OS/2" I see a lot of things, but best
>> looks like a command line interface....:
>> o HTML Search Server
>> o Command Center, Command Line Processor
>> o ODBC Installer & Administrator
>> o Client Configuration Assistant
>> o Windows Support
>> o Problem Determination Tools
>> o First steps
>>
>> Sorry to bother you with those stupid beginner questions (incredible
>> what you can forget within only 10 years...).
>>[/color]
>
> If I do remember right the Command Center was a resource hog with V6,
> but I could be totally wrong with that. I still prefer the command line,
> although I'm on Windows, that's because of the unix background I guess
> :)
>
> From the command line you can use the 'db2 list tables for all' to get
> all tables (after connecting to the database). This command can also be
> used to list all tables belonging to one schema. If you need to know
> which schema's are in your db you can run the command
> db2 "select distinct tabschema from syscat.tables where type = 'T'".
>
> For each table you need to run the command
> db2 "xport to <file.csv> of del select * from <tabschema>.<tabname>".
>
> You can create the export statements with the (wrapped) command
> db2 "select 'db2 export to ' || rtrim(tabschema) || '_' || rtrim
> (tabname) || '.csv of del select * from ' || rtrim(tabschema) || '.' ||
> rtrim(tabname) from syscat.tables where type = 'T' and tabschema =
> '<tabschema>'"
>
> Hope this helps.
>[/color]

Gert,

it works - I'm so happy!

Just a last small nice-to-know question: can I somehow read the headers
of the columns, like first column is "Artist", 2nd is "title" and so on....?

You really helped me so much - thanks a lot!!!

Udo

On Mon, 13 Mar 2006 13:18:17 UTC Udo Kerwath <no.spam.udo@online.de>
wrote:
[color=blue]
> Hi all,
>
> this might sound strange, but I'm desperately seeking for a solution for
> this (personal) issue.
>
> Ages ago, I built a DB2 database under OS/2, even with a VX-Rexx
> graphical interface. I maintained the OS/2 PC for a long time but
> meanwhile I'm considering a migration. :-)
>
> I'm aware, that I can forget all the graphical interfacing that I did,
> am just caring for the data. Also I do not intend to run a DB2 again for
> maintaining my CD collection.
>
> So I am looking for an easy way to get the tables into Excel under XP.
>
> My dream is a tool that can read the DB2 backup, and copies the tables
> in Excel spreadsheets.... okay, dreaming... whatever intermediate steps
> are needed, I will try!
>
> Thanks a lot for any hint.
>
>
> Udo
>
> P.S.: Since years I didn't program anything anymore or worked with SQL
> or DB2, please do not consider that I'm an expert.[/color]

I do this all the time so beware of one very annoying problem if your
database is large - and by large I mean anything over about 25-30,000
rows or if it produces an Excel spread sheet greater than about 8mb -
in that every version of Excel I've tried ('97 - 2003) starts to go
VERY unstable at those limits. Another thought is that I have been
able to link W2K and XP versions of Excel directly to the database via
the ODBC drivers if you have them available. That's by far the
neatest way to go.

--
Will Honea

Udo Kerwath schrieb:[color=blue]
>
> Just a last small nice-to-know question: can I somehow read the headers
> of the columns, like first column is "Artist", 2nd is "title" and so on....?
>[/color]

db2 export to Spalten.del of del select colname from syscat.columns
where tabname = '<your table>'
You get 1 row for one header. You can switch it in Excel.

Grüße,
--
Burkhard Schultheis
Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
Email: schultheis@tde-online.de
Phone: +49 7245 9287-21

In article <47njhmFghntvU1@individual.net>, schultheis@tde-online.de
says...[color=blue]
> Udo Kerwath schrieb:[color=green]
> >
> > Just a last small nice-to-know question: can I somehow read the headers
> > of the columns, like first column is "Artist", 2nd is "title" and so on.....?
> > [/color]
>
> db2 export to Spalten.del of del select colname from syscat.columns
> where tabname = '<your table>'
> You get 1 row for one header. You can switch it in Excel.
>
> Grüße,
> [/color]

Yes, that would be the easiest way. It might also be possible to
generate it in one run together with the previous command to create the
export statements using a recursive query on the syscat.columns table
but I'll leave that to Udo. If you want to read about what you can do
with SQL and DB2 the SQL Cookbook from Graeme Birchall is a great help.
You can find at at http://mysite.verizon.net/Graeme_Birchall/id1.html

Gert van der Kooij wrote:[color=blue]
> In article <47njhmFghntvU1@individual.net>, schultheis@tde-online.de
> says...[color=green]
>> Udo Kerwath schrieb:[color=darkred]
>>> Just a last small nice-to-know question: can I somehow read the headers
>>> of the columns, like first column is "Artist", 2nd is "title" and so on....?
>>>[/color]
>> db2 export to Spalten.del of del select colname from syscat.columns
>> where tabname = '<your table>'
>> You get 1 row for one header. You can switch it in Excel.
>>
>> Grüße,
>>[/color]
>
> Yes, that would be the easiest way. It might also be possible to
> generate it in one run together with the previous command to create the
> export statements using a recursive query on the syscat.columns table
> but I'll leave that to Udo. If you want to read about what you can do
> with SQL and DB2 the SQL Cookbook from Graeme Birchall is a great help.
> You can find at at http://mysite.verizon.net/Graeme_Birchall/id1.html[/color]

Gert, Burkhard, Will,

thanks a lot for all your help - this was such a relief for me, after
years of fear the OS/2 system might crash. Don't ask why I didn't do it
earlier, maybe because I thought it would be much more complicated. But
it wasn't - thanks to you!

And Will, I couldn't afford to buy sooo many CDs that database size is a
problem, current size is 1 MB, roughly 6000 rows.

Cheers,
Udo

On Tue, 14 Mar 2006 13:07:49 UTC Udo Kerwath <no.spam.udo@online.de>
wrote:
[color=blue]
> Gert van der Kooij wrote:[color=green]
> > In article <47njhmFghntvU1@individual.net>, schultheis@tde-online.de
> > says...[color=darkred]
> >> Udo Kerwath schrieb:
> >>> Just a last small nice-to-know question: can I somehow read the headers
> >>> of the columns, like first column is "Artist", 2nd is "title" and so on....?
> >>>
> >> db2 export to Spalten.del of del select colname from syscat.columns
> >> where tabname = '<your table>'
> >> You get 1 row for one header. You can switch it in Excel.
> >>
> >> Grüße,
> >>[/color]
> >
> > Yes, that would be the easiest way. It might also be possible to
> > generate it in one run together with the previous command to create the
> > export statements using a recursive query on the syscat.columns table
> > but I'll leave that to Udo. If you want to read about what you can do
> > with SQL and DB2 the SQL Cookbook from Graeme Birchall is a great help.
> > You can find at at http://mysite.verizon.net/Graeme_Birchall/id1.html[/color]
>
> Gert, Burkhard, Will,
>
> thanks a lot for all your help - this was such a relief for me, after
> years of fear the OS/2 system might crash. Don't ask why I didn't do it
> earlier, maybe because I thought it would be much more complicated. But
> it wasn't - thanks to you!
>
> And Will, I couldn't afford to buy sooo many CDs that database size is a
> problem, current size is 1 MB, roughly 6000 rows.[/color]

LOL! Actually, I still run OS/2 for the databases I serve here -
wound up with a lifetime license for DB2 V7 and that has been solid
enough to keep me happy (and they never released V8 for OS/2). When
you mentioned a CD index, I immediately thought of the database I
built for the DEVCON (later Dev. Connection) distros. The last
distribution I got of that was over 300 CD's and I've been getting
them since Vol 1 with it's 3 CD's. I haven't had the nerve to check
the db size lately but it is way up in the millions of rows - not what
you would want in a spread sheet <g>.

--
Will Honea