How to get a DB2 database's structure?

[mm8294]

I am a Oracle DBA but new to DB2. The boss wants me to clone a DB2 database DEV, but for some reasons, we must do it in two steps: first, create an empty database PROD from DEV. The new database PROD must have exactly the same structure as DEV but it's empty. Second, export/import data from DEV to PROD.

In Oracle this could be done with exp/imp, but I don't know how to do it in DB2. Please help me to get this done.

Since I am new to DB2, detailed syntax or examples will be very appreciated.

Thanks in advance.

 

[gregsimpson]

mm8294,

A utility called db2look should generate you DDL, to clone the database.

DB2 also has "import" and "export" and "load" utilities, named as such. A simple google search or even searching this forum should bring up examples of all of the utilities I mention.

As db2 runs on may platforms it might be helpful if you are able to say what operating system it's sitting on and also what version of db2 you are running.

Cheers
Greg

 

[blom0344]

Don't know why you want an empty one first, but you would be faster by taking a backup from DEV and creating a new database from this backup........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[mm8294]

GregSimpson, thank you very much for the information. Our source database, DEV, is running on IBM AIX, DB2 version 8.1; The target database, PROD, will be running on Windows XP, DB2 version 7.2

 

[mm8294]

Blom0344, any idea about which utility should be used?

Thank you very much.

 

[blom0344]

Well, I actually used control center to create a version 8 database out of a version 7.1 backup and that went well (provided that the path is exactly the same on both machines.)

However, the other way around with different operating systems would be quite another matter.



T. Blom
Information analyst
tbl@shimano-eu.com

 

[gregsimpson]

mm8294,

what I have told you should work. However the scenario painted by blom0344 is very elegant. As he/she points out to you it was done going forward releases not going back.

Let us know how you get on

In the meantime

db2look

http://salz.is.informatik.uni-duisburg.de/db2doc/en_US/index.htm?openup=core/r0002051.htm

Cheers
Greg

 

[mm8294]

GregSimpson:

Thank you very much for all your help and concern. I tried to use db2look but got no luck. Below is the output:

db2 => db2look -d satctldb -u cliang -e -o alltable.sql
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "-" was found following "db2look". Expected tokens may include : "JOIN". SQLSTATE=42601


Should I run it in another command line processor?

Thank you very much.

 

[gregsimpson]

mm8294,

don't go into the db2 command line mode first. Simply run your command from your UNIX prompt and it should establish a connection itself.

Thanks
Greg Simpson

 

[blom0344]

he/she

Last time I looked I was a boy

T. Blom
Information analyst
tbl@shimano-eu.com