Db2 for z/OS: Dealing With Application Compatibility Errors (-4743 SQL Error Code)

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.

Why now?

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.

Examples of -4743 error situations in a Db2 12 environment

A few weeks back, a Db2 for z/OS DBA, working in a Db2 12 environment with function level 500 activated, contacted me to see why the following rather straightforward-looking CREATE LOB TABLESPACE statement was failing with - yep - a -4743 error code (I have masked the actual names of the table space, database, etc.):

CREATE LOB TABLESPACE XXXXXXXX

IN YYYYYYYY

USING STOGROUP ZZZZZZZ

PRIQTY 5760 SECQTY 576

ERASE NO

LOGGED

DSSIZE 4G

BUFFERPOOL BP32K

LOCKSIZE ANY

LOCKMAX 0

CLOSE NO

COMPRESS NO

DEFINE YES;

"What application or tool did you use to issue the CREATE statement?" I asked. The DBA's response: "SPUFI." "OK," I said, "Your SPUFI packages probably have an APPLCOMPAT value of V10R1 or V11R1. Rebind them with APPLCOMPAT(V12R1M500)." The DBA rebound the SPUFI packages with the suggested APPLCOMPAT value, and the CREATE LOB TABLESPACE statement worked just fine.

What messed things up initially? The COMPRESS NO specification. Why? Because the ability to compress a LOB table space (done using a mainframe compression feature called zEDC) was introduced with Db2 12 with function level 500 activated. "So what?" you might say. "The DBA didn't specify COMPRESS YES for the LOB table space. He specified COMPRESS NO. He wasn't requesting compression for the LOB table space." True, but you can't include a COMPRESS specification - YES or NO - for a LOB table space until function level 500 has been activated for your Db2 12 system. That means application compatibility level V12R1M500 (or higher) needs to be in effect when you issue the CREATE LOB TABLESPACE statement with a COMPRESS specification, and THAT'S where APPLCOMPAT comes in. I've repeatedly referred to a Db2 application compatibility level being "in effect." What determines the application compatibility level that is in effect when a SQL statement is issued? It's the APPLCOMPAT value of the package associated with the SQL statement; and, there is ALWAYS a package associated with a Db2 SQL statement, whether the statement is static or dynamic, DML or DDL or whatever. If the SQL statement is dynamic, the package might be a SPUFI package (as in this case), or a DSNTEP2 package, or maybe an IBM Data Server Driver / Db2 Connect package, if the SQL statement is issued by a DRDA requester application or tool.

OK, example number 2. A Db2 for z/OS team at an organization here in the USA had recently activated function level 500 for a Db2 12 system, and they were eager to try out the way-improved MERGE statement enabled via that function level. They gave it a shot, and the statement failed with a -4743 SQL error code. In a meeting with the team, I asked what tool or application had been used to issue the MERGE statement. They told me: a workstation-based tool that used IBM's JDBC driver to access the target Db2 for z/OS system. Probably an issue with the APPLCOMPAT value of the IBM Data Server Driver packages, I explained; but, these folks were ahead of me. "We already rebound all of those packages with APPLCOMPAT(V12R1M500)." Oh. Still, Db2 was telling us via the -4743 that the application compatibility level in effect when the MERGE statement was issued was not what it needed to be. I requested that someone use the workstation-based tool to issue this statement:

SELECT CURRENT APPLICATION COMPATIBILITY

FROM SYSIBM.SYSDUMMY1;

Here's what the query returned:

V10R1

Aha! OK, some explanation: CURRENT APPLICATION COMPATIBILITY is one of the Db2 special registers. Where does its value come from? Well, the special register's initial value for a Db2 session comes from the APPLCOMPAT value of the package being used. The V10R1 returned by the query meant that the workstation-based tool was using a package bound with an APPLCOMPAT value of V10R1. Using that piece of information, and knowing the names of the IBM Data Server Driver packages, one of the DBAs did a quick query of the Db2 system's SYSPACKAGE catalog table, selecting packages having an APPLCOMPAT value of V10R1. Sure enough, there was a set of the IBM Data Server Driver packages bound with APPLCOMPAT(V10R1) in a collection other than the default (for those packages) NULLID collection, and the workstation-based tool was using a package in that alternate collection (these days, it is increasingly common for organizations to have several collections of the IBM Data Server Driver packages, with the packages in the various collections differentiated by bind specifications). The DBA rebound the IBM Data Server Driver packages in the alternate collection with APPLCOMPAT(V12R1M500), the MERGE was issued again via the workstation-based tool, and it worked like a champ.

"OK, I understand this," you might be thinking, "But in the case of a tool or application used with dynamic SQL statements, couldn't you just issue SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M500' (or whatever higher application compatibility level is needed), instead of rebinding the Db2 package being used?" To answer that question: you can indeed use a SET CURRENT APPLICATION COMPATIBILITY statement to change the Db2 application compatibility level in effect for subsequent SQL statements that you or a program might issue, but you can only change that level to a value BELOW the APPLCOMPAT value of the package being used - you can't dynamically go to a level higher than the package's APPLCOMPAT value. If you're wondering, "What good would dynamically going to a LOWER application compatibility level do you?" I'll cite the example of needing to create a traditional segmented table space using a package bound with an APPLCOMPAT value of V12R1M504 (or higher). You can get the details of that scenario in an entry I posted to this blog exactly one year ago.

And that wraps up this blog entry. I hope that it will help you to avoid -4743 SQL errors (or help you to get past those errors if they show up at your site).