tag:blogger.com,1999:blog-4516533711330247058.post7576265201418964828..comments2024-03-18T16:34:03.887-07:00Comments on Robert's Db2 blog: DB2 for z/OS Stored Procedures: Native SQL, or Java?Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger23125tag:blogger.com,1999:blog-4516533711330247058.post-47611662821784204692021-05-05T13:04:25.164-07:002021-05-05T13:04:25.164-07:00Being an IBMer, I can certainly recommend our prim...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).<br /><br />A 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).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-59490432784679282602021-05-04T20:48:16.345-07:002021-05-04T20:48:16.345-07:00Hi Robert,
Could you suggest some data replicatio...Hi Robert, <br />Could you suggest some data replication tools that work good with db2-z/OS. Thanks. vishnuhttps://www.blogger.com/profile/14022131683809382286noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-43524686406998921062021-05-04T15:11:54.927-07:002021-05-04T15:11:54.927-07:00Hey, Anne.
First of all, a trigger should execute...Hey, Anne.<br /><br />First 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).<br /><br />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.<br /><br />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.<br /><br />Robert<br /><br />Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-8596837590500623372021-05-04T08:50:54.528-07:002021-05-04T08:50:54.528-07:00Hi Robert,
We would like to create a table row-le...Hi Robert,<br /><br />We 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.<br /><br />Thank youAnnehttps://www.blogger.com/profile/11397867730977044586noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-75592669776616451792020-10-02T05:29:02.653-07:002020-10-02T05:29:02.653-07:00Thanks much Robert. Thanks much Robert. vishnuhttps://www.blogger.com/profile/14022131683809382286noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-7919307842880334242020-10-01T18:23:05.142-07:002020-10-01T18:23:05.142-07:00When an application accesses Db2 for z/OS by issui...When 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.<br /><br />SQL 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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-6507085824789852632020-09-30T23:45:42.063-07:002020-09-30T23:45:42.063-07:00Thank you Robert. your blogs giving different pers...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?vishnuhttps://www.blogger.com/profile/14022131683809382286noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-69557541107454936722020-09-30T17:06:27.200-07:002020-09-30T17:06:27.200-07:00It will, but not because it was called from Data S...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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-83120932571681610122020-09-30T07:29:30.870-07:002020-09-30T07:29:30.870-07:00Hi Robert,
When I use IBM DataStudio to execute na...Hi Robert,<br />When I use IBM DataStudio to execute native stored procedure, does it use ZIIP processor as well?vishnuhttps://www.blogger.com/profile/14022131683809382286noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-27883107347540412092020-09-30T06:47:15.800-07:002020-09-30T06:47:15.800-07:00This comment has been removed by the author.vishnuhttps://www.blogger.com/profile/14022131683809382286noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-25319771681140946442018-07-09T14:35:56.580-07:002018-07-09T14:35:56.580-07:00I am not familiar with the term "autarky"...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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-17915844656375294592018-06-29T02:17:15.251-07:002018-06-29T02:17:15.251-07:00Is there a difference between autonomous and autar...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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-58843552722237112122016-05-17T06:46:53.899-07:002016-05-17T06:46:53.899-07:00Thanks for the clarification, Robert.Thanks for the clarification, Robert.Anonymoushttps://www.blogger.com/profile/05221292166667256499noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-50333683549878255402016-05-09T08:59:19.384-07:002016-05-09T08:59:19.384-07:00Sorry about the delayed response, Ajay.
In the sc...Sorry about the delayed response, Ajay.<br /><br />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).<br /><br />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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-21922353670542785392016-04-20T21:00:07.272-07:002016-04-20T21:00:07.272-07:00Robert,
There is one scenario where One Batch COB...Robert,<br /><br />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.<br /><br />Now if we replace subroutine by Native stored procedure and start calling STP from Batch main program; Would it be still Ziip eligible ??.<br /><br />Am under impression that here it would not be DDF thread and hence ziip utilization might not come into picture.<br /><br />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.<br /><br />Thanks.<br />Anonymoushttps://www.blogger.com/profile/05221292166667256499noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-58914169876453978972016-04-20T16:18:35.710-07:002016-04-20T16:18:35.710-07:00A DB2 for z/OS native SQL procedure can invoke a p...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.<br /><br />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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-45116146789103993592016-04-20T16:05:15.217-07:002016-04-20T16:05:15.217-07:00I have not.
RobertI have not.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-85880363643329935872016-04-20T10:18:22.932-07:002016-04-20T10:18:22.932-07:00Have you noticed any failures using Data Studio to...Have you noticed any failures using Data Studio to create an AUTONOMOUS native stored procedure?db2dbastillhttps://www.blogger.com/profile/12818154705733675733noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-3495355091968582932016-04-13T14:44:03.725-07:002016-04-13T14:44:03.725-07:00Robert,
How calling of programs such as COBOL/CIC...Robert,<br /><br />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?.<br /><br />And How efficient it would be in terms of performance.<br /><br />Thanks<br />Anonymoushttps://www.blogger.com/profile/05221292166667256499noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-71252902451653385752015-12-01T19:25:03.334-08:002015-12-01T19:25:03.334-08:00You don't need to be concerned about precompil...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.<br /><br />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: http://www.redbooks.ibm.com/abstracts/sg247604.html?Open<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-21389188880824273162015-11-25T06:34:00.764-08:002015-11-25T06:34:00.764-08:00Thanks Robert.
I have gone thru the link for conv...Thanks Robert.<br /><br />I have gone thru the link for conversion of external to Native STP, Its really helpful.<br /><br />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.<br /><br />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.<br /><br />Same way I want to understand for external(SQL) STP. Does it requires specific PARMS in precompiler, compiler and link edit JCL.<br /><br />In short how these Native and External(SQL) STP are being compiled and executed.<br /><br />Sorry, in case if I m putting these question in wrong place/platform.<br /><br />Thanks in advance. Anonymoushttps://www.blogger.com/profile/05221292166667256499noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-8593736144395988122015-11-20T18:50:09.396-08:002015-11-20T18:50:09.396-08:00Sorry about the delay in responding.
1) An 'N...Sorry about the delay in responding.<br /><br />1) An 'N' in the ORIGIN column of the SYSIBM.SYSROUTINES catalog table indicates a native SQL procedure.<br /><br />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).<br /><br />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.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-40764938395545035902015-11-13T12:11:43.939-08:002015-11-13T12:11:43.939-08:00Hi Robert,
Your blogs are always near to develope...Hi Robert,<br /><br />Your blogs are always near to developer understanding, for that I should really appreciate that.<br /><br />I have few question on execution reagrding Native STP's, as below:<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />Thanks in advance.<br /><br />Ajay GAnonymoushttps://www.blogger.com/profile/05221292166667256499noreply@blogger.com