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

asked 19 Apr, 04:09

Martin's gravatar image

Martin
8.9k127164253
accept rate: 14%

1

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...)

(19 Apr, 05:28) Volker Barth

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;
permanent link

answered 19 Apr, 12:13

Volker%20Barth's gravatar image

Volker Barth
39.5k355539810
accept rate: 34%

edited 19 Apr, 12:17

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;
(20 Apr, 08:46) Martin
Replies hidden
1

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

(20 Apr, 09:55) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18
×13
×10

question asked: 19 Apr, 04:09

question was seen: 784 times

last updated: 20 Apr, 09:55