Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Consolidating multiple database extensions

    Hi,

    We have a growing situation that we don't know how to work around. As our databases grow in size we've been extending them onto additional devices.

    We want to be able to consolidate these extensions back onto one device again. i.e. After a database has 3 - 1000 MB extensions we want to be able to
    1. dump the database
    2. Create a new 3000 MB device/database
    3. Load the dumped database into it.
    Life is good.

    But!

    What is happenning is that no matter what we do, Sybase will inexplicably insert a LOGSEGMENT onto the device that we wanted dedicated to DATA.

    Questions:
    1. How do we prevent this from happenning?
    2. How do we safely remove this logsegment from our data device.

    We've the sp_logdevice command, etc but it just doesn't seem to work right.

    Any ideas would be greatly appreciated

    Les Guyse
    Varian Medical Systems

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    load database will consolidate contiguous fragment devices
    whose segmap (list of segments assigned to a device)
    are equal. In the simplest case there are only two
    possible segmaps: log and data. ASE will consolidate
    contiguous data fragments, on one side, and contiguous
    logsegment framents (this should be infrequent), on the
    other.

    The tricky arena of device fragments upon a load db
    is covered in several Tech Docs. I can't remember any,
    but I have this URL for a posting of mine to Usenet's
    comp.databases.sybase:
    http://groups.google.com/groups?selm...&output=gplain

    Hope it helps,
    Mariano Corral

  3. #3
    Join Date
    Jan 2003
    Posts
    5
    We work in this area, please see our web site at www.etlsolutions.com.
    Essentially we have an IDE, Transformation Manager, which can define maps between most data structures including relational schema using JDBC. The transformation is deployed as a Java jar. We use this in different areas but have worked with data warehousing tasks, data cleaning and migration etc.

    regards

    Wynne Rees (wr@etlsolutions.com)

  4. #4
    Join Date
    Jan 2003
    Posts
    28

    Re: Consolidating multiple database extensions

    The question is in step 2 in your email, how do you create your new 3000mb db.

    If you don't recreate your db in exactly the same fashion in which you initially created it, you will have the problems you are encountering.

    Suppose you created the initial db as:
    create database a on datadev=30
    log on logdev=20
    alter database a on datadev=10
    alter database a on datadev=10
    alter database a on datadev=10

    you can't create a new db as:
    create database a on datadev=60
    log on logdev=20

    since when the db is dumped each page is dumped in order. So when you load the db back in to the new db that wasn't created with data & log in the same order, the data pages & log pages aren't placed where you'd think it should go

    you need to create it as:
    create database a on datadev=30
    log on logdev=20
    alter database a on datadev=30

    you could use sp_extendsegment to move your data segments to to the data devices(and the same for log segments to log devices) and then issue sp_dropsegments for log segments on data devices and for data segments on log devices, to place the segments on their proper devices.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •