Creating Storage Objects (Revision #2)

A bufferpool is an area of main memory that has been allocated by the database manager for the purpose of caching table and index data as it is read from disk. Each new database has a default bufferpool defined, called IBMDEFAULTBP. Additional buffer pools can be created, dropped, and modified, using the CREATE BUFFERPOOL, DROP BUFFERPOOL, and ALTER BUFFERPOOL statements. 

To create a DB2 Buffer Pool, expand the DB2 Storage container and right click on Buffer Pools to select Create Buffer Pool. Enter a Buffer Pool name and select a page size. Under the Buffer Pool Size section, select the size and the number of Non-blocked and Blocked Pages. Select the Block size in pages. Under the Options section choose Self Tuning or Use default Buffer Pool size. Select Create Buffer Pool on database restart or Create Buffer Pool immediately. Preview the SQL in the SQL Preview tab. Click OK to finish.

DB2 Buffer Pools

1. Connect to Server	2. Right click on Buffer Pools	3. Enter Buffer Pool Settings	4. Preview SQL

ALTER Bufferpool

A right click within the Schema Browser on a bufferpool allows it to be altered

DROP Bufferpool

Bufferpools can be dropped with a right click through the Schema Browser.

Storage Objects for DB2 UDB: Tablespaces

A tablespace is a storage structure containing tables, indexes, large objects, and long data. Table spaces reside in database partition groups. They allow you to assign the location of database and table data directly onto containers. (A container can be a directory name, a device name, or a file name.)

To create a DB2 Tablesplace, expand the DB2 Storage container and right click on Tablespaces to select Create Tablespace. Give the Tablespace a name, enter the path to the container and select the Type. Select the Buffer Pool to use and give the tablespace page size. Under the Storage Tab give the Extent Size, Prefetch Size and set the Overhead and Transfer rate in miliseconds. Preview the SQL in the SQL Preview tab. Click OK to finish.

DB2 Tablespaces

1. Connect to Server	2. Right click on Tablespaces	3. Enter Tablespace Settings	4. Set Storage Properties	5. Preview Tablespace SQL

ALTER Tablespace

A right click within the Schema Browser on a tablespace allows it to be altered.

DROP Tablespace

Tablespaces can be dropped with a right click through the Schema Browser.

Storage Objects for Oracle: Datafiles, Tablespaces, Rollback Segments and Redo Log Groups.

A datafile is a physical part of an Oracle database and it is storage device to store Oracle data. A datafile may be created with different block sizes, spanned upon different disks, and use different rate levels. A Tablespace may be created with dictionary-managed or locally–managed Tablespaces, and a host of other tablespace options that affect the physical design of the system.

To create Oracle Tablespaces, connect to an Oracle server and expand the tree to view the Storage container. Right click on Tablespaces within the Storage container and select create Tablespace. Enter Tablespace Settings for Datafile name, file directory location, Status and Type. In the Storage Tab, set the properties for Extent Management (Automatic or Uniform Allocation), Segment Space Management (Automatic or Manual), and Logging. Adjust the Block Size as needed. Preview the SQL in the Preview SQL tab. Click OK to finish.

Oracle Tablespaces

1. Connect to Server	2. Right Click on Tablespaces	3. Enter Tablespace Settings	4. Set Storage Properties	5. Preview SQL

To create Oracle Datafiles, connect to an Oracle server and expand the tree to view the Storage container. Right click on Datafiles within the Storage container and select Create Datafile. Enter the Datafile name and Tablespace. Set the file size. In the Storage tab, select AUTOEXTEND and Increment, then set the Maximum Size in KB if needed. View the SQL in the Preview SQL tab. Click OK to finish.

ALTER Tablespace

A right click within the Schema Browser on a tablespace allows it to be altered.

DROP Tablespace

Tablespaces can be dropped with a right click through the Schema Browser.

Oracle Datafiles

1. Connect to Server	2. Right click on Datafiles	3. Enter Datafile Settings	4. Set Storage Properties	5. Preview SQL

To create Oracle Rollback Segments, connect to an Oracle server and expand the tree to view the Storage container. Right click on Rollback Segments within the Storage container and select Create Rollback Segement. Enter the Rollback Segment name and Tablespace. Select its status as Online or Offline. In the Storage tab Set the Extents Initial Size, Next Size, Optimal Size, Minimum Number and Maximum number. View the SQL in the Preview SQL tab. Click OK to finish.

ALTER datafile

A right click on a datafile within the Schema Browser allows it to be altered.

Oracle Rollback Segment

1. Connect to Server	2. Right click on Rollback Segments	3. Enter Rollback Segment Properties	4. Set Storage Properties	5. Preview SQL

To create Oracle Redo Log Group, connect to a serverand expand the tree to view the Storage container. Right Click on Redo Log Group within the Storage container and select Create Redo Log. Enter the name for the Log Number, set the filesize and name the file and its directory. View the SQL in the Preview SQL tab. Click OK to finish.

ALTER Rollback Segment

A right click within the Schema Browser on a rollback segment allows it to be altered.

DROP Rollback Segment

Rollback segments can be dropped with a right click through the Schema Browser.

Oracle Redo Log Group

1. Connect to Server	2. Right Click on Redo Log Group	3. Enter Redo Log Properties	4. Preview SQL

ALTER Redo Log

A right click within the Schema Browser on a redo log allows it to be altered.

DROP Redo Log

Redo logs can be dropped with a right click through the Schema Browser.