IBM Support

PI37525: CONCURRENT UPDATE/CREATE FAILING ON SHARED ORACLE LA/DB IN VMM

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When parallel create/update are being made from multiple JVM,
    VMM intermittently throws
    com.ibm.websphere.ce.cm.DuplicateKeyException, and also found
    perfomance degradation...
    
    Oracle performance tool shows excessive queries on certain
    tables(Lookaside tables).. so locks cannot be acquired. Also
    duplicateKeyException is hit hundreds of times when VMMLA/DB
    is being accessed from various nodes/JVMs.
    
    This's  an extension to PM60212 and PM88331, to
    include/enhance queries to Oracle database.
    

Local fix

  • NA
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  All users of IBM WebSphere Application      *
    *                  Server                                      *
    ****************************************************************
    * PROBLEM DESCRIPTION: Oracle performance tooling found        *
    *                      issues when VMM LA/DB is being          *
    *                      accessed from various nodes/JVMs.       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Oracle database is configured in VMM as Lookaside/Database
    repository. Oracle performance tooling found performance
    degration when VMM LA/DB is being accessed from various nodes
    or JVMs simultaneously.
    

Problem conclusion

  • This fix is an extension of PM60212 and PM88331 which created
    this custom property set as JVM argument
    -Dcom.ibm.ws.wim.registry.DbSharedAcrossMultipleServers=true
    to work fine in shared DB/LA repository.
    This fix created custom property set as JVM argument
    -Dcom.ibm.ws.wim.registry.useTriggerForIdInLARepo=true to
    increment Value_id using trigger.
    
    The 2 custom properties which need to be set as generic JVM
    arguments are
    -Dcom.ibm.ws.wim.registry.DbSharedAcrossMultipleServers=true
    -Dcom.ibm.ws.wim.registry.useTriggerForIdInLARepo=true
    
    Please follow this link that tells about how to set generic JVM
    arguments >>
    http://www-01.ibm.com/support/docview.wss?uid=swg21417365
    
    And also you need to set triggers on the Oracle database for
    VMM LA tables. Run the following SQL commands:
    
    1. LASTRPROP
    select coalesce(max(value_id),0) from LASTRPROP;
    
    create sequence lastrprop_seq start with <output of above
    query>+1;
    
    create or replace trigger lastrprop_trg
        before insert on lastrprop
        for each row
        begin
        select lastrprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    For eg.,
    SQL> select coalesce(max(value_id),0) from LASTRPROP;
    
    COALESCE(MAX(VALUE_ID),0)
    -------------------------
                        10357
    
    
    SQL> create sequence lastrprop_seq start with 10358;
    
    Sequence created.
    
    2. LALONGPROP
    select coalesce(max(value_id),0) from LALONGPROP;
    
    create sequence lalongprop_seq start with <output of above
    query>+1;
    
    create or replace trigger lalongprop_trg
        before insert on lalongprop
        for each row
        begin
        select lalongprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    3. LADBLPROP
    select coalesce(max(value_id),0) from LADBLPROP;
    
    create sequence ladblprop_seq start with <output of above
    query>+1;
    
    create or replace trigger ladblprop_trg
        before insert on ladblprop
        for each row
        begin
        select ladblprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    4. LAINTPROP
    select coalesce(max(value_id),0) from LAINTPROP;
    
    create sequence laintprop_seq start with <output of above
    query>+1;
    
    create or replace trigger laintprop_trg
        before insert on laintprop
        for each row
        begin
        select laintprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    
    5. LAREFPROP
    select coalesce(max(value_id),0) from LAREFPROP;
    
    create sequence larefprop_seq start with <output of above
    query>+1;
    
    create or replace trigger larefprop_trg
        before insert on larefprop
        for each row
        begin
        select larefprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    6. LATSPROP
    select coalesce(max(value_id),0) from LATSPROP;
    
    create sequence latsprop_seq start with <output of above
    query>+1;
    
    create or replace trigger latsprop_trg
        before insert on latsprop
        for each row
        begin
        select latsprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    7. LABLOBPROP
    select coalesce(max(value_id),0) from LABLOBPROP;
    
    create sequence lablobprop_seq start with <output of above
    query>+1;
    
    create or replace trigger lablobprop_trg
        before insert on lablobprop
        for each row
        begin
        select lablobprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    8. LACOMPPROP
    select coalesce(max(value_id),0) from LACOMPPROP;
    
    create sequence lacompprop_seq start with <output of above
    query>+1;
    
    create or replace trigger lacompprop_trg
        before insert on lacompprop
        for each row
        begin
        select lacompprop_seq.nextval
        into :new.value_id
        from dual;
        end;
        /
    
    The fix for this APAR is currently targeted for inclusion in
    fix pack 8.0.0.12 and 8.5.5.7.  Please refer to the
    Recommended Updates page for delivery information:
    http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI37525

  • Reported component name

    WEBSPHERE APP S

  • Reported component ID

    5724J0800

  • Reported release

    800

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-03-22

  • Closed date

    2015-06-19

  • Last modified date

    2015-07-23

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

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

Fix information

  • Fixed component name

    WEBSPHERE APP S

  • Fixed component ID

    5724J0800

Applicable component levels

  • R800 PSY

       UP

  • R850 PSY

       UP

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
27 April 2022