Skip to Content

sysusages shows segmap=1

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 01, 2017 at 03:41 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01, 2017 at 05:53 PM

    Thanks Bart, this is very helpful!

    Add comment
    10|10000 characters needed characters exceeded