btn-mdw-ace-server-local/mwconfig/init/001.MWCONFIG.sql

CREATE TABLE MWCONFIG.ADAPTOR(
    ID VARCHAR(256) NOT NULL,
    DESCRIPTION VARCHAR(2000),
    ASYNCHRONOUS SMALLINT,
    MODIFIED_BY VARCHAR(64) NOT NULL,
    TIMESTAMP TIMESTAMP NOT NULL,
    PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.CODEX(
    ID VARCHAR(256) NOT NULL,
    ALLOW_PARTIAL SMALLINT,
    XML LONG VARCHAR,
    MODIFIED_BY VARCHAR(64) NOT NULL,
    TIMESTAMP TIMESTAMP NOT NULL,
    PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.ADAPTOR_CODEX(
    ADAPTOR_ID VARCHAR(256) NOT NULL,
    ID VARCHAR(256) NOT NULL,
    CODEX_ID VARCHAR(256) NOT NULL,
    PRIMARY KEY(ADAPTOR_ID,ID),
    FOREIGN KEY(ADAPTOR_ID) REFERENCES MWCONFIG.ADAPTOR(ID),
    FOREIGN KEY(CODEX_ID) REFERENCES MWCONFIG.CODEX(ID)
)~

CREATE TABLE MWCONFIG.ADAPTOR_PARAM(
    ADAPTOR_ID VARCHAR(256) NOT NULL,
    NAME VARCHAR(256) NOT NULL,
    VALUE VARCHAR(256),
    PRIMARY KEY(ADAPTOR_ID,NAME),
    FOREIGN KEY(ADAPTOR_ID) REFERENCES MWCONFIG.ADAPTOR(ID)
)~

CREATE TABLE MWCONFIG.CHARGES(
	ID VARCHAR(512) NOT NULL,
	C1_VALUE DECIMAL(12,2),
	C1_NAME VARCHAR(256),
	C1_SCRIPT VARCHAR(1024),
	C1_ACCOUNT VARCHAR(64),
	C2_VALUE DECIMAL(12,2),
	C2_NAME VARCHAR(256),
	C2_SCRIPT VARCHAR(1024),
	C2_ACCOUNT VARCHAR(64),
	C3_VALUE DECIMAL(12,2),
	C3_NAME VARCHAR(256),
	C3_SCRIPT VARCHAR(1024),
	C3_ACCOUNT VARCHAR(64),
	C4_VALUE DECIMAL(12,2),
	C4_NAME VARCHAR(256),
	C4_SCRIPT VARCHAR(1024),
	C4_ACCOUNT VARCHAR(64),
	C5_VALUE DECIMAL(12,2),
	C5_NAME VARCHAR(256),
	C5_SCRIPT VARCHAR(1024),
	C5_ACCOUNT VARCHAR(64),
	C6_VALUE DECIMAL(12,2),
	C6_NAME VARCHAR(256),
	C6_SCRIPT VARCHAR(1024),
	C6_ACCOUNT VARCHAR(64),
	C7_VALUE DECIMAL(12,2),
	C7_NAME VARCHAR(256),
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.CLIENT(
	ADAPTOR_ID VARCHAR(256) NOT NULL,
	IDLE_WAIT BIGINT,
	TIMEOUT SMALLINT,
	CLOSE_QUEUE VARCHAR (256),
	CONTROL_QUEUE VARCHAR(256),
	KEEP_CONN SMALLINT,
	REUSE_DELAY SMALLINT,
	NODE VARCHAR(256),
	PRIMARY KEY(ADAPTOR_ID),
	FOREIGN KEY(ADAPTOR_ID) REFERENCES MWCONFIG.ADAPTOR(ID)
)~

CREATE TABLE MWCONFIG.CLIENT_TARGET(
	CLIENT_ID VARCHAR(256) NOT NULL,
	ID VARCHAR(256) NOT NULL,
	HOST VARCHAR(256) NOT NULL,
	SEQ INTEGER NOT NULL,
	WEIGHT INTEGER,
	PRIMARY KEY(CLIENT_ID,ID),
	FOREIGN KEY(CLIENT_ID) REFERENCES MWCONFIG.CLIENT(ADAPTOR_ID)
)~

CREATE TABLE MWCONFIG.CONFIG_METADATA(
	ACTIVE SMALLINT,
	XML LONG VARCHAR,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL
)~

CREATE TABLE MWCONFIG.DBCONN(
	ID VARCHAR(256) NOT NULL,
	DRIVER VARCHAR(256) NOT NULL,
	URL VARCHAR(256) NOT NULL,
	USERNAME VARCHAR(64) NOT NULL,
	PASSWORD VARCHAR(256) NOT NULL,
	SCHEMA VARCHAR(32) NOT NULL,
	TIMEOUT INTEGER,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.DBQUERY(
	ID VARCHAR(256) NOT NULL,
	QUERY LONG VARCHAR NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.DEV_TELLER_MAP (
	ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20),
	GROUP VARCHAR(64) NOT NULL,
	DEVICE_ID VARCHAR(256) NOT NULL,
	DEVICE_NAME VARCHAR(256) NOT NULL,
	TERMINAL_ID VARCHAR(256) NOT NULL,
	TERMINAL_IP VARCHAR(64) NOT NULL,
	TELLER_ID VARCHAR(256) NOT NULL,
	CTRL_UNIT_ID VARCHAR(256) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID),
	UNIQUE (GROUP,DEVICE_ID)
)~

CREATE TABLE MWCONFIG.DTREE (
	GROUP VARCHAR(256) NOT NULL,
	PATH VARCHAR(512) NOT NULL,
	VALUE VARCHAR(512) NOT NULL,
	MODULE VARCHAR(256),
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(GROUP,PATH)
)~

CREATE TABLE MWCONFIG.DTREE_MAP (
	GROUP VARCHAR(256) NOT NULL,
	PATH VARCHAR(512) NOT NULL,
	NAME VARCHAR(128) NOT NULL,
	VALUE VARCHAR(256) NOT NULL,
	PRIMARY KEY(GROUP,PATH,NAME)
)~

CREATE TABLE MWCONFIG.ERROR_MAP(
	GROUP VARCHAR(256) NOT NULL,
	ORIGINAL VARCHAR(256) NOT NULL,
	TARGET VARCHAR(256),
	DETAIL VARCHAR(256),
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(GROUP,ORIGINAL)
)~

CREATE TABLE MWCONFIG.HOST(
	ID VARCHAR(256) NOT NULL,
	HOST VARCHAR(256) NOT NULL,
	USERNAME VARCHAR(64),
	PASSWORD VARCHAR(256),
	CONTEXT VARCHAR(1024),
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.MAPPING(
	ID VARCHAR(256) NOT NULL,
	DESCRIPTION LONG VARCHAR,
	MODULE VARCHAR(256),
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.MAPPING_GROUP(
	MAPPING_ID VARCHAR(256) NOT NULL,
	ID BIGINT NOT NULL,
	SOURCE LONG VARCHAR,
	TARGET LONG VARCHAR,
	INCLUDE_MAPPING_ID VARCHAR(256),
	INCLUDE_ID BIGINT,
	PRIMARY KEY(MAPPING_ID,ID),
	FOREIGN KEY(MAPPING_ID) REFERENCES MWCONFIG.MAPPING(ID),
	FOREIGN KEY (INCLUDE_MAPPING_ID,INCLUDE_ID) REFERENCES MWCONFIG.MAPPING_GROUP(MAPPING_ID,ID)
)~

CREATE TABLE MWCONFIG.MAPPING_GROUP_LINE(
	MAPPING_ID VARCHAR(256) NOT NULL,
	MAPPING_GROUP_ID BIGINT NOT NULL,
	NAME VARCHAR(256) NOT NULL,
	TEXT LONG VARCHAR NOT NULL,
	SEQ INTEGER,
	PRIMARY KEY(MAPPING_ID,MAPPING_GROUP_ID,NAME),
	FOREIGN KEY(MAPPING_ID,MAPPING_GROUP_ID) REFERENCES MWCONFIG.MAPPING_GROUP(MAPPING_ID,ID)
)~

CREATE TABLE MWCONFIG.PARAM_MAP(
	GROUP VARCHAR(256) NOT NULL,
	NAME VARCHAR(256) NOT NULL,
	VALUE VARCHAR(1024) NOT NULL,
	DESCRIPTION VARCHAR(256),
	SEQ INTEGER,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(GROUP,NAME)
)~

CREATE TABLE MWCONFIG.PARSER(
	ID VARCHAR(256) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.PARSER_CODEX(
	PARSER_ID VARCHAR(256) NOT NULL,
	ID VARCHAR(256) NOT NULL,
	CODEX_ID VARCHAR(256) NOT NULL,
	PRIMARY KEY(PARSER_ID,ID),
	FOREIGN KEY(PARSER_ID) REFERENCES MWCONFIG.PARSER(ID),
	FOREIGN KEY(CODEX_ID) REFERENCES MWCONFIG.CODEX(ID)
)~

CREATE TABLE MWCONFIG.RELOADTS(
	BROKER VARCHAR(256) NOT NULL,
	EXECUTIONGROUP  VARCHAR(256) NOT NULL,
	TYPE  VARCHAR(16) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(BROKER, EXECUTIONGROUP, TYPE)
)~

CREATE TABLE MWCONFIG.ROUTING_TABLE(
	ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20),
	CODE_START VARCHAR(256),
	CODE_END VARCHAR(256),
	CHANNEL VARCHAR(256),
	START_DATE TIMESTAMP,
	END_DATE TIMESTAMP,
	QUEUE VARCHAR(256),
	STATUS VARCHAR(20),
	SUBCODEX VARCHAR(256),
	ADDITIONAL INTEGER,
	REPLY_TO VARCHAR(256),
	REPLY_TO_QMGR VARCHAR(256),
	CREATE_BY VARCHAR(256) NOT NULL,
	CREATE_DATE TIMESTAMP NOT NULL,
	MODIFIED_BY VARCHAR(256),
	MODIFIED_DATE TIMESTAMP,
	PRIMARY KEY(ID)
)~

CREATE TABLE MWCONFIG.RPG_CALL(
	ID VARCHAR(256) NOT NULL,
	CMD  VARCHAR(1024) NOT NULL,
	HOST VARCHAR(256) NOT NULL,
	TIMEOUT INTEGER,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	TIMESTAMP TIMESTAMP NOT NULL,
	PRIMARY KEY(ID),
	FOREIGN KEY(HOST) REFERENCES MWCONFIG.HOST(ID)
)~

CREATE TABLE MWCONFIG.RPG_CALL_ARG(
	RPG_CALL_ID VARCHAR(256) NOT NULL,
	ID INTEGER NOT NULL,
	NAME  VARCHAR(256) NOT NULL,
	TYPE VARCHAR(64) NOT NULL,
	LENGTH INTEGER NOT NULL,
	PRIMARY KEY(RPG_CALL_ID,ID),
	FOREIGN KEY(RPG_CALL_ID) REFERENCES MWCONFIG.RPG_CALL(ID)
)~

CREATE TABLE MWCONFIG.SERVER_PORT(
	ADAPTOR_ID VARCHAR(256) NOT NULL,
	PORT INTEGER NOT NULL,
	SERVER VARCHAR(256),
	GROUP VARCHAR(256),
	PRIMARY KEY(ADAPTOR_ID),
	FOREIGN KEY(ADAPTOR_ID) REFERENCES MWCONFIG.ADAPTOR(ID)
)~

-- MWCONFIG.TOKEN_3RD_PARTY definition

CREATE TABLE "MWCONFIG"."TOKEN_3RD_PARTY"  (
		  "CODE" VARCHAR(255 OCTETS) , 
		  "TOKEN" VARCHAR(4000 OCTETS) , 
		  "EXPIRY" VARCHAR(255 OCTETS)
)~