Thursday, April 29, 2021

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


4 comments:

  1. Hi Rob,

    From DB2 V12 onwards its going to be like at each Function Level ,new capability is going to be added/some thing will be deprecated as per new delivery model.
    Till V12 we had JPM (Pre migration) reports for each version.
    From V12 ,will we get JPM reports for each function level ? Pre migration reports for each Function Level is possible from IBM with list of impacted DB2 objects?.
    Wondering how we will manage incompatible risks for each function level if any .Ex. Non-UTS will be deprecated from FL 504 .But JPM reports specific to FL 504 is not available.

    Thanks in advance
    Yogi

    ReplyDelete
    Replies
    1. Hello, Yogi.

      DSNTIJPM, the pre-migration check-up job, is helpful in spotlighting things to which you should attend before migrating from one version of Db2 for z/OS to another. You're right in that there is not an equivalent job with respect to different function levels of Db2 12. There's a reason for that: activating a new function level in a Db2 12 environment is considerably simpler than migrating to a new version of Db2 - it often involves only execution of an -ACTIVATE FUNCTION LEVEL command (a CATMAINT job might also be required if the target function level is dependent on some Db2 catalog changes). That simplicity relative to version-to-version migrations is a key part of the Db2 12 continuous delivery value proposition.

      The fact of the matter is, incompatibilities introduced by Db2 12 function levels are few and far between. If you go to the function levels page in the Db2 for z/OS Knowledge Center (https://www.ibm.com/docs/en/db2-for-zos/12?topic=12-db2-function-levels) and click on the link for any of the function levels, you'll find information about incompatibilities introduced by the function level. In almost all cases, the information is as follows: "Function level nnn introduces no incompatible changes."

      You mentioned the "deprecated objects" aspect of function level 504. Actually, that's no big deal. As I pointed out in an entry posted to this blog last year, even if you have activated function level 504 (or higher) and the package of the program you usually use to create database objects (DSNTEP2, for example) has an APPLCOMPAT value of V12R1M504 (or higher), you can absolutely still create a non-universal table space - you just precede the CREATE TABLESPACE statement with the statement SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503'. You can view that blog entry at http://robertsdb2blog.blogspot.com/2020/04/clearing-air-regarding-db2-12-for-zos.html.

      Also, even in the rare case in which a Db2 12 function level introduces a SQL incompatibility, you can avoid the impact (if any) of that incompatibility on application programs by using an APPLCOMPAT value that preserves earlier behavior for the SQL operation in question (I covered this use of APPLCOMPAT in the blog entry view-able at http://robertsdb2blog.blogspot.com/2019/06/db2-for-zos-talking-about-applcompat.html).

      Robert

      Delete
  2. Thanks Robert for detailed information.

    This workaround "SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503'" will help in case something goes wrong if we drop Non-UTS tables by mistake.

    With respect to application packages we decided to keep APPLCOMPAT as it is unless they need V12 new functionality .We decided to Rebind in V12 just to utilize new Optimizer.

    As per my understanding, With respect to JPM reports ,there wont be any more JPM report till there is Db2 V13(?) talk in future then.

    I just checked with DB2 SMEs ,in longer run ,if IBM will consider JPM reports at function level for it's DB2 customers.

    Thanks a lot Robert !!

    Regards
    Yogi

    ReplyDelete
    Replies
    1. Yogi, my expectation is that there will NOT be JPM-like jobs associated with Db2 for z/OS function levels. I anticipate that DSNTIJPM will continue to be associated with preparation for migration to a new version of Db2 for z/OS.

      Robert

      Delete