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

DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

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 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

• RB10 PSN

     UP

• RB10 PSY

     UP