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.