Cursor processing in more than 1 application?

Michael Jessen

Cursor processing in more than 1 application?
The environment is Mainframe, DB2 V7, z/OS 1.3. COBOL applications.

A developer has indicated that a cursor can be opened in one program and the
fetch for that cursor could be done in another (a called or calling
program). He was not talking about stored procedures.

I have question back to the developer to understand why they would want to
do something like this, but I don't know for sure that this is even
possible.

Does anyone know if this is possible and what the pros/cons of implementing
something like this would be? I can't think of any good reasons to
implement something like this even if it was possible....

Thanks for your help!
Mike

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Cursor processing in more than 1 application?
(in response to Michael Jessen)
This doesn't work, in my opinion, because the open and the fetch statements
have to be in the same package. At least that's what I think.

Why not put the open, fetch and close in a third program, triggered by some
parameters, for example O, F and C? We do this in a generated DB2 access
module for our application (only one package with all the SQLs in it, but you
can call it from everywhere; the access module receives the name of the
SQL method and a flag O/F/C (open, fetch, close) or S (singleton select)
or blank (for insert, update, delete etc.) as parameters - and, of course,
a structure with all the host variables, both directions).

This way you could easily do what you want, that is, call open from one
program and call fetch from another. But, anyway, I don't see how this could
make sense. In our application, the open/fetch/close-calls are always in the
same function, very near to each other.

See this example (well, it's C/370, but you can call the same function
from PL/1, for example). The function alftdb2x, which resides in another
load module, does all the DB2 work. sel196_prfpaket_status is the name of
the SQL access method. ss is the structure with the host variables. See the
flags O, F and C, which control open/fetch/close.

/*****************************************************/
/* Open Cursor */
/*****************************************************/

rcdb2 = alftdb2x (sel196_prfpaket_status,
'O', 'N', &ss, db2state, &sqlca);

if (rcdb2 > 4)
db2_error (rcdb2, "sel196_prfpaket_status (OPEN)");

/*****************************************************/
/* Fetch-Schleife */
/*****************************************************/

memset (max_create_zp, 0x00, 27);

for (;;)
{
rcdb2 = alftdb2x (sel196_prfpaket_status,
'F', 'N', &ss, db2state, &sqlca);

/*****************************************************/
/* DB2-Fehler, dann Abbruch */
/* nichts gefunden, dann raus */
/*****************************************************/

if (rcdb2 > 4)
db2_error (rcdb2, "sel196_prfpaket_status (FETCH)");

if (rcdb2 == 4)
break;

if (memcmp (ss.create_zp, max_create_zp, 26) > 0)
strcpy (max_create_zp, ss.create_zp);

/*****************************************************/
/* Statuswert und NOK-Kennzeichen berechnen */
/*****************************************************/

if (memcmp (ss.prfkennz, "NOK", 3) == 0)
*noks_vorhanden = 'Y';

if (memcmp (ss.prfkennz, "OK ", 3) == 0)
*oks_vorhanden = 'Y';
}

/*****************************************************/
/* Close Cursor */
/*****************************************************/

rcdb2 = alftdb2x (sel196_prfpaket_status,
'C', 'N', &ss, db2state, &sqlca);

if (rcdb2 > 4)
db2_error (rcdb2, "sel196_prfpaket_status (CLOSE)");

Some benefits of this approach:

- the application logic and the SQL accesses are separated

- because the access module is generated, you can implement
new SQLs in minutes

- the program with the application logic needs no package

- if you change the logic, but not the SQL, you don't have
to bind a new package

- if you want to migrate to another database some day (which
I do NOT suggest!), you only need to replace the access modul,
that is, its generator. In fact, I have also versions for
ORACLE and MySQL.

Kind regards

Bernd




Am Mittwoch, 12. Januar 2005 16:12 schrieben Sie:
> The environment is Mainframe, DB2 V7, z/OS 1.3. COBOL applications.
>
> A developer has indicated that a cursor can be opened in one program and
> the fetch for that cursor could be done in another (a called or calling
> program). He was not talking about stored procedures.
>
> I have question back to the developer to understand why they would want to
> do something like this, but I don't know for sure that this is even
> possible.
>
> Does anyone know if this is possible and what the pros/cons of implementing
> something like this would be? I can't think of any good reasons to
> implement something like this even if it was possible....
>
> Thanks for your help!
> Mike
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm