Automatic Physical Database Rearrangement on Load

When loading a dump of a database that had a segregated log and data segment, SAP ASE rearranges the physical layout of the target database to ensure physical separation of the log and data segments.

How load Handles Fragments in SAP ASE 15.7 SP100 and Later

To avoid data and log fragments on the same device, SAP ASE versions 15.7 SP100 and later rearrange the physical layout of the database to accommodate for the log and data distribution in the database dump:

Example of physical database rearrangement on loading

Using the same create database command used earlier, the following example shows the results when there is insufficient 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 SAP ASE now handles loading a database during which physical rearrangement has occurred. SAP ASE 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.  

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. SAP ASE 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 SAP ASE installation using a 4K bytes page size

Results of example

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