Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Unanswered: HOW to create function index

    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

  2. #2
    Join Date
    Apr 2004
    Posts
    54
    DB2 does not support function based indexes.
    You have to create field "GENERATED ALWAYS AS " and then create an index based on the one.

  3. #3
    Join Date
    Dec 2005
    Posts
    8
    Can you give me more details?

  4. #4
    Join Date
    Apr 2004
    Posts
    54
    > 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@

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •