CREATE TABLE ADMINISTRATOR.STAFF  ( 
	ID    	SMALLINT NOT NULL,
	NAME  	VARCHAR(9),
	DEPT  	SMALLINT,
	JOB   	CHARACTER(5),
	YEARS 	SMALLINT,
	SALARY	DECIMAL(7,2),
	COMM  	DECIMAL(7,2) 
	)
IN IBMDB2SAMPLEREL
ORGANIZE BY COLUMN 
DATA CAPTURE NONE
GO
CREATE TABLE BLU.BISTUDIO_EXAMPLE998  ( 
	SALES_REASON     	VARCHAR(255),
	CUSTOMER_NAME    	VARCHAR(255),
	TERRITORY        	VARCHAR(255),
	SHIP_METHOD      	VARCHAR(255),
	CURRENCY_CODE    	VARCHAR(255),
	CARD_TYPE        	VARCHAR(255),
	CITY             	VARCHAR(255),
	STATE            	VARCHAR(255),
	POSTALCODE       	VARCHAR(255),
	ORDERDATE        	TIMESTAMP(6),
	DUEDATE          	TIMESTAMP(6),
	SHIPDATE         	TIMESTAMP(6),
	ONLINE_ORDER_FLAG	SMALLINT,
	SUB_TOTAL        	DOUBLE,
	TAX_AMOUNT       	DOUBLE,
	FREIGHT          	DOUBLE,
	TOTAL_DUE        	DOUBLE,
	SALES_ORDER_ID   	DOUBLE,
	PRODUCT_CATEGORY 	VARCHAR(255),
	PROFIT           	DOUBLE 
	)
ORGANIZE BY ROW 
DATA CAPTURE NONE
COMPRESS NO
	PARTITION BY RANGE(ORDERDATE NULLS LAST)
	(	PARTITION PART0 STARTING FROM ('2005-07-01-00.00.00.000000') INCLUSIVE  ENDING ('2005-08-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART1 STARTING FROM ('2005-08-01-00.00.00.000000') INCLUSIVE  ENDING ('2005-09-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART2 STARTING FROM ('2005-09-01-00.00.00.000000') INCLUSIVE  ENDING ('2005-10-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART3 STARTING FROM ('2005-10-01-00.00.00.000000') INCLUSIVE  ENDING ('2005-11-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART4 STARTING FROM ('2005-11-01-00.00.00.000000') INCLUSIVE  ENDING ('2005-12-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART5 STARTING FROM ('2005-12-01-00.00.00.000000') INCLUSIVE  ENDING ('2006-01-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART6 STARTING FROM ('2006-01-01-00.00.00.000000') INCLUSIVE  ENDING ('2006-02-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART7 STARTING FROM ('2006-02-01-00.00.00.000000') INCLUSIVE  ENDING ('2006-03-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART8 STARTING FROM ('2006-03-01-00.00.00.000000') INCLUSIVE  ENDING ('2006-04-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART9 STARTING FROM ('2006-04-01-00.00.00.000000') INCLUSIVE  ENDING ('2006-05-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART10 STARTING FROM ('2006-05-01-00.00.00.000000') INCLUSIVE  ENDING ('2006-06-01-00.00.00.000000') EXCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1,
		PARTITION PART11 STARTING FROM ('2006-06-01-00.00.00.000000') INCLUSIVE  ENDING ('2007-11-07-00.00.00.000000') INCLUSIVE  IN USERSPACE1 LONG IN USERSPACE1)
GO
GRANT CONTROL ON BLU.BISTUDIO_EXAMPLE998 TO USER DB2ADMIN
GO
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES ON BLU.BISTUDIO_EXAMPLE998 TO USER DB2ADMIN WITH GRANT OPTION
GO
CREATE TABLE DB2ADMIN.STAFF  ( 
	ID    	SMALLINT NOT NULL,
	NAME  	VARCHAR(9),
	DEPT  	SMALLINT,
	JOB   	CHARACTER(5),
	YEARS 	SMALLINT,
	SALARY	DECIMAL(7,2),
	COMM  	DECIMAL(7,2) 
	)
IN IBMDB2SAMPLEREL
ORGANIZE BY COLUMN 
DATA CAPTURE NONE
GO
CREATE TRIGGER DB2ADMIN.MIN_SALARY NO CASCADE BEFORE INSERT ON staff REFERENCING NEW AS newstaff FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET newstaff.salary = CASE WHEN newstaff.job = 'Mgr' AND newstaff.salary < 17000.00 THEN 17000.00 WHEN newstaff.job = 'Sales' AND newstaff.salary < 14000.00 THEN 14000.00 WHEN newstaff.job = 'Clerk' AND newstaff.salary < 10000.00 THEN 10000.00 ELSE newstaff.salary END; END
GO
CREATE TRIGGER DB2ADMIN.DO_NOT_DEL_SALES NO CASCADE BEFORE DELETE ON staff REFERENCING OLD AS oldstaff FOR EACH ROW MODE DB2SQL WHEN(oldstaff.job = 'Sales') BEGIN ATOMIC SIGNAL SQLSTATE '75000' ('Sales staff cannot be deleted... see the DO_NOT_DEL_SALES trigger.'); END
GO