Dump of a schema in DB2 / Admin_Copy_Schema

Rahul B

Hi,

I am very new to DB2, so please explain in somwhat detail.

How can i (can i ?) take the dump of a schema in DB2 like i can do in
Oracle?

Also, if i have to create a replica of a schema, i can use the
sysproc.admin_copy_schema procedure.
But the problem is that the procedures/functions/views are
interdependent and the most of the functions/procedures will not get
formed due to its dependency over a function that was created later.

In Oracle, it's not a problem since even if the proc/func is not
compiled, it'll be created and later we can compile all the procs to
remove circular dependencies.

But seems DB2 is strict enough to not allow this.

please tell how can i sort this out.

Thanks a lot.

Rahul

Aug 11 '07 #1

Serge Rielau

Rahul B wrote:

On Aug 11, 4:12 pm, Serge Rielau <srie...@ca.ibm.comwrote:
I already have a schema where many funcs/proc call the other funcs/
procs and views use some procedures. Hence, when the new schema is
created, the Errortable in Errorschema shows that these object
creations have failed with the reason code and i have to manually see
due to which function/proc that view is failing and create the
corresponding proc/function first.

The routines have the linear dependencies(and not the circular once as
i referred earlier..apologies for that) but the function
Admin_Copy_Schema is not able to find out, which ones to compile
first.

That is certainly not as advertised and should be looked at. Can you
open a PMR?

I have written an article on backup/restore and copy schema on
developerWorks. If you send me an email I'll ping you back the latest
version of the DDL. It's AS IS of course, but it has been broken in, so
to speak...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Aug 12 '07 #2

Mark A

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:5i*************@mid.individual.net...

Mark I added the -td option to db2look myself in DB2 V7.2 to support SQL
functions. If you don't use it db2look will actually append a -- to each
line with a semicolon at least for triggers and functions.
(That should be changed to --#SET TERMINATOR)

Cheers
Serge

Thanks for that tip. I never noticed that in the doc.

Aug 12 '07 #3

Rahul B

On Aug 12, 8:53 am, "Mark A" <nob...@nowhere.comwrote:

"Serge Rielau" <srie...@ca.ibm.comwrote in message

news:5i*************@mid.individual.net...

Mark I added the -td option to db2look myself in DB2 V7.2 to support SQL
functions. If you don't use it db2look will actually append a -- to each
line with a semicolon at least for triggers and functions.
(That should be changed to --#SET TERMINATOR)

Cheers
Serge

Thanks for that tip. I never noticed that in the doc.

Hi,

Can we take a dump/backup of a schema with the following db2move
command.

db2move <DB_NAMEexport -tc <SCHEMA_NAME>

It gives the ixfs of all the tables and db2look for the other db
objects.

Since you didn't suggest it, i assume that this will miss something.

What could be the problem with taking a dump like this.

Thanks again

Rahul

Aug 14 '07 #4

Serge Rielau

Rahul B wrote:

On Aug 12, 8:53 am, "Mark A" <nob...@nowhere.comwrote:

>"Serge Rielau" <srie...@ca.ibm.comwrote in message

news:5i*************@mid.individual.net...

>>Mark I added the -td option to db2look myself in DB2 V7.2 to support SQL
functions. If you don't use it db2look will actually append a -- to each
line with a semicolon at least for triggers and functions.
(That should be changed to --#SET TERMINATOR)
Cheers
Serge

Thanks for that tip. I never noticed that in the doc.

Hi,

Can we take a dump/backup of a schema with the following db2move
command.

db2move <DB_NAMEexport -tc <SCHEMA_NAME>

It gives the ixfs of all the tables and db2look for the other db
objects.

Since you didn't suggest it, i assume that this will miss something.

I didn't suggest it because I assumed you want only the DDL.
Also the db2move "copy schema" facility is online (live FTP connection),
which I didn't associate with "dump".

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Aug 14 '07 #5