IBM Support

JR30721: USER DEFINED SQL SELECTING EMPTY STRING FAILS WITH ORACLE ENTERPRISE STAGE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Using the Oracle Enterprise stage with user defined SQL that
    includes an empty string literal (meaning '') will result in an
    error messages at runtime, and the job will dump core. Example
    messages returned to the DataStage job log:
    
    APT_ParseError: In field "CLIENT_SYSTEM_CODE": Parsing
    parameters "0" for schema type "string": Length must be
    positive, got: "0"
    In field "CLIENT_SYSTEM_ID": Parsing parameters "0" for schema
    type "string": Length must be positive, got: "0"
    Aborted (core dumped)
    
    Here is an example of the user defined SQL statement that causes
    this problem:
    
    SELECT DISTINCT '' AS CLIENT_SYSTEM_CODE, '' AS
    CLIENT_SYSTEM_ID, ADDRESS_P_ID, ADDRFLD, AREAFLD
    FROM CIMS_STAN_ADDRESSES_P WHERE ATUSADD = 'S'
    

Local fix

  • If the job design permits it, you can select a different literal
    value with a non-zero length such as ' ' (a single space) and
    then trim it within the job design.
    

Problem summary

  • ****************************************************************
    USERS AFFECTED:
    all
    ****************************************************************
    PROBLEM DESCRIPTION:
    Using the Oracle Enterprise stage with user defined SQL that
    includes an empty string literal (meaning '') will result in an
    error messages at runtime, and the job will dump core. Example
    messages returned to the DataStage job log:
    
    APT_ParseError: In field "CLIENT_SYSTEM_CODE": Parsing
    
    parameters "0" for schema type "string": Length must be
    
    positive, got: "0"
    In field "CLIENT_SYSTEM_ID": Parsing parameters "0" for
    schema
    ****************************************************************
    RECOMMENDATION:
    apply patch as provided.
    ****************************************************************
    

Problem conclusion

  • Empty literal string as a select list item has a string schema
    type with length 0!
    As Orchestrate demands a minimum length of 1, we get ugly
    messages.
    This is corected easlly by coercing the empty string to a length
    of 1.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR30721

  • Reported component name

    WIS DATASTAGE

  • Reported component ID

    5724Q36DS

  • Reported release

    801

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-10-08

  • Closed date

    2009-04-15

  • Last modified date

    2009-04-15

  • 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

    WIS DATASTAGE

  • Fixed component ID

    5724Q36DS

Applicable component levels

  • R801 PSN

       UP

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 April 2009