IBM Support

IT17179: IF ARRAY USED IN AN OPEN CURSOR IS MODIFIED THEN WRONG RESULT ORA TRAP ARE POSSIBLE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • When ARRAY is used in an open cursor , but is modififed as we
    are fetching from this cursor, then wrong results or trap are
    possible.
    
    Example #1:
    V_TARGET_TABLE arrayType;--
    V_TARGET_TABLE[1] := 'value1';--
    CURSOR cur1 IS SELECT c1 FROM names WHERE c1 LIKE
    (V_TARGET_TABLE(INDEX));--
    [..]
    OPEN cur1;--
    V_TARGET_TABLE[1] := 'value2';--
    FETCH cur1 into temp_vc;--
    CLOSE cur1;--
    
    Please, note that above we opened cursor when V_TARGET_TABLE[1]
    had one value, then we changed it to a new value, before we
    started the fetch from the cursor.
    Currently we do not support this and the fix will prevent above
    from running and will return an error (SQL0901N).
    Please, modify your application to make sure that array used in
    the cursor we are fetching from is not changed until we are done
    with the cursor and closed it.
    
    Example #2:
    Another example of unsupported usage that can produce a trap ,
    but with the fix , it will return an SQL0901N:
    
            FOR COUNTER IN 1 .. V_E LOOP
              FOR CURSORX IN (SELECT COL1
                          FROM MY_TABLE
                          WHERE COL2 LIKE  '%[' || V_ARRAY(COUNTER)
    ||']%')
                          LOOP
                V_ARRAY(V_ARRAY.LAST) := CURSORX.COL1;--
              END LOOP;--
            END LOOP;--
    ...
    
    Trap Stack will be:
    sqloCrashOnCriticalMemoryValidationFailure
    sqlofmblkEx
    sqlriArrayDescriptor7destroy
    sqlricls_complex
    sqlrr_process_close_request
    sqlrr_close
    csmDriveClose
    csmClose
    pvmPackage11closeCursor
    
    Please, change your application to use local variable in the
    cursor:
    
            FOR COUNTER IN 1 .. V_E LOOP
              temp_value = V_ARRAY(COUNTER);--
              FOR CURSORX IN (SELECT COL1
                          FROM MY_TABLE
                          WHERE COL2 LIKE  '%[' || temp_value
    ||']%')
                          LOOP
                V_ARRAY(V_ARRAY.LAST) := CURSORX.COL1;--
              END LOOP;--
            END LOOP;--
    
    This change will prevent both a trap and an SQL0901N.
    

Local fix

  • Change the logic to avoid reusing updated array in the cursor as
    we are fetching. Please, see Description for examples.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to v11.1 m1fp1                                       *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IT17179

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-09-22

  • Closed date

    2017-10-11

  • Last modified date

    2017-10-11

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

    IT15618

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

Fix information

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020