Monday, October 20, 2014

DB2 for z/OS Stored Procedures: Native SQL, or Java?

Recently, a mainframe DB2 DBA put a question to me: when should DB2 for z/OS native SQL procedures be used versus Java stored procedures, and vice versa?

Why he asked: the DBA worked with a group of developers who, collectively, could code both of these DB2 stored procedure types. Within this group, individuals tended to go with what they were used to. In other words, the decision to code a native SQL or a Java stored procedure tended to be based more on momentum (e.g., "I used a Java stored procedure last time, so I'll go that route again this time") than on consideration of factors that might make one type of stored procedure more appropriate than the other for a given situation. The DBA wanted to provide the developers with guidelines that would help them to make more informed decisions regarding the use of native SQL versus Java stored procedures. In response to this request, I communicated my take on the matter, and via this blog post I'll share those thoughts with you.

I'll start out with this statement: if I needed to create a DB2 for z/OS stored procedure, I'd go with a native SQL procedure unless I needed to do something that couldn't be done (or couldn't be done in a practical sense) with that type of stored procedure. If I did have a requirement to use an external stored procedure (i.e., one with an external-to-DB2 executable that would run in a stored procedure address space), I'd look at Java as a good choice for the associated programming language, unless I needed to do something that would best be done with a COBOL stored procedure program (more on this momentarily).

Why my "native-first" mind set? Three reasons:

1) Native SQL procedures can be developed (and maintained) by lots of different people. You don't have to be proficient in a programming language such as Java (or COBOL) to create a native SQL procedure. If you know SQL, you can quickly pick up the "control" SQL statements (referring to logic flow control -- statements such as ITERATE, LOOP, GOTO, etc.) that enable coding of a SQL PL routine such as a native SQL procedure ("SQL PL" is short for SQL procedure language, the language used in a DB2 environment to write stored procedures and user-defined functions using only SQL). On top of SQL PL being a pretty easy means of writing DB2 stored procedures, the free and downloadable IBM Data Studio product provides a user-friendly GUI tool for native SQL procedure development and debugging.

2) Native SQL procedures, when called by DRDA requesters (i.e., through DDF), are zIIP-offloadable. More specifically, zIIP-offloadable to the tune of about 60%. That's because they run under the caller's task and a DRDA caller's task is an enclave SRB in the DDF address space. A Java stored procedure, like any external stored procedure, will always run under its own TCB in a stored procedure address space. Yes, execution of that stored procedure's Java code will be zAAP- or zIIP-eligible (the latter via what's called zAAP-on-zIIP, which you get when zAAP-eligible work runs on a system that has zIIP engines but no zAAP engines), but execution of the SQL statements issued by the Java stored procedure will not be zIIP-eligible (you'd actually get a little zIIP offload for these SQL statements, but not what you'd get with a native SQL procedure).

3) Native SQL procedures are where you're likely to see the bulk of DB2 for z/OS stored procedure functionality enhancements. Example: with DB2 10, the XML data type can be used for a stored procedure input or output parameter, but only for a native SQL procedure. Another example: DB2 11 introduced the autonomous stored procedure (it has a separate unit of work from that of the calling process, so that a data change made by an autonomous procedure will persist even if the calling transaction subsequently fails and is rolled back by DB2). Only a native SQL procedure can be autonomous. Yet another example: DB2 11 delivered support for array-type input and output parameters for a stored procedure, but only if it's a native SQL procedure.

When might I use an external stored procedure (such as a Java stored procedure)? I'd go this route if I needed to do something that couldn't be done (either at all, or in a practical sense) with a native SQL procedure. Maybe there is a need for a level of sophistication in the logic of the stored procedure that I can't implement with SQL PL (though in that case I might ask, "How sophisticated does the logic in a data-layer program have to be?"). I might go with an external stored procedure (such as a Java stored procedure) if there were a need to access resources (data or services) outside of DB2 (a native SQL procedure can issue SQL statements, period). Note that even in that case, there would be capabilities available in a native SQL procedure that might provide the functionality required. Keep in mind that native SQL procedures can utilize DB2 functions, which include things like MQRECEIVE and MQSEND if there is a need to interact with WebSphere MQ, and SOAPHTTPNV if I need to access a Web service. I might take the Java path if I wanted to use the same (or nearly the same) Java stored procedure program in a DB2 for z/OS and a non-DB2 relational database environment -- a Java stored procedure likely wouldn't have to be much different in the two environments, whereas a SQL PL routine created for DB2 would have to be re-created in the SQL programming language of a different relational database server in order to function properly in that non-DB2 environment (a native SQL procedure provides portability within the DB2 family of relational database systems).

If I wanted to use an external stored procedure, Java would be a fine choice for the programming language. There was a time, not so long ago, when I was not a fan of Java code running on mainframe servers, but now I'm a proponent of Java in a z/OS environment, as I noted in an entry recently posted to this blog. As pointed out in said blog entry, DB2 11 for z/OS delivered some important enhancements that boost scalability and CPU efficiency for Java stored procedures.

Are there circumstances that would lead me to choose COBOL instead of Java for an external stored procedure? Yes. One scenario that comes to my mind involves invocation of an existing COBOL subroutine. That could be done very efficiently via a COBOL CALL from a COBOL stored procedure (I wrote of a real-world example of this scenario in a blog entry I posted this past spring). I might also lean towards COBOL over Java if, in my organization, there were COBOL-knowledgeable developers but not Java-knowledgeable developers available at the time I needed a DB2 external stored procedure program to be coded.

The bottom-line message is this: SQL PL (for native SQL procedures) and Java (especially in a DB2 11 for z/OS system) are both very viable choices when it comes to development of DB2 stored procedures. Rather than taking a "what we're used to" approach to choosing one versus the other, consider the relative strengths of each language in light of the objectives you have for a given stored procedure. A thoughtful and informed choice is likely to be the right one for you.


  1. Hi Robert,

    Your blogs are always near to developer understanding, for that I should really appreciate that.

    I have few question on execution reagrding Native STP's, as below:

    1) Does DB2 look into any SYSIBM.* tables to figure out whether STP is Native or External. If yes, then which is that table and which column being referred to decide the same.

    2) Currently I m observing that there are rows in sysroutines table where language = SQL but Origin = E. Which really confuse me. Becoz I am under impression that SQL language STP's are native i.e. Origin = N. Please correct my understanding.

    3) In case of above 2 scenario, how DB2 would treat STP, Native or External ??. Is that possible if I update ORIGIN column in sysroutines to "N", then DB2 would considered as Native.

    Thanks in advance.

    Ajay G

  2. Sorry about the delay in responding.

    1) An 'N' in the ORIGIN column of the SYSIBM.SYSROUTINES catalog table indicates a native SQL procedure.

    2) Before DB2 9 for z/OS, a stored procedure could be written in SQL procedure language (SQL PL), but when such a procedure was prepared for execution it was turned into a C language program with embedded SQL data manipulation statements. That kind of stored procedure is called an external SQL procedure. DB2 9 for z/OS provided native SQL procedure support, allowing a stored procedure written in SQL PL to execute wholly as a package in the DB2 database services address space (aka the DBM1 address space).

    3) Conversion of an external SQL procedure to a native SQL procedure is not a matter of changing the value in the ORIGIN column of the procedure's row in SYSROUTINES. Information on converting external SQL procedures to native SQL procedures can be found in the IBM "tech note" at


    1. Thanks Robert.

      I have gone thru the link for conversion of external to Native STP, Its really helpful.

      But it would be great if you could provide the information on compilation JCL for native and external(SQL) procedure. How they are being pre-compiled, compiled and link edit.

      Currently I am under impression that Native STP just required pre compilation( to form DBRM) and then they would be bind to package. Correct me if I am wrong or Do I need specific PARMS for Native STP pre compilation/ compilation.

      Same way I want to understand for external(SQL) STP. Does it requires specific PARMS in precompiler, compiler and link edit JCL.

      In short how these Native and External(SQL) STP are being compiled and executed.

      Sorry, in case if I m putting these question in wrong place/platform.

      Thanks in advance.

    2. You don't need to be concerned about precompile, compile, and link-edit when you're creating a native SQL procedure. You just issue the CREATE PROCEDURE statement. Executing that statement will create the procedure's package, which is the one and only executable associated with a native SQL procedure.

      As for external stored procedures, you can find sample compile and link-edit JCL in the IBM "redbook" titled "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond." That document can be downloaded from the Web page at this URL:


    3. Robert,

      How calling of programs such as COBOL/CICS/IMS being handled in Native STP. Is it possible to call them from STP in DB2 v10?.

      And How efficient it would be in terms of performance.


    4. A DB2 for z/OS native SQL procedure can invoke a program that is not a native SQL procedure, as long as the means of invocation is a SQL CALL of a stored procedure; so, a COBOL stored procedure could be called from a native SQL procedure. A CICS program could be invoked from a native SQL procedure by way of the DB2-supplied stored procedure DSNACICS. IMS transactions can be invoked from native SQL procedures by way of the DB2-supplied stored procedures DSNAIMS and DSNAIMS2.

      I do not have performance information on utilizing these methods of invoking CICS and IMS transaction programs, but I expect that it would be pretty efficient.


    5. Robert,

      There is one scenario where One Batch COBOL program is calling Batch COBOL BMP subroutine for validation and insertion. subroutine is called for every i/p records in the main program. Hence Subroutine is being called million times to perform complex or simple validation. Subroutine consume good amount of CPU.

      Now if we replace subroutine by Native stored procedure and start calling STP from Batch main program; Would it be still Ziip eligible ??.

      Am under impression that here it would not be DDF thread and hence ziip utilization might not come into picture.

      Any thoughts on above scenario. Currently since existing sub routine is BMP type we have good control on COMMIT frequency at code level. But how control on commit frequency would be possible in case of native STP.


    6. Sorry about the delayed response, Ajay.

      In the scenario you've described (native SQL procedure called by a batch program), the native SQL procedure would not be zIIP-eligible because it would run under the batch program's TCB. To be zIIP-eligible, a native SQL procedure needs to run under a preemptible SRB, as is the case when the caller is a DRDA requester (a DRDA requester is represented in the z/OS environment by a preemptible SRB in the DB2 DDF address space).

      Commits can be issued from a native SQL procedure. You could have logic in the native SQL procedure that would cause a commit to be issued after every X number of data-change operations.


    7. Thanks for the clarification, Robert.

  3. Have you noticed any failures using Data Studio to create an AUTONOMOUS native stored procedure?

  4. Is there a difference between autonomous and autarky? When i start a Native autonomous Stored Procedure (SP), my calling Process is waiting for the Response of the SP.

    1. I am not familiar with the term "autarky" in a Db2 for z/OS context. If a Db2 native SQL procedure is defined as AUTONOMOUS, that does not make it asynchronous with respect to the calling process - the calling process will not receive control back until the called procedure (AUTONOMOUS or otherwise) completes.