IBM Support

JR36122: ILLEGAL CHARACTERS IN SUBSTR OR LIKE PREDICATES GENERATED FOR INFORMIX DATA SOURCE IN NON-UNICODE ENVIRONMENT CAUSING SQL1822N

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When query with SUBSTR or LIKE predicates are issued against
    Informix data source, Federation Server might return following
    error message:
    
    SQL1822N Unexpected error code "-202" received from data source
    "S80UATSOC". Associated text and tokens are "An illegal
    character has
    been found in the statement." SQLSTATE=560BD.
    
    The problem may happen when all of the following conditions are
    met:
    1. The query contains nicknames from Informix datasource
    2. The query has one or both of following predicates:
       1) predicate with SUBSTR in a style like: SUBSTR(C1, 1, 1) =
    '9'
       2) predicate with LIKE in a style like: C1 LIKE '9%', which
    has a '%'
    3. The federation database and remote Informix datasource are
    non-unicode databases, with the same collating sequence.
    
    The problem is, in the above scenarios, Federation Server will
    pad characters to the constants of the SUBSTR or LIKE predicate,
    then send them to remote Informix server, but these paded
    characters are considered illegal and cause error in Informix
    side.
    

Local fix

  • Setting collating_sequence server option to 'N' can block
    pushdown of predicates with strings, hence avoids the error.
    

Problem summary

  • Users affected:
       Users of the Informix wrapper in the DB2 for LUW Homogeneous
    Federation Feature or InfoSphere Federation Server
    Problem description and summary:
       See error description.
    

Problem conclusion

  • Problem was first fixed in Version 9.5 FixPak 6 (s100805 ). This
    fix should be applied on Federation Server.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR36122

  • Reported component name

    INFORMIX WRAPPE

  • Reported component ID

    5724N9710

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-03-31

  • Closed date

    2010-09-19

  • Last modified date

    2010-09-19

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

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

    JR36125 JR36126 IC67930

Fix information

  • Fixed component name

    INFORMIX WRAPPE

  • Fixed component ID

    5724N9710

Applicable component levels

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPK","label":"Data Sources and Wrappers - Informix"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.5","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
19 September 2010