Chapter 18: Rose Data Modeler

Adding a Database

A database is modeled in Rose as a stereotyped component. It is given a unique name, and assigned to a specific DBMS. At the time of this writing, Rose supports the following DBMS products:

∙

ANSI SQL 92

∙

IBM DB2 5.x

∙

IBM DB2 6.x

∙

IBM DB2 7.x

∙

IBM DB2 OS390 5.x

∙

IBM DB2 OS390 6.x

∙

Microsoft SQL Server 6.x

∙

Microsoft SQL Server 7.x

∙

Microsoft SQL Server 2000.x

∙

Oracle 7.x

∙

Oracle 8.x

∙

Sybase Adaptive Server 12.x

You can set the DBMS for a database using the Database Specification window.

To add a database:

1.

Right−click the Component View entry in the browser.

2.

Select Data Modeler → New → Database.

3.

614

Chapter 18: Rose Data Modeler

Type the name of the database.

4.

Right−click the new database in the browser and select Open Specification.

5.

In the Target field, select the appropriate DBMS.

Adding Tablespaces

When using DB2, Oracle, or SQL server, you can add tablespaces to your database. A tablespace is a logical unit of storage for your tables. Within each tablespace are one or more containers, where a container is a physical storage device such as a hard drive. Each container is divided into smaller units called extents. Tables in the tablespace are evenly distributed across the containers within the tablespace.

Note In Microsoft SQL Server, tablespaces are called filegroups, and containers are called files. In Oracle, containers are known as data files.

Each tablespace has an initial size, in KB. Once that space has been used, the DBMS can automatically increase the size of the tablespace in preset increments. The size of the increments (in KB) can be set in Rose. Even when increments are set, the container cannot grow beyond its maximum size, which can also be set in Rose. Once tablespaces are established, you can assign tables to them.

To add a tablespace (SQL Server):

1.

Right−click the database in the browser.

2.

Select Data Modeler → New → Tablespace.

3.

Type the name of the tablespace.

4.

Right−click the new tablespace in the browser and select Open Specification. The namespace specification window will appear:

615

Chapter 18: Rose Data Modeler

5.

Check the Default field if you want this to be the default tablespace. Any tables that are not assigned to another tablespace will be assigned to the default tablespace.

To add a tablespace (Oracle):

1.

Right−click the database in the browser.

2.

Select Data Modeler → New → Tablespace.

3.

Type the name of the tablespace.

4.

Right−click the new tablespace in the browser and select Open Specification. The namespace specification window will appear:

616

Chapter 18: Rose Data Modeler

5.

Set the tablespace type to Permanent or Temporary. A temporary tablespace will allocate space for only the duration of the current database session. A permanent tablespace will remain in existence even after the end of the database session.

To add a tablespace (DB2):

1.

Right−click the database in the browser.

2.

Select Data Modeler → New → Tablespace.

3.

Type the name of the tablespace.

4.

Right−click the new tablespace in the browser and select Open Specification. The namespace specification window will appear.

5.

Set the tablespace type to Regular or Temporary. A temporary tablespace will allocate space only for the duration of the current database session. A regular tablespace will remain in existence even after the end of the database session.

6.

617

Chapter 18: Rose Data Modeler

Set whether the tablespace is managed by the DBMS or by the operating system. If it is managed by the operating system, you cannot add new containers after creating the tablespace, but the existing tablespaces can be expanded. If it is managed by the DBMS, the existing tablespaces cannot be expanded, but you can add new containers.

To set up containers within a tablespace (SQL Server):

1.

Right−click the tablespace in the browser and select Open Specification.

2.

Select the Containers tab on the specification window.

3.

Right−click anywhere in the white space, and select New.

4.

Enter the tablespace filename, initial size, maximum size, and file growth (increment amount).

To set up containers within a tablespace (Oracle):

1.

Right−click the tablespace in the browser and select Open Specification.

2.

Select the Containers tab on the specification window.

3.

Right−click anywhere in the white space, and select New.

4.

Enter the tablespace filename, initial size, maximum size, and extent size (increment amount).

618

Chapter 18: Rose Data Modeler

To set up containers within a tablespace (DB2):

1.

Right−click the tablespace in the browser and select Open Specification.

2.

Select the Containers tab on the specification window.

3.

Right−click anywhere in the white space, and select New.

4.

Enter the tablespace extent size, prefetch size in pages, page size, and buffer pool name.

The extent size is the tablespace increment amount in number of pages. A prefetch can speed up a query by fetching more pages than are currently being read by the query. The Prefetch Size field shows the number of pages to be prefetched. The page size is the amount of space (in KB) per page. Finally, the buffer pool is a memory buffer that can be used to hold the prefetched pages.

619

Chapter 18: Rose Data Modeler

To view the tables that have been assigned to a tablespace:

1.

Right−click the tablespace in the browser and select Open Specification.

2.

Select the Containers tab on the specification window.

3.

Select the Tables tab to view a list of tables in the tablespace.

620