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.
Hi Robert,
ReplyDeleteYour 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
Sorry about the delay in responding.
ReplyDelete1) 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 http://www-01.ibm.com/support/docview.wss?uid=swg21297948.
Robert
Thanks Robert.
DeleteI 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.
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.
DeleteAs 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: http://www.redbooks.ibm.com/abstracts/sg247604.html?Open
Robert
Robert,
DeleteHow 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.
Thanks
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.
DeleteI 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.
Robert
Robert,
DeleteThere 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.
Thanks.
Sorry about the delayed response, Ajay.
DeleteIn 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.
Robert
Thanks for the clarification, Robert.
DeleteHave you noticed any failures using Data Studio to create an AUTONOMOUS native stored procedure?
ReplyDeleteI have not.
DeleteRobert
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.
ReplyDeleteI 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.
DeleteRobert
This comment has been removed by the author.
ReplyDeleteHi Robert,
ReplyDeleteWhen I use IBM DataStudio to execute native stored procedure, does it use ZIIP processor as well?
It will, but not because it was called from Data Studio. The native SQL procedure will get up to 60% zIIP offload in this case because it was invoked through the Db2 distributed data facility (DDF), which is how Data Studio and other DRDA requester programs access Db2 for z/OS. A native SQL procedure always runs under the task of its caller. If the caller is a DRDA requester, the associated task in the z/OS system will be a preemptable SRB in the DDF address space. SQL that runs under such a task is up to 60% zIIP eligible, and a native SQL procedure is all SQL.
DeleteRobert
Thank you Robert. your blogs giving different perspectives and change the momentum. One final question to make my understanding correct. I have a java application running in j2ee server accessing DB2 SP via Type-4 JDBC driver using TCP protocol. in this case the java application is DRDA requestor? will it be ziip eligible? if not, how can I make it DRDA requestor?
DeleteWhen an application accesses Db2 for z/OS by issuing SQL statements through the IBM Data Server Driver (essentially, the successor to Db2 Connect) and over a TCP/IP connection, that application is a DRDA requester. That is so because the IBM Data Server Driver takes SQL statements that are coded in a non-DBMS-specific form (such as JDBC) and translates them to DRDA (distributed relational database architecture - Db2's distributed database protocol). The IBM Data Server Driver provides drivers that support a variety of non-DBMS-specific SQL forms, including JDBC, ODBC and ADO.NET.
DeleteSQL statements issued by DRDA requesters (and issued by Db2 native SQL procedures called by DRDA requesters) are up to 60% zIIP-eligible when executed on the target Db2 for z/OS system. So, too, are SQL statements (and native SQL procedures) that are invoked by REST clients, as the REST interface to Db2 for z/OS is an extension of the Db2 distributed data facility (DDF), which also handles the interaction between Db2 and DRDA requester applications.
Robert
Thanks much Robert.
ReplyDeleteHi Robert,
ReplyDeleteWe would like to create a table row-level AFTER trigger that will send a message to MQ. It is a very active table hence we would like your opinion on an optimized way to achieve the result without impacting the transactional applications on zOS and reducing CPU cost with zIIP.
Thank you
Hey, Anne.
DeleteFirst of all, a trigger should execute under the task of the process that caused the trigger to fire, and so zIIP eligibility of whatever the trigger does should depend on the zIIP-eligibility of the trigger-firing task (so, if the trigger is fired by a CICS transaction program, no zIIP offload would be expected, whereas if the firing process were a DDF-using application then zIIP offload would be expected up to 60% of the time).
As for making the trigger as non-impactful as possible with respect to trigger-firing tasks, you will want to make the trigger definition as specific as you can. For example, if the trigger should only be fired after column C1 of the target table has been updated, you'll want to specify that in the CREATE TRIGGER statement. Aside from that, if what you want to do is have the trigger place a message on an MQ queue, it seems to me that the most straightforward way to do that is via Db2's built-in MQSEND function. If your trigger is going to require some relatively advanced logic, I would definitely recommend implementing that logic via an advanced trigger - something introduced via Db2 12 function level 500 (an advanced trigger can have a SQL PL routine in its body - for more information see http://robertsdb2blog.blogspot.com/2017/08/db2-12-for-zos-sql-enhancements.html). You'd want to go this route versus having your trigger call a stored procedure that implements the advanced logic.
You could also eliminate the effect on updating programs by taking a slightly different approach: use a data replication tool to keep a mirror table very close to in-synch with the source table, and define the after-update trigger on the table that's updated via the data replication tool. That approach may seem like overkill, but it could be appropriate if the programs that update the source table are really sensitive to even the slight overhead that would result from firing an after-update trigger.
Robert
Hi Robert,
DeleteCould you suggest some data replication tools that work good with db2-z/OS. Thanks.
Being an IBMer, I can certainly recommend our primary data replication tool, which works very well with Db2 for z/OS. It's called IBM InfoSphere Data Replication (more information at https://www.ibm.com/products/infosphere-data-replication).
DeleteA more specialized replication solution, designed to enable the provisioning of a copy of Db2 for z/OS tables in a cloud-based environment, with the intention that the data copy be used for read-only access, and with the copy being extremely close to currency in relation to the Db2 for z/OS source and with the replication process being extremely lightweight on the z/OS side, is IBM Db2 for z/OS Data Gate (more information at https://www.ibm.com/products/db2-for-zos-data-gate).
Robert