Post Database Creation Tasks for Microsoft SQL and Sybase

Installation and Configuration › How to Prepare Your DBMS Environment › Tasks to Create a Sybase DBMS › Post Database Creation Tasks for Microsoft SQL and Sybase

Post Database Creation Tasks for Microsoft SQL and Sybase

You must run the Administrative Setup program located on the CD. The Setup program prompts you for an installation directory. By default this directory is C:\Program Files\CA\ERwin Model Manager, but the path may have been changed during installation.

Note: Do not attempt to run the Setup program unless you are certain that the computer you are using can connect to the target server. For more information about tips on how to determine if your installation computer can connect to the target server, see Microsoft SQL Server Connections or Sybase Connections.

When you install the software, the Microsoft SQL installer can be 'owner,' 'user with DB owner privileges,' or 'user with alias as DB owner privileges.' The Sybase installer can be 'owner,' 'user with sa_role,' or 'user with alias as DB owner.'

Transact SQL Commands

If a graphical DBMS access tool is not available you can use Transact SQL commands through ISQL.

The following is an example of how to create a device using the Transact SQL DISK INIT command through ISQL:

DISK INIT NAME = 'mmdata',	/* The logical name. */
PHYSNAME = 'C:\SQL\DATA\mmdata.dat',	/* The physical name. */
VDEVNO = 1<= virtual_device_number => 255	
/* System dependent. */
 SIZE = number_of_2K_blocks	/* 1024 here is 2MB!!! */
 [, VSTART = virtual_address,	/* Optional */
CNTRLTYPE = controller_number]	/* Optional */

The following is an example of how to create a database using the Transact SQL CREATE DATABASE command through ISQL:

CREATE DATABASE mmmaster	
 [ON {DEFAULT | database_device} [= size_in_megabytes] /* The device created in #1. */
 [, database_device [= size_in_megabytes]]...]  /* A database can span devices. */
 [LOG ON database device [= size_in_megabytes>]  /* Separate log device. */
 [, database device [= size_in_megabytes]]...] /* A transaction log can span devices. */

The following is an example of how to add logins to the database with the sp_addlogin and sp_adduser commands using Transact SQL through ISQL:
```
sp_addlogin login_id [, passwd [, defdb [, deflanguage]]]
```
and
```
sp_adduser login_id [, username [, grpname]]
```
Once you have executed these commands, the DBA can alias an existing login as the Database Owner (dbo) or change the dbo to an existing login using sp_changedbowner. Use ISQL to execute the following:
```
sp_changedbowner login_id [,true]
```

Tasks to Create an Oracle DBMS

The following tasks must be performed by the DBA and system administrator responsible for installing the software on an Oracle database management system. Information provided in this section includes installation and Oracle connectivity software requirements.

These steps can be performed using graphical tools but you can also use SQL *Plus (all versions), SQL*DBA (either command line or full screen mode), or the Oracle Enterprise Management Console that started shipping with Oracle Version 8.0. Examples of SQL commands are included where appropriate. Data file paths, data file sizes, role names, and user names are included for example only.

Check SYSTEM tablespace.
The installation creates several stored procedures. All triggers, stored procedures, and packages are kept in the Oracle SYSTEM tablespace. The standard size of the SYSTEM tablespace assumes that you are not using procedural options, so the SYSTEM tablespace often needs to be expanded. If other Oracle applications are not using procedural code, then the SYSTEM tablespace should be expanded to 32 MB. If other Oracle applications also use procedural code, expand the SYSTEM tablespace to at least 32 MB.
Check Rollback Segment tablespace.
If your instance uses UNDO tablespace, do not create rollback segments.

Significant rollback space is required for installation and use. The rollback segments should be in their own separate tablespace and each have at least 16 MB of available space. There should be one rollback segment for every four concurrent users, with a maximum of 50 rollback segments. The available space should scale upward with increasing numbers of rollback segments. Finally, the rollback segment optimal parameter should be set to control rollback segment growth and space consumption.

Note: For Steps 3, 4, and 5, use Dictionary-managed tablespaces.

Create a data tablespace of at least 32 MB.

For example:

CREATE TABLESPACE MyMart

DATAFILE '/db01/oracle/rdbms9i/data/mymart.ora' SIZE 100M;

Or For Locally Managed extents:

CREATE TABLESPACE Mymart

DATAFILE '/db01/oracle/rdbms9i/data/mymart.ora' SIZE 100M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Create a index tablespace of at least 32 MB.

For example:

CREATE TABLESPACE MMARTINDEX

DATAFILE '/db02/oracle/rdbms9i/data/mmartindex.ora' SIZE 75M;

Or For Locally Managed extents:

CREATE TABLESPACE MMARTINDEX

DATAFILE '/db02/oracle/rdbms9i/data/mmartindex.ora' SIZE 75M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Create a temporary tablespace.

For example:

CREATE TEMPORARY TABLESPACE MMTEMP TEMPFILE '/db03/oracle/rdbms9i/data/mmarttemp.ora' SIZE 50M;

Or For Locally Managed extents:

CREATE TEMPORARY TABLESPACE MMTEMP TEMPFILE '/db03/oracle/rdbms9i/data/mmarttemp.ora' SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Note: For more details on syntax and options regarding tablespace creation, see the appropriate Oracle documentation.

Create an Oracle user with DBA privileges to be used by the CA ERwin MM Installer or designated schema owner.
Assign the data tablespace as this user's default tablespace, and the temporary tablespace as this user's temporary tablespace.

For example:
```
CREATE USER STEVE IDENTIFIED BY STEVE
```
```
DEFAULT TABLESPACE MyMart
```
```
TEMPORARY TABLESPACE MMTEMP
```
```
QUOTA UNLIMITED ON MyMart
```
```
QUOTA UNLIMITED ON MMARTINDEX;
```
Create the CA ERwin MM Installer role.
The following example is the role required by the Oracle user installing CA ERwin MM.
```
CREATE ROLE MMINSTALL;
```

Grant Oracle privileges to the Installer role.

The following example shows the Oracle privileges that the CA ERwin MM Installer needs to install on Oracle. For the last command, you must log in as sys with the sysdba role in the user-name or the command will fail.

grant create sequence to MMINSTALL;

grant create table to MMINSTALL;

grant create view to MMINSTALL;

grant drop public synonym to MMINSTALL;

grant create public synonym to MMINSTALL;

grant create procedure to MMINSTALL;

grant select on dba_data_files to MMINSTALL;

Create the CA ERwin MM User role.
For example:
```
CREATE ROLE MMUSER;
```
When you select this role as the CA ERwin MM User role during Step 4 of the installation procedure, the Setup program generates grant statements that grant object level privileges to this role.
Grant the create session Oracle privilege to the User role.
For example:
```
grant create session to MMUSER;
```
Note: The create session privilege is the only privilege that an Oracle user needs to use the database.
Grant the CA ERwin MM User role to each Oracle user that uses the database.
For example:
```
grant MMUSER to USER1;
```
```
grant MMUSER to USER2;
```
```
grant MMUSER to USER3;
```
Important! You must specify the User role name created in Step 9 for these grant statements.
Grant the CA ERwin MM User role to the CA ERwin MM Installer role.
For example:
```
grant MMUSER to MMINSTALL;
```
Grant the CA ERwin MM Installer role to the CA ERwin MM Installer user. You must also grant DBA privileges to the user you designate as the CA ERwin MM Installer.
For example:
```
grant MMINSTALL to STEVE;
```
```
grant DBA to STEVE;
```
Note: The DBA role is necessary only during the installation or upgrade. You can strip the installer of the DBA role after the installation or upgrade is complete.
Run the Setup program.
When the setup is complete, select the Initialize CA ERwin MM option and connect to Oracle as the CA ERwin MM Installer user (for example, STEVE).
Select the CA ERwin MM tablespaces and CA ERwin MM User role.
When prompted for tablespace and role information, select the CA ERwin MM data tablespace, the CA ERwin MM index tablespace, and the CA ERwin MM User role (the role created in Step 9).
Add the CA ERwin MM Installer user as the CA ERwin MM administrator.
When the Security Manager is opened, your user name is automatically added to the Administrator profile in the Security Profile list. You can also designate one of the users with the MMUSER role (from step 12) for routine operations. This completes the setup and you can begin using the software.

Note: After successful installation, you can revoke the CA ERwin MM Installer role and grant the CA ERwin MM User role to the CA ERwin MM Installer user.

Email CA about this topic