Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to handle Db2 smallint Overflow error

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Mon Jun 25, 2007 3:10 pm    Post subject: How to handle Db2 smallint Overflow error
Reply with quote

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
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Jun 25, 2007 5:11 pm    Post subject: Re: How to handle Db2 smallint Overflow error
Reply with quote

bhoja wrote:
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

Since you are exceeding the max value for a smallint, your only choices would be to change the field to an integer, ignore the overflow, or add another field to indicated that an overflow has occurred? You don't want to change the field so that leaves the other two choices neither of which is a great choice.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Mon Jun 25, 2007 7:01 pm    Post subject:
Reply with quote

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.
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: Mon Jun 25, 2007 7:31 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Mon Jun 25, 2007 9:55 pm    Post subject:
Reply with quote

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
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: Mon Jun 25, 2007 11:14 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Mon Jun 25, 2007 11:24 pm    Post subject:
Reply with quote

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
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 Jun 26, 2007 12:43 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
bhoja

New User


Joined: 31 Jan 2006
Posts: 49

PostPosted: Tue Jun 26, 2007 12:08 pm    Post subject:
Reply with quote

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
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 Jun 26, 2007 6:01 pm    Post subject:
Reply with quote

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
Quote:
you have a choice of making the field bigger, starting over at 1, or discontinue using the counter completely.
are your choices.

What other kind of suggestion might we make with the info provided?

It sounds like you are stuck dealing with a poor design that has not been detected before it was critical. Not an enviable place to be. If this number has grown each week, this could/should have been detected long before it filled. Several systems that i've worked on had a process that ran periodically to identify high-water marks to prevent this sort of issue from happening "overnight". If it is a 6 row table, it seems odd that the "control number" was defines as a smallint. Space surely wasn't the concern. . .
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Tue Jun 26, 2007 9:44 pm    Post subject:
Reply with quote

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.
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
No new posts FUSE - IBM MQ - CICS integration error Kevin Vaz CICS 1 Mon Sep 18, 2017 4:31 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us