DB2 copy questions 1

[whegge]

What command would I used to copy the tables structures but not the data from one server to another? And then what command would I use to copy the data over after the structures have been created?

 

[lynchdog]

I'd think that you would want to generate the ddl for the database. From there look through the file and change the database name to your new one. After running this script this will get you a database the looks like the original. Next, export and than import (or load depending on the amount and recoverability options that you need) This should do the trick.

 

[whegge]

OK. What is ddl? The rest I think I understand. I am used to running commands at the command prompt. I was hoping there was something like the db2move command that just moves the database structures. Or possibly something at the table level.

Thanks for the help.

 

[lynchdog]

I did a little more reading on this. I've never used the db2move (though I should start!) that sounds like it will move the data for you by exporting and importing the data for you. As for a ddl (Database defination language) It is created view the db2look command. I use the gui tools so the exact terminology isn't the greatest.

db2look -d DBNAME -t TBLNAME -a -e -x -c -o FILENAME.out

-t TBLNAME could be left out if you would like. The DDL would be your complete database. From there you can look through the file and take out the tables that you are looking for.

 

[whegge]

Only advise I can give you on the db2move is be carefull of LOB's. At least in version 6.1 it does not work right. We had to write a Perl program to do the data move of the tables with LOB's in them. I finally found ddl and db2look in the docs. What GUI tool are you using for db2look? I have not see it in Control Center, but I could have missed it.

 

[lynchdog]

In the control center I right click on a table or on the database container. From the menu that select Generate DDL. The next screen that appears is for the parameters for the dblookup command.

 

[whegge]

I finally got the db2look program to produce what I needed. Had to do it from the command line, the gui would not give me everything. But now I am having problems running the ddl on the new machine.

What I am trying to do is:

db2 etest.sql

This errors out saying something about expected more after 'etest.sql' Now the ddl was created on an NT machine running version 6.x of DB2 and I am trying to run the ddl on a Linux machine running DB2 7.2. Is there a problem between the two versions? I did look at the etest.sql file and all that they are is CLP commands. I could eidt the file and put db2 in front every line but that seems like alot of work for somethng that DB2 should already be able to handle.

You have been a great help, thanks!

 

[lynchdog]

I've never ran the ddl from the command line. I've always done it in the command window gui. As for knowing if there is a problem between the versions, i don't know for sure. I've been working with db2 for about six months now and have only used it on NT.

good luck

 

[kkapadia]

at command prompt use :-
db2 -tvf <filename> -z <output file>
where output file captures all your outputs.
For your second question, use export and load to load all the tables. Feel free to contact for more help.
Contact :- k_kapadia@hotmail.com

 

[whegge]

That worked great! Thanks.