|
View previous topic :: View next topic
|
| Author |
Message |
bcil
New User
Joined: 30 Nov 2009
Posts: 3
Location: chicago
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello - good to see your first post after "lurking" a few years 
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 |
|
|
bcil
New User
Joined: 30 Nov 2009
Posts: 3
Location: chicago
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
You're welcome - good luck
d |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
bcil
New User
Joined: 30 Nov 2009
Posts: 3
Location: chicago
|
|
|
|
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  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Dick,
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
:-) 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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
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  I guess the answer depends on what the SP is written in; native SQL? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
As an after thought . . . .
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 |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| > SP >>> Cobol Program |
|
| Back to top |
|
|
|
|
