The DB2 DDL script "fcm_ddl_db2.sql" that is shipped with SAS⪚ Financial Crimes Monitor creates a unique constraint for the FCM_ALERT table. This constraint causes Alert Process Generation to fail with an error:
ERROR: CLI execute error: [IBM][CLI Driver][DB2/NT64] SQL0803N One or more values in
the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE
statement are not valid because the primary key, unique constraint or unique index
identified by "2" constrains table FINCRMDB.FCM_ALERT" from having duplicate values
for the index key.
SQLSTATE=23505
New and existing SAS Financial Crimes Monitor 2.3 deployments, and sites applying the first maintenance release, must take action to alter the DDL. Use the following instructions for running the DDL Alter script for DB2 for SAS Financial Crimes Monitor 2.3 and 2.3_M1.
New Financial Crimes Monitor 2.3 deployments can modify the "fcm_ddl_db2.sql" script by replacing the FCM_TABLE SQL commands with the following code:
CREATE TABLE FCM_ALERT
(
ALERT_SK NUMERIC(19) NOT NULL ,
SEGMENT_ID VARCHAR(128) NOT NULL ,
RECORD_VERSION_NO NUMERIC(10) ,
PROJECT_SK NUMERIC(10) NOT NULL ,
SCENARIO_SK NUMERIC(10) NOT NULL ,
ENTITY_SK NUMERIC(10) NOT NULL ,
ENTITY_VARIABLE_SK NUMERIC(10) NOT NULL ,
ALERT_STATUS_CD VARCHAR(20) ,
SCENARIO_SEVERITY_SCORE_NO NUMERIC(10,2) ,
SCENARIO_DESC VARCHAR(100) ,
ALERT_SUPPRESSED_FLG CHAR(1) ,
SUPPRESSION_END_DT TIMESTAMP ,
ALERT_TRIGGER_TXT VARCHAR(512) ,
RUN_DT TIMESTAMP ,
OPEN_FLG CHAR(1) ,
RISK_FLG CHAR(1) ,
ENTITY_NM VARCHAR(255) ,
RECIPIENT_ID VARCHAR(32) NOT NULL ,
RECIPIENT_TYPE_CD VARCHAR(20) NOT NULL ,
CREATED_USER_ID VARCHAR(32) ,
PROCESSED_DTTM TIMESTAMP
);
ALTER TABLE FCM_ALERT
ADD PRIMARY KEY (ALERT_SK);
CREATE INDEX XAK3FCM_ALERT ON FCM_ALERT
(PROJECT_SK ASC);
CREATE INDEX XIF4FCM_ALERT ON FCM_ALERT
(SCENARIO_SK ASC);
Existing SAS Financial Crimes Monitor 2.3 deployments and sites that are applying the first maintenance release must first drop the FCM_ALERT table that was previously created. Then, the same SQL code that was outlined for new SAS Financial Crimes Monitor 2.3 deployments immediately above can be run. Here is an example SQL statement for dropping the table:
New SAS Financial Crimes Monitor 2.3_M1 deployments do not require that you take action to alter the DDL for default deployments.
The DB2 DDL script "fcm_ddl_db2.sql" is shipped with the above modifications.
Operating System and Release Information
| SAS System | SAS Financial Crimes Monitor | Microsoft® Windows® for x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows Server 2003 Datacenter Edition | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows Server 2003 Enterprise Edition | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows Server 2003 Standard Edition | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows Server 2003 for x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows Server 2008 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows Server 2008 for x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Microsoft Windows XP Professional | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Windows 7 Enterprise 32 bit | 2.3 | | 9.3 TS1M2 | |
| Windows 7 Enterprise x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Windows 7 Home Premium 32 bit | 2.3 | | 9.3 TS1M2 | |
| Windows 7 Home Premium x64 | 2.3 | | 9.3 TS1M2 | |
| Windows 7 Professional 32 bit | 2.3 | | 9.3 TS1M2 | |
| Windows 7 Professional x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Windows 7 Ultimate 32 bit | 2.3 | | 9.3 TS1M2 | |
| Windows 7 Ultimate x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Windows Vista | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Windows Vista for x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| 64-bit Enabled AIX | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| 64-bit Enabled Solaris | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| HP-UX IPF | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Linux for x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
| Solaris for x64 | 2.3 | 2.3_M1 | 9.3 TS1M2 | 9.3 TS1M2 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.