The notion of "application compatibility," in a Db2 for z/OS context, has been around for a while - it was introduced with Db2 11, which came out in 2013; still, in my experience it's been only recently that a number of Db2 for z/OS administrators have found themselves dealing with problems in this area. The incidents typically involve dynamic SQL statements. A person - maybe a Db2 DBA, maybe an application developer, maybe a user - issues a dynamic SQL statement via some tool (Db2's built-in SPUFI, the Db2-supplied DSNTEP2 program, a query tool such as IBM QMF, whatever) and execution of the statement fails, with Db2 returning a -4743 SQL error code: "ATTEMPT TO USE NEW FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL." Quite often, it's the first time that a Db2 DBA has encountered this error code, and he or she is left wondering, "Huh? What does THAT mean, and what am I supposed to do about it?" I'll try to provide some helpful information through this blog entry.
If the -4743 Db2 SQL error code has been around since 2013 (and it has), why is it that a number of people have encountered problems of this nature just within the last year or so? I'd say that's largely due to a couple of factors:
- Very helpful new SQL syntax and functionality introduced with Db2 12 for z/OS, with function level 500 or higher activated. Wait - hasn't Db2 12 itself been out for over four years? Yes, but there's often a lag between a new Db2 feature being introduced and people acting to take advantage of the new feature. Db2 12 delivered a number of important SQL-related enhancements - piece-wise DELETE, result set pagination and advanced triggers being just a few examples - and as people try these enhancements out they find that the appropriate application compatibility level needs to be in effect. Want to use the LISTAGG function, introduced with Db2 12 function level 501? Application compatibility level V12R1M501 (or higher) needs to be in effect.
- In a Db2 12 environment, application compatibility level affects DDL statements (e.g., CREATE and ALTER) as well as DML statements such as SELECT and DELETE. When the Db2 for z/OS concept of application compatibility level was introduced with Db2 11, it applied only to SQL DML statements (DML is short for data manipulation language); so, if you tried to execute, in a Db2 11 new-function mode environment, a query that referenced a Db2 global variable (something introduced with Db2 11), and the application compatibility level in effect were V10R1, the statement would fail with a -4743 SQL error code. If on the other hand you issued, in that same Db2 11 environment, a CREATE VARIABLE statement, with application compatibility level V10R1 again being in effect, that statement would execute successfully (assuming you had the Db2 privileges needed to create a global variable). Why would creating a global variable be OK in a Db2 11 environment with the application compatibility level being V10R1, while in the same Db2 environment with that same application compatibility level being in effect a query referencing a global variable would fail with a -4743 error code? The reason: CREATE VARIABLE is an SQL DDL statement, and SELECT is a DML statement, and as noted the application compatibility level affected execution of DML statements but not DDL statements in a Db2 11 system. In a Db2 12 system, the application compatibility level affects both DDL and DML statements.
CREATE LOB TABLESPACE XXXXXXXXIN YYYYYYYYUSING STOGROUP ZZZZZZZPRIQTY 5760 SECQTY 576ERASE NOLOGGEDDSSIZE 4GBUFFERPOOL BP32KLOCKSIZE ANYLOCKMAX 0CLOSE NOCOMPRESS NODEFINE YES;