IBM Support

LI74133: IN THE DB2SUPPORT OPTIMIZER TOOL '-RA | -REFRESHAGE' (-RA ANY) IS IGNORED.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In the db2support optimizer tool '-ra | -refreshage' (-ra ANY)
    is ignored.
    
    The following will be seen in the exfmt_badquery.opt_put.
    
    In the exfmt_badquery.opt_out the following is seen:
    EXP0054W  The following REFRESH DEFERRED MQT was
    not considered for rewrite matching because the
    CURRENT REFRESH AGE register was not set to ANY:
    "V9INST5 "."MQT".
    
    
    Example:
    
    STEP 1)
    drop db REFRESH;
    create db REFRESH;
    connect to REFRESH;
    set schema v9inst5;
    create table t1(i1 int not null primary key, i2 int);
    insert into t1 with v(i1, i2) as
    ( values (1,1)
      union all
       select i1+1, mod(i2+1,10) from v where i1<1000
    )
    select * from v;
    select count(*) from t1;
    
    create table mqt as (select i1,i2 from t1 where i2 = 1)
    data initially deferred refresh deferred enable query
    optimization
    maintained by system compress yes  not logged initially;
    refresh table mqt;
    create index ix2_mqt on mqt(i2);
    runstats on table v9inst5.t1 with distribution and index all;
    runstats on table v9inst5.mqt with distribution and index all;
    
    
    STEP 2)
    Run the following db2support command:
    
    db2support . -d refresh -sf sel.sql -cl 1 -ra ANY
    
    <sel.sql> contents:
    select count(*) from v9inst5.t1 where i2=1;
    
    Grep for exp0054 in files from OPTIMIZER the below result will
    be returned.
    $ grep -i exp0054 *
    exfmt_badquery.opt_out:Diagnostic Details:      EXP0054W  The
    following
    REFRESH DEFERRED MQT was
    

Local fix

  • In the <sel.sql> file add the syntax 'set current refresh age
    ANY;'
    E.G.
    set current refresh age ANY;
    select count(*) from v9inst5.t1 where i2=1
    
    Then do NOT include the -ra ANY in the db2support syntax.
    E.G.
    db2support . -d refresh -sf sel.sql -cl 1
    

Problem summary

  • IN THE DB2SUPPORT OPTIMIZER TOOL '-RA | -REFRESHAGE' (-RA ANY)
    IS IGNORED.
    

Problem conclusion

  • In the db2support optimizer tool '-ra | -refreshage' (-ra ANY)
    is ignored.
    
    The following will be seen in the exfmt_badquery.opt_put.
    
    In the exfmt_badquery.opt_out the following is seen:
    EXP0054W  The following REFRESH DEFERRED MQT was
    not considered for rewrite matching because the
    CURRENT REFRESH AGE register was not set to ANY:
    "V9INST5 "."MQT".
    
    
    Example:
    
    STEP 1)
    drop db REFRESH;
    create db REFRESH;
    connect to REFRESH;
    set schema v9inst5;
    create table t1(i1 int not null primary key, i2 int);
    insert into t1 with v(i1, i2) as
    ( values (1,1)
      union all
       select i1+1, mod(i2+1,10) from v where i1<1000
    )
    select * from v;
    select count(*) from t1;
    
    create table mqt as (select i1,i2 from t1 where i2 = 1)
    data initially deferred refresh deferred enable query
    optimization
    maintained by system compress yes  not logged initially;
    refresh table mqt;
    create index ix2_mqt on mqt(i2);
    runstats on table v9inst5.t1 with distribution and index all;
    runstats on table v9inst5.mqt with distribution and index all;
    
    
    STEP 2)
    Run the following db2support command:
    
    db2support . -d refresh -sf sel.sql -cl 1 -ra ANY
    
    <sel.sql> contents:
    select count(*) from v9inst5.t1 where i2=1;
    
    Grep for exp0054 in files from OPTIMIZER the below result will
    be returned.
    $ grep -i exp0054 *
    exfmt_badquery.opt_out:Diagnostic Details:      EXP0054W  The
    following
    REFRESH DEFERRED MQT was
    

Temporary fix

  • In the <sel.sql> file add the syntax 'set current refresh age
    ANY;'
    E.G.
    set current refresh age ANY;
    select count(*) from v9inst5.t1 where i2=1
    
    Then do NOT include the -ra ANY in the db2support syntax.
    E.G.
    db2support . -d refresh -sf sel.sql -cl 1
    

Comments

APAR Information

  • APAR number

    LI74133

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-01-12

  • Closed date

    2009-05-28

  • Last modified date

    2009-05-28

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    LI74134

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
28 May 2009