IBM Support

PI05956: Provide an option to disable running of 'ALTER SEQUENCE ... INCREMENT BY' statement for sequences

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The connect user in websphere doesn't have the permission to
    run the statement, hence the statement fails with:
    CWWJP9991W: openjpa.Runtime: Warn: Unable to cache sequence
    values for  sequence "OID_SEQ". Your application does not have
    permission to run an  ALTER SEQUENCE command. Ensure that it
    has the appropriate permission  to run an ALTER SEQUENCE
    command.
    
    Customer asking for some switch / property to just switch off
    the unconditional ALTER SEQUENCE run
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  All users of IBM WebSphere Application      *
    *                  Server V8.5.0 and V8.5.5 who make           *
    *                  use of a JPA Sequence where the user does   *
    *                  not have permission to run an 'ALTER        *
    *                  SEQUENCE' for the Sequence.                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: Option to disable running of 'ALTER     *
    *                      SEQUENCE...INCREMENT BY' statement      *
    *                      for sequences.                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    OpenJPA attempts to run an 'ALTER SEQUENCE....INCREMENT BY'
    SQL statement for a user defined sequence. This is done to
    ensure that the 'allocationSize' value defined by the entity's
    sequence, or default value, matches the sequence defined in
    the database. For example, with an allocationSize of 1000 for
    a sequence named 'SEQ_JPASAMPLE', the following SQL will be
    generated (the SQL might vary slightly depending on the
    databases):
    ALTER SEQUENCE SEQ_JPASAMPLE INCREMENT BY 1000
    If the user running this command doesn't have permissions to
    run the command, it will fail and in turn OpenJPA will
    disable sequence caching. Users have asked for a way to
    disable this SQL statement and have full control over the
    sequence they define in their domain model and its
    corresponding definition in the database.
    A property, to be described below, has been added to disable
    this SQL statement. However, the onus is then on the user to
    ensure that they keep in sync the 'allocationSize' in their
    entity's sequence definition with the 'INCREMENT BY' for the
    corresponding sequence in the database.
    

Problem conclusion

  • With this fix, code has been added to OpenJPA to allow a user
    to disable the 'ALTER SEQUENCE...INCREMENT BY'.  To enable
    this code and disable the SQL, a new property has been added
    to DBDictionary named 'disableAlterSeqenceIncrementBy', with a
    default of false.  To set this property to true, set the
    property as follows:
    
    openjpa.jdbc.DBDictionary="disableAlterSequenceIncrementBy=true"
    
    The fix for this APAR is currently targeted for
    inclusion in Service Level (Fix Pack) 8.5.5.2 of
    WebSphere Application Server version 8.5.5.
    
    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

    PI05956

  • Reported component name

    WEBS APP SERV N

  • Reported component ID

    5724H8800

  • Reported release

    850

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2013-11-12

  • Closed date

    2014-02-13

  • Last modified date

    2015-08-07

  • 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

    WEBS APP SERV N

  • Fixed component ID

    5724H8800

Applicable component levels

  • 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.5","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
28 April 2022