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

I guess the answer is "yes" but are not sure how to check that...

Running, I have added a dbspace (*) to store blob data on that separate dbspace. Now it looks like the system dbspace has unexpectedly grown, too.

However, according to SYSTAB and SYSIDX, the freshly created tables and their indexes are stored on the dbspace, as desired.

Because most rows are stored in extension pages, I'm unsure whether they are stored in the dbspace, too.

Is there a system procedure to check that?

(*) I usually do not use separate dbspaces but in that case, it's quite likely the blob data will be purged lateron, and I do not want to have the main database file grown too much.

asked 15 Oct '18, 12:27

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 15 Oct '18, 12:28

> do not want to have the main database file grown too much

Why is it ok to put the blobs in a secondary dbspace but not the system dbspace? (no, I am not accusing you of Fat Shaming :)

(19 Oct '18, 17:04) Breck Carter
Replies hidden

It's a particular situation: The CLOB data will only be needed for some time (probably a few weeks) and will then be discarded. As it needs a significant amount of space (almost half the size of the main database file), I just don't want to increase the main file that much. So, I can later simply drop the new dbspace and have a still fitting main file without the need to rebuild...

(20 Oct '18, 13:47) Volker Barth

That is an excellent idea for dealing with huge amounts of data that must be stored in permanent tables on a temporary basis: DROP TABLE followed by DROP DBSPACE when it is no longer needed.

The need to store temporary data in permanent tables sounds like a contradiction but it is fairly common in practice: the words "temporary" and "permanent" describe different time frames, and permanent tables have some huge advantages like persistence and recoverability.

So thanks, Volker, I have just added this item to the Foxhound to-do list: Investigate the applicability of DROP DBSPACE to various Foxhound functions.

(22 Oct '18, 08:49) Breck Carter

The extension pages are stored in the same dbspace as the other table pages.

Are you updating or deleting blobs? If so, perhaps the increased file size you are seeing is the checkpoint log. The checkpoint log at the end of the system dbspace is used for saving images from all dbspaces. You can use db_property('CheckpointLogSize') to find how many pages in the system dbpsace file are being used for the checkpoint log.

permanent link

answered 15 Oct '18, 12:42

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

edited 16 Oct '18, 03:07

Volker%20Barth's gravatar image

Volker Barth

Ah yes, the checkpoint log has grown that much... - although I have just used LOAD TABLE to insert these blobs, so there should not have been any blob updates...

(16 Oct '18, 03:11) Volker Barth
Replies hidden

Insertion doesn't usually generate many checkpoint log pages since newly allocated pages don't get a preimage stored in the checkpoint log (there is no preimage for a newly allocated page). That's why I asked if you were updating or deleting. Index pages will get modified for inserts though.

(16 Oct '18, 07:00) John Smirnios

Thanks again, John – Then I guess this is due to modified index pages because I created several indixes before loading the data, and loading will have led to modified index pages. So I'm thinking about delaying the index creation after the initial loading...

(16 Oct '18, 07:54) 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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 15 Oct '18, 12:27

question was seen: 3,011 times

last updated: 22 Oct '18, 08:49