DDL Scripts

In this section:

The Correlation Driver and the Audit Driver (also called the Activity Log Driver) attempt to construct the tables needed for record keeping. Some databases cannot use the built-in DDL scripts and must be constructed manually as part of the installation. This chapter contains some sample DDL scripts for various databases. To apply the statements in an SQL DDL file, run the script against a remote database.

Note (PDF and Online Only): Each of the following scripts is accompanied by an attached file that contains the text of the script. To view a script in an external editor, double-click the icon corresponding to the script you would like to view, then select Open this file. To save the attached file, right-click the icon, select Save Embedded File to Disk, and change the file extension to .sql. This option is not available in printed copies of this manual.
Top of page
x
xalog_mssql.sql
CREATE TABLE [IAM_ACTIVITY] (
  [recordkey] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [recordtype] [int] NULL ,
  [signature] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [protocol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [address] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [tstamp] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [correlid] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [tid] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [msg] [image] NULL ,
  [context] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [text] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [status] [int] NULL ,
  [subtype] [int] NULL ,
  [partner_to] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [partner_from] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [encoding] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Top of page
x
xalog_mysql.sql
CREATE TABLE IAM_ACTIVITY (
     recordkey varchar (65) NOT NULL,  
     recordtype int,  
     signature varchar (64) NULL,  
     protocol varchar (64) NULL,  
     address varchar (128) NULL,  
     tstamp varchar (65) NULL,  
     correlid varchar (128) NULL,  
     tid varchar (128) NULL,  
     msg BLOB NULL ,  
     context varchar (4000) NULL,  
     text varchar (2000) NULL, 
     status int NULL,  
     subtype int NULL, 
     partner_to varchar (128) NULL,  
     partner_from varchar (128) NULL,  
     encoding varchar (32) NULL 
)

Note: If you are setting up MYSQL for your data storage, be aware that MYSQL categorizes blobs by size. You may need to modify the script if you are logging messages and the messages may exceed the maximum for a standard BLOB. Failure to do so can result in a "data too long for column" error.

For example,

TINYBLOB (or TINYTEXT) = 1+(2^ 8) = 257 characters

BLOB (or TEXT) = 2+(2^16) = 65538 characters

MEDIUMBLOB (or MEDIUMTEXT) = 3+(2^24) = 16777219 characters

LONGBLOB (or LONGTEXT) = 4+(2^32) = 4294967300 characters
Top of page
x
xalog_oracle.sql
CREATE TABLE IAM_ACTIVITY
(
  RECORDKEY     VARCHAR2(65 BYTE)                   NULL,
  RECORDTYPE    INTEGER                             NULL,
  SIGNATURE     VARCHAR2(64 BYTE)                   NULL,
  PROTOCOL      VARCHAR2(64 BYTE)                   NULL,
  ADDRESS       VARCHAR2(128 BYTE)                  NULL,
  TSTAMP        VARCHAR2(65 BYTE)                   NULL,
  CORRELID      VARCHAR2(128 BYTE)                  NULL,
  TID           VARCHAR2(128 BYTE)                  NULL,
  MSG           BLOB                                NULL,
  CONTEXT       VARCHAR2(4000 BYTE)                 NULL,
  TEXT          VARCHAR2(2000 BYTE)                 NULL,
  STATUS        INTEGER                             NULL,
  SUBTYPE       INTEGER                             NULL,
  PARTNER_TO    VARCHAR2(128 BYTE)                  NULL,
  PARTNER_FROM  VARCHAR2(128 BYTE)                  NULL,
  ENCODING      VARCHAR2(32 BYTE)                   NULL 
)
Top of page
x
xanotes.sql
CREATE TABLE IAM_ACTIVITY_NOTES (
  NoteKey varchar (65) NOT NULL, 
  ActivityTID varchar (128) NOT NULL, 
  ActivityRecordKey varchar (65) NULL, 
  NoteTime varchar (65) NULL ,
  NoteAuthor varchar (20) NULL, 
  NoteText varchar (264) NULL ,
  PrevNoteKey varchar (65) NULL 
)
iWay Software