1. Configuring Siebel Business Applications
  2. What’s New in This Release

12Configuring Tables

Using the New Table Wizard to Create a New Table

The New Table Wizard allows you to create a new stand-alone table, extension table, or intersection table. It includes lists that display choices for each type of table and that makes sure you use the correct naming formats. For more information, see Guidelines for Creating a New Table.

To use the New Table Wizard to create a new table

  1. In Siebel Tools, click the File menu, and then click New Object.

  2. On the General tab of the New Object Wizards dialog box, click Table, and then click OK.

  3. In the General dialog box, in the Enter a Name for the New Table field, enter a new table that begins with CX_.

    If you do not enter a name, then the New Table Wizard adds a prefix.

    You must enter the Table Name in uppercase. A mixed case or lowercase name might result in problems if you apply the modifications to some databases.

  4. In the Choose a Project in Which You Wish to Create the Table field, choose a project.

    The New Table Wizard restricts the Project list to only locked projects. The wizard restricts all lists that display in the wizard to objects that belong to locked projects.

  5. In the Select the Type of the Table field, choose from the following options:

    • A stand-alone Table

    • 1:1 Extension Table for a predefined Table

    • 1:M Extension Table for a predefined Table

    • Intersection Table between two existing Tables

    If you choose 1:1 Extension Table for an Existing Table, then the New Table Wizard applies the _X suffix to the table name.

  6. Click Next.

    The subsequent dialog box displays depending on the type of table you are adding.

  7. If you are creating a stand-alone table, then click OK in the Finish dialog box.

  8. If you are creating a one-to-one or many-to-one extension table, then choose the parent table in the Parent Table Specification dialog box, click Next, and then click Finish.

    The wizard restricts the list of available parent tables to Data (Public) tables.

  9. If you are creating an intersection table, then do the following:

    1. Add the parent tables and names of foreign key columns to the parent table in the Parent Table Specification dialog box.

      The New Table Wizard restricts the lists for the Select the First Parent Table field and the Select the Second Parent Table field to all Data (Public) tables. The wizard verifies the names of the Foreign Key columns that you enter. This verification makes sure that they are unique and do not conflict with each other or with other system column names.

    2. Click Next.

      Siebel Tools displays the Finish dialog box that allows you to review the modifications before the wizard creates the objects.

    3. Click Finish to create the table.

      Siebel Tools displays the new table in the Tables list. The name is CX_YOUR_CUSTOM_NAME_X.

  10. Compile and test your modifications.

    For more information, see Using Siebel Tools.

    Work That the New Table Wizard Performs

    If you use the New Table Wizard to create a custom extension table, then this wizard adds a U1 index to the table. The User Key column is empty. Siebel CRM does not support creating a user key on a custom table.

    For all tables, the New Table Wizard creates seven system columns and a P1 index on ROW_ID.

    For a one-to-one extension table, the wizard sets the Type property to Extension and does the following work:

    • Creates a column for PAR_ROW_ID

      • Sets the User Key Sequence property to 1

      • Sets the Foreign Key Table property to BASE_TABLE_NAME

    • Creates a U1 index that includes PAR_ROW_ID(1) and CONFLICT_ID(2)

      • Sets the Unique and Cluster properties to TRUE

      • Sets the Type property to User Key

      • Sets the User Primary Key property to TRUE

      For a many-to-one extension table, the New Table Wizard sets the Type property to Data (Public) and does the following work:

    • Creates the following columns:

      • PAR_ROW_ID

      • TYPE

      • NAME

    • Creates a U1 index that includes PAR_ROW_ID(1), TYPE (2), NAME (3), and CONFLICT_ID (4)

      • Sets the Unique and Cluster properties to TRUE

      • Sets the Type property to User Key

      • Sets the User Primary Key property to TRUE

    • Creates an M1 index on TYPE (1) and NAME (2)

      • Sets the Unique and Cluster properties to FALSE

      • Sets the Type property to System

      For an intersection table, the New Table Wizard sets the Type property to Data(Intersection) and does the following work:

    • Creates a TYPE column for added user functionality

    • Creates two Foreign Key columns using names you defined in the wizard

      • Sets the User Key Sequence property to 1 and 2

      • Sets the Foreign Key Table property to Parent Table

    • Creates a U1 index on the two Foreign Keys (1, 2), TYPE (3), and CONFLICT_ID (4)

      • Sets the Unique and Cluster properties to TRUE

      • Sets the Type property to User Key

      • Sets the User Primary Key property to TRUE

    • Creates an F1 index on the Foreign Key to the second parent table

      Creating a Custom Index

      This topic describes how to create a custom index. For more information, see Guidelines for Creating a Custom Index.

      To create a custom index

      1. In Siebel Tools, in the Object Explorer, click Table.

      2. In the Tables list, locate the table where you must add an index.

      3. In the Object Explorer, expand the Table tree, and then click Index.

      4. In the Indexes list, add a new index.

        When you add a custom index to a table, Siebel Tools appends an _X to the index name. Do not use an index name that includes a word that is reserved on your server or client database. For more information, see Indexes of a Siebel Table and Siebel Object Types Reference.

      5. In the Object Explorer, expand the Index tree, and then click Index Column.

      6. In the Index Columns list, add a new record for each index column.

        For more information, see Index Columns of an Index.

      Adding an Extension Column to a Base Table

      This topic describes how to add an extension column to a base table. For more information, see Guidelines for Adding an Extension Column to a Base Table.

      Caution: Be extremely careful if you use a custom extension column to track a foreign key. If you use this configuration, then it is recommended that you consult with Oracle concerning the visibility rules that Siebel CRM applies to the foreign key table. To use Enterprise Integration Manager to load values into the column, you must set the Foreign Key Table Name property to NULL for that column. For information on creating a foreign key mapping for Enterprise Integration Manager, see About Interface Tables.

      To add an extension column to a base table

      1. In the Object Explorer, click Table.

      2. In the Tables list, choose the table where you must add an extension column.

      3. Make sure the Type property for the table is not Data (Private).

      4. In the Object Explorer, expand the Table tree, and then click Columns.

      5. In the Columns list, add a new record.

      6. Apply the modifications to your local database.

      Configuring Objects to Use a One-To-Many Extension Table

      To use a one-to-many extension table, you must configure the objects that this topic describes.

      Caution: Do not use a one-to-many extension table as an extension to a predefined one-to-many extension table. This configuration causes problems with Enterprise Integration Manager and docking processes.

      To configure objects to use a one-to-many extension table

      1. Create a new business component and new fields in the business component that references columns in the one-to-many extension table that you use to store data.

      2. Define the following business component fields:

        1. PAR_ROW_ID. References the foreign key field that the one-to-many link uses.

        2. NAME. Makes the record unique for each parent record.

        3. TYPE. Groups records in the extension table.

          • Set a default value for the Type field.

          • Define the search specification for the business component to search for records in the extension table that contain the default value. For more information, see Options to Filter Data That Siebel CRM Displays in an Applet.

            To satisfy the U1 index of the one-to-many extension table, the combination of NAME, TYPE, and PAR_ROW_ID must be unique.

      3. Add a link and business object component that creates the parent-child relationship between the new, child business component and the parent business component.

      Configuring an Extension Table

      This topic describes options to configure an extension table. It includes the following information:

        Creating a LONG Column on an Extension Table

        You can create a LONG extension column. For more information, see Guidelines for Creating a LONG Column.

        To create a LONG column on an extension table

        1. Locate an appropriate one-to-one extension table that corresponds to the base table that requires the LONG column.

          The S_EVT_ACT_X table is an example of a one-to-one extension table for the S_EVT_ACT table.

        2. Create a column in the table.

        3. Set the Physical Type property of the column to Long and the Length property 0.

          For more information, see Adding an Extension Column to a Base Table.

        4. Apply the modifications to your local database.

          Manually Creating a One-to-One Extension Table

          You can manually create a one-to-one extension table, but it is recommended that you use the New Table Wizard. For more information, see Guidelines for Creating a Custom One-to-One Extension Table and Using the New Table Wizard to Create a New Table.

          To manually create a one-to-one extension table

          1. In Siebel Tools, in the Object Explorer, click Table.

          2. In the Tables list, locate the base table where you must create an extension table.

          3. Verify that the Type property for the table contains Data (Public).

          4. Click Extend.

            The Database Extension Designer creates the required predefined columns and predefined indexes, and then Siebel Tools displays the extension table in the Tables list. If necessary, the designer creates temporary columns in an interface table that Siebel Tools imports to the base table for this extension table.

          5. Optional. Define more extension columns on the custom extension table.

            For more information, see Adding an Extension Column to a Base Table

            Modifying a Custom Extension Table or Column

            After you create a custom extension table or column, you can only modify the properties of the table or column. You can rename a column before you apply it to the Siebel Server. After you add the column or apply it to the Siebel Server, you cannot rename the column. Instead, you must deactivate the column and create a replacement extension column.

            Be careful if you modify the Physical Type property of a column. Depending on existing data that resides in the column, it might not be possible to do this modification.

            Siebel CRM does not support modifying a predefined base table or the columns of a predefined base table. You must not modify the extension tables that come predefined with Siebel CRM. For more information, see How an Extension Table Stores Custom Data.

            To modify a custom extension table or column

            1. Open Siebel Tools.

            2. In the Object Explorer, click Table.

            3. Optional. Modify a custom extension column:

              1. In the Tables list, locate the table that contains the extension column you must modify.

                If you are adding a new extension table to the EIM Table Mapping list, then make sure you click Activate to create all the temporary columns that Enterprise Integration Manager (EIM) requires.

              2. In the Object Explorer, click Column.

              3. In the Columns list, locate the extension column you must modify, and then modify the properties.

            4. Optional. Rename a custom extension column:

              1. In the Tables list, locate the table you must modify, and then deactivate the unwanted column.

              2. In the Tables list, create a new table column.

              3. Export the data from the old column.

              4. Use ddlsync.ksh to synchronize the logical and physical schema and to import the data.

              5. Delete the column you deactivated.

            5. Optional. Modify a custom extension table:

              1. In the Tables list, locate the extension table you must modify.

              2. Modify properties, as you require.

              Deleting a Custom Extension Table or Column

              You can delete from the logical schema a custom extension table or column that you defined. Deleting a table or column removes it from the logical schema in the Siebel repository, but it does not remove it from the physical schema of the Siebel database.

              You can only delete a custom extension column or table. You cannot delete a predefined table or the columns of a predefined table.

              After you delete an extension table, Siebel Tools does not delete any corresponding temporary columns in an interface table. You cannot use Siebel Tools to delete these columns. The columns will remain in the logical and physical schema.

              If a column is empty at the database level, and if the column is in the Siebel repository, and if the column is in the development environment but is not in the production environment, then your database administrator can use a database tool to remove a column that you do not require. The administrator must do a full database backup before removing a column. The administrator must be careful when deleting a column because removing a column that Siebel CRM still references might require the administrator to revert to a full backup.

              Using the Siebel Database Configuration Wizard to run the Synchronize Schema Definition (ddlsync) utility does not delete a column that is inactive or that was deleted from the Siebel Repository. For more information, see Siebel Database Upgrade Guide.

              To delete a predefined extension column

              1. In the Object Explorer, click Table.

              2. In the Tables list, locate the table that contains the extension column you must delete.

              3. In the Object Explorer, expand the Table tree, and then click Column.

              4. In the Columns list, locate the extension column you must delete.

              5. Click the Edit menu, and then click Delete.

                Siebel Tools does not cascade the deletion of an extension column. You must delete or deactivate the attribute map after you delete an extension column. To delete an attribute map, you can navigate to the Attribute Mappings list in Siebel Tools, and then delete the record.

              To delete a predefined extension table

              1. In the Object Explorer, click Table.

              2. In the Tables list, locate the table you must delete.

              3. Click the Edit menu, and then click Delete.

                Applying a Data Layer Customization to the Server Database

                You must apply your customization to the physical server database. Until you do this, Siebel CRM only updates the logical database schema, as stored in the repository tables of the Siebel database. You can use Siebel Tools or the Database Configuration Utility to apply a customization to the data objects layer.

                Caution: If a table is marked as Inactive in the Siebel Repository, and if you click Apply/DDL, then Siebel Tools removes the underlying table from the Siebel database.

                To apply a data layer customization to the server database

                1. Test your customization in the local environment.

                2. Prepare the server database:

                  1. Make sure all remote users synchronize.

                  2. Make sure all connected clients are disconnected from the database server.

                  3. After Siebel CRM merges and routes all transactions for remote users, stop all Siebel Servers.

                  4. Do a full backup of the server database.

                3. Connect to the Siebel Server.

                4. Check your projects into to the server database.

                5. In the Object Explorer, click Table.

                6. In the Tables list, locate the table where you must apply a modification to the Siebel database.

                7. In the Tables list, click Apply/DDL.

                  Siebel Tools disables the Apply/DDL button for tables that contain External in the Type property. For more information, see Overview: Siebel Enterprise Application Integration.

                8. In the Choose option dialog box, choose the Apply option, and then click OK.

                9. In the Apply Schema dialog box, perform step 7.

                  If you receive an error message and cannot apply your customization on the server database, then you must use the Database Server Configuration Utility. For more information, see Downloading a Data Layer Customization to Remote Users.

                10. In the Apply Schema dialog box, click Apply.

                11. In the Tables list, click Activate.

                  Siebel Tools increases the version of the custom database schema and prepares the upgrade of the remote client. The customization now exists physically on the server database.

                12. Restart the Siebel Server.

                  Your customization tables and columns are now available to use in your configuration.

                  Downloading a Data Layer Customization to Remote Users

                  After you check in extensions to your server database and apply the physical database, you can download the schema modifications to remote users.

                  Note: For Siebel Innovation Pack 2016, the local database for Siebel Tools or for Siebel Mobile Web Client uses Oracle Database XE. SAP SQL Anywhere is no longer available or supported.

                  To download a data layer customization to remote users

                  1. Make sure all remote users perform a full synchronization.

                  2. If you use Siebel Anywhere, then do the following:

                    1. Create an Upgrade Kit on your Server database that includes the Siebel Database Schema as the upgrade kit component.

                      For more information, see Siebel Anywhere Administration Guide.

                    2. Click Activate on the Upgrade Kits View to make the upgrade kit available.

                  3. If you do not use Siebel Anywhere, then do the following:

                    1. Log in to Siebel Tools while connected to the server database.

                    2. In the Object Explorer, click Table.

                    3. In the Tables list, locate the table that includes your customization.

                    4. In the Tables list, click Activate.

                      Siebel Tools increases the version of the custom database schema and prepares the upgrade of the remote client.

                  4. To recreate the template local database, run gennewdb.

                    For more information, see Siebel Remote and Replication Manager Administration Guide.

                  5. Reextract remote clients.

                    Each remote client must reinitialize the local database with the extracted data. This procedure differs depending on if you use Siebel Anywhere.

                  6. If you use Siebel Anywhere, then click Distribute in the Upgrade Configurations View.

                    This step makes the new custom schema version available for a schema upgrade. You must manually set the Required flag. For more information, see Siebel Anywhere Administration Guide.

                  If you do not use Siebel Anywhere, then manually reextract and reinitialize all remote user databases.