Progress Database Structures and How to Create Them
Posted on: May 29, 2009 | By: SuperUser Account | QAD Business Process
With each Progress database there are 2 types of database structures: The physical structure, as layout on the disk system, and the internal logical structure, how the database areas are defined.
Physical Structure
The database physical structure can span multiple directories and have many files associated with it.
Physical Files:
Database files – The parent file of a database has an extension of .db. This file contain only data related to the physical structure of the file and data internal to the operation of the database. The extents (physical files that make up the data areas) have an extension of .dnn where nn is a number from 1 to 99. These are the physical files that hold the data and indexes.
Before image files – thses files are used to store in flight transactional data has an extension of .bi if single extent, and .bnn if multiple extents.
After Image files – these files are used to store the rollback data, they have an extension of .ai if single extent or ann if multiple extents.
Each extent can be either fixed in size or variable in size, although you are limited to one variable extent per area. The normal maximum size of an area is 2 gigabytes, although certain file systems that allow large files can be larger.
The database is further divided into areas. Each area has its own set of physical extents. This allows Progress to store huge amounts of data – depending on configuration this can be into the petabyte range. Each extent can be placed anywhere on a system. Although network drives should be avoided.
Areas 1- 6 are system areas. User areas start with 7. The schema is in area 6.
Sample Database.
Mydatabase.db
Mydatabase_6.d1 Area 6 Schema Fixed Size
Mydatabase_6.d2 Area 6 Schema Variable size
Mydatabase_7.d1 Area 7 Data Fixed Size
Mydatabase_7.d2 Area 7 Data Variable Size
Mydatabase_8.d1 Area 8 Indexes Fixed Size
Mydatabase_8.d2 Area 8 Indexes Variable Size
Mydatabase.b1 Before Image Fixed Size
Mydatabase.b2 Before Image Variable Size
Mydatabase.a1 After Image Fixed Size
Mydatabase.a2 After Image Fixed Size
Mydatabase.a3 After Image Fixed Size
Mydatabase.a4 After Image Fixed Size
Each area can be either fixed length or variable length. The restriction being only the last data extent can be variable length. The same hold true for before image files. After image files may be either fixed or variable.
`
Physical extents are be defined with a logical block sizez, either 1, 4, 8 K. The block size to use depends on the OS.
Each area can contain tables, indexes or both. Tables and indexes are limited to a single area in a data base.
* BEST PRACTICE: Always separate data and indexes into separate areas.
You can specify a limit to the number of records in a block. Unless you know that you are going to have very small records use 32. This is a good default – balancing wasted space in a block and block usage.
Progress Version 10 and later supports 2 types of extents:
Normal – a block can contain records from multiple tables and indexes.
Type II (V10 only) – a block can contain records from only a single table and use a cluster.
* BEST PRACTICE Areas Containing tables should use max number of blocks(512) for cluster size. For areas containing indexes use 8 blocks per cluster
When the database is designed the following rules should be followed:
• Arrays should be RAID 1+0 Striped & Mirrored – Avoid RAID 5 if possible.
• After Image and Data extents should NOT be on the same set of physical drives.
• If not using large files – create extents of 1 Gigabyte.
• Transactional data areas should be on the fastest drives. Static data areas should be on the slowest.
• Avoid placing Before Image extents on the same physical drives as the data extents.
• Use 4K block for Linux & Windows. 8K block for Unix.
• Always have a variable extent for each area.
• Backup directories should not be on the same physical drives as the database.
• Use 32 records per block unless data says otherwise.
• For Type II extents cluster size should be large for data extents and small for index extents.
Creating and extending a database using the prostrct command
Create a Database
The prostruct command will create a database from a predefined structure file. The structure file is named – db-name.st.
Below is a sample structure file:
This is not necessarily the way a DB should be sized rather is shows the various types of entries that are available in a structure file.
# Sample Structure Description File: largedb.st
#
# largedb.st to create database largedb with large file processing enabled.
#
# A fixed length bi file of 1GB and a variable length bi file with a
# maximum size of available disk
#
b tests/largedb.b1 f 1048576
b tests/largedb.b2 v 4194304
#
# SCHEMA AREA with a fixed length file of 3GB and a variable length file with
# a maximum size of 3GB.
#
d “Schema Area”:6,64 tests/largedb.d1 f 3145728
d “Schema Area”:6,64 tests/largedb.d2 v 3145728
#
# TABLE AREA with a fixed length file of just over 2GB and a variable
# length file with a maximum size of available disk using 512 block clusters.
#
d “Table Area”:7,64;512 tests/largedb_7.d1 f 2097280
d “Table Area”:7,64;512 tests/largedb_7.d2
#
# A fixed length ai file of 2GB and a variable length file with a
# maximum size of – available disk.
a tests/largedb.a1 f 2097152
a tests/largedb.a2
#
Extent paths may be either fully qualified path names or relative path names.
Using the prostruct command.
The syntax for creating a database is as follows:
$DLC/bin/prostrct create /path/db-name -blocksize 4096
The prostruct command will look for a file with a db-name.st extension in the path that the database is to be created. The database will be created with a 4K block size.
Depending on the size of the database this may take a while to execute. The database create function will pre-format all fixed length extents and create variable extents with a 32K or cluster size (whichever is larger).
Load Meta Schema into the void database.
$DLC/bin/procopy $DLC/empty4 Mydatabase
Other ways to create a database:
If you have an st file the following commands will use it to create a new database.
Procopy
Prorestore
Prodb