SEO

There are 2 tables A and B with A being the parent of B.

Table A
(
Col1 varchar(5) Not Null
)

Table B
(
B_PK varchar(5) Not Null ,
B1 varchar(5) ,
B2 varchar(5)
)

Basically what we want to do was if any A.Col1 is deleted and there
are matching B1 or B2, set them to null for those records.

I know if we use 'on delete' with rules other than 'SET NULL', it
would be fine. But what is wrong with setting any related FK values to
null when its corresponding PK is deleted??

D:\DBA\LDS>db2 create table A (Col1 varchar(5) not null)
DB20000I The SQL command completed successfully.

D:\DBA\LDS>db2 alter table A add primary key (col1)
DB20000I The SQL command completed successfully.

D:\DBA\LDS>db2 create table B (B_PK varchar(5) not null, B1
varchar(5), B2 varchar(5))
DB20000I The SQL command completed successfully.

D:\DBA\LDS>db2 alter table B add primary key (B_PK)
DB20000I The SQL command completed successfully.

D:\DBA\LDS>db2 alter table B add foreign key (B1) references A on
delete set null
DB20000I The SQL command completed successfully.

D:\DBA\LDS>db2 alter table B add foreign key (B2) references A on
delete set null
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0632N FOREIGN KEY "B2..." is not valid because the table cannot be
defined
as a dependent of table "LDSDEV.A" because of delete rule restrictions
(reason
code = "3"). SQLSTATE=42915

What product and what version? DB2 for Linux/Unix/Windows added support
for what you're trying to do in Fixpack 3 of Version 8.1

Regards,
Miro

We are running DB2 v8.1.1.0, fixpak 0 on AIX v5. I'll see if we could
put FixPak3 on the server, which has Websphere stuffs on it ...
Thanks.

DB21085I Instance "ldsdbdev" uses "32" bits and DB2 code release
"SQL08010"
with level identifier "01010106".
Informational tokens are "DB2 v8.1.1.0", "s021023", "", and FixPak
"0".
Product is installed at "/usr/opt/db2_08_01".


miro flasza <miro@torolab.ibm.com> wrote in message news:<bo6oof$3ig$1@hanover.torolab.ibm.com>...
> What product and what version? DB2 for Linux/Unix/Windows added support
> for what you're trying to do in Fixpack 3 of Version 8.1
>
> Regards,
> Miro

"sql-db2-dba" <leungb@aptea.com> wrote in message
news:c8b20941.0311050621.7c7e6bfb@posting.google.c om...
> We are running DB2 v8.1.1.0, fixpak 0 on AIX v5. I'll see if we could
> put FixPak3 on the server, which has Websphere stuffs on it ...
> Thanks.
>
Base code with no fixpaks is very buggy. Fixpak 4 is expected soon.