INTRODUCTION
The layout of Sybase SQL and ASE Server databases cannot be
easily altered. A database, once created and altered, carries the
logical layout of data and log segments for the duration of its
life. A major re-rganization of data and log segments is
possible only by completely rebuilding the database using bcp
or a similar method. A limited reorganization is possible via
the sp_logdevice stored procedure.
A reorganization of a database’s logical layout is not achievable
via dump and load. A database created for load must be laid out
in exactly the same way as the database that was dumped, in order
to retain its layout of data and log segments. In a simplest scenario,
a database created for load must be created and altered in exactly
the same way, and in the same order, with the same size parameters,
(though not necessarily on the same database devices,) as the database
that was dumped.
This note describes how to create a database and load a dump of
when the logical layout may be unknown. (It may also be used to
create and load a database when the logical layout is known.)
The issues od segments are described in much detail in a white paper
entitled: “Segment Remapping with load database When Moving a Database”,
doc Id: 1324. The document is available on the Sybase Web site
www.sybase.com.
BACKGROUND INFORMATION
The internal layout of a database’s data and log pages is stored
in the system table in the master database called sysusages.
If you run the following query:
select * from sysusages where order by lstart
…you’ll see output similar to the following:
dbid segmap lstart size vstart pad unreservedpgs
—- —— —— —– ———– —— ————-
1 7 0 1536 4 NULL 208
1 7 1536 1024 3588 NULL 1016
1 7 2560 10240 6660 NULL 10240
2 7 0 1024 2564 NULL 632
2 7 1024 14336 83886082 NULL 14336
3 7 0 1024 1540 NULL 632
4 3 0 46080 100663298 NULL 40936
4 4 46080 15360 117440514 NULL 15352
4 3 61440 1024 100709378 NULL 512
4 3 62464 2048 100710402 NULL 1024
Each row in sysusages is referred to as a fragment and
represents a contiguous chunk of database space on
a single database device.
segmap 7 indicates data and log are mixed,
3 = data only, 4 = log only, any higher values indicate
user defined segments, any values lower than 3 indicate
that segments have been dropped using sp_dropsegment.
size is expressed in 2K pages; divide it by 512 to obtain megabytes.
lstart indicates the logical start of the segment.
vstart points to the location on the disk. The vstart value
falls in between the low and high column values in the
sysdevices table (a join between these two tables will show
the device names and paths.)
Each row in the sysusages table represents one size specification
with a create or alter statement. There is an exception to this.
When the server is unable to locate contiguous disk space to match
a size specification, it breaks the size into multiple smaller
values and adds multiple fragments to the sysusages table.
These fragments are still located on the same database device.
If any of the sizes end in a 1/2 megabyte, a special situation exists.
When the server creates a database device and the size requested
is a page larger than the space available on the disk partition,
disk init does not fail. Instead, a half megabyte chunk is allocated.
If subsequently a database is created or altered to completely fill
this database device, it will end up with a fragment that ends with
a 1/2 megabyte. This situation, a quirk in the software, should be
avoided, because as we stated, a database cannot be easily re-organized.
Make sure that the size specified with disk init, is available.
If by chance a database device has been created with a 1/2 megabyte size,
do not fill the device to the last page. On IBM AIX, in order to accomodate
the Logical Volume Control Block, specify size with the disk init
command as one megabyte less than the size of the partition.
DUMP AND LOAD
Let’s suppose that we wish to dump database with dbid of 4 and load
it into another server. Or, we have a dump of this database,
no information about its layout (the source server is unavailable,)
and want to load it into another server. How to proceed?
The key to success is creating the target database with the same
logical layout as the source database contained in the dump.
(This is why Sybase recommends keeping (1) database create and alter
scripts, and (2) bcp and hard copies of key system tables such as
sysdatabases, sysdevices, sysusages and syslogins.)
Keep in mind that if you have a database with 1/2 megabyte chunks
in the sysusages table you will have to create the database devices
in exactly the same way on the target server as they had been
created on the source server. Otherwise, if 1/2 megabyte fragments
do not exist, the database device sizes, as long as they are large enough
to accomodate the sizes of database fragments, do not come into play.
Let’s examine the layout of database with dbid = 4 in the above example.
The database consists of 4 fragments: 90 mb of data, 30Mb of log,
followed by 2 and 4 mb fragments of data. This is the permanent
logical organization of the database: logical page 46079 will
always be a data page and it will always be followed by the
first page of the log, logical page 46080. As we stated, it is not
possible to alter this layout. Only additions to the bottom
(with appropriate ALTER DATABASE commands) are possible.
SCENARIO 1: The source sysusages table is available.
If the create and alter scripts are available, edit them to
update the database device information (presumably, the
device names are different on the target server, but they can be the same)
and run them. Then, load the database.
If the create and alter scripts are unavailable, examine the
source sysusages table. We are interested in two columns:
segmap and size. Create and alter the database in the same order
as the fragments appear in the above query (ordered by lstart)
specifying the same size parameter for each fragment.
Treat any rows with segmap values other than 3, 4 or 7 as data
– these values, stored in the dump, will be overwritten anyway.
Note that any adjacent rows with identical segmap values
may be combined in the create and alter statements into
fewer rows. Note, also, that the fragments are device
independent – they may be created on any database devices
which have the available space. In this way, a limited
reorganization of a database is in the end possible.
Once the database has been created, compare the sysusages
tables of the source and target database before loading the dump.
Except where fragments have been combined, the size
columns of rows and the order in which they appear should match exactly.
SCENARIO 2: The source sysusages table is unavailable.
When the layout of a database contained in a dump is unknown,
you may need to load the dump twice.
First, create the database as well as you can, estimating the sizes of
the segments. Create it with as few fragments as possible.
Load the database. Next, examine the sysusages table:
select segmap, size from sysusages where dbid = n order by lstart
The output does not necessarily show how the source database was organized,
because some source fragments may have had to be split up to fit the
database as it was created on the target server. These fragments will
be shown as adjacent with identical segmap values.
If the placement of database segments on database device is unsatisfactory
drop the just loaded database and proceed with the steps below.
Next, create and alter the database in the same order
as the fragments appear in the above query (ordered by lstart)
specifying the same size parameter for each fragment.
Treat any rows with segmap values other than 3, 4 or 7 as data
– these values, stored in the dump, will be overwritten anyway.
Note that any adjacent rows with identical segmap values
may be combined in the create and alter statements into
fewer rows. Note also, that the fragments are device
independent – they may be created on any database devices
which have the available space. In this way, a limited
reorganization of a database is in the end possible.
Finally, load the database.
END NOTES
To assure the success of a load it is best to create the
database for load the same size as the source database.
(The target database may not be smaller than the source database.)
If you need to enlarge a database, alter it after the load
has completed.
What then is possible? Since in the dump and load scenario,
database fragments are independent of the devices on which
they reside, they can be moved among the devices, dispersed
among larger number of devices or else, consolidated on fewer devices.
Remember, the value and order of the logical start of fragments,
the lstart column, is critical, but the vstart column indicating
the actual physical location of the fragment is enterily irrelevant
to this undertaking.