| View previous topic :: View next topic |
| Author |
Message |
pai_sunil_bs
Beginner
Joined: 19 Sep 2005
Posts: 21
Topics: 11
|
 Posted: Mon Jun 19, 2006 4:10 am Post subject: WHENEVER clause in DB2 programs |
 |
|
In my program there is a statement:-
EXEC SQL WHENEVER SQLERROR
GO TO 9800-ABEND
END-EXEC.
EXEC SQL WHENEVER SQLWARNING
CONTINUE
END-EXEC.
However, there is a SQL Query in the same program:-
EXEC SQL
SELECT MAX(PHN_NBR)
INTO :WS-SEQ:WS-NULL-1
FROM PHONE_V
WHERE <Clause>
END-EXEC
IF SQLCODE = +0
ADD +1 TO WS-SEQ
MOVE WS-SEQ TO WS-PHN-SEQ-NBR
WS-PRV-SEQ-NBR
ELSE
MOVE +1 TO WS-PHN-SEQ-NBR
WS-PRV-SEQ-NBR.
My query is if a SQL error occurs, whether the control goes to the ELSE part OR the code within WHENEVER SQLERROR will be executed.
I couldnt find my help from the IBM manual about overriding the WHENEVER cause. |
|
| Back to top |
|
 |
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Mon Jun 19, 2006 4:28 am Post subject: |
|
|
The WHENEVER statement is valid for EVERY SQL statement you code. On the other hand, you are right, with these WHENEVER statements in place, the IF-statement doesn't make sense.
Since the WHENEVER statement is used by the DB2 precompiler to generate GOTO-statements into your program, you have no choice to override this setting during runtime. This is why I could not recommend to use the WHENEVER statement at all!!!
regards
Christian |
|
| Back to top |
|
|
pai_sunil_bs
Beginner
Joined: 19 Sep 2005
Posts: 21
Topics: 11
|
| Posted: Mon Jun 19, 2006 7:21 am Post subject: |
|
|
I checked in XPED..The control doesnt come to the SQLCODE check. Instead it goes to the the PARA specified within the WHENEVER Clause. So I have commented OUT the WHENEVER clause.
Anyway Thanks for the response.
If anyone has more information on this please share. |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Mon Jun 19, 2006 8:00 am Post subject: |
|
|
Sunil,
Whenever you want to show code please insert them between the code tags and send so that it will be easier to understand.Why do you not try using EVALUATE statement.Try this code below:
| Code: |
EXEC SQL
SELECT MAX(PHN_NBR)
INTO :WS-SEQ:WS-NULL-1
FROM PHONE_V
WHERE <Clause>
END-EXEC.
EVALUATE SQLCODE
WHEN +0
ADD +1 TO WS-SEQ
MOVE WS-SEQ TO WS-PHN-SEQ-NBR WS-PRV-SEQ-NBR
WHEN 100
CONTINUE
WHEN OTHER
MOVE +1 TO WS-PHN-SEQ-NBR WS-PRV-SEQ-NBR.
END-EVALUATE.
|
More information on the WHENEVER STATEMENT:
[code:1:1a379be14e]
The WHENEVER statement :
The WHENEVER statement causes DB2 to check the SQLCA and continue processing your program, or branch to another area in your program if an error, exception, or warning occurs. The condition handling area of your program can then examine SQLCODE or SQLSTATE to react specifically to the error or exception. The WHENEVER statement allows you to specify what to do if a general condition is true. You can specify more than one WHENEVER statement in your program. When you do this, the first WHENEVER statement applies to all subsequent SQL statements in the source program until the next WHENEVER statement. The WHENEVER statement looks like this:
EXEC SQL
WHENEVER condition action
END-EXEC
The condition of the WHENEVER statement is one of these three values:
SQLWARNING Indicates what to do when SQLWARN0 = W or SQLCODE contains a positive value other than 100. DB2 can set SQLWARN0 for several reasons
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
