IBM Support

IT22962: CREATED FEDERARED PROCEDURE FOR A SP WITH NO ARGUMENTS IN ORACLEDATABASE MAY FAIL WITH -901

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • Create federated store procedure for a SP with no arguments in
    Oracle database may fail with -901:
    
        2017-09-20-13.16.07.188754+480 E66394A1654          LEVEL:
    Info (Origin)
        PID     : 16711756             TID : 3600           PROC :
    db2sysc 0
        INSTANCE: db2inst1             NODE : 000           DB   :
    SAMPLE
        APPHDL  : 0-8                  APPID:
    *LOCAL.db2inst1.170920051147
        AUTHID  : DB2INST1             HOSTNAME: myhost
        EDUID   : 3600                 EDUNAME: db2agent (SAMPLE) 0
        FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl,
    probe:650
        MESSAGE : ZRC=0x80260160=-2144992928=SQLQG_ERROR "Error
    constant for gateway."
        DATA #1 : String, 62 bytes
        An unexpected error was detected during statement
    compilation.
        DATA #2 : Boolean, 1 bytes
        false
        DATA #3 : Boolean, 1 bytes
        false
        DATA #4 : Boolean, 1 bytes
        true
        DATA #5 : Boolean, 1 bytes
        false
        DATA #6 : Hex integer, 4 bytes
        0x00000000
        DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
         sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901
    sqlerrml: 0
         sqlerrmc:
         sqlerrp : SQLNN067
         sqlerrd : (1) 0x80260160      (2) 0x00000160      (3)
    0x00000000
                   (4) 0x00000000      (5) 0xFFFFFDA8      (6)
    0x00000000
         sqlwarn : (1)      (2)      (3)      (4)        (5)
    (6)
                   (7)      (8)      (9)      (10)        (11)
         sqlstate:
        DATA #8 : Hex integer, 4 bytes
        0x00000040
        DATA #9 : String with size, 165 bytes
        CREATE OR REPLACE PROCEDURE <skipped> ...
        DATA #10: String, 171 bytes
        Compiler error stack for rc = -2144992928:
        sqlnn_cmpl[300]
        sqlnp_main[250]
        sqlnp_parser[510]
        sqlnp_smactn[100]
        sqlnq_create_fedproc_stmt[40]
        sqlnq_create_fedproc_one[130]
    
    The root cause is that Federation Server uses a SQL against
    sys.all_arguments incorrectly to verify if a particular
    procedure exists - after bugfix 14675058 of Oracle, the SQL
    always returns empty result indicating the SP doesn't exist.
    Oracle changed this behavior starting from 12.1.0.2 release and
    this change doesn't affect procedures without package, so this
    problem only happens with data source Oracle 12.1.0.2 or later,
    and this problem only happens when the procedure in the source
    database is within a package.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * The user is running oracle wrapper.                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 V10.5 FP10 or later.                          *
    ****************************************************************
    

Problem conclusion

  • The problem is firstly fixed on v10.5 FP10
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT22962

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-10-29

  • Closed date

    2018-07-11

  • Last modified date

    2018-07-11

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

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

    IT22979

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
11 July 2018