Type-1 -> Type-2 Conversion/creation

Hello All,

I am a bit confused with type-1 to type-2 conversion/creation. Please check below for the same:

1. If we take full back-up of type-1 database and restore it over type-2 structure file (new created structure file from type-1 structure file, number of database blocks is 8) then will it be a database of type-2 storage areas.

I tired doing that and am able to create database with new type-2 structure file but when I see table detail under Data dictionary -> Reports -> Detailed table then there are many tables (more then 8) under single area define with 8 database blocks.

2. If we dump the database and load it with new type-2 structure file then will it be a database of type-2 storage areas.

I tried this as well but found the same (mentioned above) that there are many tables under single area define with 8 database blocks.

3. As per my understanding, if our cluster has 8 database blocks then our area associated with the cluster should have 8 database tables associated with it (one database block for one table), is this correct?

Kindly suggest.

Regards,
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
You cannot get from Type I storage to Type II via backup/restore. You must dump and load.

When you dump from the source database, you dump both the data and the schema. (I am simplifying; there is more to it than this.) The assignment of tables and indexes to storage areas is in the schema. So if you create a target database with new area names, you will have to edit your schema file (dbname.df) to reflect the new area names, so that your tables and indexes go where you expect. If a table or index has no area assignment in the schema file, it will be assigned to area 6, the schema area. You do not want to have any application tables or indexes in the schema area.

As per my understanding, if our cluster has 8 database blocks then our area associated with the cluster should have 8 database tables associated with it (one database block for one table), is this correct?
No. For each storage area, the structure file specifies two storage characteristics: the maximum number of records per block and the number of blocks per cluster. In Type II, storage is allocated a cluster at a time. Each cluster contains data associated with a single storage object (i.e. table or index), and each storage object occupies a minimum of one cluster.

There are many threads in this forum dealing with dump and load best practices and storage area design. I encourage you to read those.

Before doing so however, I strongly encourage you to read the Database Essentials manual and the introductory chapter of the Database Administration manual. They explain information you need to know to get value out of the tuning concepts discussed in the forum. Don't skip this reading. At this point you are not ready to dump and load a database or design a database structure.
 

RealHeavyDude

Well-Known Member
You cannot use backup/restore to migrate from type I to type II storage area. The restore requires the area definitions in the target regarding type, and records per block setting to be identical to the database from which you've taken the backup. The only thing that can be different, given that each stroage area provides enough space, is the number and the location of the extents. Using backup/restore you can change the number and location of the extents but not the characteristics of a storage area.

The only way to migrate from type I to type II storage areas is dump & load. You can dump & load ascii ( via data administration tool ) or binary with the proutil command line utility.

A cluster size of 8 blocks means that at least 8 blocks are reseverd for each table with the blocks being adjacent to each other in the storage area. Still, you can have any number of tables per storage area. Therefore, without containing any records, your storage area will be as large as the number of tables multiplied with the block size. The only variable in the formula is the number of tables. That is the reason why an empty type II storage area consumes more disk space that a type I area does. When you start populating the storage area, for example, the first 8 blocks in the 1st cluster are used and, when these are filled, a new cluster with 8 blocks gets created and filled and so on.

There are numerous posts on this site and on the internet to be found on how you should split the tables across the storage areas. You should also think about which storage areas you need and how the tables should be split across them. Type II storage areas have numerous benefits over type I, therefore, for a starter, it is an execellent idea, to just migrate the storage areas. To take full advantage of an optimized storage model, the next step, moving tables into storage area where they fit best, is required.

The most important rule is that tables and indexes should reside in their distinct storage areas - meaning, do not mix tables and indexes into the same storage area.

Heavy Regards, RealHeavyDude.
 
Top