How to handle Db2 smallint Overflow error

bhoja · New User Joined: 31 Jan 2006 Posts: 49

I have a doubt on the DB2 smallint.
DB2 smallint can accept max values of 65536. One of my program is updating the table, same time
it is adding one count to this field, that is smallint field. Upon adding the count crossed the limit
65536, so the field not accepting the count, means it is overflow. Could anyone tell me how to handle
this situation, without altering the field.

Regards
Raj

Craq Giegerich · Posted: Mon Jun 25, 2007 5:11 pm

TG Murphy · Posted: Mon Jun 25, 2007 7:01 pm

You mention that 65536 is the maximum value for a SMALLINT. Are you sure?

Pasted below from the DB2 manual:

Smallest SMALLINT value -32768
Largest SMALLINT value 32767

Check to make sure that the overflow you are encountering is not a result of using COMPUTE with the TRUNC OPT compiler option.

Suggestion: If altering the field is the best solution to this problem - and I suspect it is - then I think the first question you need to ask your DBA is: Please explain again why altering the field is not an option? If the DBAs cannot or will not provide an explanation then you may need to escalate the issue.

dick scherrer · Posted: Mon Jun 25, 2007 7:31 pm

Hello,

To add a bit to what TG recommends. . .

Did this field value grow suddenly or has the number been "creeping" up for a longer time (looking at the rows in the table may help identify this)? What is this a count of? How many rows are in this table (the table containing this count)?

Has your process changed so that the numbers are being used faster than planned? Was there a growth plan or was this count never supposed to even approach the limit? I believe the +/- 32k limit is if your smallint is signed, but if it is unsigned, the max would be 64k - the sign takes the high-order bit, cutting the range of values in half.

bhoja · New User Joined: 31 Jan 2006 Posts: 49

I am using the update query like this.
UPDATE AXJJ0ASD
SET LTD_CNT = LTD_CNT + 1 , LAST_UPDT_USERID =
:TCLAXJJ0ASD.ASD-LAST-UPDT-USERID , LAST_UPDT_TSTAMP
= CURRENT TIMESTAMP
WHERE TOT_CNT_CODE = :TCLTXJJ0ASD.ASD-TOT-CNT-CODE

In which LTD_CNT is smallint

while processing this I am getting -413

Is there any solution without altering field

Thanks
Raj

dick scherrer · Posted: Mon Jun 25, 2007 11:14 pm

Hello,

You need to review the questions asked earlier in this topic.

If the value has reached the max, you will have to expand the field size or start over at 1. You will need to look at your situation to see if the number might be reset or if it must continue to increase. This is likely something that should be discussed with your project lead and a DBA and possibly a business analyst.

If you review the earlier questions and answer them rather than re-posting your initial question in different words, we may be able to offer suggestions. I believe we understand your problem, but we will need more info from you for us to be able to help.

bhoja · New User Joined: 31 Jan 2006 Posts: 49

Hi Dick,
We can not increase, means the count already reached the max value.
If I go for changing the smallint to integer, there are 10,000 programs impacting. Without altering this is there any solution then it will be great.

Thanks
Raj

dick scherrer · Posted: Tue Jun 26, 2007 12:43 am

Hello,

If you will not answer the questions posted, we can't be of much help.

From what little you have provided, you have a choice of making the field bigger, starting over at 1, or discontinue using the counter completely. For any of those choices, the people i mentioned earlier should be involved.

bhoja · New User Joined: 31 Jan 2006 Posts: 49

Hi Dick,
I have not understood this part,
If the value has reached max, you will have to expand the field size or start over at 1.
The table is having 6 rows.
This is not a sudden increase. Every week the the count will be increasing. But this week it crossed the limit. The number is not reset. You need any more information in this?

Thanks
Raj

dick scherrer · Posted: Tue Jun 26, 2007 6:01 pm

Hello,

Yes, we need more information if we are going to be able to offer any new suggestions.

If you can provide no additional info, these

TG Murphy · Posted: Tue Jun 26, 2007 9:44 pm

Are you being serious when you say 10,000 programs?

Seems like an awful lot of programs to me.

Unless you can reset your counters back to 1 as suggested by Dick - you have no choice but to alter your table to use INTEGER instead of SMALLINT. And if 10,000 programs are impacted then you will need to modify 10,000 programs.

Big change but not complicated. If there are really 10,000 programs impacted then there must be a lot of programmers on staff to support such a huge system.