Creating database tables
The BMC Change Automation installer automatically configures the Derby database to store the incoming data from an automation system and a change management system.
If you are using external databases such as External Derby, Oracle, or SQL, use this information to create the database tables. The following table lists the tables and their description.
Table | Purpose |
|---|---|
IncomingMessages | Stores all messages that are sent to BMC Atrium Orchestrator from any end point, which may be either automation system or BMC Remedy Change Management. |
AutomationHosts | Stores the host details of both the automation system and BMC Remedy Change Management. |
You can create database tables for the following supported databases:
External Derby
Create a table called AutomationHosts by using the following query:
HOSTINFO VARCHAR(255) NOT NULL,
PRODUCTCODE VARCHAR(255) NOT NULL,
LASTSUCCESSTIME_OUTBOUND VARCHAR(200) DEFAULT '0',
LASTSUCCESSTIME_TRANSACTIONAL VARCHAR(200) DEFAULT '0',
CONNECTIONDETAILS CLOB NOT NULL,
PRIMARY KEY (HOSTINFO)
);
Create a table called IncomingMessages by using the following query:
INPUTPARAMETERS CLOB NOT NULL,
STATUS VARCHAR(24) NOT NULL,
ORIGIN VARCHAR(255) NOT NULL,
JOBID VARCHAR(255),
CREATETIME BIGINT NOT NULL,
UPDATETIME BIGINT,
ERROR VARCHAR(1024),
ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
PRIMARY KEY (ID)
);
Index the table by using the following query:
CREATE INDEX productCode_idx ON CLA.AUTOMATIONHOSTS(PRODUCTCODE);
CREATE INDEX origin_idx ON CLA.INCOMINGMESSAGES(ORIGIN);
Oracle12c
Create a table called AutomationHosts by using the following query:
HOSTINFO varchar2(255) not null,
PRODUCTCODE varchar2(255) not null,
LASTSUCCESSTIME_OUTBOUND varchar2(200) default '0',
LASTSUCCESSTIME_TRANSACTIONAL varchar2(200) default '0',
CONNECTIONDETAILS CLOB not null,
CONSTRAINT HOSTINFO_PK PRIMARY KEY (HOSTINFO)
);
Create a table called IncomingMessages by using the following query:
INPUTPARAMETERS CLOB NOT NULL,
STATUS VARCHAR2(24) NOT NULL,
ORIGIN VARCHAR2(255) NOT NULL,
JOBID VARCHAR2(255),
CREATETIME NUMBER NOT NULL,
UPDATETIME NUMBER ,
ERROR VARCHAR2(1024),
ID number generated as identity,
CONSTRAINT ID_PK PRIMARY KEY (ID)
)
Index the table by using the following query:
CREATE INDEX productCode_idx ON CLA.AUTOMATIONHOSTS(PRODUCTCODE);
CREATE INDEX origin_idx ON CLA.INCOMINGMESSAGES(ORIGIN);
SQL Server 2012
Create a table called AutomationHosts by using the following query:
HOSTINFO varchar(255) not null,
PRODUCTCODE varchar(255) not null,
LASTSUCCESSTIME_OUTBOUND varchar(200) default '0',
LASTSUCCESSTIME_TRANSACTIONAL varchar(200) default '0',
CONNECTIONDETAILS VARCHAR(MAX) not null,
CONSTRAINT HOSTINFO_PK PRIMARY KEY (HOSTINFO)
);
Create a table called IncomingMessages by using the following query:
INPUTPARAMETERS VARCHAR(MAX) not null,
STATUS varchar(24) not null,
ORIGIN varchar(255) not null,
JOBID varchar(255),
CREATETIME BIGINT not null,
UPDATETIME BIGINT,
ERROR varchar(1024),
ID int not null identity(1,1) primary key
)
Index the table by using the following query:
CREATE INDEX productCode_idx ON CLA.AUTOMATIONHOSTS(PRODUCTCODE);
CREATE INDEX origin_idx ON CLA.INCOMINGMESSAGES(ORIGIN);
Related topics
Configuring-external-database-type-in-BMC-Atrium-Orchestrator