Search code examples
databaseintegerdb2ibm-midrangeinteger-overflow

How to handle AS400 DB2 data overflow on a packed decimal field?

I am working with an AS400 IBM i-series machine. The problem is that I have a packed-decimal field in one of my files. The design has put the length of field to 9 but now my data is growing and when the data is greater than 999999999 an overflow occurs. so for values greater than 999999999 it turns to 0 and it will write the remaining of value in the file. As my machine is under high loads and there are lots of data I cannot change the file design. So What's the best way to handle this problem?

It's a really big problem and I would appreciate any help. Thanks

Solution

  • You really only have two choices: change your file or change your code.

    If you truly have one billion records and this is a key field, then you should go through the process of changing the file. If all of these records do not need to be "live", then you might be able to offload some of these to a "historical" table with one extra key so that you would be able to delete records and reuse some of these numbers.

    If you need to keep all of this data as "live", then you should plan to widen the field. First of all make a modified version of the file in another library. Then manipulate your library list to test it with the existing code. Revise and compile as needed. At last schedule a known limited downtime, put the latest data into the new file, and swap the new file and programs into the production library.

    If you are not to that scale and want to try to change your code and not this file, ask yourself why the data takes up nine digits to describe it. Are you skipping large blocks of numbers? Are you trying to make certain digits specify certain meanings? Those techniques can eat up numbers at a much faster rate than taking them out sequentially. You could change the code to change how these numbers are assigned, but if multiple parts of the application expect them to play by a certain set of rules, changing all of those places might be much more work than just changing the file. This can be better or worse depending on how the application is structured. That is a judgement that you will have to make.