Application compatibility levels in Db2 12

The application compatibility level of your applications controls the adoption and use of new capabilities and enhancements, and sometimes reduces the impact of incompatible changes. The advantage is that you can complete the Db2 12 migration process without the need to update your applications immediately.

Start of changeAfter function level 500 or higher is activated, you can continue run applications with the features and behavior of previous versions or specific Db2 12 function levels. End of change

Start of changeYou can change the application compatibility level for each application when you are ready for it to run with the features and behavior of a higher Db2 version or function level. The application compatibility level applies to most SQL statements, including data definition statements (such as CREATE and ALTER statements) and data control statements (such as GRANT and REVOKE statements).End of change

The application compatibility of a package is initially set when you bind a package, based on the following values:

  1. The APPLCOMPAT bind option value, if specified.
  2. If the bind option is omitted, the APPLCOMPAT subsystem parameter.

For static SQL statements, the APPLCOMPAT column of the SYSIBM.SYSPACKAGE catalog table stores the application compatibility setting. This setting changes for the following reasons:

  • You issue a REBIND command for the package and specify a different value for the APPLCOMPAT option. If you omit this option, the previous value for the package is used. If no previous value is available (such as for packages last bound before the introduction of application compatibility) the APPLCOMPAT subsystem parameter value is used.
  • An automatic bind of the package occurs. The application compatibility is set to the previous value. If no previous value is available, the APPLCOMPAT subsystem parameter value is used.

For dynamic SQL statements, the CURRENT APPLICATION COMPATIBILITY special register stores the application compatibility setting. This setting changes for the following reasons:

  • The special register is initialized to the application compatibility of the package, as described above.
  • During execution of the package, SET CURRENT APPLICATION COMPATIBILITY statements can change the special register. The value must be equivalent to the APPLCOMPAT bind option for the package or lower, if the value is V12R1M500 or above.

Start of changeFor new installations, the default APPLCOMPAT subsystem parameter value is V12R1M500. However, you can specify a higher value. For migrated environments, the default value is the value from the migration input member.End of change

Tip: Start of changeWhen you migrate to Db2 12, or activate any higher function level, change the APPLCOMPAT subsystem parameter value only after all applications can use the SQL capabilities of Db2 12 or the higher function level. For details, see Enabling default application compatibility with function level 500 or higher.End of change

Supported application compatibility levels in Db2 12

Db2 12 supports the following application compatibility levels in most contexts.

Tip: Start of changeFor best results, configure your development environment to use the lowest application compatibility level that the application will run at in the production environment. For dynamic SQL, remember to consider the application compatibility levels of client and NULLID packages. If you develop and test applications at a higher application compatibility level and try to run them at a lower level in production, you are likely to encounter SQL code -4743 and other errors when you deploy the applications to production.End of change
Start of changeVvvRrMmmmEnd of change
Start of change

Compatibility with the behavior of the identified Db2 function level. For example, V12R1M510 specifies compatibility with the highest available Db2 12 function level. The equivalent function level or higher must be activated.

Start of changeFor the new capabilities that become available in each application compatibility level, see: End of change

Tip: Start of changeExtra program preparation steps might be required to increase the application compatibility level for applications that use data server clients or drivers to access Db2 for z/OS. For more information, see Setting application compatibility levels for data server clients and drivers.End of change
End of change
Start of changeV12R1End of change
Start of changeCompatibility with the behavior of Db2 12 function level 500. This value has the same result as specifying V12R1M500.End of change
V11R1
Compatibility with the behavior of Db2 11 new-function mode. After migration to Db2 12, this value has the same result as specifying V12R1M100. For more information, see V11R1 application compatibility level
V10R1
Compatibility with the behavior of DB2 10 new-function mode. For more information, see V10R1 application compatibility level.

Example: V10R1 application compatibility

Start of changeThe following example shows the results of using a capability that is introduced in the application compatibility level V11R1, with application compatibility level set to V10R1. Assume that the APPLCOMPAT subsystem parameter value is V10R1. The example CREATE PROCEDURE statement does not specify the APPLCOMPAT keyword. In this example the CREATE TYPE statement is successful but the CREATE PROCEDURE statement results in SQL code -4743.End of change

CREATE TYPE PHONENUMBERS AS VARCHAR(12) ARRAY ??(1000000??)  
DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION
CREATE PROCEDURE FIND_CUSTOMERS(                                     
  IN NUMBERS_IN KRAMSC01.PHONENUMBERS,                               
  IN AREA_CODE CHAR(3),                                              
  OUT NUMBERS_OUT KRAMSC01.PHONENUMBERS)                             
    BEGIN                                                            
      SET NUMBERS_OUT =                                              
        (SELECT ARRAY_AGG(T.NUM)                                     
        FROM UNNEST(NUMBERS_IN) AS T(NUM)                            
        WHERE SUBSTR(T.NUM, 1, 3) = AREA_CODE);                      
    END 
DSNT408I SQLCODE = -4743, ERROR:  ATTEMPT TO USE A FUNCTION WHEN THE 
   APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL

Start of changeThe APPLCOMPAT bind option value for the CREATE PROCEDURE statement is then set to V11R1 or higher and result of the statement is then successful.End of change

CREATE PROCEDURE FIND_CUSTOMERS(                                     
  IN NUMBERS_IN KRAMSC01.PHONENUMBERS,                               
  IN AREA_CODE CHAR(3),                                              
  OUT NUMBERS_OUT KRAMSC01.PHONENUMBERS)     
  APPLCOMPAT V11R1                        
    BEGIN                                                            
      SET NUMBERS_OUT =                                              
        (SELECT ARRAY_AGG(T.NUM)                                     
        FROM UNNEST(NUMBERS_IN) AS T(NUM)                            
        WHERE SUBSTR(T.NUM, 1, 3) = AREA_CODE);                      
    END 
DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION