Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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 '23, 04:09

Martin's gravatar image

Martin
9.0k130169257
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 '23, 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 '23, 12:13

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

edited 19 Apr '23, 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 '23, 08:46) Martin
Replies hidden
1

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

(20 Apr '23, 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 '23, 04:09

question was seen: 1,088 times

last updated: 20 Apr '23, 09:55