remote stored procedure getting -751

Brad Wissink

remote stored procedure getting -751

We have a request program that is issuing a call to a remote stored procedure that is getting a -751 when it returns.  A -751 says it is attempting to execute an SQL statement that is not allowed.   The remote stored procedure program has display statements in it that show it is completing successfully, but the requesting program is then getting this -751.   We then setup a local request program on the same server as the stored procedure and made the request to the stored procedure from there.  That works fine.    So looking at the explaination of the -751 it talks about stored procedures can not issue COMMIT or ROLLBACK.  It believe it is the COMMIT that is causing the problem.  so I have some questions

1.  Why would the local request work and the remote request get a -751 .

2.  The -751 explaination under programmer response say the requester can indicate commits are allowed by sending a DRDA RDBCMTOK=TRUE indications.   What is that and where is it explained?   

3.  Is there something in the way I defined the stored procedure to DB2 that controls this?

Thanks

 

in the programmer response for -751 it says to 'either remove all COMMIT and ROLLBACK statements from the stored procedure, or modify the client application so that it establishes an environment that allows the stored procedure to execute statements. '  

bernd oppolzer

remote stored procedure getting -751
(in response to Brad Wissink)
My question would be: why do you want to allow stored procs to do
the commit themselves?

From what I learned stored procs never should do commits or rollbacks,
but instead they should leave the decision about commit or rollback to the
caller (which maybe could decide based on return values from the SP).

And: the stored proc should need so little time, that intermediate commits
(like in Batch) should not be a concern. After all, you can limit the
amount
of time used in a SP call.

Kind regards

Bernd



Am 17.01.2018 um 23:29 schrieb Brad Wissink:
>
> We have a request program that is issuing a call to a remote stored
> procedure that is getting a -751 when it returns.  A -751 says it
> is attempting to execute an SQL statement that is not allowed.   The
> remote stored procedure program has display statements in it that show
> it is completing successfully, but the requesting program is then
> getting this -751.   We then setup a local request program on the same
> server as the stored procedure and made the request to the stored
> procedure from there.  That works fine.    So looking at the
> explaination of the -751 it talks about stored procedures can not
> issue COMMIT or ROLLBACK.  It believe it is the COMMIT that is causing
> the problem.  so I have some questions
>
> 1.  Why would the local request work and the remote request get a -751 .
>
> 2.  The -751 explaination under programmer response say the requester
> can indicate commits are allowed by sending a DRDA RDBCMTOK=TRUE
> indications.   What is that and where is it explained?
>
> 3.  Is there something in the way I defined the stored procedure to
> DB2 that controls this?
>
> Thanks
>
> in the programmer response for -751 it says to 'either remove all
> COMMIT and ROLLBACK statements from the stored procedure, or modify
> the client application so that it establishes an environment that
> allows the stored procedure to execute statements. '
>
>
> -----End Original Message-----

Daniel Luksetich

remote stored procedure getting -751
(in response to bernd oppolzer)
I disagree. Commits can be important in stored procedures, especially when developed as services. As for the -751 I don’t have an answer as we’d really need to hear about the client and what the procedure is doing.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Wednesday, January 17, 2018 4:52 PM
To: Brad Wissink <[login to unmask email]>
Subject: [DB2-L] - RE: remote stored procedure getting -751



My question would be: why do you want to allow stored procs to do
the commit themselves?

From what I learned stored procs never should do commits or rollbacks,
but instead they should leave the decision about commit or rollback to the
caller (which maybe could decide based on return values from the SP).

And: the stored proc should need so little time, that intermediate commits
(like in Batch) should not be a concern. After all, you can limit the amount
of time used in a SP call.

Kind regards

Bernd





Am 17.01.2018 um 23:29 schrieb Brad Wissink:

We have a request program that is issuing a call to a remote stored procedure that is getting a -751 when it returns. A -751 says it is attempting to execute an SQL statement that is not allowed. The remote stored procedure program has display statements in it that show it is completing successfully, but the requesting program is then getting this -751. We then setup a local request program on the same server as the stored procedure and made the request to the stored procedure from there. That works fine. So looking at the explaination of the -751 it talks about stored procedures can not issue COMMIT or ROLLBACK. It believe it is the COMMIT that is causing the problem. so I have some questions

1. Why would the local request work and the remote request get a -751 .

2. The -751 explaination under programmer response say the requester can indicate commits are allowed by sending a DRDA RDBCMTOK=TRUE indications. What is that and where is it explained?

3. Is there something in the way I defined the stored procedure to DB2 that controls this?

Thanks



in the programmer response for -751 it says to 'either remove all COMMIT and ROLLBACK statements from the stored procedure, or modify the client application so that it establishes an environment that allows the stored procedure to execute statements. '



-----End Original Message-----



-----End Original Message-----

Brad Wissink

remote stored procedure getting -751
(in response to Daniel Luksetich)
We are running DB2 V11 on two z/OS lpars, which for this scenario are PROD1 and PROD2.

The current setup is that we have a DB2 subsystem running on PROD1 and it is connected to a DB2 subsystem on PROD2. The requesting program runs on PROD1. It is Cobol and just sets the necessary data for the DB2 request. It then calls our interface program, which is also Cobol. The interface program also runs on PROD1 and does a EXEC SQL CONNECT TO the remote DB2 on PROD2. It then does a EXEC SQL CALL stored procedure. The stored procedure is defined to the DB2 on PROD2 with EXTERNAL NAME SP-PROGRAM. The SP-PROGRAM is Cobol and just takes the parameters passed in, parses them and then does a CALL to an application program. The application program then does all kinds of stuff to get the necessary information to return to the requesting program. I don’t know what all those application programs do and they were originally written to execute in a batch environment. So I am pretty sure they are doing the COMMIT.

To remove the DB2 to DB2 connection from equation I setup a local test by moving the requesting and interface programs to PROD2 and then execute them from there. When we do that everything works correctly and we get the desired results back. So why do I get a -751 when executing the same thing from the remote DB2?

Brad Wissink| Sr. Systems Programmer
Sammons® Financial Group Member Companies
4350 Westown Pkwy | West Des Moines | IA 50266
Phone 515-267-2735 | ext. 33735 | Cell: 515-231-1511

[login to unmask email]<mailto:[login to unmask email]> | www.SammonsFinancialGroup.com http://www.sammonsfinancialgroup.com
________________________________

The information contained in this message is confidential, protected from disclosure and may be legally privileged. If the reader of this message is not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any disclosure, distribution, copying, or any action taken or action omitted in reliance on it, is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to this message and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.

Daniel Luksetich

remote stored procedure getting -751
(in response to Brad Wissink)
OK, we are starting to narrow it down. Are you using CAF or RRSAF in the calling program?



You could try recompiling the calling program with precompiler option for a type 1 connection, although I’m just guessing here.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Wissink, Brad [mailto:[login to unmask email]
Sent: Thursday, January 18, 2018 8:18 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: remote stored procedure getting -751



We are running DB2 V11 on two z/OS lpars, which for this scenario are PROD1 and PROD2.



The current setup is that we have a DB2 subsystem running on PROD1 and it is connected to a DB2 subsystem on PROD2. The requesting program runs on PROD1. It is Cobol and just sets the necessary data for the DB2 request. It then calls our interface program, which is also Cobol. The interface program also runs on PROD1 and does a EXEC SQL CONNECT TO the remote DB2 on PROD2. It then does a EXEC SQL CALL stored procedure. The stored procedure is defined to the DB2 on PROD2 with EXTERNAL NAME SP-PROGRAM. The SP-PROGRAM is Cobol and just takes the parameters passed in, parses them and then does a CALL to an application program. The application program then does all kinds of stuff to get the necessary information to return to the requesting program. I don’t know what all those application programs do and they were originally written to execute in a batch environment. So I am pretty sure they are doing the COMMIT.



To remove the DB2 to DB2 connection from equation I setup a local test by moving the requesting and interface programs to PROD2 and then execute them from there. When we do that everything works correctly and we get the desired results back. So why do I get a -751 when executing the same thing from the remote DB2?



Brad Wissink| Sr. Systems Programmer

Sammons® Financial Group Member Companies

4350 Westown Pkwy | West Des Moines | IA 50266

Phone 515-267-2735 | ext. 33735 | Cell: 515-231-1511



[login to unmask email] <mailto:[login to unmask email]> | http://www.sammonsfinancialgroup.com www.SammonsFinancialGroup.com

_____


The information contained in this message is confidential, protected from disclosure and may be legally privileged. If the reader of this message is not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any disclosure, distribution, copying, or any action taken or action omitted in reliance on it, is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to this message and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.

-----End Original Message-----