SimManager stores metadata in a set of RDBMS tables, which are called a database in the SimManager documentation. The following sections explain the relationship between the SimManager database terms and the respective RDBMS database terms.
Mapping to IBM DB2
The IBM DB2 database system uses a concept of database instances where there can be several database instances on the same host. An instance is identified by an instance owner, who is also the operating system user owning the files and directories that make up the instance.
Each IBM DB2 instance:
• Can be configured, started and stopped independently
• Can have multiple databases
• Instance owner has administrative power to all databases in the instance
Databases in the instance are comprised of configuration parameters and data files. Data files are grouped into table spaces.
Databases are used by application programs by means of network connections. To facilitate this, the database instance must be configured to accept network connections. After accepting a connection, the instance calls service daemons to operate on the requested database on behalf of the application program.
DB2 relies on the operating system to create and authenticate users. Any user connecting to DB2 must exist in the operating system. Depending on the configuration, user authentication can be performed either on the client or on the database server.
In contrast to users authentication, database privileges granted to users (known as authorization) are established within the individual database. Privileges can be granted to single users as well as to groups.
For each user, a schema is created using the same name as the user. Subsequently, tables are created inside a table space and inside a schema. The table space defines the physical characteristics, such as data files,
space allocation and so on; the schema defines the logical organization. By default, the schema with the same name as the user creating the table is used.
Database users are typically assigned different privileges with respect to what operations are available to them and what schemata and table spaces they can use. For example, at runtime, the application program can be forced to use a less privileged user, such as one who cannot modify the schema (for example, create or delete tables). Initial schema creation must then be done by an administrator, or a privileged user who may also own the schema.
SimManager supports this privilege separation by specifying one username/password for the schema owner and separate username/password for the database user to use at runtime. When using the automatic database initialization process, an additional username/password of an administrative account is required.
During the automatic database initialization process, the administrative user is used to grant appropriate permissions to the other two users: the schema owner and the runtime user. The schema owner is then used to create the database objects, such as tables, views, indexes, and so on. These database objects are owned by the schema owner and reside in a DB2 schema of the same name.
During the automatic database deletion process, the schema owner is used to delete the database objects, such as tables, views, indexes, and so on. The user privileges are not modified after deletion.
If you initialize the database manually, using SQL scripts, the administrative user is never used by SimManager, so this parameter is irrelevant. The schema owner, however, is used as the name of the database schema, so this name must be specified correctly (the password is irrelevant).
If you want to use the same DB2 database for different SimManager databases, you must use a different schema name (schemaOwner parameter) for each instances. There are no special considerations to using different databases, even within the same DB2 instance.
The table below summarizes the configuration settings needed to access a DB2 database:
Table 6-1 DB2 Database Connection Parameters
Parameter Meaning
dbServerHost Network host name of the server hosting the database instance dbServerPort Listening port number database instance; conventionally this is a
number in the 50000 or 60000 range dbServerName Database name (created within the instance)
dbManagerClassName com.msc.sdm.db.sql.db2.DB2ServerDatabaseManager
The DB2 table space page size limits the maximum row size of any table in the table space, and uses a buffer pool with a page size matching the table space. If no table space is specified when creating tables, DB2 selects a table space that matches the row size requirement automatically, as long as the user (schema owner) has permission to use that table space.
Mapping to Oracle
Oracle uses a concept of database instances, whereby:
• There can be several database instances running on the same host
• An instance is identified by an ORACLE_SID
• Each instance can be configured, started and stopped independently
• An instance is comprised of a set of configuration parameters (init.ora settings) and data files.
Data files are grouped into table spaces.
Database instances are used by application programs by means of network connections. To facilitate this, a listener process (tnslsnr) must be running on the database server host. The listener accepts incoming connection requests on a TCP/IP port and forwards them to the requested database instance.
Privileged users are created by default when the database instance is created. These can define additional database users and their passwords, such that each user is assigned a "schema" of the same name as the user name.
dbAdminUser/
...Password
Privileged DB2 user (e.g., instance owner); used for assigning privileges to schemaOwner and dbUser, if using the automatic process; if using SQL scripts, the user name is used as the name of the DB2 schema.
schemaOwner/
...Password
Using the automatic process, this user is first granted schema creation privileges, and then the schema is created by this user.
If using SQL scripts, the name given as schemaOwner must be the DB2 schema name.
appuser/password The user which is used to connect at runtime. If using the automatic process, this user is granted appropriate privileges, otherwise the DB2 administrator must grant the required privileges.
dbTableSpace Used to place all tables into this tablespace. Optional. When not specified, the DB2 automatic placement algorithm will be used to select the best matching tablespace.
dbIndexTableSpace Used to place all indexes into the given tablespace. Optional.
dbIndexTableSpace can only be given if dbTableSpace is also specified.
schema The qualified name of the SimManager portal schema; it has no connection to any DB2 entity.
Table 6-1 DB2 Database Connection Parameters (continued)
Parameter Meaning
Tables are created inside a table space and inside a schema. The table space defines the physical characteristics, such as the data file, space allocation etc., while the schema defines the logical organization. By default, the schema uses the same name as the user who created the table.
Database users are typically assigned privileges with respect to what operations are available to them and what schemata and table spaces they can use. For example, at runtime, the application program can be forced to use a less privileged user, such as one who cannot modify the schema (for example, create or delete tables). Initial schema creation must then be done by an administrator, or a privileged user who may also own the schema.
SimManager supports this privilege separation by specifying one username/password for the schema owner and separate username/password for the database user to use at runtime. When using the automatic database initialization process, username/password of an administrative account is required.
During the automatic database initialization process, the administrative user is used to create two other users, the schema owner and the runtime user. The schema owner is then used to create the database objects, such as tables, views, indexes, and so on. These database objects are owned by the schema owner and reside in an Oracle schema of the same name.
During the automatic database deletion process, the schema owner is used to delete the database objects, such as tables, views, indexes, and so on. The administrative user is then used to delete the schema owner and runtime user.
If you initialize the database manually, using SQL scripts, the administrative user is never used by SimManager, so this parameter is irrelevant. The schema owner, however, is used as the name of the database schema, so this name must be specified correctly (the password is irrelevant).
You can use the same database instance for different SimManager databases, as long as the schema name (schemaOwner parameter) is different for each one.
The table below summarizes the configuration settings needed to access an Oracle database:
Table 6-2 Oracle Database Connection Parameters
Parameter Meaning
dbServerHost Network host name of the Oracle server running the tnslsnr process dbServerPort Listening port number of the Oracle tnslsnr process; default is 1521 dbServerName ORACLE_SID of the Oracle database instance
dbManagerClassName com.msc.sdm.db.sql.oracle.OracleDatabaseManager dbAdminUser/
...Password
Privileged Oracle user; used for creating and deleting schemaOwner and dbUser if using the automatic process; not used if using SQL scripts schemaOwner/
...Password
Using the automatic process, this user is created first, and then the schema is created by this user;
If using SQL scripts the name of the schemaOwner must match the Oracle schema name.
appuser/password The user which is used to connect at runtime; if using the automatic process, this user is created for you, otherwise the Oracle administrator must create it.
dbTableSpace Used in the automatic process to assign a default tablespace to the schemaOwner so that all database objects will be created in this tablespace. (Optional)
dbIndexTableSpace Used to place all indexes into this tablespace by default. Optional schema The qualified name of the SimManager portal schema; it has no
connection to any Oracle entity.
Table 6-2 Oracle Database Connection Parameters (continued)
Parameter Meaning
Automatic Database Creation and Deletion
For automatic database creation, configure the database parameters in the Classic Studio Tool (or provide the correct settings in DbConfig.properties), and then select “Create Database…” and confirm the selection. Classic Studio will initialize the RDBMS and all vaults of your schema.
Figure 6-1 Automatic Database Creation example in Studio
After creating the database, you can run a command file to load the user and role setup, load some sample data as required.
To automatically delete the database, using Classic Studio Tool, select “Delete Database…” and confirm when prompted.
Classic Studio first deletes the vaults, and then the RDBMS. Therefore, if an error occurs during deletion of the database, the process will abort, and the vaults will have already been deleted. Exercise extreme caution before selecting the delete option.
Figure 6-2 Automatic Database Deletion example in Studio Manual Database Creation and Deletion
For manual database creation, use Classic Studio to save the creation scripts to files. There are two scripts, the admin script and the schema script. The admin script contains commands to create the database users needed by SimManager. The schema script contains commands to create the database objects, such as the tables, views, stored procedures, and so on.
To manually create the database, execute “Create Schema Script…”, enter the file names to save the scripts, and confirm when prompted, as shown in the figure below:
Figure 6-3 Creating the Database Creation Scripts example
After creating the scripts, the database administrator must run them in the database’s SQL command line processor. For Oracle, this would be the sqlplus command, for DB2, the db2 command, and for SQL Server, the osql command.
Before executing the SQL scripts, verify that there is a database schema containing all tables, views, etc., and that there is a user (authorized to access that schema) for SimManager to connect to at run time.
Run the admin script under a privileged database user to create the schema owner and run time user.
Then, run the schema script (under the schema owner just created) to create the database objects in the schema owners schema.
After the RDBMS has been initialized by the database administrator, a separate step is necessary to initialize the vaults. Start Classic Studio, select “Create Vaults…” and confirm when prompted.
Figure 6-4 Create Vaults example
Proceed with run command file loading user and roles setup, and data, as in the automatic database creation process.
Note: Request the final schema name and connect user/password from the database administrator, and enter the information in the SimManager database configuration (DbConfig.properties).
To delete the database via SQL scripts, use Classic Studio to delete the vaults. This step must be executed before deleting the RDBMS. The Studio cannot delete vaults after the RDBMS has been deleted. Select
“Delete Vaults…” and confirm.
Figure 6-5 Delete Vaults using SQL Scripts example
Next, save the database deletion scripts. Select “Delete Schema Scripts…”, enter the file names, and confirm when prompted. Give these scripts to your database administrator. Who can run the schema deletion script when connected as the schema owner, then the admin deletion script when connected as a privileged database administrator. The former drops the database objects that were used by
SimManager, the latter remove the users that were created for SimManager.
Figure 6-6 Creating the Database Deletion Scripts example