Conference orarep::nomahs::rdb_60

~    I can create a drop procedure by selecting from rdb$relation_constraints.

Right, you need to generate ALTER TABLE ... DROP CONSTRAINT syntax.
    
~    I wanted to use RMU/EXTRACT/ITEM=CONSTRAINT to create the add
~    procedure. It is documented that it won't extract table constraints,
~    but when I tried it it wouldn't extract column constraints either. I
~    suspect that this is a documentation problem and that it only extracts
~    contraints define prior to V3.1. I am using V7.0.

The docs should say table *and column* constraints.  /ITEM=CONSTRAINT is used
to define constraints defined by RDO using the DEFINE CONSTRAINT syntax.
    
~    How can I generate a script for adding the constraints back in?

Firstly you can use rdb$relation_constraints to generate the NOT NULL, UNIQUE
and PRIMARY KEY constraints.  You'll need to edit the output from
RMU/EXTRAT/ITEM=TABLE to get the FOREIGN KEY and CHECK constraints, these are
output already as ALTER TABLE syntax.

~    I am trying to create scripts for dropping all constraints in a
~    database, and then adding them back in again. (This is to workaround
~    the problem described in REPOSITORY note 1049).

I would be very surprised if this is any better, the constraint evaluation
will use as much I/O or more than that used by CDD INTEGRATE.

Ian

>~    I am trying to create scripts for dropping all constraints in a
>~    database, and then adding them back in again. (This is to workaround
>~    the problem described in REPOSITORY note 1049).
>
>I would be very surprised if this is any better, the constraint evaluation
>will use as much I/O or more than that used by CDD INTEGRATE.
    
    That may be true, but memory and CPU usage is more of a problem for the
    customer (32 Mbyte VAX 3100), so I thought it was worth a try. I will be
    entering a BUG for this when the BUG system is available again.
    
    If several contraints are added in one alter table statement will they
    each be evaluated separately, even if they are NOT NULL constraints and
    CHECK clauses doing range checks, such that one pass over the table
    should be sufficient?
    
    Peter

>Firstly you can use rdb$relation_constraints to generate the NOT NULL, UNIQUE
>and PRIMARY KEY constraints.  You'll need to edit the output from
    
    I am trying this for the NOT NULL constraints. How can I tell whether
    it is deferrable or not?
    
    Peter

    re: documentation error
    
    The next update of the RMU Reference Manual and/or RMU Help 
    will include the correction to the ITEM=CONSTAINTS qualifier.
    
    
    Thanks for pointing out the error. 

~    If several contraints are added in one alter table statement will they
~    each be evaluated separately, even if they are NOT NULL constraints and
~    CHECK clauses doing range checks, such that one pass over the table
~    should be sufficient?

Each is a separately executed request.  We have on our wishlist to perform
some optimizations (such as do all the NOT NULL constraints in a single scan,
but we haven't gotten there yet.

~>Firstly you can use rdb$relation_constraints to generate the NOT NULL, UNIQUE
~>and PRIMARY KEY constraints.  You'll need to edit the output from
~    
~    I am trying this for the NOT NULL constraints. How can I tell whether
~    it is deferrable or not?

There is a value stored in the table RDB$CONSTRAINTS in column
RDB$EVALUATION_TIME for each constraint.  It has the values:

	1 means COMMIT time for DEFERRABLE (initially defferred)
	2 means UPDATE time (initially immediate)
	3 means UPDATE time (not deferrable)

This should be under the system relations help.

Ian

>There is a value stored in the table RDB$CONSTRAINTS in column
>RDB$EVALUATION_TIME for each constraint.  It has the values:
    
    Thanks. I had just looked at the help on rdb$constraint_relations and
    rdb$relation_constraints.
    
    Integrating with no constraints took about 3000 IO, 45s CPU
    Integrating with only the not null constraints took about 72000 IO, 15
    min CPU.
    Adding the not null constraints took about 100000 IO but less than 1.5
    min CPU.
    
    Peter

    					IO 		CPU	MEM
    Integrate with constraints	    149766 	   45:49.40    4096
      following commit		    215371	   19:18.07    4096
      total			    365137       1:05:07.47    
    
    Integrate without constraints     2379	      44.01    2770
      following commit		        52	       0.42    2770
      adding check constraints	     68616	      51.79    1549
      adding not null constraints    91356	    2:17.07    1584
      final commit		        29             0.05    1584
      total			    162432	    3:53.34
    
    So it uses less IO, less CPU, and less memory. I'll see if this is
    acceptable to the customer tomorrow.
    
    Peter