Automatic Physical Database Rearrangement on Load

When loading a dump of a database that had a segregated log and data segment, Adaptive Server 15.7 SP100 and later will now rearrange the physical layout of the target database to ensure physical separation of the log and data segments.

How load Handled Fragments in Releases Earlier than 15.7 SP100

A disk fragment, or fragment, is a contiguous number of database pages that are stored on a single database disk and is represented by a single row in the sysusages catalog. In previous releases, the way load handled disk fragments of the target database could result in undesirable results in the physical layout of the database such as:

Fragments of the data segment and log segment being mapped to the same device
An increased number of fragments, when compared with the fragments that made up the source database
A database whose layout is not possible to re-create with create database

In versions earlier than 15.7 SP100, to move a database (the source database) to new devices on your system or to rebuild a database on the old devices, you needed to first create the new database (the target database), then load a dump of the source database onto the target database. Adaptive Server would preserve the logical structure of the source database when it was loaded onto the target database. That is, every database page in a fragment of the source database occupies the same logical space in the target database, irrespective of the physical location of the fragment in the target database to which it is copied. This can result in parts of the data segment and the log segment being physically located on the same device. In order to prevent this, you needed to create a target database that was identical to the source database by:

Creating the target database fragments of exactly the same size as the source database and
Creating the target database with the fragments in the same order as the source database and
Creating the target database with fragments that belong to the same data segment or log segment as the source database

Example

The following example demonstrates how loading a dump onto a database created with fragments of different sizes, or fragments in a different order than in the source database, increases the number of fragments, and places data and log on the same device. This example is based on an Adaptive Server installation using a 4K bytes page size

Creating source_db

The source database, source_db, was created as follows:

1> create database source_db  
2> on dev1 = 6 
3> log on dev2 = 3 
4> go

Later, two more data fragments on different devices were added to the database:

1> alter database source_db  
2> on dev3 = 4, 
3> dev4 = 4 
4> go

A select from sysusages shows four fragments for source_db:

select dbid, segmap, lstart, size, vdevno, vstart, location 
from master..sysusages where dbid=db_id("source_db")   
go

dbid  segmap  lstart  size  vdevno  vstart  location    
---- -------- ------ ------ ------- ------ ---------
 4      3        0     1536    1       0       0         
 4      4     1536      768    2       0       0         
 4      3     2304     1024    3       0       0         
 4      3     3328     1024    4       0       0     
(4 rows affected)

Note: The device identification number is indicated by the values of vdevno. dev1 has a vdevno value of 1, dev2 a value of 2, dev3 a value of 3, and dev4 a value of 4.

Creating target_db

target_db is created differently than source_db. The database is created in a single command on all of the devices at once instead of being altered onto the devices later and with a different size:

1> create database target_db    
2> on dev1 = 10   
3> log on dev2=10   
6> go

1> select dbid, segmap, lstart, size, vdevno, vstart, location   
2> from master..sysusages where dbid=db_id("target_db")   
3> go

dbid  segmap  lstart  size  vdevno  vstart  location    
---- -------- ------ ------ ------- ------ ---------
 7      3        0     2560    1      3072       0         
 7      4     2560     2560    2      1536       0

Loading the dump from source_db onto target_db

The database source_db is dumped and loaded ontotarget_db:

1> load database target_db from "/dumps/source_db.dmp"   
2> go

Adaptive Server keeps the logical distribution of pages from the dump when they are copied to the target, which results in the following:

1> select dbid, segmap, lstart, size, vdevno, vstart, location   
2> from master..sysusages where dbid=b_id("target_db")   
3> go

dbid  segmap  lstart  size  vdevno  vstart  location    
---- -------- ------ ------ ------- ------ ---------
 7      3        0     1536    1      3072       0         
 7      4     1536      768    1      6144       0         
 7      3     2304      256    1      7680       0         
 7      3     2560     1792    2      1536       0         
 7      3     4352      768    2      5120       0

Results of example

Loading the dump of source_db onto target_db caused the following undesirable results:

The number of disk fragments increased from two to five.

Log and data are on the same physical device (with vdevno = 1). The log segment (segmap = 4) and the data segment (segmap = 3) are segregated at the logical page level, but not at the device level.

dbid  segmap  lstart  size  vdevno  vstart  location    
---- -------- ------ ------ ------- ------ ---------
 7      3        0     1536    1      3072       0         
 7      4     1536      768    1      6144       0         
 7      3     2304      256    1      7680       0         
 7      3     2560     1792    2      1536       0         
 7      3     4352      768    2      5120       0

You can see from the above, a log fragment is on device vdevno = 1. However, there are two data fragments, (the first and third row) that are also on vdevno = 1. Meaning that the log segment is on the same device as the data segment.

How load Handles Fragments in Adaptive Server 15.7 SP100 and Later

To avoid data and log fragments on the same device, Adaptive Server now rearranges the physical layout of the database to accommodate for the log and data distribution in the database dump. These Adaptive Server changes have been made:

Adaptive Server checks that there is as much disk space for data and log on the target database as there was in the source database for data and log respectively. In versions earlier than 15.7 SP100, Adaptive Server only checked that the total size of the target database was large enough to accommodate the database dump of the source. Adaptive Server now the checks that there is enough disk space for log and data segments separately. If any of these segments in the target database are too small, the following error will be raised:
The %s segment in the target database '%.*s' is too small (%d MB) to accommodate the %s segment from the dumped database (%d MB). The target database will be rearranged not to mix up data and log
The target database is rearranged to prevent data and log fragments on the same device.

Example of physical database rearrangement on loading

Using the same create database command as was used earlier, the following example shows the results when there is not enough space to accommodate the data segment when loading a database:

load database target_db from '/dumps/source_db.dmp'   
go   
Msg 3185, Level 16, State 2:   
Line 1:   
The data segment in the target database 'target_db' is too small (10 MB) to 
accommodate the data segment from the dumped database (14 MB).

Because the data segment in the source database is 14 MB and only 10 MB in the target database, the space must be increased before loading the database:

1> alter database target_db on dev1=4   
2> go   
Extending database by 1024 pages (4.0 megabytes) on disk dev1

Now load the database:

1> load database target_db from '/dumps/source_db.dmp'   
2> go       
1> select dbid, segmap, lstart, size, vdevno, vstart, location   
2> from master..sysusages where dbid=b_id("target_db")   
3> go

The sysusages rows for the target database shows how Adaptive Server now handles loading a database during which physical rearrangement has occurred. Adaptive Server has placed the data segment (with a segmap of 3) on the device with vdevno = 1, and the log segment (with a segmap of 4), on the device with a vdevno = 2. This means that the log and data segments are now on separate devices.

dbid  segmap  lstart  size  vdevno  vstart  location    
---- -------- ------ ------ ------- ------ ---------
 7      3        0     1536    1      3072       0         
 7      4     1536      768    2      1536       0         
 7      3     2304     1024    1      6144       0         
 7      3     3328     1024    1      8192       0         
 7      4     4352     1792    2      3072       0

The rearrangement of these pages and segments has left the same logical ordering, but a different physical layout. All pages in vdevno = 1 are data pages and all pages in vdevno = 2 are now log pages. The new pages from 4352 to 6143 that did not exist in source_db, because they have been created in vdevno = 2 which is the device holding the log, have become log pages as well.