Troubleshooting
Problem
Starting with version 11.70.xC7W2 or 12.10.xC2 and with MAX_FILL_DATA_PAGES set to on, data pages of tables with variable row size potentially can hold more rows. On pre-existing data oncheck would report large numbers of inconsistencies between bitmap recorded and calculated mode (fullness). The release notes require a "repair" after migrating to such version.
Symptom
oncheck -cD on many tables reporting many errors of following type:
TBLspace data check for <db_name>:<owner>.<table_name>
WARNING: data page <page_num> in tablespace <partnum> appears to be
more or less full than is indicated in the bitmap.
Bitmap mode: 0xc, Calculated mode: 0x4.
WARNING: The bitmap mode for page <page_num> has been reset from 0xc to 0x4.
The second warning only appears when oncheck was ran with the -y auto-repair option.
Note that system catalog as well as user tables can be affected by this if they contain variable length (varchar, nvarchar, lvarchar) columns or if they are compressed.
Cause
This has been reported as APAR IC98053: ONCHECK -CD CAN REPORT 'WARNING: DATA PAGE APPEARS TO BE MORE OR LESS FULL ...' IF MAX_FILL_DATA_PAGES SET TO 1 ON 11.70.FC7W2
Resolution of this APAR is the release notes requirement to have such tables repaired by oncheck.
While these reported inconsistencies (warnings) don't pose any real problem in themselves, i.e. no data inconsistency, and only point to some unused space in those pages that could be used by more rows, they still should be fixed as soon as possible after the migration. If not fixed it would be very hard to tell real problems from these in the future.
Background - old vs. new behavior:
To allow for variable length row growth, the following old and new rules determine whether a data page is considered full or would still allow for at least one more row to fit onto it:
In earlier versions, there needed to be at least maximum row size worth of free space on a page for it to allow an additional row (bitmap mode 0x4), otherwise it would be flagged full (bitmap mode 0xc). With MAX_FILL_DATA_PAGES set to on, newly filled pages could even have less free space as long as there remained at least 10% free space.
Especially, but not only, for compressed rows, the new rule only requires the smaller of a page's average row size or 20% of the page size worth of free space before the page would be considered full. This way a lot more real rows might fit onto a page.
Note that there are additional APARs for certain cases where the database server, for newly filled pages, has decided on different bitmap mode (data page fullness) than what oncheck considers appropriate:
- IC98529 (fixed in 11.70.xC8 and 12.10.xC3)
- IT00781 (fixed only after 11.70.xC8 and 12.10.xC4).
These APARs might cause same oncheck warnings for new pages and even after tables got corrected.
Environment
You've migrated databases running under Informix server versions 11.70.xC7W2 or newer or 12.10.xC2 or newer.
You had and have MAX_FILL_DATA_PAGES set to on before and after the migration.
You migrated tables with variable length fields (varchar, nvarchar, lvarchar) - you always have these, if only among system catalog tables - or compressed tables.
Those tables contain rows significantly shorter than what their maximum row size would be.
Diagnosing The Problem
Tables that came back clean from oncheck before the migration suddenly get a lot of such bitmap inconsistency warnings reported after a migration and with MAX_FILL_DATA_PAGES set to on.
Resolving The Problem
At a convenient time after the migration run oncheck -cD -y, either on whole databases or table by table.
This not only would make those warnings go away, but potentially also would make a lot of existing pages' unused space available for new rows.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21675845