Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC74206 Status: Closed

DB2 INSTANCE MAY ABEND IF QUERY HAS FACT TABLE JOINING DIMENSION TABLE ON
PRIMARY FOREIGN KEY AND FACT TABLE HAS UNIQUE COLUMN.

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
An example that can hit the problem: 
-- ddl 
create table dim (pk int not null primary key, uk int); 
create unique index dimuk on dim(uk); 
create table fact (fk int, uk varchar(4)); 
create unique index factuk on fact(uk); 
ALTER TABLE fact ADD CONSTRAINT ri FOREIGN KEY (fk) REFERENCES 
dim (pk); 
 
-- query 
select fact.uk from fact, dim where fk=pk and fact.uk = 'abc' 
order by dim.uk; 
 
Necessary conditions: 
1. There is primary key table and foreign key table. Query has 
join predicate between primary key and foreign key. 
2. Primary key table has unique key, and the unique key is 
present in query ORDER BY clause. 
3. Foreign key table also has unique key. The unique key column 
type is varchar or vargraphic, and it is involved in a local 
equality predicate. 
4. Query select list has no column from primary key table.
Problem Summary:
An example that can hit the problem: 
-- ddl 
create table dim (pk int not null primary key, uk int); 
create unique index dimuk on dim(uk); 
create table fact (fk int, uk varchar(4)); 
create unique index factuk on fact(uk); 
ALTER TABLE fact ADD CONSTRAINT ri FOREIGN KEY (fk) REFERENCES 
dim (pk); 
 
-- query 
select fact.uk from fact, dim where fk=pk and fact.uk = 'abc' 
order by dim.uk; 
 
Necessary conditions: 
1. There is primary key table and foreign key table. Query has 
join predicate between primary key and foreign key. 
2. Primary key table has unique key, and the unique key is 
present in query ORDER BY clause. 
3. Foreign key table also has unique key. The unique key column 
type is varchar or vargraphic, and it is involved in a local 
equality predicate. 
4. Query select list has no column from primary key table.
Local Fix:
Remove order by clause, because query returns at most one row.
available fix packs:
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Solution
First fixed in DB2 Version 9.7 Fixpack 5.
Workaround
Remove order by clause, because query returns at most one row.
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
28.01.2011
07.12.2011
07.12.2011
Problem solved at the following versions (IBM BugInfos)
9.7.FP5
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.5 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.