DB2 CREATE DATABASE FINANCE ON H:

DB2 CONNECT TO FINANCE

DB2 DROP TABLE BOOKS
DB2 DROP TRIGGER ADDRECNUMBOOK
DB2 DROP TABLE ACCOUNTS

DB2 DROP TABLE ACC_INFO
DB2 DROP TRIGGER ADDRECNUMA
DB2 DROP TRIGGER UPDTRANSNUM
DB2 DROP TRIGGER UPDBALINFO
DB2 DROP TRIGGER UPDBALINFOA
DB2 DROP TRIGGER UPDACCBAL
DB2 DROP TRIGGER UPDBALINFODDEL
DB2 DROP TRIGGER UPDBALINFOADEL
DB2 DROP TRIGGER UPDACCBALDEL

DB2 DROP TABLE ACC_ADDRESS
DB2 DROP TRIGGER ADDADDRRECNUMA

DB2 DROP TABLE ACCOUNT_TYPES
DB2 DROP TRIGGER ADDTYPERECNUMA

DB2 DROP TABLE CATAGORY
DB2 DROP TRIGGER ADDCATARECNUMA
DB2 DROP TABLE CATTYPE
DB2 DROP TRIGGER ADDCATTYPENUMA


DB2 DROP TABLE IMPORTFILE

DB2 DROP TABLE CHECKIMG

DB2 DROP TABLE SPLITINFO

DB2 DROP TABLE TAXFORMS

DB2 DROP TABLE TAXFORMDATA
DB2 DROP TRIGGER TAXFORMDREC
REM /* NO NEED TO DROP INDEXES THEY ARE DELETED WHEN TABLE IS DROPPED */

DB2 DROP TABLE ACC_TAXINFO

DB2 CREATE TABLE BOOKS(RECORD_NUMBER INTEGER NOT NULL WITH DEFAULT 0, DESCRIPTION CHAR(60), ACCOUNT_NUMBER INTEGER NOT NULL WITH DEFAULT 0)
DB2 CREATE TRIGGER ADDRECNUMBOOK AFTER INSERT ON BOOKS REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE BOOKS SET RECORD_NUMBER = ((SELECT MAX(RECORD_NUMBER) FROM BOOKS) + 1) WHERE DESCRIPTION = I.DESCRIPTION;END
DB2 CREATE INDEX IDXACCOUNTSH ON ACCOUNTS(ACCOUNT_HOLDER)
DB2 CREATE INDEX IDXACCOUNTSHN ON ACCOUNTS(ACCOUNT_HOLDER, ACCOUNT_NAME)
DB2 CREATE INDEX IDXACCOUNTREC ON ACCOUNTS(RECORD_NUMBER)

DB2 CREATE TABLE ACCOUNTS(BOOK_NUMBER INTEGER NOT NULL WITH DEFAULT 0, "RECORD_NUMBER" INTEGER NOT NULL WITH DEFAULT 0, "ACCOUNT_HOLDER" CHAR(40), "ACCOUNT_NAME" CHAR(40), "ACCOUNT_NUMBER" CHAR(40), "ACCOUNT_BALANCE" DECIMAL(9,2), ACCOUNT_LIMIT DECIMAL(9,2), ACCOUNT_TYPE INTEGER, COMMENTS CHAR(100), ACTIVE_FROM DATE, ACTIVE_TO DATE, EXPIRED CHAR NOT NULL DEFAULT ' ') IN "USERSPACE1"
DB2 CREATE TRIGGER ADDRECNUMA AFTER INSERT ON ACCOUNTS REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE ACCOUNTS SET RECORD_NUMBER = ((SELECT MAX(RECORD_NUMBER) FROM ACCOUNTS) + 1) WHERE ACCOUNT_HOLDER = I.ACCOUNT_HOLDER AND ACCOUNT_NAME = I.ACCOUNT_NAME;END

REM DB2 CREATE TABLE ACC_INFO("RECORD_NUMBER" INTEGER, ACCOUNT_REF INTEGER, CROSSREC INTEGER, CROSSACC INTEGER, ACC_DATE DATE, "TRANS_NUM" INTEGER NOT NULL WITH DEFAULT 0, "AMOUNT" DECIMAL(9,2), "BALANCE" DECIMAL(9,2) NOT NULL WITH DEFAULT 0, CROSSBALANCE DECIMAL(9,2) NOT NULL WITH DEFAULT 0,"CLEARED" CHAR, "CHK_NUM" CHAR(20), "PAYEE" CHAR(60), "MEMO" CHAR(60), "ADDRESS_REF" INTEGER, "CATEGORY" INTEGER NOT NULL WITH DEFAULT 0, "CATSPLIT" INTEGER NOT NULL WITH DEFAULT 0, "MEMOSPLIT" INTEGER NOT NULL WITH DEFAULT 0, "DOLLARSPLIT" DECIMAL(9,2))
DB2 CREATE TABLE ACC_INFO("RECORD_NUMBER" INTEGER, ACCOUNT_REF INTEGER, CROSSREC INTEGER, CROSSACC INTEGER, CROSSNUM INTEGER, ACC_DATE DATE, "TRANS_NUM" INTEGER NOT NULL WITH DEFAULT 0, "AMOUNT" DECIMAL(9,2), "BALANCE" DECIMAL(9,2) NOT NULL WITH DEFAULT 0, CROSSBALANCE DECIMAL(9,2) NOT NULL WITH DEFAULT 0,"CLEARED" CHAR, "CHK_NUM" CHAR(20), "PAYEE" CHAR(60), "MEMO" CHAR(60), "CATEGORY" INTEGER NOT NULL WITH DEFAULT 0, STMTBAL DECIMAL(9,2) NOT NULL WITH DEFAULT 0)

REM DB2 CREATE TRIGGER SETRECNUMINFO NO CASCADE BEFORE INSERT ON ACC_INFO REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM ACC_INFO), 0);END
DB2 CREATE TRIGGER UPDTRANSNUM NO CASCADE BEFORE INSERT ON ACC_INFO REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC  SET I.TRANS_NUM = VALUE(((SELECT MAX(TRANS_NUM) FROM ACC_INFO WHERE ACC_DATE = I.ACC_DATE) + 1), 1);END
DB2 CREATE TRIGGER UPDBALINFO NO CASCADE BEFORE INSERT ON ACC_INFO REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.BALANCE = I.AMOUNT + VALUE((SELECT BALANCE FROM ACC_INFO WHERE ACC_DATE = (SELECT MAX(ACC_DATE) FROM ACC_INFO WHERE "ACC_DATE <= I.ACC_DATE" AND ACCOUNT_REF = I.ACCOUNT_REF) AND TRANS_NUM = (SELECT MAX(TRANS_NUM) FROM ACC_INFO WHERE ACC_DATE = (SELECT MAX(ACC_DATE) FROM ACC_INFO "WHERE ACC_DATE <= I.ACC_DATE" AND ACCOUNT_REF = I.ACCOUNT_REF) AND ACCOUNT_REF = I.ACCOUNT_REF) AND ACCOUNT_REF = I.ACCOUNT_REF ),0);END
DB2 CREATE TRIGGER UPDBALINFOA AFTER INSERT ON ACC_INFO REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL WHEN ("(SELECT COUNT(*) FROM ACC_INFO WHERE ACC_DATE > I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF) > 0") BEGIN ATOMIC UPDATE ACC_INFO SET BALANCE = BALANCE + I.AMOUNT "WHERE ACC_DATE > I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF";END
DB2 CREATE TRIGGER UPDACCBAL AFTER INSERT ON ACC_INFO REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE ACCOUNTS SET ACCOUNT_BALANCE = ACCOUNT_BALANCE + I.AMOUNT WHERE RECORD_NUMBER = ACCOUNT_REF;END
DB2 CREATE TRIGGER UPDBALINFODDEL AFTER DELETE ON ACC_INFO REFERENCING OLD AS I FOR EACH ROW MODE DB2SQL WHEN ("(SELECT COUNT(*) FROM ACC_INFO WHERE ACC_DATE = I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF AND TRANS_NUM > I.TRANS_NUM) > 0") BEGIN ATOMIC UPDATE ACC_INFO SET BALANCE = BALANCE - I.AMOUNT "WHERE ACC_DATE = I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF AND TRANS_NUM > I.TRANS_NUM";END
DB2 CREATE TRIGGER UPDBALINFOADEL AFTER DELETE ON ACC_INFO REFERENCING OLD AS I FOR EACH ROW MODE DB2SQL WHEN ("(SELECT COUNT(*) FROM ACC_INFO WHERE ACC_DATE > I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF) > 0") BEGIN ATOMIC UPDATE ACC_INFO SET BALANCE = BALANCE - I.AMOUNT "WHERE ACC_DATE > I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF";END
DB2 CREATE TRIGGER UPDACCBALDEL AFTER DELETE ON ACC_INFO REFERENCING OLD AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE ACCOUNTS SET ACCOUNT_BALANCE = ACCOUNT_BALANCE - I.AMOUNT WHERE RECORD_NUMBER = ACCOUNT_REF;END
DB2 CREATE INDEX IDXDATETRANS ON ACC_INFO(ACCOUNT_REF, ACC_DATE, TRANS_NUM)
DB2 CREATE INDEX IDXACCDATE ON ACC_INFO(ACCOUNT_REF, ACC_DATE)
DB2 CREATE INDEX IDXACCREC ON ACC_INFO(RECORD_NUMBER)

DB2 CREATE TABLE ACC_ADDRESS(INFO_REC_NUM INTEGER, RECORD_NUMBER INTEGER, INFO_ADDRESS CHAR(60))
DB2 CREATE TRIGGER ADDADDRRECNUMA NO CASCADE BEFORE INSERT ON ACC_ADDRESS REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT COUNT(*) FROM ACC_ADDRESS WHERE INFO_REC_NUM = I.INFO_REC_NUM), 0);END

DB2 CREATE TABLE ACCOUNT_TYPES(RECORD_NUMBER INTEGER, DESCRIPTION CHAR(40))
DB2 CREATE TRIGGER ADDTYPERECNUMA NO CASCADE BEFORE INSERT ON ACCOUNT_TYPES REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM ACCOUNT_TYPES),0);END

DB2 CREATE TABLE CATTYPE(RECORD_NUMBER INTEGER, NAME CHAR(10), DESCRIPTION CHAR(40))
DB2 CREATE TRIGGER ADDCATTYPENUMA NO CASCADE BEFORE INSERT ON CATTYPE REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM CATTYPE), 0);END

DB2 CREATE TABLE IMPORTFILE(ACCOUNT_REF INTEGER, FILE_NAME VARCHAR(512), IMPORT_TIMESTAMP TIMESTAMP)
REM DB2 CREATE INDEX IDXIMPFILE ON IMPORTFILE(ACCOUNT_REF, FILE_NAME)

DB2 CREATE TABLE CHECKIMG(ACCOUNT_REF INTEGER, CHKIMG BLOB(1 M) NOT NULL)
DB2 CREATE INDEX IDXCHKIMG ON CHECKIMG(ACCOUNT_REF)

DB2 CREATE TABLE SPLITINFO(INFO_REC_NUM INTEGER, CATEGORY INTEGER, MEMO CHAR(60), AMOUNT DECIMAL(9,2), MEMORIZED INTEGER)
DB2 CREATE INDEX IDXSPLINFO ON SPLITINFO(INFO_REC_NUM)

DB2 CREATE TABLE TAXFORMS(ACCOUNT_REF INTEGER, FORM_NUM CHAR(20))
DB2 CREATE INDEX IDXTAXFORM ON TAXFORMS(ACCOUNT_REF)

DB2 CREATE TABLE TAXFORMDATA(RECORD_NUM INTEGER, FORM_NUM CHAR(20), LINENUM INTEGER, SUBCHAR CHAR NOT NULL DEFAULT ' ', DESCRIP CHAR(30))
DB2 CREATE TRIGGER TAXFORMDREC NO CASCADE BEFORE INSERT ON TAXFORMDATA REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUM = 1 + VALUE((SELECT MAX(RECORD_NUM) FROM TAXFORMDATA), 0);END
DB2 CREATE INDEX IDXFORMDATA ON TAXFORMDATA(FORM_NUM, LINENUM)

DB2 CREATE TABLE ACC_TAXINFO(FORMDATA_NUM INTEGER, ACCINFO_NUM INTEGER)
DB2 CREATE INDEX IDXFORMDATATAX ON ACC_TAXINFO(FORMDATA_NUM, ACCINFO_NUM)

DB2 CREATE TABLE BUDGET(RECORD_NUMBER INTEGER, CATEGORY INTEGER, BEGIN_DATE DATE, END_DATE DATE, MONTH INTEGER, AMOUNT DECIMAL(9,2))
DB2 CREATE INDEX IDXBUDGETCATMON ON BUDGET(CATEGORY, MONTH)

DB2 CREATE TABLE CATEGORY(RECORD_NUMBER INTEGER, PARENT INTEGER, NAME CHAR(40), DESCRIP CHAR(40), CAT_TYPE INTEGER, BUDGET INTEGER, TAX_SCHEDULE INTEGER)
DB2 CREATE INDEX IDXCATEGORYNAME ON CATEGORY(PARENT, NAME)
DB2 CREATE TRIGGER ADDCATRECNUMA NO CASCADE BEFORE INSERT ON CATEGORY REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM CATEGORY),0);END

DB2 CREATE TABLE BUDGET(RECORD_NUMBER INTEGER, CATEGORY INTEGER, BEGIN_DATE DATE, END_DATE DATE, MONTH INTEGER, AMOUNT DECIMAL(9,2))
DB2 CREATE TRIGGER ADDBUDGETRECNUMA NO CASCADE BEFORE INSERT ON BUDGET REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM BUDGET),0);END

DB2 CREATE TABLE MEMORIZED(RECORD_NUMBER INTEGER, TRANS_TYPE INTEGER, AMOUNT DECIMAL(9,2), CLEARED INTEGER, PAYEE CHAR(40), MEMO CHAR(40), ADDRESS INTEGER, CATEGORY INTEGER, AMORT1PAYDATE DATE, AMORTYEARS INTEGER, AMORTNUMPAID INTEGER, AMORTNUMPER INTEGER, AMORTINTEREST DECIMAL(6, 4), AMORTBAL DECIMAL(9,2), AMORTORGAMT DECIMAL(9,2))
REM "this is done in the code" DB2 CREATE TRIGGER ADDMEMRECNUMA NO CASCADE BEFORE INSERT ON MEMORIZED REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM MEMORIZED),0);END
DB2 CREATE INDEX IDXMEMTYPEPAY ON MEMORIZED(TRANS_TYPE, PAYEE)

DB2 CREATE TABLE CLASS(RECORD_NUMBER INTEGER, NAME CHAR(40), DESCRIP CHAR(40))
DB2 CREATE TRIGGER ADDCLASSRECNUMA NO CASCADE BEFORE INSERT ON CLASS REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM CLASS),0);END

DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 1, 'Wages, tips other compensation')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 2, 'Federal income tax withheld')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 3, 'Social security wages')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 4, 'Social security tax withheld')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 5, 'Medicare wages and tips')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 6, 'Medicare tax withheld')

DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('CHECKING')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('SAVINGS')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('CREDIT CARD')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('CASH')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('MONEY MARKET')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('LIABILITY')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('ASSET')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('INVESTMENT')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('SHARES')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('RETAIL STORE')

DB2 SELECT * FROM ACCOUNT_TYPES

DB2 CONNECT RESET

SQLBIND FinSQL.bnd Finance
