Invalid packages after dropping Prmary key : URGENT

[Kumar]

Need urgent help....

I wanted update one table which has a primary key and also has few
dependents.

I dropped Primary key before update sothat it won't affect the
dependent tables.

After dropping promary key, I could see many packages were invalid in
syscat.packages (VALID column).

Can anyone help me in understanding this please ?

Once I issue db2rbind, all packages will be VALID.

[Mark A]

"Kumar" <ku***@tumkurcity.com> wrote in message
news:2f**************************@posting.google.c om...

Need urgent help....

I wanted update one table which has a primary key and also has few
dependents.

I dropped Primary key before update sothat it won't affect the
dependent tables.

After dropping promary key, I could see many packages were invalid in
syscat.packages (VALID column).

Can anyone help me in understanding this please ?

Once I issue db2rbind, all packages will be VALID.

The access plan in the package was dependent on the primary key. This could
be because DB2 created a unique index on the primary key and the index was
used in the access path, or for some other reason.

When an invalid package is executed, DB2 does an automatic rebind so that
DB2 can determine a new access path. If the rebind fails (for example, if an
referenced table, view or column is missing) the package is marked as
inoperative.

[Mark A]

"Kumar" <ku***@tumkurcity.com> wrote in message
news:2f**************************@posting.google.c om...

Need urgent help....

I wanted update one table which has a primary key and also has few
dependents.

I dropped Primary key before update sothat it won't affect the
dependent tables.

After dropping promary key, I could see many packages were invalid in
syscat.packages (VALID column).

Can anyone help me in understanding this please ?

Once I issue db2rbind, all packages will be VALID.

The access plan in the package was dependent on the primary key. This could
be because DB2 created a unique index on the primary key and the index was
used in the access path, or for some other reason.

When an invalid package is executed, DB2 does an automatic rebind so that
DB2 can determine a new access path. If the rebind fails (for example, if an
referenced table, view or column is missing) the package is marked as
inoperative.

[Ian]

Mark A wrote:

"Kumar" <ku***@tumkurcity.com> wrote in message
news:2f**************************@posting.google.c om...

Need urgent help....

I wanted update one table which has a primary key and also has few
dependents.

I dropped Primary key before update sothat it won't affect the
dependent tables.

After dropping promary key, I could see many packages were invalid in
syscat.packages (VALID column).

Can anyone help me in understanding this please ?

Once I issue db2rbind, all packages will be VALID.

The access plan in the package was dependent on the primary key. This could
be because DB2 created a unique index on the primary key and the index was
used in the access path, or for some other reason.

When an invalid package is executed, DB2 does an automatic rebind so that
DB2 can determine a new access path. If the rebind fails (for example, if an
referenced table, view or column is missing) the package is marked as
inoperative.

You can avoid this by creating a unique index _prior_ to adding the primary
key. For example:

create table t1 (
c1 int not null,
c2 char(20)
);

create unique index t1_px
on t1 (c1);

alter table t1
add constraint pk_t1
primary key (c1);
The primary key will then use the index t1_px instead of automatically
creating and index for the primary key. This also has an advantage in
that it allows you to specify other options for this index, such as
allowing reverse scans or including other keys in the unique index.
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Ian]

Mark A wrote:

"Kumar" <ku***@tumkurcity.com> wrote in message
news:2f**************************@posting.google.c om...

Need urgent help....

I wanted update one table which has a primary key and also has few
dependents.

I dropped Primary key before update sothat it won't affect the
dependent tables.

After dropping promary key, I could see many packages were invalid in
syscat.packages (VALID column).

Can anyone help me in understanding this please ?

Once I issue db2rbind, all packages will be VALID.

The access plan in the package was dependent on the primary key. This could
be because DB2 created a unique index on the primary key and the index was
used in the access path, or for some other reason.

When an invalid package is executed, DB2 does an automatic rebind so that
DB2 can determine a new access path. If the rebind fails (for example, if an
referenced table, view or column is missing) the package is marked as
inoperative.

You can avoid this by creating a unique index _prior_ to adding the primary
key. For example:

create table t1 (
c1 int not null,
c2 char(20)
);

create unique index t1_px
on t1 (c1);

alter table t1
add constraint pk_t1
primary key (c1);
The primary key will then use the index t1_px instead of automatically
creating and index for the primary key. This also has an advantage in
that it allows you to specify other options for this index, such as
allowing reverse scans or including other keys in the unique index.
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----