Creating Storage Objects

Aqua Data Studio provides visual editors for creating database Storage objects. By navigating the schema browser you may locate the Storage container (a hard drive shaped icon) underneath server connections. Storage objects vary based on database vendor and version.

Contents

• Storage Objects for DB2 UDB: Bufferpools
• Storage Objects for DB2 UDB: Tablespaces
• Storage Objects for Oracle: Datafiles, Tablespaces, Rollback Segments and Redo Log Groups.

Storage Objects for DB2 UDB: Bufferpools

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 the 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

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. Tablespaces reside in database partition groups. They allow you to assign the location of the 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 milliseconds. Preview the SQL in the SQL Preview tab. Click OK to finish.

DB2 Tablespaces

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 a 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

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

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 Segment. 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

To create Oracle Redo Log Group, connect to a server and 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

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.