Hi,
everyone
I create a function as follows :


drop function convid!
create function convid(id varchar(25))
returns char(15)
begin atomic
return
case
when length(ltrim(rtrim(id)))=18 then substr(id,1,6)||substr(id,9,9)
when length(ltrim(rtrim(id)))=15 then id
else null
end;

end!

The function convid will be used in many place ,for example
update bd_psndoc set ts=? where (psnclscope=0 or psnclscope=1) and pk_psndoc in (select pk_psndoc from bd_accpsndoc where convid(id)=?)

I found the perfomance is not well,so I want to create function index for convid via index extender,can i achiveve?
Any one help me

DB2 does not support function based indexes.
You have to create field "GENERATED ALWAYS AS " and then create an index based on the one.

Can you give me more details?

> Can you give me more details?

create table ex (
id int not null,
name char(30),
dt date,
-- here is a generated field
yearquarter char(6) generated always as (
substr(char(dt),1,4)||
case
when (month(dt)<4) then '.1'
when (month(dt)<7) then '.2'
when (month(dt)<10) then '.3'
else '.4'
end
),
constraint pk primary key(id)
)@

create unique index iquarter on ex (yearquarter,id) allow reverse scans@
rollback@