sysusages shows segmap=1

[neal_stack2]

Hello,

I've recently seen a couple of user databases that show segmap=1 at a couple of different customer sites. I found the following SAP knowledge base article How to change sysusages fragments with segmap 1 or 2 to segmap 3 - SAP ASE

I haven't figured out a way to make my own database so that it shows up in sysusages with segmap=1.

Based on the existence of this article, I'm not sure if it is normal to have segmap=1 or if this article illustrates how to change it so that segmap=3?

1. Does this article mean that there was some kind of bug or user error that caused the database to be created with segmap=1 and that users should generally follow this procedure to change it?
2. Is there an easy way to create a database so that sysusages shows segmap=1?

Thanks,

Neal

[bart_van_kuijk · Accepted solution]

Hi Neal

I don't think this article referred to a bug. A value of segmap of 1 or 2 is perfectly valid, though very rarely used these days. If memory serves, there was a recommendation in the one of ASE P&T or Admin courses to split the 'system defined' segments (default (=data), log and system out to avoid contention, particularly when there were many objects dropped and recreated (this may have been aimed at tempdb espcially).

You can't create a database from scratch with those segmaps. Once you created the database, you have to run sp_dropsegment to make change that sets segmap of 1 or 2. I copied a small example, created 3 devices data1 (vdevno 3) , sys1 (vdevno 4) and log1 (vdevno 5) to have each just have 1 of the 'system defined' segments.

To echo what is said in the article, be careful with manually updates to system tables. The extent and drop SPs come with safety checks.

1> create database seg_check on data1=10, sys1=2 log on log1=2
2> go
1> select segmap, vdevno from sysusages where dbid=4
2> go
segmap vdevno
------ -----------
3 3
3 4
4 5

1> use seg_check
2> go
1> sp_dropsegment "system", "seg_check", "data1"
2> go

1> select segmap, vdevno from sysusages where dbid=4
2> go
segmap vdevno
------ -----------
2 3 => data1 now only default, with segmap 2
3 4
4 5

1> sp_dropsegment "default", "seg_check", "sys1"
2> go

1> select segmap, vdevno from sysusages where dbid=4
2> go
segmap vdevno
------ -----------
2 3 => data1 now only default, with segmap 2
1 4 => sys1 now only system, with segmap 1
4 5

HTH

Bart van Kuijk

[neal_stack2]

Thanks Bart, this is very helpful!