How to identify which DB object is affected when a page checksum fails?

[MCMartin]

Is it possible to identify which database object (table or index or ...) is using the DB page which generates a checksum failure assertion?

[VolkerBarth]

Hm, the SQL Central Fragmentation Tab can be used to display which particular object uses which pages and show their numbers, unfortunately you cannot select more than one object at a time, and there does't seem to be a "vice-versa" display, i.e. to show which object occipies a particular page.

(I would very wildly guess the SYSTAB.tab_page_list and/or ext_page_list columns might reveal more but I don't know their internals...)

[VolkerBarth · Accepted solution]

This query might do the trick. It checks whether the page bitmaps are marked for the according page number as table page, extension page or index page.

Note: These are just my own test results, DO NOT AT ALL CONSIDER THIS VALID INFORMATION.

-- These queries rely on field marked as "For internal use only." - Handle with care
begin
   declare nDbSpaceID int = 0;
   declare nPageNr bigint = 218;

   select table_id, table_name, table_page_count, ext_page_count,
      len(tab_page_list), len(ext_page_list),
      substr(tab_page_list, nPageNr + 1, 1) as uses_page_as_tab_page,
      substr(ext_page_list, nPageNr + 1, 1) as uses_page_as_ext_page
   from sys.systab ST
   where dbspace_id = nDbSpaceID
      and (uses_page_as_tab_page = '1' or uses_page_as_ext_page = '1');

   select SPX.table_id, table_name, SPX.phys_index_id, index_name,
      len(allocation_bitmap),
      substr(allocation_bitmap, nPageNr + 1, 1) as uses_page_as_idx_page
   from sys.systab ST
      inner join sys.sysphysidx SPX on ST.table_id = SPX.table_id
      inner join sys.sysidx SIX
         on SPX.table_id = SIX.table_id and SPX.phys_index_id = SIX.phys_index_id
   where SIX.dbspace_id = nDbSpaceID
      and uses_page_as_idx_page = '1';
end;

[MCMartin · Accepted solution]

Thanks Volker that is awesome!

For all who want even more comfort I placed Volkers code in a procedure:

CREATE PROCEDURE "DBA"."GetDBPageObject"( IN nPageNr bigint)

on exception resume

BEGIN

    declare nDbSpaceID int = 0;


   select table_id, table_name, table_page_count, ext_page_count,
      substr(tab_page_list, nPageNr + 1, 1) as uses_page_as_tab_page,
      substr(ext_page_list, nPageNr + 1, 1) as uses_page_as_ext_page,
      null as phys_index_id, null as index_name, null as llen, NULL  as uses_page_as_idx_page
   from sys.systab ST
   where dbspace_id = nDbSpaceID
      and (uses_page_as_tab_page = '1' or uses_page_as_ext_page = '1')
    union 
  select SPX.table_id, table_name, null as table_page_count, null as ext_page_count, null as uses_page_as_tab_page, null as uses_page_as_ext_page, SPX.phys_index_id, index_name,
      len(allocation_bitmap) as llen,
      substr(allocation_bitmap, nPageNr + 1, 1) as uses_page_as_idx_page
   from sys.systab ST
      inner join sys.sysphysidx SPX on ST.table_id = SPX.table_id
      inner join sys.sysidx SIX
         on SPX.table_id = SIX.table_id and SPX.phys_index_id = SIX.phys_index_id
   where SIX.dbspace_id = nDbSpaceID
      and uses_page_as_idx_page = '1';

END;

[VolkerBarth · Accepted solution]

Well, I'd add the nDbSpaceID as 2nd parameter (probably default 0) then 🙂