Database Tools

The Business Modeler provides a simple user interface for creating and modifying database tables that is useful during implementation.

This chapter covers the following topics:

Creating a Table

To create a table

  1. Log onto the Business Modeler as described in “Logging onto the Business Modeler”.

  2. Click Tools > Database > Create Table.

  3. In the Table Name field, type a name for the new table.

  4. In the Column Name field, enter the name of a column in the new table.

  5. In the Column Type field, enter the column type (one of the standard data types supported by the database).

  6. If the selected column type contains a string of characters, select a width in the Width field.

  7. In the Decimal field (if field is numeric), specify the number of positions that should appear after the decimal point.

  8. In the Nulls field, specify whether this field is allowed to contain a null value.

  9. If this field should be a primary key for this table, select the Primary Key check box.

  10. When you have completed creating the field, click Add or press Tab to move to the next field. Repeat this process for any field you want to create for the table.

    Note: To delete a field, select it and then click Delete. The field cannot be deleted after the table has been created.

  11. When you have completed creating all the fields for the new table, click Create to create the table, and then click Close.

Modifying a Table

To modify a table

  1. Click Tools > Database > Alter Table.

    The Select Table dialog box appears

  2. Double-click the name of the table you want to modify.

    The Alter Table dialog box appears. Here, each row displays information about a field (column) of the table.

    You can modify the white columns: Width; Dec (number of decimal points), and Primary Key.

  3. To add a column, click Add and enter the required information, as described in “To create a table”.

  4. To save modifications to the table, click Alter.

  5. To close the dialog box, click Close.

To delete a column

  1. Select the column.

  2. Click Delete.

    Note: A column cannot be deleted if you have modified it.

Recompiling the Database Functions and Procedures

To recompile the database functions and procedures

  1. Log onto the Business Modeler as described in “Logging onto the Business Modeler.”

  2. Click Tools > Recompile.

    The database procedures and functions compilation dialog box appears.

  3. Click Compile to compile the SQL procedures.

  4. (Optional) Click Show Error to view possible expression errors. Click Print to print the errors.

  5. Click Close to close the dialog box.

Viewing the Procedure Error Log

The procedure error log enables you to see error messages produced by procedures. For each error message, it displays the date and time of the message, the procedure name, and the text content of the message.

To display the procedure error log

  1. Log onto the Business Modeler as described in “Logging onto the Business Modeler.”

  2. Click Tools > Procedure Error Log.

    The Procedure Error Log screen appears. The most recent errors are displayed first.

To display an error message

  1. Select an error from the procedure name and date list.

    The error message appears in the error message pane.

To sort the list

  1. Right-click the list and then select Sort.

    The sort screen appears.

  2. To determine how the items will be sorted, click one or more required boxes in Columns Available for Sorting, and drag them to Sort Columns, or double-click the required boxes in Columns Available for Sorting.

    To reverse this process drag a box in the opposite direction, or double-click a box in Sort Columns.

  3. To specify an ascending sort order, make sure Ascending is checked. For a descending order, clear the box.

  4. Click OK.

To find an item in the list

  1. Right-click the list and then select Find.

  2. In the Find where box, select the type of element to find.

  3. In the Find what box, type the text you want to find.

  4. In the Search box, select Up or Down to specify the direction of the search.

  5. (Optional) Select one or more of the check boxes:

    • Whole Word: Search for the exact match of a word.

    • Match Case: Search for the exact match of a word (case sensitive).

    • On Line Search: For immediate search results (even if only part of a word has been entered in the Find what box).

  6. Click Find Next to begin (or continue) searching.

To filter the list

  1. Right-click the list and then select Filter.

    The filter screen appears.

  2. Select the required Column, Operator and Value.

  3. (Optional) To add additional criteria, select the required Logical operator and click Add.

  4. Click OK.

To copy the error message to the clipboard

  1. Click Copy.

To clear (truncate) the list

  1. Click Truncate.

    A warning message appears.

    Caution: The truncate action cannot be reversed.

  2. Click Yes to confirm the action.

To refresh the list from the database

  1. Click Refresh.

To close the Procedure Error Log screen

  1. Click Close.

Wrapping Database Procedures

So that you can more easily locate and resolve problems, the database procedures are provided in unwrapped form. When you go live, it is required that you wrap the procedures and compile them in wrap mode into the database.

To wrap the database procedures

  1. Search the Demantra installation directory for a file called wrap30.bat. The location depends on which database you are using.

  2. Create a directory, for example, wrap.

  3. Inside this folder, create two subdirectories called, for example, source and target.

  4. Copy this file into the folder:

  5. Copy the database procedures into the source folder.

  6. From the command line, execute the following command:

    wrap30 source_dir target_dir

    For example: wrap30 source target

    After the execution is finished, the wrapped file will be in the target folder.

  7. In the database, replace the unwrapped procedures with the new wrapped ones.

Cleaning Up Temporary Tables

Demantra provides an option for deleting its temporary tables.

To clean up tables

  1. Log onto the Business Modeler as described in “Logging onto the Business Modeler.”

  2. Click Tools > Maintain > Tables Cleanup.

    The Tables Clean Up dialog box appears, listing the Demantra temporary tables.

  3. Click the appropriate check boxes for the tables you want to delete, and then click Delete.

Monitoring Oracle Sessions

The Oracle Sessions Monitor enables you to monitor Oracle sessions and their status.

To monitor the Oracle sessions

  1. Log into the Business Modeler.

  2. Click Tools > Oracle Sessions Monitor.

    The Oracle Sessions Monitor window appears.

  3. Here, do any of the following:

    • To sort or filter sessions, right-click and then select the appropriate command from the pop-up menu.

    • To select all sessions, right-click and then click Select All.

    • To delete a session, right-click the session and then click Delete.

    • To terminate a session, select a session, and then click Kill a Session.

Index Adviser

The Index Adviser is a tool that automatically analyzes a Demantra database schema and recommends what indexes should be added in order to optimize the performance of the Worksheets Run and Filter Executions (in the Worksheet wizard).

The following procedure shows an example of how the tool could be used.

  1. Access the program in your browser from the following url:

    http://localhost:8081/demantra/admin/indexAdvisor.jsp

  2. Log in (e.g. as dm/dm)

  3. The Index Adviser screen appears:

    the picture is described in the document text

  4. Click ‘Get List’ in the ‘Worksheets’ section to display all worksheets.

    the picture is described in the document text

  5. From the Index Adviser screen, click ‘Get List’ in the ‘Users’ section to display all users below the Index Advisor screen.

    the picture is described in the document text

  6. Select one or more users and worksheets.

  7. 7. After selecting a set of Users and Worksheets, their corresponding UserIDs and WorksheetIDs appear in the Index Advisor screen, as shown below.

    the picture is described in the document text

  8. Click Execute. The schema is analyzed and the following Results page is displayed. This page shows Filter Indexes and Worksheet Indexes.

    the picture is described in the document text

    the picture is described in the document text

  9. Check at least one index from the Filters (Users) or Worksheet Indexes, and click Create Script. An SQL script is generated for adding indexes to the schema. From this page click Open/Download to save the result to a file or to open an editor for viewing and modifying the script.

    the picture is described in the document text

The DBA can refer to the report to manually create all or a sub-set of the recommended indexes in the database.