IBM Support

IT02511: READEVENTS IN DATABASEINPUT NODE FAILS IN ORACLE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The ESQL code generated by the "Generate Query" button of
    DatabaseInput node does not enclose table column names that are
    in lowercase with appropriate quotation marks. This causes the
    SQL SELECT for ReadEvents to fail because Oracle automatically
    converts unprotected names to uppercase and there is now a
    mismatch with the names in the table.
    

Local fix

  • A solution for the problem is to manually edit two blocks of
    auto generated ESQL source code. The first block of
    ESQL source code is for "ReadEvents". Edit that code to protect
    each lower case column name with 3 quotation marks.
    For instance, change:
    
    SET NewEvents.Event[] = SELECT EVENTTABLE.event_id AS Key,
    EVENTTABLE.event_id AS Usr.event_id,
    EVENTTABLE.event_status AS Usr.event_status,
    ...
    FROM Database.schema1.EVENTTABLE
    WHERE EVENTTABLE.event_status = 0;
    
    to become:
    
    SET NewEvents.Event[] = SELECT EVENTTABLE."""event_id""" AS Key,
    EVENTTABLE."""event_id""" AS Usr.event_id,
    EVENTTABLE."""event_status""" AS Usr.event_status,
    ...
    FROM Database.schema1.EVENTTABLE
    WHERE EVENTTABLE."""event_status""" = 0;
    
    Also change the "EndEvent" code from:
    
    UPDATE Database.schema1.EVENTTABLE
    SET event_status = 1
    WHERE EVENTTABLE.event_id = DispatchedEvent.Usr.event_id;
    
    to:
    
    UPDATE Database.schema1.EVENTTABLE
    SET """event_status""" = 1
    WHERE EVENTTABLE."""event_id""" = DispatchedEvent.Usr.event_id;
    .
    Be aware that if you are generate the query again for the
    DatabaseInput node after the modification, then the auto
    generated ESQL will be refreshed and the quotation marks that
    you added manually will be lost.
    

Problem summary

  • ****************************************************************
    USERS AFFECTED:
    All users of IBM Integration Toolkit V9.0 who have lowercase
    character table column names in Oracle, and are using the
    DatabaseInput node to generate ESQL code.
    
    
    Platforms affected:
    MultiPlatform
    
    ****************************************************************
    PROBLEM DESCRIPTION:
    The ESQL code generated in the DatabaseInput node does not
    enclose table column names that are
    in lowercase with appropriate quotation marks, and this causes
    the SQL SELECT for ReadEvents to fail because Oracle
    automatically converts unprotected names to uppercase which
    leads to a mismatch with the names in the table.
    

Problem conclusion

  • The product now generates quotation marks in the ESQL code for
    table column names with lowercase characters for the
    DatabaseInput node.
    
    ---------------------------------------------------------------
    The fix is targeted for delivery in the following PTFs:
    
    Version    Maintenance Level
    v9.0       9.0.0.3
    
    The latest available maintenance can be obtained from:
    'Recommended fixes for WebSphere Message Broker Toolkit'
    http://www-1.ibm.com/support/docview.wss?rs=849&uid=swg27009742
    
    If the maintenance level is not yet available, information on
    its planned availability can be found in 'WebSphere Message
    Broker Planned Maintenance Release Dates'
    http://www-1.ibm.com/support/docview.wss?rs=849&uid=swg27006308
    ---------------------------------------------------------------
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT02511

  • Reported component name

    IB TOOLKIT

  • Reported component ID

    5724J0531

  • Reported release

    900

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-06-12

  • Closed date

    2014-07-30

  • Last modified date

    2014-07-30

  • 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

    IB TOOLKIT

  • Fixed component ID

    5724J0531

Applicable component levels

  • R900 PSY

       UP



Document information

More support for: IBM Integration Bus

Software version: 9.0

Reference #: IT02511

Modified date: 30 July 2014