REM DB2 CREATE DATABASE FINANCE ON C:

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
REM DB2 DROP TRIGGER UPDACCCROSSDEL
REM DB2 DROP TRIGGER SETRECNUMINFO

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 MEMORIZED
DB2 DROP TRIGGER ADDMEMRECNUMA

DB2 DROP TABLE GASRECEIPT

DB2 DROP TABLE GASSTORE

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 DROP INDEX IDXCATEGORYNAME
DB2 DROP TABLE CATEGORY
DB2 DROP TRIGGER ADDCATRECNUMA

DB2 DROP TABLE BUDGET
DB2 DROP TRIGGER ADDBUDGETRECNUMA

DB2 DROP TABLE CLASS
DB2 DROP TRIGGER ADDCLASSRECNUMA

DB2 DROP TABLE GAS_REC
DB2 DROP TRIGGER ADDGASRECRECNUMA


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 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, CONTACT CHAR(40), PHONE CHAR(20), "COMMENTS" CHAR(100), ACTIVE_FROM DATE, ACTIVE_TO DATE, EXPIRED CHAR NOT NULL DEFAULT ' ') IN "USERSPACE1"
REM DB2 CREATE TRIGGER ADDRECNUMA NO CASCADE BEFORE INSERT ON ACCOUNTS REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = (SELECT COUNT(*) FROM ACCOUNTS) + 1;END
DB2 CREATE TRIGGER ADDRECNUMA NO CASCADE BEFORE INSERT ON ACCOUNTS REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = VALUE((SELECT MAX(RECORD_NUMBER) FROM ACCOUNTS) + 1, 1);END

REM DB2 CREATE TABLE ACC_INFO("RECORD_NUMBER" INTEGER, ACCOUNT_REF INTEGER, CROSSACC INTEGER, CROSS INTEGER, ACC_DATE DATE, "TRANS_NUM" INTEGER NOT NULL WITH DEFAULT 0, "AMOUNT" DECIMAL(7,2), "BALANCE" DECIMAL(7,2) NOT NULL WITH DEFAULT 0, CROSSBALANCE DECIMAL(7,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(7,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 = ((SELECT COUNT(*) FROM ACC_INFO WHERE ACC_DATE = I.ACC_DATE AND ACCOUNT_REF = I.ACCOUNT_REF) + 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
REM DB2 CREATE TRIGGER UPDACCCROSSDEL AFTER DELETE ON ACC_INFO REFERENCING OLD AS I FOR EACH ROW MODE DB2SQL WHEN ("I.CROSSACC > 0") BEGIN ATOMIC DELETE FROM ACC_INFO WHERE RECORD_NUMBER = I.CROSSACC;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 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 CATAGORY(RECORD_NUMBER INTEGER, NAME CHAR(40),TYPE INTEGER, DESCRIPTION CHAR(40), PARENT INTEGER)
DB2 CREATE TRIGGER ADDCATARECNUMA NO CASCADE BEFORE INSERT ON CATAGORY REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM CATAGORY), 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 CHECKIMGS(ACCOUNT_REF INTEGER, CHKIMG BLOB(4 M) NOT NULL)
DB2 CREATE INDEX IDXCHKIMGS ON CHECKIMGS(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 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))
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 TABLE GASRECEIPT(RECORD_NUM INTEGER, STORE INTEGER, PDATE DATE, PTIME TIME, RECEIPTNUM CHAR(10), PUMPNUM INTEGER, GASTYPE INTEGER, SERVICE INTEGER, UNIT_TYPE INTEGER, UNITS DECIMAL(4,4), AMOUNT_TYPE INTEGER, AMOUNT_UNIT DECIMAL(6,4) )
DB2 CREATE TABLE GASSTORE(RECORD_NUM INTEGER, STORE_NAME CHAR(40), STORE_ADDRESS CHAR(40), STORE_CITY CHAR(25), STORE_STATE CHAR(25), STORE_ZIP CHAR(20))

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 TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 7, 'Social security tips')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 8, 'Allocated tips')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 9, 'Advance EIC payment')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 10, 'Dependant care benefits')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 11, 'Nonqualified plans')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 12, 'See Instructions')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 14, 'Other')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 16, 'State wages, tips, etc')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 17, 'State Income tax')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 18, 'Local wages, tips etc.')
DB2 INSERT INTO TAXFORMDATA(FORM_NUM, LINENUM, DESCRIP) VALUES('W-2', 19, 'Local income tax')

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 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, YEAR 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 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 CREATE TABLE GAS_REC(RECORD_NUMBER INTEGER, ACCOUNT_REF INTEGER, CAR_REF INTEGER, ACC_DATE DATE, FUEL_TYPE INTEGER, FUEL_AMT DECIMAL(9,2), FUEL_PRICE DECIMAL(9,2), COST DECIMAL(9,2), CLEARED CHAR, CATEGORY INTEGER, TRIP_ODO DECIMAL(9,2), ODOMETER DECIMAL(9,2), FILL_UP CHAR)
DB2 CREATE TRIGGER ADDGASRECRECNUMA NO CASCADE BEFORE INSERT ON GAS_REC REFERENCING NEW AS I FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET I.RECORD_NUMBER = 1 + VALUE((SELECT MAX(RECORD_NUMBER) FROM GAS_REC),0);END



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 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('GAS STATION')
DB2 INSERT INTO ACCOUNT_TYPES(DESCRIPTION) VALUES ('CAR')
REM DB2 SELECT * FROM ACCOUNTS

DB2 CONNECT RESET
SQLBIND FINSQL.BND FINANCE
