Enabling the Clarify CRM application for the connector

Before you can use the connector to process application events and send event notification business objects to the integration broker, you must set up the event notification mechanism in the Clarify CRM application. To do this, complete the following tasks:

  1. Create the event and archive tables in Clarify CRM.
  2. Install database triggers on Clarify CRM tables to support the business objects needed by the collaborations running at your site.

The event table is used to queue events for pickup by the connector. The event table contains enough information so that the connector can determine the name and verb of the business object instance that it will create to represent an event.

The sections that follow provide information on these tasks.

Creating the event and archive tables

To set up event processing, you create an event table in Clarify CRM. An event table is required; you must create this table even if the connector will not poll for events. An archive table is optional; however, if the connector is polling for events and there is no archive table, all events are lost once the connector retrieves them, whether or not they were successfully processed.

Table 1 shows the Clarify CRM information for the event and archive tables.

Table 1. Connector schema information

Table type Schema file Default table name
Event Table
xrdsevts.txt
  
 xrdsevts10.txt (Clarify version 10)
 
table_xrds_events
 
Archive Table
xrdsarch.txt
  
 xrdsarch10.txt (Clarify version 10)
 
table_xrds_archive
 

Table 2 shows the location of the schema files (xrdsevts.txt, xrdsevts10.txt, xrdsarch.txt, and xrdsarch10.txt) based on the operating system.

Table 2. Schema file locations

Operating system Schem file location
Windows %ProductDir%\connectors\Clarify\dependencies\ddcomp
UNIX $ProductDir/connectors/Clarify/dependencies/ddcomp

To install the event and archive tables, follow these steps:

  1. Make sure that type IDs for the event and archive tables are available.
  2. Execute the ddcomp command to create the tables.
  3. Confirm that the event and archive tables were created.

These tasks are described in greater detail in the following sections.

Note:
If your site will not archive events into an archive table, be sure to remove the value for the connector's ArchiveTableName configuration parameter using System Manager.

Type IDs for the event and archive tables

By default, the type IDs of the event and archive tables are 501 and 502. Follow these steps to determine the type IDs for your site.

  1. Log into the Data Dictionary Editor and determine whether the type IDs 501 and 502 are available. If these IDs are available, you can skip to the next section. If the IDs are already in use, continue with the next step in this procedure.
  2. If type IDs 501 and 502 are not available, choose new type IDs. The range of numbers reserved for customer use is from 480 to 512 and from 2000 to 4999. Select type IDs within these ranges.
  3. Once you have chosen new type IDs, change the type IDs in the xrdsevts.txt and xrdsarch.txt scripts. To do this, open the xrdsevts.txt (xrdsevts10.txt for Clarify version 10)and xrdsarch.txt (xrdsarch10.txt for Clarify version 10)files in the directory appropriate for your operating system (see Table 2).
       OBJECT xrds_events 501
     
    

    to

       OBJECT xrds_events new_id 
     
    

When you have type IDs, continue with the next section.

Creating the event and archive tables

To create the event table and archive table, run the ddcomp command to execute the xrdsevts.txt (xrdsevts10.txt for Clarify version 10)and xrdsarch.txt (xrdsarch10.txt for Clarify version 10)scripts. The ddcomp utility creates the database schema and populates the Clarify CRM data dictionary with the information contained in the scripts. Typically, WebSphere Business Integration Adapter uses the ddcomp utility to create tables, such as the event table, or views as required by some Clarify CRM application-specific business objects.

Note that if you want to change the name of the event table, you can do this by changing the name in the xrdsevts.txt (xrdsevts10.txt for Clarify version 10)script before you run the ddcomp command. In this case, you will also need to set the EventTableName connector configuration parameter to the new name. To change the name of the archive table, change the name in the xrdsarch.txt (xrdsarch10.txt for Clarify version 10)script before running the ddcomp command, and set the ArchiveTableName configuration parameter to the new name.

Running the ddcomp command

You can execute the ddcomp command from the dbadmin directory and point to the schema-file directory (see Table 2). Alternatively, you can put the path for the directory containing the ddcomp executable in your PATH and run the ddcomp command from the schema-file directory.

Running ddcomp on SQL Server on Windows:

For Microsoft SQL Server on Windows, the syntax for the ddcomp command is:

ddcomp db_name db_server user_name password filename
 

where:

db_name
 
is the name of the Clarify CRM database.
db_server
 
is the name of the Clarify CRM database server.
user_name is the Clarify CRM system administrator login name.
password
 
is the Clarify CRM system administrator password.
filename
 
is the name of the event table or archive table script.
Running ddcomp on Oracle on Windows or Unix:

For Oracle, the syntax for the ddcomp command is:

ddcomp oracle_database oracle_alias user_name password filename
 

where:

oracle_database 
 
is the name of the Clarify CRM database.
oracle_alias 
 
is a relation that ties a physical machine name to an Oracle SID. An Oracle SID is a system identifier that points to the database. It can be thought of as the name of a database instance. Clarify CRM treats the oracle_alias as a server name. Whenever the Clarify CRM application asks for a server name, the oracle_alias can usually be substituted.
user_name is the Clarify CRM system administrator login name.
password
 
is the Clarify CRM system administrator password.
filename
 
is the name of the event table or archive table script.

On Oracle, the ddcomp and ddedit utilities also require you to build a public synonym for an object table before you can access that table using the Clarify CRM client or API. For the WebSphere Business Integration Adapter event and archive tables, execute the following SQL statements to create the public synonyms. Use SQL Plus to execute the statements.

create public synonym TABLE_XRDS_EVENTS for sa.TABLE_XRDS_EVENTS;
 create public synonym TABLE_XRDS_ARCHIVE for sa.TABLE_XRDS_ARCHIVE;
 

Confirming the event and archive tables

To confirm that the tables were created, use the Clarify CRM Data Dictionary Editor. Log into the Data Dictionary Editor and check for the existence of objects with type ID of 501 and 502, or for objects with the type IDs that you have substituted for those IDs.

Descriptions of table schema

The event table contains the following columns:

objid
 
Internal identifier
object_name
 
Description of the object
object_verb
 
Verb associated with the event
object_key
 
Primary key for the object
event_priority
 
Event priority. Defined as 0-1, where 0 is the highest priority.
event_time
 
Time of the event
event_status
 
Status of the transaction associated with the event; 0 = OK, 1 = ERROR
event_description
 
Description of the event or error string

The archive table contains the following columns:

objid
 
Internal identifier
event_id
 
Value of objid for the event when the event was in the event table
object_name
 
Description of the object
object_verb
 
Verb associated with the event
object_key
 
Primary key for the object
event_priority
 
Event priority
event_time
 
Time of the event
event_status
 
Status of the transaction associated with the event. Status can be Success, Error, or Skipped.
event_description
 
Description of the event or error string

Installing database triggers for event notifications

You install database triggers in Clarify CRM to support the business objects used by the collaborations running at your site. The database triggers are implemented so that a row is generated to the event table whenever an application object is created, updated, or deleted.

You also install a database trigger that deletes events from the event table. If an archive table is available, the event record is moved to the archive table; otherwise, it is lost. Table 3 shows the name of the event table deletion trigger based on the database server for the Clarify CRM database.

Table 3. SQL script names for event table deletion triggers

Database server Event table deletion trigger
MSSQL
MSSQL_event_delete_trigger.sql
 
Oracle
Oracle_event_delete_trigger.sql
  
 Oracle_event_delete_trigger10.sql (Clarify version 10)
 

The files in Table 3 are located in the directory:

Note:
Before you can execute the trigger scripts, you must have a user account in Clarify CRM. For information on creating a user account, see Prerequisites.

To execute the event table deletion SQL script and install the database trigger, follow these steps:

  1. Open a query window.
  2. Connect to the server that hosts the Clarify CRM database.
  3. Open the Clarify CRM database.
  4. Execute the script for the event table delete trigger. See Table 3 for the name of the script.
  5. Execute the following commands in your SQL processor:
    grant all on table_xrds_events to username
     grant all on table_xrds_archive to username
     
    

    For SQL Server, use isql_w or a similar tool.

If the script executes with no errors, a message indicates that no data was returned.

You might also need to execute scripts for triggers for the business objects required by your business processes. WebSphere Business Integration Adapter provides sample business objects. You can use these business objects or create your own customized business objects. To execute triggers for your business objects, perform the following steps:

  1. Identify the WebSphere Business Integration Adapter triggers for the business objects required by your business processes.
  2. Connect to the database server and open the Clarify CRM database.

    Follow Steps 1 through 3 in the previous list of steps.

  3. If the connector user name is the default "cw", skip to the next step. If the user name is not "cw", update the scripts with the correct connector user name before executing them. Setting the user name correctly prevents the trigger from generating an event for an application change that resulted from a business object request.

    To update the scripts, follow these steps:

    1. Edit each script to replace "cw" with the correct user name. Change the line:
         if (@user <> "cw")
       
      

      to read:

         if (@user <> "<username>")
       
      
    2. Recompile the scripts.

    Note that the ApplicationUserName connector configuration property should be set to the name for the Clarify CRM user account.

  4. Execute the scripts for the business object triggers. You must be the database owner to execute the scripts.

Copyright IBM Corp. 1997, 2003