IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need some help in debugging.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Mon Mar 28, 2011 6:05 pm
Reply with quote

There is table with following primary key
COL1
COL2
COL3
COL4
COL5

We need into insert above table.

Before inserting into table we get the value of COL4 as

SELECT IFNULL(MAX(NUM_ALRT)+1,1)
INTO WS-COL4
FROM (Same table into which we need to insert)
WHERE COL1 = WS-COL1
COL2 =WS-COL2
COL3 =WS-COL3
COL5 =WS-COL5 (Please note that COL4 is not included in the WHERE clause)

By using above mentioned query, I think we can not get any duplicate rows while inserting(as COL4 will always be different), but still I am getting -803.

Any pointers....

Also it might be relevant to know that this table is empty and it is the first time we are trying to insert into it.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Mon Mar 28, 2011 6:37 pm
Reply with quote

What SQLCODE do you get from the above query when you execute it for the first record?

If you get a nonzero SQLCODE you should not use any values in the result set - they are invalid, regardless you have used the IFNULL function. That only deals with if you get a row back with NULL value in NUM_ALRT.
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Mon Mar 28, 2011 6:50 pm
Reply with quote

Thanks Kjeld for your reply.

The way exception handling is done is like

SELECT IFNULL(MAX(NUM_ALRT)+1,1)
INTO WS-COL4
FROM (Same table into which we need to insert)
WHERE COL1 = WS-COL1
COL2 =WS-COL2
COL3 =WS-COL3
COL5 =WS-COL5 (Please note that COL4 is not included in the WHERE clause)

EVALUATE SQLCODE
WHEN +0
CONTINUE
WHEN +100
CONTINUE
WHEN OTHER
MOVE 'Y' TO ERROR

After this check we try to Insert where we get -803


So I think if we getting to the Insert query we have got either 0 as SQLCODE or 100. (But in case of 100, I think result will come as NULL)
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Mon Mar 28, 2011 6:57 pm
Reply with quote

Keanehelp wrote:
So I think if we getting to the Insert query we have got either 0 as SQLCODE or 100. (But in case of 100, I think result will come as NULL)

When you get SQLCODE +100, you will not get any valid result back.

Change the code executed in WHEN +100 to assign 1 to WS-COL4, then I think it will work.
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Mon Mar 28, 2011 7:14 pm
Reply with quote

I tried running the code and found that in case no record is selected in above query the SQLCODE is still 0000(not 100) with 1 as a value in NUM_ALRT.

Thoughts?????
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 28, 2011 8:10 pm
Reply with quote

1) yes, that is true : a MAX() with a where clause will return null with sqlcode +000 when no rows qualify the where-clause.

2) Are you sure the -803 is on the primary index ? maybe there is another unique index on the table.

3) Is the same transaction running several times ?
could be that tran1 is selecting max between other transaction select max() and insert.

4) Display col1 ,... col5 when inserting. maybe something else is wrong in your code.
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Tue Mar 29, 2011 12:45 pm
Reply with quote

Thanks GuyC for your reply.

I notice one more thing..

This batch program executes in 4 different region(Test,QA,UAT, Prod). It is failing in only one of them.

I compared order of Primary keys in all these environments and found that while sequence is like COL1,COL2,COL3,COL4,COL5 in 3 of them, one has sequence like COL1,COL2,COL4,COL3,COL5(Please note COL3 and COL4 are not in order).

Could this be a reason? If so then why?
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Tue Mar 29, 2011 12:59 pm
Reply with quote

Is the region with the disorderly index the one where the SQL is failing?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Mar 29, 2011 1:10 pm
Reply with quote

Keanehelp,

if you would use DSNTIAR to expand and describe the -803, it will tell you exactly which index you are violating.....then you can stop guessing.

if the index is Unique, hard to see why a poorly created index (columns out of order)
would be the cause of an -803.
but, have not had time to think it thru.
Regardless, you are better off determining which index was violated instead of theorising.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Mar 29, 2011 1:32 pm
Reply with quote

select max(c4) will take longer when index is C1,C2,C4,C3,C5
thus giving the opportunity for parallel transactions to annoy eachother
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Debugging not getting triggered throu... CICS 3
No new posts Not able to view the values of variab... CA Products 1
No new posts Compiling program in endeavor for tra... Compuware & Other Tools 4
No new posts Debugging with EDF CICS 5
No new posts Starts DEBUGGING by Procedure Divisio... Testing & Performance 4
Search our Forums:

Back to Top