Tablespaces

This section discusses factors related to tablespaces.

PeopleSoft provides DDL scripts to create a database, and set database manager and database tuning parameters. These scripts are on the PeopleSoft installation file server in the \scripts directory. Run the following scripts:

\scripts\createdb.sql -- creates DB2 LUW database.
.
\scripts\xxddldms.sql -- creates DMS (Data Managed Storage) tablespaces

Where xx is the product identifier, such as HR for PeopleSoft HCM or FS for PeopleSoft Financials and Supply Chain Management.

Create all tables and indexes in Data Managed Storage (DMS) tablespaces using PeopleSoft standard tablespace names as described in the installation guide. This storage option, as oppose to System Managed Storage (SMS), is appropriate for a database that you plan to change and grow. DMS is appropriate for a system test or production database.

Note: DROPPED TABLE RECOVERY feature is turned off in the xxddldms.sql script to avoid performance issue when dropping large number of tables. This feature can be turn on again with ALTER TABLESPACE command.

Here are some installation guidelines for manually creating your PeopleSoft database and tablespaces:

On the database server, edit and run CREATEDB.SQL to create a database and default tablespace USERSPACE1. Note that this script assumes you will use Circular Logging; if archival logging is desired, you must make the necessary changes.
On the database server, edit the DMS script /sql/hrddldms.sql. Instructions for editing this file are contained inside the file. This script creates all the PeopleSoft standard tablespaces.
In Windows, use Data Mover to create and populate tables and indexes. In Data Mover, the command line below—if it exists in the Data Mover script—should either be removed or commented out (disabled) in the Data Mover script (the ‘;’ in position 1 disables the command):
```
; set space * as USERSPACE1 ;
```
Note: Disabling the above command causes Data Mover to use PeopleSoft’s standard tablespace grouping strategy.

See the product documentation for PeopleSoft 9.2 Application Installation for DB2 for Linux, UNIX, and Windows for more information.

DMS tablespaces may be created as either COOKED Files System or RAW Storage Devices. PeopleSoft provides DDL script /sql/hrddldms.sql to support DMS COOKED Files System.

PeopleSoft does not provide a tablespace script to support the Raw device, but you can create the RAW device with the proper Operating System command and the following DB2 UDB command:

CREATE TABLESPACE PSAPP MANAGED BY DATABASE USING
         (device '/dev/data1_lv' 20000)

On AIX, the COOKED File System refers to the Journal File System (JFS). On Windows, the COOKED Files System refers to NTFS.

Note: There is a roughly 5-10% performance gain on RAW device over COOKED file system on tablespaces which are frequently being updated. However, it is generally much easier to administer a COOKED file system than a RAW device.

For system test and production databases, PeopleSoft recommends that you consider tailoring the Create Database statement to override the DB2 LUW default tablespace definitions for SYSCATSPACE and TEMPSPACE1. An example of this is provided below, where CATALOG Tablespace defines the SYSCATSPACE and TEMPORARY Tablespace defines TEMPSPACE1:

CREATE DATABASE db2-database-name ON dir-name|drive COLLATE USING IDENTITY  \
		CATALOG TABLESPACE MANAGED BY SYSTEM USING 
			(‘/cat-dir-name‘ )
			EXTENTSIZE 16  PREFETCHSIZE 32
		TEMPORARY TABLESPACE MANAGED BY SYSTEM USING 
			(‘/temp-dir-name’)
         		EXTENTSIZE 8

Note: The above tablespaces may be defined as DMS tablespaces. If you omit these tablespace definitions, DB2 LUW will create these tablespaces in the file system directory denoted by dir-name.

DB2 LUW offers the “AUTORESIZE” option for use with DMS table spaces so that the database system can automatically manage the allocation of additional space when a previous limit has been reached. PeopleTools supports the use of AUTORESIZE.

The AUTORESIZE option enables database administrators to create table spaces with an ample yet reasonable initial size and then specify the increment by which the system enlarges the table space when necessary. AUTORESIZE is transparent to any application connected to the database, and because it automatically manages table space size based on the specified configuration settings, database administrators do not need to enlarge table spaces manually on an ad hoc basis.

Note: AUTORESIZE is only available for table spaces within database-managed spaces (DMS). However, PeopleTools, as a standard, only creates DMS table spaces, so this restriction does not apply within the PeopleTools context. PeopleTools does not create system-managed spaces (SMS).

Enabling AUTORESIZE

The SQL parameters for enabling and configuring auto-resize are:

Parameter	Description
AUTORESIZE	YES \| NO Indicates whether auto-resize should be enabled for a table space. Disabling auto-resize is not recommended.
INCREASESIZE	K \| M \| G Specify the size of the increments by which the system should increase the table space size. Use an integer value in either kilobytes (K), megabytes (M), or gigabytes (G). Note: This value can also be expressed in terms of a percentage by which to increase the table space size. However, expressing the increase size value in terms of a percentage is discouraged, unless you have detailed knowledge of how DB2 calculates the percentage. If you do not set the percentage to a value that optimally increases table space size based on the current size and the amount of data typically inserted in your system, you may incur undesired amounts of wasted space.
MAXSIZE	K \| M \| G \| NONE Specify a maximum size that the table space can reach. Use an integer value in either kilobytes (K), megabytes (M), or gigabytes (G). NONE indicates that the table space can grow to the limit imposed by the file system.

Parameter

Description

AUTORESIZE

YES | NO

Indicates whether auto-resize should be enabled for a table space. Disabling auto-resize is not recommended.

INCREASESIZE

K | M | G

Specify the size of the increments by which the system should increase the table space size. Use an integer value in either kilobytes (K), megabytes (M), or gigabytes (G).

Note: This value can also be expressed in terms of a percentage by which to increase the table space size. However, expressing the increase size value in terms of a percentage is discouraged, unless you have detailed knowledge of how DB2 calculates the percentage. If you do not set the percentage to a value that optimally increases table space size based on the current size and the amount of data typically inserted in your system, you may incur undesired amounts of wasted space.

MAXSIZE

K | M | G | NONE

Specify a maximum size that the table space can reach. Use an integer value in either kilobytes (K), megabytes (M), or gigabytes (G).

NONE indicates that the table space can grow to the limit imposed by the file system.

How you enable AUTORESIZE depends on the status of your current implementation, as in, whether you are creating or upgrading a database, or just working with an existing database.

If you are creating a new PeopleSoft database or performing an upgrade on a PeopleSoft database, you use the Database Creation Wizard and the delivered DDL scripts to enable auto-resize as per the instructions in your PeopleSoft upgrade and/or installation documentation.

The DDL of the provided scripts and those created using the Database Creation Wizard is similar to the following CREATE TABLESPACE example:

CREATE TABLESPACE PTTLRG MANAGED BY DATABASE USING
(
 FILE '/data1/psdb2/ptdbname/PTTLRG.DBF'  10 M
) EXTENTSIZE 16 PREFETCHSIZE 48 DROPPED TABLE RECOVERY OFF
AUTORESIZE YES INCREASESIZE 10 M MAXSIZE NONE ;

In this example, the system creates the table space PTTLRG with an initial size of 10 Mb, with the AUTORESIZE option on, specifying that the database system will increase the table space size by 10 Mb each time a limit is reached.

Note: If you have already determined the appropriate initial size, increase size, and maximum size for table spaces at your site, edit the delivered scripts to reflect those values.

If you are working with an existing PeopleSoft database and not performing any database creation or upgrade tasks on the entire database, you can submit a SQL ALTER command to update any existing table spaces for which you want to enable this feature.

The ALTER syntax is:

ALTER TABLESPACE <name> AUTORESIZE YES INCREASESIZE <size> MAXSIZE <size> ;

Disabling Auto-Resize

By default, AUTORESIZE is enabled for all new and upgraded PeopleSoft databases. Because of its convenience and because the PeopleSoft system assumes AUTORESIZE is on, disabling AUTORESIZEis not recommended.

To disable the auto-resize option for a table space, issue a SQL ALTER statement using the following syntax:

ALTER TABLESPACE <name> AUTORESIZE NO ;

Determining Increase Size

To determine the appropriate increase size value for your table spaces, Oracle recommends first categorizing your table spaces into the following categories: small, medium, and large. These categories reflect the growth potential of the table space. For example, a small table space is one that is not expected to increase at the same rate or reach the same size as a large table space.

The following table provides some suggested increase sizes by category.

Category	Suggested Increase Size Range
Small	10 M – 100 M
Medium	300 M – 500 M
Large	700 M – 1000 M

Confirming that AUTORESIZE is Enabled

To confirm that AUTORESIZEis enabled and working as desired, use the DB2 table space monitor snapshot output. For example, assume you create the table space PSHRDATA with AUTORESIZEon. PSHRDATA, historically, is a table space that typically reached maximum space if not properly monitored and adjusted by database administrators. In this example, PSHRDATA is created with an initial space allocation of 1500 8k pages, using the following SQL:

CREATE TABLESPACE  PSHRDATA  PAGESIZE 8K MANAGED BY DATABASE USING
(
 FILE '/data1/psdb2/ptdbname/PTTREEIDX.DBF'  1500
) EXTENTSIZE 16 PREFETCHSIZE 48 BUFFERPOOL PSUBUFPOOL DROPPED TABLE RECOVERY OFF
AUTORESIZE YES INCREASESIZE 10 M MAXSIZE NONE ;

After a period of time in which you can assume large amounts of transactional data has been inserted into your database, connect to the database and show the detail report on the table spaces. Use the list tablespaces show detail command.

db2 connect to <db-name>
db2 list tablespaces show detail

A section of the output would appear similar to the following:

......
Tablespace ID                        = 45
 Name                                 = PSHRDATA
 Type                                 = Database managed space
 Contents                             = Any data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2768
 Useable pages                        = 2752
 Used pages                           = 2000
 Free pages                           = 752
 High water mark (pages)              = 2000
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 16
 Prefetch size (pages)                = 48
 Number of containers                 = 1
......

The current footprint (Total Pages) of PSHRDATA is now 2768 8k pages, well over the initial allocation. This clearly indicates that DB2 detected a request for additional free pages beyond the initial space allocation for PSHRDATA and automatically extended the table space.

Monitoring Table Space Size Allocation

While the AUTORESIZEoption greatly reduces the amount of manual intervention, database administrators should continue to monitor the size and growth of the following elements of your database to ensure that you are optimizing space usage:

File system
Table space container

Tablespaces

DDL Scripts

Using the PeopleSoft DMS Tablespace DDL

DMS Tablespaces: Cooked or Raw

System Catalog Tablespace and Other Initial Tablespaces

Optimizing Table Space Capacity With Auto-Resize

Enabling AUTORESIZE

Disabling Auto-Resize

Determining Increase Size

Confirming that AUTORESIZE is Enabled

Monitoring Table Space Size Allocation