Robert's Blog


Thursday, June 18, 2009

DB2, Stored Procedures, COBOL, and Result Sets

Last week, I helped a DBA at a large financial services firm with a couple of questions related to DB2 for z/OS stored procedures and result sets (referring to the row and column information accessed via a query included in a DECLARE CURSOR statement). Both of the DBA's questions had to do with COBOL programs called by DB2 stored procedures. A lot of mainframe sites have COBOL programs that are invoked by stored procedures, and plenty of people have some misconceptions with regard to the accessibility of result sets generated through cursors in stored-procedure-called programs, so in this post I'll share the information that I provided to the aforementioned DBA.

When it comes to DB2 for z/OS stored procedures calling COBOL programs, the situation is most interesting when both the stored procedure program and the program called by the stored procedure are written in COBOL. In that case, you have a couple of viable options: the COBOL stored procedure program could invoke the target COBOL program by way of an SQL CALL statement (the target program would run as a nested stored procedure, assuming that it had been set up to execute that way through a CREATE PROCEDURE statement referencing the program name), or it could execute the target program through a COBOL CALL (so that the target would run as a COBOL subroutine). Either way, both the COBOL stored procedure program and the target COBOL program would run in a WLM-managed stored procedure address space - the same address space if the target program executes as a COBOL subroutine, and the same or a different address space if the target is invoked via SQL CALL, depending on whether or not the same or a different WLM application environment was specified in the target's CREATE PROCEDURE statement (if you are using the DB2-managed stored procedure address space, get away from that and into WLM-managed address spaces soon - the DB2-managed space is not supported in a DB2 9 environment).

One of the differences between the nested-procedure and COBOL subroutine scenarios has to to with task control blocks (TCBs). If the target COBOL program is invoked via SQL CALL, it will run under its own TCB. If the target is executed as a COBOL subroutine, it will run under the TCB of the stored procedure program that issued the COBOL CALL for the subroutine.

Another difference - the one with which the question-asking DBA was concerned - has to do with access to a result set defined by a cursor declared in the target COBOL program. The DBA first brought up a situation in which a program running on an off-mainframe application server was seemingly able to fetch rows from a cursor declared in a "two levels down" stored procedure: the mid-tier program calls DB2 for z/OS COBOL stored procedure A, and stored procedure A calls COBOL stored procedure B. Stored procedure B issues a DECLARE CURSOR statement (on which the WITH RETURN option is specified) and opens this cursor. The mid-tier program subsequently fetches the result set rows associated with the cursor declared and opened in stored procedure B. That was working, but it shouldn't have been, because a DB2 for z/OS stored procedure generating a result set can return that result set only one level up within a series of nested calls. In other words, if stored procedure B declares and opens a cursor, stored procedure A (which called B via SQL CALL) can fetch rows from that cursor-defined result set by issuing an ASSOCIATE LOCATOR statement to get the locator value for the result set, and an ALLOCATE CURSOR statement to define a cursor and associate it with the result set locator value. If the program that called stored procedure A wants to retrieve the result set generated by the cursor declared in stored procedure B, it cannot use this ASSOCIATE LOCATOR/ALLOCATE CURSOR mechanism, because that mechanism only works one level up in a nested SQL CALL structure (DB2 for Linux, UNIX, and Windows allows result-set retrieval at both the one-level-up level and at the top level of a nested SQL CALL structure - "top" referring to the program that issued the initial CALL to a stored procedure).

So, how was the mid-tier program mentioned by the DBA able to get the two-levels-down result set generated by stored procedure B? Upon further investigation, the DBA found that stored procedure B, in addition to declaring and opening a cursor defining a result set, inserted the result set rows into a global temporary table (these come in two flavors, declared temporary tables and created temporary tables, with the latter usually being the best choice in terms of performance). Stored procedure A then declared and opened a cursor (WITH RETURN) referencing this global temporary table, and the mid-tier program (caller of stored procedure A) could then access the result set because it (the mid-tier program) was only one level up from stored procedure A. That's in fact an excellent way to make a stored procedure-generated result set available to a program several levels up in the nested call structure: put the result set in a global temporary table.

So, we had one mystery solved. The DBA then pointed to another situation that had him scratching his head: a program (again running on an off-mainframe middle tier) called COBOL stored procedure X, stored procedure X invoked COBOL subroutine Y via COBOL CALL, and the middle tier program was subsequently able to access a result set generated through a cursor declared (WITH RETURN) and opened by COBOL subroutine Y.

This was actually a working-as-designed situation. The DBA was thinking that it shouldn't have worked, because he was under the impression that a result set generated by program Y could be returned if program Y were invoked via SQL CALL, and could not be returned if program Y executed as a COBOL-called subroutine. You can in fact find passages in DB2 manuals and "red books" that appear to confirm this understanding of result set processing. It's not that the documentation is wrong - it's just that it can be easily misinterpreted if you consider it from a different perspective versus that of the documentation authors. Here's what I mean by that: when you read in a DB2 book that a COBOL-called subroutine cannot return a cursor-defined result set, what's being communicated is the fact that the subroutine can't return a result set to the program that invoked it via COBOL call. A subroutine called via COBOL call from a COBOL DB2 stored procedure program can return a result set to the program that called the stored procedure. This is consistent with the result set processing mechanism I described above for nested stored procedures: a SQL-called stored procedure can pass a result set to a one-level-up program (i.e., to the program that called it). In the context of result set processing, a subroutine called via COBOL CALL from a stored procedure program runs at the same "level" as the calling stored procedure; therefore, a result set generated by that subroutine, while not accessible by the calling stored procedure, can be accessed by the caller of the stored procedure (i.e., by the "one level up" program). Just remember that the cursor declared in the subroutine has to include the WITH RETURN option, and the stored procedure invoking the subroutine has to be defined with DYNAMIC RESULT SETS 1 (or more than 1, if multiple result sets will be generated by the stored procedure program and/or by COBOL-called subroutines invoked by the stored procedure program).

Is that clear? I hope so. I'm very big on DB2 stored procedures, and I want people to know how they can use them.

51 Comments:

Anonymous Anonymous said...

The concept of returning resulet-sets is both very powerfull and ... clumsy. The only thing you (as caller) can do is fetching row-by-row.
So, pooring the result in a temp-table and returning that is more usefull for the caller, which can use/join the result in another query which cannot be done with genuine result-sets
(althought I asked https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14000113&#14000113)

June 18, 2009 at 11:28 PM  
Blogger Robert Catterall said...

Good point. Temporary tables may indeed offer the greatest flexibility, but they may also entail a slightly larger CPU cost, owing to the cost of instantiating the temp table and inserting rows into same. It's a trade-off situation. Some client processes that indeed want just a row-by-row result set retrieval capability will probably go for the genuine result set approach, while processes requiring greater flexibility may do better with the temp table approach. Thanks for your comment.

June 24, 2009 at 3:09 PM  
Blogger Balakrishna said...

Hi
We have our application in Mainframe Z/os environment.
We use DB2 Stored procedures to get necessary informations from different interface application. Now I have Program A, B and C. First I call stored procedure from A and I get the necesssary information. Then Program A calls program B and here again in program B I call the same stored procedure with with different parameters.But I see that in the called program B, the parameters are still of that used in program A. why is this? How do I overcome this problem? ls help me.

November 4, 2009 at 6:25 AM  
Blogger Robert Catterall said...

I do not think that this should be happening, and I do not know why it is happening. You may need to contact IBM Support to get a resolution.

November 4, 2009 at 6:30 PM  
Anonymous Russ Cooper said...

We are having locking issues with a CURSOR WITH RETURN. The programmer added FOR FETCH ONLY and it did not seem to change anything. Is a CURSOR WITH RETURN automatically considered an update CURSOR?

November 6, 2009 at 10:32 AM  
Blogger Robert Catterall said...

Sorry about the delayed response, Russ.

I don't see that WITH RETURN would affect locking associated with a cursor. I'd ensure that the bind options for the two packages involved (the package containing the DECLARE CURSOR WITH RETURN and the package of the program that FETCHes the cursor's result set rows) are what you want them to be, concurrency-wise (i.e., check the ISOLATION and CURRENTDATA options). Also, consider whether or not row-level locking would be an option for the target tablespace.

November 11, 2009 at 7:15 AM  
Anonymous Louie L said...

Hey Robert,

I'm poking around the web simply looking for a book or manual that covers writing stored procedures in COBOL. I've been to places like IBMs Book store and MVSHELP.net, and have been frustrated at the dearth of anything that deals simply with that one topic.
I came across your blog, this thread specifically, and was happy to have learned something tonight.

Can you recommend any specific documentation on this topic that I can purchase and/or download off the web? I have an interview on Monday afternoon and need to cram pronto.

November 13, 2009 at 3:52 PM  
Blogger Robert Catterall said...

I suggest that you download a copy of the IBM "redbook" titled "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond." You can get a copy in PDF form by going to this url:

http://www.redbooks.ibm.com/abstracts/sg247604.html?Open

Check out Chapter 10, "COBOL programming". Also, go to the following url to get an associated zip file of sample COBOL programs:

ftp://www.redbooks.ibm.com/redbooks/SG247604/

Good luck with the interview!

Robert

November 13, 2009 at 6:29 PM  
Anonymous Louie L said...

Great. This is very useful.

Thanks

If I get the job, I owe you a beer!

November 13, 2009 at 8:37 PM  
Blogger Robert Catterall said...

OK - let me know how it turns out.

Robert

November 14, 2009 at 4:53 PM  
Anonymous Lou L said...

Hello Robert,

Well, I thought the interview went well, but, I didn't get the position. It was for a large bank in NY City and the app made heavy use of CICS and VSAM, with DB2.

As it turns out, they didn't ask any Stored Proc questions.

Anyway, thanks for the advice. I now have more time to read up.

Lou L

November 24, 2009 at 6:54 PM  
Blogger Robert Catterall said...

Hang in there, Lou, and keep putting up the shots - you don't score if you don't shoot.

November 25, 2009 at 12:26 PM  
Blogger Subhash said...

Hi Robert,
I just got on to project which is being migrated from Mainframe to Web-based system. And i was also told that significant parts of the legacy application uses cobol based store procs and wherever it deems fit they are going to be reused in the new system. My question is, can these store proc written in Cobol be executed on a DB2 database running on Linux? The DBA said that they can be used only on a DB2 on Mainframe but i think it should be possible to run on a Linux based DB2.

Please clarify.

Thanks,
Reddy

December 2, 2009 at 8:46 AM  
Blogger Robert Catterall said...

It would be possible to make some use of those mainframe COBOL DB2 stored procedures in a DB2 for Linux environment, but going that route would require some work.

First of all, the mainframe COBOL stored procedure programs in their compiled form (i.e., the object modules) will not execute on a Linux server, because of differences between the operating systems and processors on the two platforms. You'll have to work with the source code for the programs, and for that source code to be usable, you'll need to run it through the DB2 for Linux COBOL precompiler. I do NOT know if the mainframe COBOL source will precompile OK on the DB2 for UNIX box as-is. The COBOL source code (as modified by the DB2 for Linux precompiler) will then need to be compiled. To do that, you'll need a COBOL compiler on the Linux server. The only one of which I'm aware is the Micro Focus product: http://www.microfocus.com/products/ServerExpress/index.asp. You link the compiled code produced by the COBOL compiler, you code and execute a CREATE PROCEDURE statement to define the COBOL procedure to DB2 for Linux, you bind the program's SQL statements into a package (unless that was done as part of the PREP process in which the program was precompiled), grant privileges as needed to execute the procedure, and that's about it.

In my experience, it's very unusual for organizations to actually move DB2-accessing COBOL code from a mainframe to a Linux server. If an application is migrated, the COBOL programs are usually replaced by programs written in another language (e.g., Java, C, or SQL PL in the case of a stored procedure). Migrating mainframe COBOL stored procedure code to a Linux server, versus rewriting the stored procedures in SQL PL (SQL Procedure Language) or C or some other language, is technically possible, but, I think, not trivial. Note that Micro Focus offers consulting services to go along with their Linux COBOL-deployment software.

December 3, 2009 at 1:22 PM  
Anonymous Anonymous said...

Hi Rob,

I have scenario where my java client calls store procedure A, which inturns call B and C. When i use the COBOL subroutine Call,
Proc B:
Declare the cursor with return option
open the cursor
Same as for C

Will this return the results set of B and C to java client if the A is declared as Dynamic results sets 2.

April 21, 2010 at 12:49 PM  
Blogger Robert Catterall said...

That should work, because, if invoked from proc A via COBOL CALL, programs B and C would be at the same "level" as stored procedure A within the SQL CALL structure of the process. That being the case, the "one level up" result set passing capability should make the result sets generated by programs B and C available to the caller of stored procedure A.

Robert

April 21, 2010 at 8:22 PM  
Anonymous Anonymous said...

Robert,
reading your comments on result sets, I have a question.
I have a cobol stored procedured (A) that calls a sub-routine(B) to return data to the proc(A). In A I declare a temp table, load the temp table with data from B and open a cursor. A will be called from a JAVA web app. Is this the correct procedure or do I need to put the cursor and temp table in B?

thanks

July 14, 2010 at 6:56 AM  
Blogger Robert Catterall said...

The temporary table declared in A will certainly be available to B, because B is associated with the same DB2 thread as A. I assume that B populates the temp table declared by A, then A declares and opens a cursor referencing the temp table. Given this set-up, the Java application that calls stored procedure A should be able to retrieve data from the temp table by way of the cursor declared and opened in A (i.e., the Java application should be able to fetch rows in that cursor's result set).

July 14, 2010 at 8:41 PM  
Anonymous Anonymous said...

Robert,

I am new to this Cobol SP stuff so bear with me. I "created" a Cobol SP "A" in WLM3. Within the Cobol SP "A", I called an existing Cobol SP "B" which was "created" in WLM2. When I execute my SP "A" I get a -805 when trying to call the SP "B". Why? Is there a way around this without "creating" SP "B" in the same WLM as SP "A"?

Thanks, Jimmy

July 27, 2010 at 1:05 PM  
Blogger Robert Catterall said...

Jimmy,

A -805 is a "package not found situation." The WLM address space in which stored procedure B is to execute has nothing to do with it. I would guess that when stored procedure A tries to call B, DB2 finds that there is a mismatch between B's load module and its package. Either the package name isn't found, or B's package is found but its consistency token is different from B's load module.

Is it possible that DB2 is looking in the wrong collection for B's package? In what collection (or collections) is B's package located? Is A invoked by a program that is using a particular plan (for example, plan XYZ, used by a batch job, as opposed to the DISTSERV plan with which all DRDA requesters are associated)? If so, is B's collection in that plan's PKLIST?

July 27, 2010 at 8:06 PM  
Anonymous Anonymous said...

Robert,

Some more facts that I should have told you. SP "A" uses VSAM datasets found only in WLM3. SP "B" uses flat files only found in WLM2. I am thinking that SP "A" cannot find SP "B" because SP "B" was not defined in WLM3. But sometimes I no think very good.

Thanks,
Jimmy

July 28, 2010 at 2:26 PM  
Blogger Robert Catterall said...

Again, the different WLM address spaces should have nothing to do with the error code you're getting. There is NO requirement that a stored procedure called by another stored procedure has to be assigned to the same WLM address space as the caller.

-805 doesn't mean that the stored procedure program can't be found -- it means that the DB2 package associated with the stored procedure couldn't be found. Does B's package exist (you could see if there is an entry for the package in the SYSPACKAGE table in the DB2 catalog)? If so, in what collection was the package bound? Under what DB2 plan is A running? Does that plan have access to the collection of which B's package is a part (assuming that B's package exists)?

Robert

July 28, 2010 at 6:06 PM  
Anonymous Anonymous said...

We have a situation where we got audit notification for user iD PSYSADM accessing DSNR.DBPP.BATCH dataset. After doing some research I found a job DBPPWC01 is running everyday around midnight. The JCL description says 'JCL FOR RUNNING THE WLM-ESTABLISHED STORED PROCEDURES ADDRESS SPACE'. When we contacted DB2 DBA he doesn't know anything. Our systems guy also doesn' know how this job gets triggerred. Can you help? We are on ZOS / DB2.

November 1, 2011 at 8:25 AM  
Blogger Robert Catterall said...

That's not a batch job that's supplied with DB2, as far as I know. It's possible that this is a JCL procedure that was generated during DB2 installation or migration (check the value of the WLM PROC NAME field on the DSNTIPX panel of the DB2 install CLIST). It's also possible that someone coded JCL for the startup of a WLM-managed stored procedure address space and packaged it in the form of this job. As to why the job is run every day at midnight, I don't know. Are you using DB2 stored procedures? If so, is there a stored procedure that is assigned to the WLM execution environment associated with this startup JCL, and is that stored procedure called by some program that runs in the midnight time frame? If so, and if that address space is not running when the stored procedure is called, the startup JCL procedure would be executed.

November 1, 2011 at 1:22 PM  
Anonymous rakesh said...

While calling stored procedure through cobol module I am getting -443 sql error and sqlstate 38402.This stored procedure call is returning 443 instead of 466. It has result sets. If we run the stored procedure directly it is giving correct results. But where as if we invoke through Cobol module its giving -443 sql error .

August 21, 2013 at 6:47 AM  
Blogger Robert Catterall said...

According to the DB2 10 for z/OS Codes manual, 38402 is not a valid DB2 SQLSTATE value. That being the case, I don't know why the call from the COBOL module is not working for you.

Robert

August 25, 2013 at 10:26 PM  
Blogger Khalinder Attar said...

Hi Robert,

I am designing External high-level language procedures in COBOL in z/OS. Output of the Stored Procedure is the result of Select C1, C2, C3 from TableA. The result of the SQL has thousands of rows.

Please suggest me best possible solution to define the output parameter for this Stored Procedure.

This Stored procedure is going to invoked by Online Java Application and generating the report in csv format.

Advance thanks

Khalinder

January 24, 2014 at 1:39 AM  
Anonymous Anonymous said...

Hi,
I have a cobol Db2 program which inserts row in DB2 table. I want this sub routine to be called from DB2 Cobol stored procedure as well as from a cobol Db2 batch program executed in BMP mode (it uses GSAM files as input and output). If I compile the sub routine with IMS attach, then the batch program works fine, if I compile it with WLM attach the cobol DB2 Stored Procedure works fine. Is there any compile linklist which I can use so that the same sub routine can be called from both Cobol Db2 SP as well as Cobol batch program?

Thanks,
Rao.

January 26, 2014 at 3:27 PM  
Blogger Robert Catterall said...

Khalinder, I would not recommend returning a multi-thousand-row result set to a calling program in the form of a file. I think that a much better approach would be to have the COBOL stored procedure declare the cursor in question with the WITH RETURN option (additionally, the CREATE PROCEDURE statement would include the clause DYNAMIC RESULT SETS n, with "n" being greater than or equal to 1). The stored procedure would open this cursor, and then the calling Java program would fetch the result set rows. The DB2 for z/OS Application Programming Guide and Reference for Java provides information on retrieving result set rows from a cursor declared and opened in a called stored procedure (http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.java/src/tpc/imjcc_tjvjdmlt.htm). Another useful reference for you would be the IBM "redbook" titled, "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (http://www.redbooks.ibm.com/abstracts/sg247604.html?Open).

Robert

January 26, 2014 at 10:35 PM  
Blogger Robert Catterall said...

With regard to having a single COBOL load module that could be invoked as a subroutine in some situations and as a DB2 stored procedure in others, I recommend that you download a copy of the IBM "redbook" titled, "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (http://www.redbooks.ibm.com/abstracts/sg247604.html?Open). Look over section 10.3.3, particularly the information under the heading, "Solution 2: Dynamic invocation of the language interface module."

Robert

January 26, 2014 at 10:50 PM  
Blogger Khalinder Attar said...

Thanks Robert.

February 8, 2014 at 11:42 PM  
Blogger Khalinder Attar said...

This comment has been removed by the author.

March 7, 2014 at 2:43 AM  
Blogger Khalinder Attar said...

Hi Robert,

I am going to to re-design the DB2 Cobol program with External COBOL Stored Procedure for CURSORS in COBOLprogram on z/OS.

CURSOR-1 is calling CURSOR-2 for each records of Cursor-1. And Cursor-2 is calling CURSOR-3 for each record of Cursor-2. And Cursor-3 will write the output file.

Example : If Cursor-1 is fetching 10 rows and Cursor-2 is fetching 10 rows of each row of Cursor-1 and Cursor-3 is fetching 30 records for each row of Cursor-2. Total 10*10*30=3000 records will be written to output file.

Could you please provide me some tips to use the Stored Procedure for this scenario.

Advance thanks.
Khalinder

March 7, 2014 at 2:48 AM  
Blogger Robert Catterall said...

I can only offer two observations at this time:

1) I would prefer to do this with one versus three stored procedures, if possible, for the sake of execution efficiency. To have the three different cursors opened in three different stored procedures would be, I think, a too-granular approach.

2) Looks as though these cursors are matching rows from different tables. Rather than use three different SELECTs, I would prefer to see if I could generate the desired result set with a single SELECT that might involve a three-table join or some subquery predicates that reference different tables.

Robert

March 8, 2014 at 8:53 AM  
Blogger Khalinder Attar said...

This comment has been removed by the author.

March 8, 2014 at 9:03 AM  
Blogger Khalinder Attar said...

Thanks Robert

March 8, 2014 at 9:06 AM  
Blogger Khalinder Attar said...

This comment has been removed by the author.

March 16, 2014 at 11:38 PM  
Blogger Khalinder Attar said...

Reference to my above example : If Cursor-1 is fetching 10 rows and Cursor-2 is fetching 10 rows of each row of Cursor-1 and Cursor-3 is fetching 30 records for each row of Cursor-2. Total 10*10*30=3000 records will be written to output file.

I redesign the cursors with three Stored Procedures, there is need to use COMMIT statement at SP cursor-3. Could you guide me, how & where to use the CURSOR WITH HOLD.

My question is, do I need to use the CURSOR WITH HOLD in calling program ALLOCATE cursor_name CURSOR FOR RESULT SET rs-locator-variable or in the stored procedure when declaring the cursor.

Thanks
Khalinder

March 16, 2014 at 11:40 PM  
Blogger Robert Catterall said...

The cursor declared WITH RETURN in the stored procedure is the one that must also be declared WITH HOLD if there will be a commit issued between the time the cursor is opened and the result set rows are fetched by the calling program.

Robert

March 18, 2014 at 7:03 PM  
Anonymous Roman Glodowski said...

I just have found this blog entry by the search engine, looking for sqlstate 38402.
I'm developing native SQL proc now, and I'm using mqsend function to put message into MQ queue.
This is true: v10 codes manual omits 38402 sqlstate, but v8 manual says:
38402 - MQ user defined function failed to open Websphere MQ Queue.

And this is exactly what I did for test purposes. Funny.

December 11, 2014 at 2:10 AM  
Blogger Robert Catterall said...

Sorry about the delay in responding, Roman.

What you have observed is perhaps a result of the MQSEND function (a DB2-provided, user-defined function) having a new version in a DB2 10 environment (and a new schema: DB2MQ. In a DB2 10 for z/OS system, the SQLSTATE codes that pertain to the MQ user-defined functions begin with 38H0 (e.g., 38H01).

Robert

December 22, 2014 at 8:25 AM  
Anonymous Anonymous said...

Awesome Post !!! I have a query ...

I have a cobol module 'X' that calls around 6 cobol-db2 sub routines to update DB2 databases. Each subroutine updates/deletes/inserts 1 DB2 table at a time.
Now, I want to replace this cobol module 'X' with a DB2 Stored procedure on COBOL (as the module 'W' which calls module 'X' is getting replaced from COBOL to some .net web interface).
I do not need to take care of WLMs and all, that will be handled by DBAs.
I am very new to stored procedures, so could you please help me in slotting out the steps required. Specially now, I require the pseudo code for the DB2 stored procedure.
Its URGENT !!! An advance thanks to you for helping.

March 26, 2015 at 2:14 PM  
Blogger Robert Catterall said...

I recommend that you consult the IBM "redbook" titled, "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (you can download this document in PDF form from the Web page at this URL: http://www.redbooks.ibm.com/abstracts/sg247604.html?Open). The document contains a lot of information about COBOL stored procedures, including code samples.

Robert

March 27, 2015 at 8:25 PM  
Anonymous Anonymous said...

Hi Robert,
I am creating new COBOL Stored Procedure in which we are opening a cursor to send the data back to Java Screen. Cursor is having 50+ columns and we are seeing the performance issues whenever the number of records are crossing more than 40K rows. SP is taking more time to return the data. However If we are executing those queries separatly, They are running fine and getting completed in less time. Please provide your input to tune the performance of it.

April 28, 2015 at 2:24 PM  
Blogger Robert Catterall said...

Using a stored procedure should not significantly slow result set retrieval. How are the result set rows returned to the calling program when the stored procedure is used? By way of a WITH RETURN cursor, or by way of a temporary table into which the stored procedure places rows, and from which the calling program retrieves the rows? Is the stored procedure in question a nested stored procedure (i.e., is it called by another stored procedure), or is it directly invoked by the requester Java program? And, is that requester Java program running in the same z/OS LPAR in which the target DB2 subsystem is running, or is it running in a remote application server and connecting to the target DB2 system via TCP/IP? Why are you using a COBOL stored procedure program? Why not code a native SQL procedure? Does the query in the stored procedure contain an OPTIMIZE FOR n ROWS clause?

Robert

April 29, 2015 at 7:43 PM  
Anonymous Anonymous said...

Hi Robert,
You stated in your post that "a COBOL-called subroutine cannot return a cursor-defined result set". I know this is several years old and was wondering if that is still true for v10/v11? The reason I ask is because I'm researching if an existing COBOL Stored Procedure that returns a result set, can also be compiled as a subroutine so that another COBOL program can use a COBOL CALL (instead of a SQL CALL) to execute it. Also, what documentation would be the best place for covering this topic?

December 28, 2015 at 7:29 PM  
Blogger Robert Catterall said...

I'm not aware of any changes in this area. The WITH RETURN option of DECLARE CURSOR, and the related SQL statements ASSOCIATE LOCATORS and ALLOCATE cursor, apply, respectively, to DB2 stored procedures and to programs that invoke stored procedures (using the SQL statement CALL). That being the case, a DB2 for z/OS stored procedure written in COBOL and containing a DECLARE CURSOR... WITH HOLD statement would not provide identical functionality if the procedure's COBOL program were invoked as a subroutine (via COBOL CALL). If a DB2 stored procedure written in COBOL were to provide a FETCH-able result set to a caller by placing result set rows into a declared global temporary table (or a created global temporary table) that would be subsequently accessed by the calling program, that should be a mechanism that would work if the COBOL program were invoked as a DB2 stored procedure or as a COBOL subroutine.

Two good references for DB2 for z/OS stored procedure coding are an IBM redbook titled "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (downloadable from http://www.redbooks.ibm.com/abstracts/sg247604.html?Open), and the DB2 for z/OS Application Programming and SQL Guide (the DB2 11 version of this manual is accessible at http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/apsg/db2z_apsg.dita).

Robert

Robert

December 29, 2015 at 9:00 AM  
Anonymous Anonymous said...

I have written a db2 COBOL stored procedure that is called from a client program. It returns data that is written to a temporary table. It works fine on the 1st call, but subsequent calls return a -601. Fairly new to db2 stored procedures and not sure how to fix this one.

March 3, 2017 at 11:37 AM  
Blogger Robert Catterall said...

First of all, I would recommend using a native SQL procedure versus a COBOL stored procedure.

Second, I would not use a temporary table if I didn't have to. RETURN TO CLIENT cursors, introduced with DB2 10 for z/OS, can often be used instead of temporary tables. I wrote a blog entry about RETURN TO CLIENT cursors: http://robertsdb2blog.blogspot.com/2011/08/important-db2-10-for-zos-stored.html

Hard to diagnose a -601 without the text of the error message that accompanied the -601. The error could be caused by an ALTER, a CREATE, or a RENAME statement. See if you can determine which of these statements generated the -601 error code for your stored procedure, and again, see if you can get the full text of the error message associated with the -601.

Is the temporary table a declared or a created temporary table?

Robert

March 7, 2017 at 8:49 AM  
Anonymous Anonymous said...

Thanks for your response and sorry for the delay - the temporary table is declared in the program. In this case some of the data is calculated in the program and not retrieved from db2 tables, so that is the reason for using a temporary table. I was able to get the return to client cursor working in another program, but in this case I'm using the temporary table because of a number of calculated fields that are being returned.

March 17, 2017 at 5:02 AM  
Anonymous Anonymous said...

I was able to add the calculated fields to the return to client cursor and get the results I needed. As far as the temporary table goes I think the problem was it just stayed open even after the client ended the transaction. Thanks for your help

March 17, 2017 at 11:18 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home