Generate DB2 import/export ddl

Carmine

unread,

Sep 28, 2002, 11:02:41 PM9/28/02

to

Does anyone know of a utility/script that will generate DB2 import and
export DDL for the whole database, so that I can send the scripts to a
client of mine?
Thanks.

Harald Wilhelm

unread,

Sep 29, 2002, 4:27:16 AM9/29/02

to

Carmine,

use something like that (notice the space between "-td" and "!"):

db2look -d dbname -a -e -o dbname.ddl -p -l -x -td !

On the target machine use something like that (notice the _missing_
space between "-td" and "!"):

db2 -c -td! -f dbname.ddl

I always use the "!" as the statement termination character because of
the CREATE TRIGGER statement that might contain the default termination
character ";".

Lloyd D Budd

unread,

Sep 29, 2002, 6:16:32 PM9/29/02

to

Also see 'db2move'

Carmine

unread,

Sep 29, 2002, 9:35:20 PM9/29/02

to

Thanks, I'll give it a shot.

Harald Wilhelm <Harald....@hawi.de> wrote in message news:<1103_10...@news.t-online.de>...

Carmine

unread,

Sep 29, 2002, 9:54:02 PM9/29/02

to

I should have been more specific. I need to generate a script that
contains export statements for each table in the database. And another
script that contains import statements for each table as well. I can
do it for one table at a time in the Control Center, but I have over
400 tables. Any help would be appreciated...

Harald Wilhelm <Harald....@hawi.de> wrote in message news:<1103_10...@news.t-online.de>...

lsu...@mb.sympatico.ca

unread,

Sep 29, 2002, 10:53:59 PM9/29/02

to

You can use REXX and the REXXSQL library to do that. The SYSCAT tables
have the names of all the tables and you can use that to generate a
script containing the db2move and/or db2look commands to export the
DDL and the data.

--
Lorne Sunley

Abhi

unread,

Sep 30, 2002, 12:01:57 AM9/30/02

to

how about using the SQL.
like
select 'export to d:\'||name||' of del'||name from syscat.systables where type = 'T'
and saving the output to a file.
Same can be done for import also.

Harald Wilhelm

unread,

Sep 30, 2002, 5:06:22 AM9/30/02

to

Carmine,

I see, you will catch individual tables. Just use the
information from SYSCAT.TABLES:

select distinct tabschema,
tabname
from syscat.tables
where type = 'T'
and tabschema <> 'SYSCAT'
and tabschema <> 'SYSIBM'
and tabschema <> 'SYSSTAT'

BTW, DB2LOOK and DB2MOVE might help anyway. Just edit manually,
or process by script, the db2move.lst file. After a full
db2move it contains one line per table. After changing this
file, load the tables of your chice with db2move. This will be
much faster than IMPORT.

Carmine

unread,

Sep 30, 2002, 11:31:28 AM9/30/02

to

Thanks, I appreciate the help.

getab...@indiatimes.com (Abhi) wrote in message news:<67db1745.02092...@posting.google.com>...

Gerrit-Jan Linker

unread,

Nov 10, 2002, 11:32:27 AM11/10/02

to

You could consider to use SQL*XL. It does not create the DDL as you
requested but you can use it to send your database to your client as a
spreadsheet. You can pull the data into Excel using SQL*XL. Once it is in
Excel it will be easially viewable by your clients.

SQL*XL specialises in dealing with databases from MS Excel. It works with
all databases and has been tested to work fine against Oracle, Access,
Foxpro, SQL Server, Sybase, etc etc

SQL*XL is full features. You can use Excel cells in the SQL, it supports
macro recording, VBA coding, scripting, comes with an installer/uninstaller
and has a help system.

Find SQL*XL at www.oraxcel.com

I suggest you download both SQL*XL3 and SQL*XL ADO

Kind regards, Gerrit-Jan Linker
"Carmine" <carmine...@iqfinancial.com> wrote in message
news:1eeb8b4.02092...@posting.google.com...