TomLiotta Oct 12, 2011   8:22 PM GMT

For a probable explanation, see Database file sizes. The general limit for a member is 4 294 967 294 records. You show 4 285 259 542 records, so that's close. Beyond that, you indicate that it's a keyed physical with ACCPTHSIZ(*MAX1TB). For keys and that access path size, a maximum member size may be modified by this approximation: 725,680,000,000 / (12 + (.8 x key length)) Even then it's only an approximation that may have some restrictions imposed by different HLLs. Regardless, the strict maximum of 4 294 967 294 records is close enough to what you show to figure that you are at the maximum for the platform. You didn't say what the message ID was, but I assume that it was MCH2804. That is a machine (MCH) limit, so it can't be exceeded. There are at least four general alternatives that come to mind:

1. Make the file smaller. Reorganize it to remove deleted rows, and delete unnecessary rows.
2. Re-architect the database. There might be logical changes that would separate elements into a set of smaller distinct tables.
3. Convert OBJECTNM to a partitioned table and access it as one. That's not a trivial task if you're using older HLL database access methods, and installing DB2 Multisystem might be a problem.
4. Convert to a platform that handles even larger database sizes.

Maybe someone else has more ideas. Tom

125,585 pointsBadges:

sim400 Oct 13, 2011   2:45 PM GMT

Thanks Tom. We are going for Partitioned tables. Just one more question on this....iSeries V5R4M0 is 64 bit OS (I guess). but why this size is limited to 4 294 967 294 ? This is highest address with 32 bit pointer.

380 pointsBadges:

TomLiotta Oct 14, 2011   0:22 AM GMT

but why this size is limited to 4 294 967 294 ? Most likely it's something like because the storage area in a file description object that holds a record counter is a 32-bit integer. This is highest address with 32 bit pointer. But this isn't an address -- it's probably a simple counter. The system does support far larger values, so I suppose IBM simply hasn't seen any economic reason to restructure file descriptions. Only the very largest customers actually require that many records in a single member of a single file. There are so many ways of restructuring data to avoid it that there has been no pressure to increase the limit. For the very largest customers, DB2 Multisystem may be reasonable, or even a move into a mainframe system. So, why would IBM put effort (money) into something that would benefit a tiny fraction when alternatives exist? Enabling a 64-bit integer would technically be a cost that would have to be paid by all customers rather than just the few that need it (and perhaps the few that can afford it.) Many systems can't even hold a single useful table with 4 billion records. (A record needs maybe a couple dozen bytes before it's got meaningful data in each record.) So most customers prefer that IBM puts development dollars elsewhere. Other IBM divisions might also have reasons to keep some physical limits in place. The AS/400 line can compete with some elements of the mainframe class, but it simply isn't intended to be a mainframe system. Tom

125,585 pointsBadges:

sim400 Oct 14, 2011   2:09 AM GMT

Thanks Tom. This makes sense.

380 pointsBadges: