This was exposed during S1 CRM for customer "Geico Insurance".
The customer was upgrading from v97fp6 to v101fp2 and keep on
getting engine trap in upgrade code. The upgrade code was trying
to construct runstats profile command checking for NULLability
of statistics_profile column. But in reality , the runstats
profile is deleted via view regeneration code. Simple repro
script to see this behavior :
In v97 ,
create db test
connect to test
create table t(c1 int, c2 int, c3 int)
insert into t values(1,1,1)
create view v as select * from t
alter view v enable query optimization
runstats on table samadesa.v WITH DISTRIBUTION ON COLUMNS(c1,c2)
set profile
select name , creator, substr(statistics_profile,1,50) from
sysibm.systables where name ='V'
alter view v disable query optimization
alter table t drop column c2
select name , creator, substr(statistics_profile,1,50) from
sysibm.systables where name ='V'
connect reset
db2cat -d test -p table -n V -s SAMADESA -t|egrep "^Table:|^
Stats Profile Offset"|gnugrep -B1 ": 0"
hotel85:/home/hotel85/samadesa> |^ Stats Profile Offset"|gnugrep
-B1 ": 0" <
DB2 Version 9.7, 5622-044 (c) Copyright IBM Corp. 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Catalog Analysis and Repair Tool
Please use single quote for delimited name
Connecting to the Database.
Connect to Database Successful.
Bind was Successful.
Table: SAMADESA.V (VIEW)
Stats Profile Offset : 0
Now upgrade to v10 and you will get a trap in the same function
while constructing runstats command. |
