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

Rahul B wrote:
> 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?
To dump the DDL use db2look. There are various options to pick
individual schemas, include statistics, etc, ...
>
> 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.
This is a problem for db2look (just run in multiple times), but the
admin_copy procedure does a fair bit of analysis to sort out order.
Is this a principle concern of your or do you have an actual failing
example?
Note that in DB2 routines cannot statically have circular dependencies.
The creation can always be linearized.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Aug 11, 4:12 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Rahul B wrote:
> > 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?
>
> To dump the DDL use db2look. There are various options to pick
> individual schemas, include statistics, etc, ...
>
> > 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.
>
> This is a problem for db2look (just run in multiple times), but the
> admin_copy procedure does a fair bit of analysis to sort out order.
> Is this a principle concern of your or do you have an actual failing
> example?
> Note that in DB2 routines cannot statically have circular dependencies.
> The creation can always be linearized.
>
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Hi Serge,

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.

Also, regarding the DB2 look, i am quite reluctant to use it, simply
because the IBM site itself says that
"The DDL generated might not exactly reproduce all characteristics of
the original SQL objects. Check the DDL generated by db2look. "
at
http://publib.boulder.ibm.com/infoce...e/r0002051.htm

I am not sure why it is recommended to check the DB2 look DDLs and not
even told what could be the problems in DB2 look and what should be
done to avoid them.

Rahul

"Rahul B" <rahul.babbar1@gmail.com> wrote in message
news:1186836362.568464.285790@g12g2000prg.googlegr oups.com...
> Also, regarding the DB2 look, i am quite reluctant to use it, simply
> because the IBM site itself says that
> "The DDL generated might not exactly reproduce all characteristics of
> the original SQL objects. Check the DDL generated by db2look. "
> at
> http://publib.boulder.ibm.com/infoce...e/r0002051.htm
>
> I am not sure why it is recommended to check the DB2 look DDLs and not
> even told what could be the problems in DB2 look and what should be
> done to avoid them.
>
> Rahul

I have not noticed any problems with recent versions of db2look, although it
might help if you have the latest fixpack. One thing that they may be
referring to is identity columns, where db2look will show the original DDL
used for defining the starting number instead of the most current starting
number based on inserted rows. I don't recall any other issues off-hand.

However, since that doc comment was added for FP7 (first release of 8.2)
then perhaps it refers to some new 8.2 feature that has the problem (or
maybe they have fixed that by now since we are at FP14).

The big problem with db2look for procedures, functions, and triggers
(especially if you have a lot of them) is that you have to manually put in
terminators at the end of each one that are different than the default
semi-colon (which is used to terminate lines within the procedure, function,
trigger). This also requires you to put these in a separate file from all
the other DDL unless you want to manually change the terminators on these
also. IBM really needs to fix this.

Some 3rd party tools will process the db2look DDL correctly without the
statement termination changes above. One example is DBArtisan.

Mark A wrote:
> "Rahul B" <rahul.babbar1@gmail.com> wrote in message
> news:1186836362.568464.285790@g12g2000prg.googlegr oups.com...
>> Also, regarding the DB2 look, i am quite reluctant to use it, simply
>> because the IBM site itself says that
>> "The DDL generated might not exactly reproduce all characteristics of
>> the original SQL objects. Check the DDL generated by db2look. "
>> at
>> http://publib.boulder.ibm.com/infoce...e/r0002051.htm
>>
>> I am not sure why it is recommended to check the DB2 look DDLs and not
>> even told what could be the problems in DB2 look and what should be
>> done to avoid them.
>>
>> Rahul
>
> I have not noticed any problems with recent versions of db2look, although it
> might help if you have the latest fixpack. One thing that they may be
> referring to is identity columns, where db2look will show the original DDL
> used for defining the starting number instead of the most current starting
> number based on inserted rows. I don't recall any other issues off-hand.
db2look cannot observe the original semantics of e.g. a SELECT * within
a view if a column was added at a later point.
the same is true for function references where better matches were added
after the creation of the original object.
These "anomalies", I dare say, are applicable across vendors. DB2 just
spells the problem out. It's all rooted in "conservative semantics"

> The big problem with db2look for procedures, functions, and triggers
> (especially if you have a lot of them) is that you have to manually put in
> terminators at the end of each one that are different than the default
> semi-colon (which is used to terminate lines within the procedure, function,
> trigger). This also requires you to put these in a separate file from all
> the other DDL unless you want to manually change the terminators on these
> also. IBM really needs to fix this.
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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Rahul B wrote:
> On Aug 11, 4:12 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> 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

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:5i7dogF3nqcfoU1@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.

On Aug 12, 8:53 am, "Mark A" <nob...@nowhere.com> wrote:
> "Serge Rielau" <srie...@ca.ibm.com> wrote in message
>
> news:5i7dogF3nqcfoU1@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_NAME> export -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

"Rahul B" <rahul.babbar1@gmail.com> wrote in message
news:1187067418.500478.35040@m37g2000prh.googlegro ups.com...
> Hi,
>
> Can we take a dump/backup of a schema with the following db2move
> command.
>
> db2move <DB_NAME> export -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

Importing an IXF when the table does not already exist on the target will
create the new table and PK, but will not create referential constraints and
foreign key definitions. There are probably some other column or table
attributes that are also not automatically created on a new table with IXF
files , but I don't know them off-hand.

The best procedure is do the db2look on the schema, create the new schema on
the target, then do a db2 move on the data. However, I prefer to strip the
FK's out of the db2look output into a separate file and run them after the
db2move import/load in order to avoid check pending issues.

On Aug 14, 11:44 am, "Mark A" <nob...@nowhere.com> wrote:
> "Rahul B" <rahul.babb...@gmail.com> wrote in message
>
> news:1187067418.500478.35040@m37g2000prh.googlegro ups.com...
>
>
>
> > Hi,
>
> > Can we take a dump/backup of a schema with the following db2move
> > command.
>
> > db2move <DB_NAME> export -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
>
> Importing an IXF when the table does not already exist on the target will
> create the new table and PK, but will not create referential constraints and
> foreign key definitions. There are probably some other column or table
> attributes that are also not automatically created on a new table with IXF
> files , but I don't know them off-hand.
>
> The best procedure is do the db2look on the schema, create the new schema on
> the target, then do a db2 move on the data. However, I prefer to strip the
> FK's out of the db2look output into a separate file and run them after the
> db2move import/load in order to avoid check pending issues.

Thanks Mark.

I agree that should be the way.

Rahul