Creating an Empty CES Database

About Creating an Empty CES Database

Database creation involves specifying values for the following:

  • Database NLS Setup

  • Registry Variables

  • DBM Configuration Parameters

  • DB Configuration Parameters

  • Buffer Pools

  • DB2 System Tablespaces

  • CES Tablespaces

Default values are available for all these parameters and you can accept these defaults and start creating the database. You can specify your own values while creating the database.

This task explains the procedure to create an empty CES Database:
  1. Log onas aspadm and run edm.sh from ${EDS_HOME}/bin directory. For additional information, see Using CES Database Manager.

  2. In the EDM main window, specify the database name to be created.

  3. Select Build > Create an Empty Database option. The first window of the database creation wizard appears. See Database NLS Setup .

  4. This wizard guides you through the database creation process. During this operation, you can do the following:

  • Click Next to proceed with the database creation wizard.
  • Click Cancel to cancel the database creation, and return to the EDM main menu.
  • clicking Finish to ccept all the default values, and start the database creation.
  • Click View Summary to see a summary of all the information.

Database NLS Setup

The first window in the database creation process is Database NLS Setup window. This table describes the fields:

Name Description
Territory This is a portion of the POSIX locale that is mapped to the country code for internal
processing by the database manager.

Select the country code to be used for database creation.
Code Set Refers to the encoding values for a character set that provides the interface between
the system and its input and output devices.

Select the codeset to be used for database creation.
You should enter a valid combination of CODESET and TERRITORY.
  • After specifying these values, click Next to proceed.

Registry Variables

The second step in the database creation process is specifying the registry variables. This table describes the variables:

Variable Description
DB2_HASH_JOIN Used to specify Hash Join as a possible join method when compiling an access plan. The default is YES.
DB2_PINNED_BP This variable is used to specify the database global memory (including buffer pools) associated with the database in the main memory on some AIX operating systems. Keeping this database global memory in the system main memory allows database performance to be more consistent.
DB2_RR_TO_RS This variable guarantees Repeatable Read (RR) isolation level by automatically locking the next key for all INSERT and DELETE statements and the next higher key value above the result set for SELECT statements.
DB2_SQLROUTINE_PREPOPTS This variable is used to customize the precompile and bind options for SQL procedures.

Note:Default values are available for all the registry variables.

  1. To modify the value, select a variable from the list, and then click Edit. A window with the default value appears.

  2. You can specify a new value, and then click OK to change the value for the selected variable.

    Note: To restore default values after making any changes, click Restore Default(s).

  3. Click Next to proceed with the next step in the database creation process.

DBM Configuration Parameters

The database manager configuration parameters

  • Affect the amount of system resources that will be allocated to a single instance of the database manager.OR

  • Configure the setup of the database manager, and the different communications subsystems based on environmental considerations.

All these parameters are independent of any single database stored under that instance of the database manager.

You can define and set the database manager configuration parameters in DBM Configuration Parameters window.

The default values for these parameters are displayed.

Modifying a Value

  1. Select a parameter from the list and click Edit. A window with the default value appears.

  2. You can specify a new value and click OK to change the value for the selected parameter

Adding a new parameter

  1. Click Add. The list of available database manager configuration parameters appears.

  2. Select a parameter from the list, define the value for this parameter and click OK to add this parameter.

    Note: To restore default values after making any changes, click Restore Default(s).

  3. Click Next to proceed.

DB Configuration Parameters

The next step in the database creation process is specifying the database level configuration parameters. You can define several parameters that are used to specify the amount of resources allocated to the database and improve the database performance or increase capacity. You can modify these parameters based on the type of activity in a database.

The default values for these parameters are displayed.

Modifying a value

  1. Select a parameter from the list and click Edit. A window with the default value appears.

  2. Specify a new value and click OK to modify the value.

    Name Description Default Values
    APPLHEAPSZ This is the Application Heap Size. It defines the
    number of private memory pages available to be used
    by the database manager on behalf of a specific agent or subagent.
    Range: 16 - 60000
    DB2: 256
    EDM: 2048
    APP_CTL_HEAPSZ1 This is the Application Control Heap Size. It
    determines the maximum size, in 4 KB pages, for the
    application control shared memory. Application control heaps are allocated from this shared memory.
    Range: 1- 64000
    DB2: 128
    EDM: 8000
    CATALOGCACHE_SZ This is the Catalog Cache Size. It indicates the
    maximum amount of space that the catalog cache can use from the database heap (dbheap).
    Range: 8- DBHEAP
    DB2: -1
    EDM: 5000
    DBHEAP This is the Database Heap. There is a one Database Heap per database and the database manager uses it on behalf of all the applications connected to the database. Range: 32 - 524288
    DB2: 1200
    EDM: 20000
    LOCKLIST This is the maximum storage for Lock List. This
    parameter indicates the amount of storage that is
    allocated to the lock list. There is one lock list per
    database and it contains the locks held by all applications concurrently connected to the database. Locking is the mechanism that the database manager uses to control concurrent access to data in the database by multiple applications. Both rows and tables can be locked. The database manager may also acquire locks for internal use
    Range: 4 - 524288
    DB2: 100
    EDM: 3353
    LOCKTIMEOUT This is the Lock Timeout. It specifies the number of seconds that an application will wait to obtain a lock. This helps avoid global deadlocks for applications. Range: 0 - 30000
    DB2: -1
    EDM: 90
    LOGBUFSZ This is the Log Buffer Size. This parameter allows you to specify the amount of the database heap (defined by the dbheap parameter) to use as a buffer for log records before writing these records to disk. Range: 4 - 65535
    DB2: 8
    EDM: 128
    LOGFILSIZ This is the Log file Size. This parameter defines the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written to them before they become full and a new log file is required. Range: 4 - 262144
    DB2: 1000
    EDM: 1024
    PCKCACHESZ This is the Package Cache Size. It is allocated out of the database global memory, and is used for caching static and dynamic SQL statements on a database. Range: 32 - 524288
    DB2: -1
    EDM: 6000
    SORTHEAP This is the Sort Heap Size. It defines the maximum
    number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts.
    Range: 16 - 1048575
    DB2: 256
    EDM: 8196
    STAT_HEAP_SZ This is the Statistics Heap Size. It indicates the
    maximum size of the heap used in collecting statistics using the RUNSTATS command.
    Range: 1096 - 524288
    DB2: 4384
    EDM: 5000
    STMTHEAP This is the Statement Heap Size. The statement heap is used as a work space for the SQL compiler during compilation of an SQL statement. This parameter specifies the size of this work space. Range: 128 - 65535
    DB2: 2048
    EDM: 25600

Adding a new parameter

  1. Click Add. The list of available DB2 configuration parameters appears.

  2. Select a parameter from the list, and define the value for this parameter and click OK to add this parameter.

    Note: To restore default values after making any changes, click Restore Default(s).

  3. Click Next to proceed with the database creation process.

Buffer Pools

The next step in the database creation process is defining the Buffer Pool. The purpose of the Buffer Pool is to improve system performance. Buffer Pool is the amount of main memory that should be allocated to cache table and index data pages as they are read from disk.

Name Description
CIS_8K_POOL This is the default buffer pool. The page size of the CIS_8K_POOL is fixed at 8k and cannot be changed.
CIS_ROT_8K_BPOOL This is the default buffer pool that is associated with Root Class Data (CISDB_ROT_SPACE) tablespace. The page size of the CIS_ROT_8K_BPOOL is fixed at 8K and cannot be changed.

You can:

  • Add a new Buffer Pool.

    To do so, click Add. A window prompting the Buffer Pool details appears. You can specify the name, page size and the number of pages for the Buffer Pool and click OK.
  • Modify the value of an existing Buffer Pool.

    To do so, select a Buffer Pool from the list and click Edit. A window with the default value appears. You can specify a new value and click OK.
  • Delete a Buffer Pool.

    To delete a Buffer Pool, select the Buffer Pool and click Delete. You cannot delete the predefined Buffer Pools.

DB2 System Tablespaces

The next step in the database creation process is specifying the DB2 System Tablespaces.

In this window, you can setup the values for the following DB2 System Tablespaces:

  • SYSCATSPACE - The default system catalog tablespace.

  • USERSPACE1 - The default user tablespace.

  • TEMPSPACE1 - The default temporary tablespace.

  • TEMPSPACE 2 - Additional temporary tablespace.

    Notes:

  1.  Each Tablespace should have a Buffer Pool associated with it.

  2. The page size of the Tablespace and the page size of the associated Buffer Pool should be the same.

  3. SYSCATSPACE, USERSPACE1 and TEMPSPACE1 tablespaces support Buffer Pool of fixed size of only 4K.

Name Description
Name This is the name of the tablespace.
Total Size For only DMS tablespaces, the total size of the tablespace will be displayed here.
Type This is the type of the tablespace. This can be:
  • Regular - Stores all data except for temporary tables
  • Long - Stores long or LOB table columns.
  • User Temporary - Stores declared global temporary variables.
  • System Temporary - Stores temporary tables (work areas used by the database manager to perform operations such as sorts or joins.)
Space Management This can be:
  • SMS (System Managed Tablespace)
  • DMS (Database Managed Tablespace)

    For SMS tablespaces, you should specify the name of the tablespace and the path
    names of all its containers. For DMS tablespaces, you should specify the names of
    the files/devices that implement the containers and their sizes.
Containers that make up this tablespace
Container Name This is the name and the fully qualified path of the container. For:
  • SMS - you need to specify the full path to a directory.
  • DMS - you need to specify the full path to a file or device.
Size This is the storage size of the container. This is applicable only for DMS.
Unit This is the storage units of the container. This is applicable only for DMS and can be:
  • P (4 K Pages)
  • K (KB)
  • M (MB)
  • G (GB)

You can:

  • Accept a container entry with a default path to the container of <instance home>/<database name>.
  • Add a new container name. To do so, click Add. A window appears. You can specify the Container Name, Size and Storage Units and click OK to add it to the list.
  • Modify the path to a container. To do so, select the container entry from the list and click Edit. A window with the Container Name and the Destination Directory appears. You can modify only the Destination Directory. You cannot modify the directory or file associated with the container.
  • Delete an existing container entry. To do so, select the container entry from the list and click Delete. The selected container entry will be deleted.
  • Add a new system temporary tablespace. To do so, click Add Temp Tablespace. A window where you define the values for the new tablespace appears. Enter all the information and click OK to add this tablespace.
  • Storage Parameters and Buffer Pool

The fields given in the table are not configurable for SYSCATSPACE, USERSPACE1 and TEMPSPACE1 tablespaces:

Name Description
Page Size This can be 4K, 8K, 16K or 32K.
Extent Size The space allocated to a database object within the tablespace.
Prefetch Size The number of pages to be fetched from the tablespace in advance of being referenced
in an attempt to anticipate page references and reduce writing for I/O.
Buffer Pool Indicates the Buffer Pool associated with this tablespace. It is the amount of main
memory allocated to the cache table and index data pages as they are being read
from disk, or being modified.

Click Next to proceed with the database creation process.

CES Tablespaces

The final step in the database creation process is setting up the CES Tablespaces.

In this window you can setup values for the following CES tablespaces:

  • Data - Used for all tables in the CISDBA schema.

  • Root Class Data - Used for storing CES root class data.

  • Index - Used for separating indexes from the tablespace CISDB_SPACE (Used only if container_type is DMS).

  • Temp - Used to facilitate sorting during run time.

You can also add tablespaces by clicking Add Tablespace. A window where you define the values for the new tablespace appears. Enter all the information and click OK to add this tablespace.
For additional information about the fields in this window, see DB2 System Tablespaces. < doc note : create link here>

Notes:

  1. Databases upgraded from earlier versions to EDS 9.1.9 will have CES Root class data in the same tablespace as the Data tablespace.

  2. All CES tablespaces should have the same tablespace type, that is, they should all be SMS or DMS. However, the type of tablespace can be different for newly added tablespaces.

Summary

To view the summary of all the information, click View Summary. The Summary window appears.

Review all the information in this window and if you wish to make changes, click Close to return to the previous window or click a specific step (displayed in the left panel) to edit the information in that window. The summary information is stored into a log file under $EDS_HOME/log directory. For additional information, see Log Files.

You can now click Finish to start creating the database. A confirmation message indicating that the database creation will start and that the process will take some time appears. Click OK to proceed.