Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting the return code from stored procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
bcil

New User


Joined: 30 Nov 2009
Posts: 3
Location: chicago

PostPosted: Tue Nov 06, 2012 8:40 pm    Post subject: Getting the return code from stored procedure
Reply with quote

I have created a REXX to insert a row into a DB2 table. This insert fires a trigger which then calls a stored procedure. The stored procedure calls and passes a SQL string to a COBOL program which performs a 'EXECUTE IMMEDIATE' on that string.
The process works, but:
How do I get the results of success or failure of the SQL executed by the 'EXECUTE IMMEDIATE' back to the REXX?

thanks for any assistance.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 06, 2012 8:58 pm    Post subject:
Reply with quote

Hello - good to see your first post after "lurking" a few years icon_smile.gif

I've never done what you ask, but if the REXX calls a COBOL module, you might try to set the RETURN-CODE when leaving the COBOL code and see if it is available to REXX.

As you know what the INSERT will trigger, you might ensure whatever the trigger did happened successfully.

You might modify the trigger to log return codes (in an audit table) and then look at the audit table to determine success/failure.

Be aware that when multiple "things" run concurrently, sometimes the result can be "looked for" before the process has run . . . My preference is to run this sort of thing serially.
Back to top
View user's profile Send private message
bcil

New User


Joined: 30 Nov 2009
Posts: 3
Location: chicago

PostPosted: Tue Nov 06, 2012 9:16 pm    Post subject:
Reply with quote

I will try the 'set the RETURN-CODE' route first. If that isn't successful I will modify the trigger to log the results. I hadn't thought of these solutions. Thanks Dick for the help. bc
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 06, 2012 9:31 pm    Post subject: Reply to: Getting the return code from stored procedure
Reply with quote

You're welcome - good luck icon_smile.gif

d
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Tue Nov 06, 2012 11:07 pm    Post subject: Reply to: Getting the return code from stored procedure
Reply with quote

A quick google and it looks like the RETURN-CODE should work, and there are other suggestions for passing data back as well.
Back to top
View user's profile Send private message
bcil

New User


Joined: 30 Nov 2009
Posts: 3
Location: chicago

PostPosted: Tue Nov 06, 2012 11:39 pm    Post subject:
Reply with quote

Thanks Bill. Dick's suggestion recalled a vague memory so I looked back in some old code and found a 12 yr old program using the COBOL special register. So all ends well. thanks icon_biggrin.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 06, 2012 11:51 pm    Post subject:
Reply with quote

I will be very interested in the outcome.

first, the REXX communicates directly with DB2, for an INSERT,
the REXX does not CALL the COBOL program.

Next, DB2 (in its own, separate, isolated task) performs the INSERT,
which immediately invokes a trigger (again, this is in db2 task space)
and this trigger CALLs an SP, which CALLs a COBOL program.

I believe (and bet money on it)
that the only way to communicate to the REXX,
is if the TRIGGER interprets a return from the SP,
which would have interpreted something from the COBOL pgm.

i.e.: set a SIGNAL in the trigger.

I could be wrong.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 07, 2012 12:29 am    Post subject:
Reply with quote

Hi Dick,

Quote:
I could be wrong.
Nope, i don't believe so . . .

When i posted my initial reply, in my head i was trying to think of how the RC could get "all the way back". Which is why i mentioned the other possibilities.

As far as "getting the answer back", i was reminded of a situation some years ago when processes issued a database request (not db2) and then looked (waited) for the reply to post. This went well until a hardware and operating system upgrade were installed. Unfortunately, the reply was returning back before the process was ready to check it - and this looked like a lost interrupt. . . C'mon Down - APAR<g>.

Be interesting to see what happens here!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Nov 07, 2012 1:21 am    Post subject: Reply to: Getting the return code from stored procedure
Reply with quote

:-) I went by the "if the rexx calls a Cobol program..."

RETURN-CODE ends up in Register 15 when the Cobol program "finishes". Where that ends up in the above scenario, I don't know.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Nov 07, 2012 3:15 am    Post subject: Re: Reply to: Getting the return code from stored procedure
Reply with quote

Bill Woodger wrote:
:-) I went by the "if the rexx calls a Cobol program..."

RETURN-CODE ends up in Register 15 when the Cobol program "finishes". Where that ends up in the above scenario, I don't know.

Special variable RC.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Nov 07, 2012 3:22 am    Post subject: Reply to: Getting the return code from stored procedure
Reply with quote

Sorry, I don't know in the "rexx does db2 insert, blah, blah, Cobol, blah" mentioned above where it ends up.

If you are succinctly saying that is still in rc, that's fine with me, but then I'm confused from earlier...

Does the "DB2 (in its own, separate, isolated task)" communicate the value of R15 from termination of the Cobol program "across tasks" and back to the rc?

Maybe I'll end up googling again...
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Nov 07, 2012 3:40 am    Post subject:
Reply with quote

Ah; looking back over the OP, I see that the SP is invoking the program, rather than the other way around as I'd misremembered icon_redface.gif I guess the answer depends on what the SP is written in; native SQL?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 07, 2012 3:57 am    Post subject:
Reply with quote

come on guys,

the SP is invoked by an INSERT TRIGGER.

and I doubt that the language of the SP would affect anything
in this scenario.

about the only thing available is the sqlstate.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 07, 2012 4:22 am    Post subject:
Reply with quote

Hello,

As an after thought . . . . icon_redface.gif

If testing for an RC of zero, strongly suggest that "something" be done to force what should be a non-zero.

Receiving an RC=0 may not have anything to what really went on in the triggered event.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Nov 07, 2012 10:26 am    Post subject:
Reply with quote

As I understand the process is

REXX --> DB2 INSERT --> TRIGGER --> SP (Cobol Program).

Now looks like TS wants the return code of SP back to REXX.

First of all, This should be one unit of work.

As far as I know, the return code cannot be propagated directly to REXX.

The only solution I could think of as Dick suggested, Raise a SIGNAL in case of SP throws RC anything that is undesirable.

Then the Trigger would in turn fail as SP failed. This trigger will in turn fail the insert statement called by REXX with SQLCODE -723 will be returned to REXX, which means trigger failed.

In Short

SP throws RC <> 0 (Failed) --> Trigger Raises the error (Failed) --> Insert Fails --> REXX Receives SQLCODE -723.



Quote:

-723

AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN trigger-name. INFORMATION RETURNED: SQLCODE: sql-code, SQLSTATE: sql-state, MESSAGE TOKENS token-list, SECTION NUMBER section-number
Explanation

During execution of an UPDATE, MERGE, INSERT, or DELETE statement, a trigger was activated. One of the triggered SQL statements received an SQL error condition.

trigger-name
The trigger that was activated when the error occurred.
section-number
The section number that is associated with the failing triggered SQL statement. For triggers that contain a WHEN clause, the WHEN clause is section number one. The triggered SQL statements are numbered sequentially, beginning with section number two. This is true for triggers with or without a WHEN clause.
sql-code
A numeric value that indicates why the SQL statement execution was unsuccessful. This code is received by the activated trigger.
sql-state
The corresponding SQLSTATE for the SQLCODE.
token-list
The list of tokens from the original SQL error. This list might be truncated.

System action

The trigger and the original statement cannot be processed. The triggering table is unchanged.
System programmer response

Identify the failing SQL statement by using one of the following methods:

Use the trigger name and section number.
If the trigger definition is available, use the section number.
Use the following SELECT statement to retrieve the statement from the SYSIBM.SYSPACKSTMT catalog table:

SELECT STMT, SEQNO FROM SYSIBM.SYSPACKSTMT
WHERE COLLID = 'schema-name' AND
NAME = 'trigger-name' AND
SECTNOI = section-number
ORDER BY SEQNO

For triggers that were created in DB2® Version 8 new-function mode or later, the STMT result will be in Unicode. To view a Unicode result as EBCDIC, issue the DISPLAY UTF8 command in the ISPF browse program within SPUFI.

Refer to the explanation of the SQLCODE that is indicated by sql-code in this code and take the appropriate action or actions.
Programmer response

Contact your database administrator to determine why the trigger named in the message received the error.
SQLSTATE

09000
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 07, 2012 5:29 pm    Post subject:
Reply with quote

> SP >>> Cobol Program
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm
No new posts DFHWS2LS return value divated CICS 4 Mon Nov 07, 2016 5:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us