#!/usr/local/bin/tclsh8.6
#LOAD LIBRARIES AND MODULES
set library Oratcl
if [catch {package require $library} message] { error "Failed to load $library - $message" }
if [catch {::tcl::tm::path add modules} ] { error "Failed to find modules directory" }
if [catch {package require tpcccommon} ] { error "Failed to load tpcc common functions" } else { namespace import tpcccommon::* }
proc CreateStoredProcs { lda timesten num_part } {
puts "CREATING TPCC STORED PROCEDURES"
set curn1 [ oraopen $lda ]
if { $timesten && $num_part != 0 } {
set sql(1) { CREATE OR REPLACE PROCEDURE NEWORD (
no_w_idINTEGER,
no_max_w_idINTEGER,
no_d_idINTEGER,
no_c_idINTEGER,
no_o_ol_cntINTEGER,
no_c_discountOUT NUMBER,
no_c_lastOUT VARCHAR2,
no_c_creditOUT VARCHAR2,
no_d_taxOUT NUMBER,
no_w_taxOUT NUMBER,
no_d_next_o_idIN OUT INTEGER,
timestampIN DATE )
IS
no_ol_supply_w_idINTEGER;
no_ol_i_idNUMBER;
no_ol_quantityNUMBER;
no_o_all_localINTEGER;
o_idINTEGER;
no_i_nameVARCHAR2(24);
no_i_priceNUMBER(5,2);
no_i_dataVARCHAR2(50);
no_s_quantityNUMBER(6);
no_ol_amountNUMBER(6,2);
no_s_dist_01CHAR(24);
no_s_dist_02CHAR(24);
no_s_dist_03CHAR(24);
no_s_dist_04CHAR(24);
no_s_dist_05CHAR(24);
no_s_dist_06CHAR(24);
no_s_dist_07CHAR(24);
no_s_dist_08CHAR(24);
no_s_dist_09CHAR(24);
no_s_dist_10CHAR(24);
no_ol_dist_infoCHAR(24);
no_s_dataVARCHAR2(50);
xNUMBER;
rbkNUMBER;
stmt_strVARCHAR2(512);
mywidINTEGER;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
integrity_violEXCEPTION;
PRAGMA EXCEPTION_INIT(integrity_viol,-1);
BEGIN
--assignment below added due to error in appendix code
no_o_all_local := 0;
SELECT c_discount, c_last, c_credit, w_tax
INTO no_c_discount, no_c_last, no_c_credit, no_w_tax
FROM customer, warehouse
WHERE warehouse.w_id = no_w_id AND customer.c_w_id = no_w_id AND
customer.c_d_id = no_d_id AND customer.c_id = no_c_id;
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id, d_tax INTO no_d_next_o_id, no_d_tax;
o_id := no_d_next_o_id;
INSERT INTO ORDERS (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (o_id, no_d_id, no_w_id, no_c_id, timestamp, no_o_ol_cnt, no_o_all_local);
INSERT INTO NEW_ORDER (no_o_id, no_d_id, no_w_id) VALUES (o_id, no_d_id, no_w_id);
--#2.4.1.4
rbk := round(DBMS_RANDOM.value(low => 1, high => 100));
--#2.4.1.5
FOR loop_counter IN 1 .. no_o_ol_cnt
LOOP
IF ((loop_counter = no_o_ol_cnt) AND (rbk = 1))
THEN
no_ol_i_id := 100001;
ELSE
no_ol_i_id := round(DBMS_RANDOM.value(low => 1, high => 100000));
END IF;
--#2.4.1.5.2
x := round(DBMS_RANDOM.value(low => 1, high => 100));
IF ( x > 1 )
THEN
no_ol_supply_w_id := no_w_id;
ELSE
no_ol_supply_w_id := no_w_id;
--no_all_local is actually used before this point so following not beneficial
no_o_all_local := 0;
WHILE ((no_ol_supply_w_id = no_w_id) AND (no_max_w_id != 1))
LOOP
no_ol_supply_w_id := round(DBMS_RANDOM.value(low => 1, high => no_max_w_id));
END LOOP;
END IF;
--#2.4.1.5.3
no_ol_quantity := round(DBMS_RANDOM.value(low => 1, high => 10));
SELECT i_price, i_name, i_data INTO no_i_price, no_i_name, no_i_data
FROM item WHERE i_id = no_ol_i_id;
SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
INTO no_s_quantity, no_s_data, no_s_dist_01, no_s_dist_02, no_s_dist_03, no_s_dist_04, no_s_dist_05, no_s_dist_06, no_s_dist_07, no_s_dist_08, no_s_dist_09, no_s_dist_10 FROM stock WHERE s_i_id = no_ol_i_id AND s_w_id = no_ol_supply_w_id;
IF ( no_s_quantity > no_ol_quantity )
THEN
no_s_quantity := ( no_s_quantity - no_ol_quantity );
ELSE
no_s_quantity := ( no_s_quantity - no_ol_quantity + 91 );
END IF;
UPDATE stock SET s_quantity = no_s_quantity
WHERE s_i_id = no_ol_i_id
AND s_w_id = no_ol_supply_w_id;
no_ol_amount := ( no_ol_quantity * no_i_price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) );
IF no_d_id = 1
THEN
no_ol_dist_info := no_s_dist_01;
ELSIF no_d_id = 2
THEN
no_ol_dist_info := no_s_dist_02;
ELSIF no_d_id = 3
THEN
no_ol_dist_info := no_s_dist_03;
ELSIF no_d_id = 4
THEN
no_ol_dist_info := no_s_dist_04;
ELSIF no_d_id = 5
THEN
no_ol_dist_info := no_s_dist_05;
ELSIF no_d_id = 6
THEN
no_ol_dist_info := no_s_dist_06;
ELSIF no_d_id = 7
THEN
no_ol_dist_info := no_s_dist_07;
ELSIF no_d_id = 8
THEN
no_ol_dist_info := no_s_dist_08;
ELSIF no_d_id = 9
THEN
no_ol_dist_info := no_s_dist_09;
ELSIF no_d_id = 10
THEN
no_ol_dist_info := no_s_dist_10;
END IF;
mywid := mod(no_w_id, 10);
IF ( mywid = 0 )
THEN
mywid := 10;
END IF;
stmt_str := 'INSERT INTO order_line_'||mywid||'(ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (:o_id, :no_d_id, :no_w_id, :loop_counter, :no_ol_i_id, :no_ol_supply_w_id, :no_ol_quantity, :no_ol_amount, :no_ol_dist_info)';
--dbms_output.put_line(stmt_str);
EXECUTE IMMEDIATE stmt_str USING o_id, no_d_id, no_w_id, loop_counter, no_ol_i_id, no_ol_supply_w_id, no_ol_quantity, no_ol_amount, no_ol_dist_info;
END LOOP;
COMMIT;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old OR integrity_viol OR no_data_found
THEN
ROLLBACK;
END; }
} else {
set sql(1) { CREATE OR REPLACE PROCEDURE NEWORD (
no_w_idINTEGER,
no_max_w_idINTEGER,
no_d_idINTEGER,
no_c_idINTEGER,
no_o_ol_cntINTEGER,
no_c_discountOUT NUMBER,
no_c_lastOUT VARCHAR2,
no_c_creditOUT VARCHAR2,
no_d_taxOUT NUMBER,
no_w_taxOUT NUMBER,
no_d_next_o_idIN OUT INTEGER,
timestampIN DATE )
IS
no_ol_supply_w_idINTEGER;
no_ol_i_idNUMBER;
no_ol_quantityNUMBER;
no_o_all_localINTEGER;
o_idINTEGER;
no_i_nameVARCHAR2(24);
no_i_priceNUMBER(5,2);
no_i_dataVARCHAR2(50);
no_s_quantityNUMBER(6);
no_ol_amountNUMBER(6,2);
no_s_dist_01CHAR(24);
no_s_dist_02CHAR(24);
no_s_dist_03CHAR(24);
no_s_dist_04CHAR(24);
no_s_dist_05CHAR(24);
no_s_dist_06CHAR(24);
no_s_dist_07CHAR(24);
no_s_dist_08CHAR(24);
no_s_dist_09CHAR(24);
no_s_dist_10CHAR(24);
no_ol_dist_infoCHAR(24);
no_s_dataVARCHAR2(50);
xNUMBER;
rbkNUMBER;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
integrity_violEXCEPTION;
PRAGMA EXCEPTION_INIT(integrity_viol,-1);
BEGIN
--assignment below added due to error in appendix code
no_o_all_local := 0;
SELECT c_discount, c_last, c_credit, w_tax
INTO no_c_discount, no_c_last, no_c_credit, no_w_tax
FROM customer, warehouse
WHERE warehouse.w_id = no_w_id AND customer.c_w_id = no_w_id AND
customer.c_d_id = no_d_id AND customer.c_id = no_c_id;
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id, d_tax INTO no_d_next_o_id, no_d_tax;
o_id := no_d_next_o_id;
INSERT INTO ORDERS (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (o_id, no_d_id, no_w_id, no_c_id, timestamp, no_o_ol_cnt, no_o_all_local);
INSERT INTO NEW_ORDER (no_o_id, no_d_id, no_w_id) VALUES (o_id, no_d_id, no_w_id);
--#2.4.1.4
rbk := round(DBMS_RANDOM.value(low => 1, high => 100));
--#2.4.1.5
FOR loop_counter IN 1 .. no_o_ol_cnt
LOOP
IF ((loop_counter = no_o_ol_cnt) AND (rbk = 1))
THEN
no_ol_i_id := 100001;
ELSE
no_ol_i_id := round(DBMS_RANDOM.value(low => 1, high => 100000));
END IF;
--#2.4.1.5.2
x := round(DBMS_RANDOM.value(low => 1, high => 100));
IF ( x > 1 )
THEN
no_ol_supply_w_id := no_w_id;
ELSE
no_ol_supply_w_id := no_w_id;
--no_all_local is actually used before this point so following not beneficial
no_o_all_local := 0;
WHILE ((no_ol_supply_w_id = no_w_id) AND (no_max_w_id != 1))
LOOP
no_ol_supply_w_id := round(DBMS_RANDOM.value(low => 1, high => no_max_w_id));
END LOOP;
END IF;
--#2.4.1.5.3
no_ol_quantity := round(DBMS_RANDOM.value(low => 1, high => 10));
SELECT i_price, i_name, i_data INTO no_i_price, no_i_name, no_i_data
FROM item WHERE i_id = no_ol_i_id;
SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
INTO no_s_quantity, no_s_data, no_s_dist_01, no_s_dist_02, no_s_dist_03, no_s_dist_04, no_s_dist_05, no_s_dist_06, no_s_dist_07, no_s_dist_08, no_s_dist_09, no_s_dist_10 FROM stock WHERE s_i_id = no_ol_i_id AND s_w_id = no_ol_supply_w_id;
IF ( no_s_quantity > no_ol_quantity )
THEN
no_s_quantity := ( no_s_quantity - no_ol_quantity );
ELSE
no_s_quantity := ( no_s_quantity - no_ol_quantity + 91 );
END IF;
UPDATE stock SET s_quantity = no_s_quantity
WHERE s_i_id = no_ol_i_id
AND s_w_id = no_ol_supply_w_id;
no_ol_amount := ( no_ol_quantity * no_i_price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) );
IF no_d_id = 1
THEN
no_ol_dist_info := no_s_dist_01;
ELSIF no_d_id = 2
THEN
no_ol_dist_info := no_s_dist_02;
ELSIF no_d_id = 3
THEN
no_ol_dist_info := no_s_dist_03;
ELSIF no_d_id = 4
THEN
no_ol_dist_info := no_s_dist_04;
ELSIF no_d_id = 5
THEN
no_ol_dist_info := no_s_dist_05;
ELSIF no_d_id = 6
THEN
no_ol_dist_info := no_s_dist_06;
ELSIF no_d_id = 7
THEN
no_ol_dist_info := no_s_dist_07;
ELSIF no_d_id = 8
THEN
no_ol_dist_info := no_s_dist_08;
ELSIF no_d_id = 9
THEN
no_ol_dist_info := no_s_dist_09;
ELSIF no_d_id = 10
THEN
no_ol_dist_info := no_s_dist_10;
END IF;
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
VALUES (o_id, no_d_id, no_w_id, loop_counter, no_ol_i_id, no_ol_supply_w_id, no_ol_quantity, no_ol_amount, no_ol_dist_info);
END LOOP;
COMMIT;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old OR integrity_viol OR no_data_found
THEN
ROLLBACK;
END; }
}
if { $timesten } {
if { $num_part != 0 } {
set sql(2) { CREATE OR REPLACE PROCEDURE DELIVERY (
d_w_idINTEGER,
d_o_carrier_idINTEGER,
timestampIN DATE )
IS
d_no_o_idINTEGER;
d_d_id INTEGER;
d_c_id NUMBER;
d_ol_totalNUMBER;
stmt_strVARCHAR2(512);
mywidINTEGER;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
BEGIN
FOR loop_counter IN 1 .. 10
LOOP
d_d_id := loop_counter;
SELECT no_o_id INTO d_no_o_id from (SELECT no_o_id FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id ORDER BY no_o_id ASC) where rownum = 1;
DELETE FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id AND no_o_id = d_no_o_id;
SELECT o_c_id INTO d_c_id FROM orders
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE orders SET o_carrier_id = d_o_carrier_id
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
mywid := mod(d_w_id, 10);
IF ( mywid = 0 )
THEN
mywid := 10;
END IF;
stmt_str := 'UPDATE order_line_'||mywid||' SET ol_delivery_d = :timestamp WHERE ol_o_id = :d_no_o_id AND ol_d_id = :d_d_id AND ol_w_id = :d_w_id';
EXECUTE IMMEDIATE stmt_str USING timestamp, d_no_o_id, d_d_id, d_w_id;
SELECT SUM(ol_amount) INTO d_ol_total
FROM order_line
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id
AND ol_w_id = d_w_id;
UPDATE customer SET c_balance = c_balance + d_ol_total
WHERE c_id = d_c_id AND c_d_id = d_d_id AND
c_w_id = d_w_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('D: ' || d_d_id || 'O: ' || d_no_o_id || 'time ' || timestamp);
END LOOP;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old
THEN
ROLLBACK;
END;
}
} else {
set sql(2) { CREATE OR REPLACE PROCEDURE DELIVERY (
d_w_idINTEGER,
d_o_carrier_idINTEGER,
timestampIN DATE )
IS
d_no_o_idINTEGER;
d_d_id INTEGER;
d_c_id NUMBER;
d_ol_totalNUMBER;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
BEGIN
FOR loop_counter IN 1 .. 10
LOOP
d_d_id := loop_counter;
SELECT no_o_id INTO d_no_o_id from (SELECT no_o_id FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id ORDER BY no_o_id ASC) where rownum = 1;
DELETE FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id AND no_o_id = d_no_o_id;
SELECT o_c_id INTO d_c_id FROM orders
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE orders SET o_carrier_id = d_o_carrier_id
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE order_line SET ol_delivery_d = timestamp
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id AND
ol_w_id = d_w_id;
SELECT SUM(ol_amount) INTO d_ol_total
FROM order_line
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id
AND ol_w_id = d_w_id;
UPDATE customer SET c_balance = c_balance + d_ol_total
WHERE c_id = d_c_id AND c_d_id = d_d_id AND
c_w_id = d_w_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('D: ' || d_d_id || 'O: ' || d_no_o_id || 'time ' || timestamp);
END LOOP;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old
THEN
ROLLBACK;
END; }
}
} else {
set sql(2) { CREATE OR REPLACE PROCEDURE DELIVERY (
d_w_idINTEGER,
d_o_carrier_idINTEGER,
timestampIN DATE )
IS
d_no_o_idINTEGER;
d_d_id INTEGER;
d_c_id NUMBER;
d_ol_totalNUMBER;
current_ROWIDUROWID;
--WHERE CURRENT OF CLAUSE IN SPECIFICATION GAVE VERY POOR PERFORMANCE
--USED ROWID AS GIVEN IN DOC CDOUG Tricks and Treats by Shahs Upadhye
CURSOR c_no IS
SELECT no_o_id,ROWID
FROM new_order
WHERE no_d_id = d_d_id AND no_w_id = d_w_id
ORDER BY no_o_id ASC;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
BEGIN
FOR loop_counter IN 1 .. 10
LOOP
d_d_id := loop_counter;
open c_no;
FETCH c_no INTO d_no_o_id,current_ROWID;
EXIT WHEN c_no%NOTFOUND;
DELETE FROM new_order WHERE rowid = current_ROWID;
close c_no;
SELECT o_c_id INTO d_c_id FROM orders
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE orders SET o_carrier_id = d_o_carrier_id
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE order_line SET ol_delivery_d = timestamp
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id AND
ol_w_id = d_w_id;
SELECT SUM(ol_amount) INTO d_ol_total
FROM order_line
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id
AND ol_w_id = d_w_id;
UPDATE customer SET c_balance = c_balance + d_ol_total
WHERE c_id = d_c_id AND c_d_id = d_d_id AND
c_w_id = d_w_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('D: ' || d_d_id || 'O: ' || d_no_o_id || 'time ' || timestamp);
END LOOP;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old
THEN
ROLLBACK;
END; }
}
set sql(3) { CREATE OR REPLACE PROCEDURE PAYMENT (
p_w_idINTEGER,
p_d_idINTEGER,
p_c_w_idINTEGER,
p_c_d_idINTEGER,
p_c_idIN OUT INTEGER,
bynameINTEGER,
p_h_amountNUMBER,
p_c_lastIN OUT VARCHAR2,
p_w_street_1OUT VARCHAR2,
p_w_street_2OUT VARCHAR2,
p_w_cityOUT VARCHAR2,
p_w_stateOUT VARCHAR2,
p_w_zipOUT VARCHAR2,
p_d_street_1OUT VARCHAR2,
p_d_street_2OUT VARCHAR2,
p_d_cityOUT VARCHAR2,
p_d_stateOUT VARCHAR2,
p_d_zipOUT VARCHAR2,
p_c_firstOUT VARCHAR2,
p_c_middleOUT VARCHAR2,
p_c_street_1OUT VARCHAR2,
p_c_street_2OUT VARCHAR2,
p_c_cityOUT VARCHAR2,
p_c_stateOUT VARCHAR2,
p_c_zipOUT VARCHAR2,
p_c_phoneOUT VARCHAR2,
p_c_sinceOUT DATE,
p_c_creditIN OUT VARCHAR2,
p_c_credit_limOUT NUMBER,
p_c_discountOUT NUMBER,
p_c_balanceIN OUT NUMBER,
p_c_dataOUT VARCHAR2,
timestampIN DATE )
IS
namecntINTEGER;
p_d_nameVARCHAR2(11);
p_w_nameVARCHAR2(11);
p_c_new_dataVARCHAR2(500);
h_dataVARCHAR2(30);
CURSOR c_byname IS
SELECT c_first, c_middle, c_id,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_last = p_c_last
ORDER BY c_first;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
BEGIN
UPDATE warehouse SET w_ytd = w_ytd + p_h_amount
WHERE w_id = p_w_id;
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name
INTO p_w_street_1, p_w_street_2, p_w_city, p_w_state, p_w_zip, p_w_name
FROM warehouse
WHERE w_id = p_w_id;
UPDATE district SET d_ytd = d_ytd + p_h_amount
WHERE d_w_id = p_w_id AND d_id = p_d_id;
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name
INTO p_d_street_1, p_d_street_2, p_d_city, p_d_state, p_d_zip, p_d_name
FROM district
WHERE d_w_id = p_w_id AND d_id = p_d_id;
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = p_c_last AND c_d_id = p_c_d_id AND c_w_id = p_c_w_id;
OPEN c_byname;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
FOR loop_counter IN 0 .. (namecnt/2)
LOOP
FETCH c_byname
INTO p_c_first, p_c_middle, p_c_id, p_c_street_1, p_c_street_2, p_c_city,
p_c_state, p_c_zip, p_c_phone, p_c_credit, p_c_credit_lim, p_c_discount, p_c_balance, p_c_since;
END LOOP;
CLOSE c_byname;
ELSE
SELECT c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
INTO p_c_first, p_c_middle, p_c_last,
p_c_street_1, p_c_street_2, p_c_city, p_c_state, p_c_zip,
p_c_phone, p_c_credit, p_c_credit_lim,
p_c_discount, p_c_balance, p_c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
END IF;
p_c_balance := ( p_c_balance + p_h_amount );
IF p_c_credit = 'BC'
THEN
SELECT c_data INTO p_c_data
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
-- The following statement in the TPC-C specification appendix is incorrect
-- copied setting of h_data from later on in the procedure to here as well
h_data := ( p_w_name || ' ' || p_d_name );
p_c_new_data := (TO_CHAR(p_c_id) || ' ' || TO_CHAR(p_c_d_id) || ' ' ||
TO_CHAR(p_c_w_id) || ' ' || TO_CHAR(p_d_id) || ' ' || TO_CHAR(p_w_id) || ' ' || TO_CHAR(p_h_amount,'9999.99') || TO_CHAR(timestamp) || h_data);
p_c_new_data := substr(CONCAT(p_c_new_data,p_c_data),1,500-(LENGTH(p_c_new_data)));
UPDATE customer
SET c_balance = p_c_balance, c_data = p_c_new_data
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND
c_id = p_c_id;
ELSE
UPDATE customer SET c_balance = p_c_balance
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND
c_id = p_c_id;
END IF;
--setting of h_data is here in the TPC-C appendix
h_data := ( p_w_name|| ' ' || p_d_name );
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id,
h_w_id, h_date, h_amount, h_data)
VALUES (p_c_d_id, p_c_w_id, p_c_id, p_d_id,
p_w_id, timestamp, p_h_amount, h_data);
COMMIT;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old
THEN
ROLLBACK;
END; }
set sql(4) { CREATE OR REPLACE PROCEDURE OSTAT (
os_w_idINTEGER,
os_d_idINTEGER,
os_c_idIN OUT INTEGER,
bynameINTEGER,
os_c_lastIN OUT VARCHAR2,
os_c_firstOUT VARCHAR2,
os_c_middleOUT VARCHAR2,
os_c_balanceOUT NUMBER,
os_o_idOUT INTEGER,
os_entdateOUT DATE,
os_o_carrier_idOUT INTEGER )
IS
TYPE numbertable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
os_ol_i_id numbertable;
os_ol_supply_w_id numbertable;
os_ol_quantity numbertable;
TYPE amounttable IS TABLE OF NUMBER(6,2) INDEX BY BINARY_INTEGER;
os_ol_amount amounttable;
TYPE datetable IS TABLE OF DATE INDEX BY BINARY_INTEGER;
os_ol_delivery_d datetable;
namecntINTEGER;
iBINARY_INTEGER;
CURSOR c_name IS
SELECT c_balance, c_first, c_middle, c_id
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id
ORDER BY c_first;
CURSOR c_line IS
SELECT ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount, ol_delivery_d
FROM order_line
WHERE ol_o_id = os_o_id AND ol_d_id = os_d_id AND ol_w_id = os_w_id;
os_c_line c_line%ROWTYPE;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
BEGIN
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
OPEN c_name;
FOR loop_counter IN 0 .. (namecnt/2)
LOOP
FETCH c_name
INTO os_c_balance, os_c_first, os_c_middle, os_c_id;
END LOOP;
close c_name;
ELSE
SELECT c_balance, c_first, c_middle, c_last
INTO os_c_balance, os_c_first, os_c_middle, os_c_last
FROM customer
WHERE c_id = os_c_id AND c_d_id = os_d_id AND c_w_id = os_w_id;
END IF;
-- The following statement in the TPC-C specification appendix is incorrect
-- as it does not include the where clause and does not restrict the
-- results set giving an ORA-01422.
-- The statement has been modified in accordance with the
-- descriptive specification as follows:
-- The row in the ORDER table with matching O_W_ID (equals C_W_ID),
-- O_D_ID (equals C_D_ID), O_C_ID (equals C_ID), and with the largest
-- existing O_ID, is selected. This is the most recent order placed by that
-- customer. O_ID, O_ENTRY_D, and O_CARRIER_ID are retrieved.
BEGIN
SELECT o_id, o_carrier_id, o_entry_d
INTO os_o_id, os_o_carrier_id, os_entdate
FROM
(SELECT o_id, o_carrier_id, o_entry_d
FROM orders where o_d_id = os_d_id AND o_w_id = os_w_id and o_c_id=os_c_id
ORDER BY o_id DESC)
WHERE ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No orders for customer');
END;
i := 0;
FOR os_c_line IN c_line
LOOP
os_ol_i_id(i) := os_c_line.ol_i_id;
os_ol_supply_w_id(i) := os_c_line.ol_supply_w_id;
os_ol_quantity(i) := os_c_line.ol_quantity;
os_ol_amount(i) := os_c_line.ol_amount;
os_ol_delivery_d(i) := os_c_line.ol_delivery_d;
i := i+1;
END LOOP;
EXCEPTION WHEN not_serializable OR deadlock OR snapshot_too_old THEN
ROLLBACK;
END; }
set sql(5) { CREATE OR REPLACE PROCEDURE SLEV (
st_w_idINTEGER,
st_d_idINTEGER,
thresholdINTEGER,
stock_countOUT INTEGER )
IS
st_o_idNUMBER;
not_serializableEXCEPTION;
PRAGMA EXCEPTION_INIT(not_serializable,-8177);
deadlockEXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock,-60);
snapshot_too_oldEXCEPTION;
PRAGMA EXCEPTION_INIT(snapshot_too_old,-1555);
BEGIN
SELECT d_next_o_id INTO st_o_id
FROM district
WHERE d_w_id=st_w_id AND d_id=st_d_id;
SELECT COUNT(DISTINCT (s_i_id)) INTO stock_count
FROM order_line, stock
WHERE ol_w_id = st_w_id AND
ol_d_id = st_d_id AND (ol_o_id < st_o_id) AND
ol_o_id >= (st_o_id - 20) AND s_w_id = st_w_id AND
s_i_id = ol_i_id AND s_quantity < threshold;
COMMIT;
EXCEPTION
WHEN not_serializable OR deadlock OR snapshot_too_old
THEN
ROLLBACK;
END; }
for { set i 1 } { $i <= 5 } { incr i } {
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
oraclose $curn1
return
}
proc TTPLSQLSettings { lda } {
set curn1 [ oraopen $lda ]
set sql(1) "alter session set PLSQL_OPTIMIZE_LEVEL = 2"
set sql(2) "alter session set PLSQL_CODE_TYPE = INTERPRETED"
set sql(3) "alter session set NLS_LENGTH_SEMANTICS = BYTE"
set sql(4) "alter session set PLSQL_CCFLAGS = ''"
set sql(5) "alter session set PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE'"
for { set i 1 } { $i <= 5 } { incr i } {
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
oraclose $curn1
return
}
proc GatherStatistics { lda tpcc_user timesten num_part } {
puts "GATHERING SCHEMA STATISTICS"
set curn1 [ oraopen $lda ]
if { $timesten } {
set sql(1) "call ttOptUpdateStats('WAREHOUSE',1)"
set sql(2) "call ttOptUpdateStats('DISTRICT',1)"
set sql(3) "call ttOptUpdateStats('ITEM',1)"
set sql(4) "call ttOptUpdateStats('STOCK',1)"
set sql(5) "call ttOptUpdateStats('CUSTOMER',1)"
set sql(6) "call ttOptUpdateStats('ORDERS',1)"
set sql(7) "call ttOptUpdateStats('NEW_ORDER',1)"
set sql(8) "call ttOptUpdateStats('HISTORY',1)"
for { set i 1 } { $i <= 8 } { incr i } {
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
if { $num_part eq 0 } {
set sql(9) "call ttOptUpdateStats('ORDER_LINE',1)"
set i 9
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
} else {
set sql(9a) "call ttOptUpdateStats('ORDER_LINE_1',1)"
set sql(9b) "call ttOptUpdateStats('ORDER_LINE_2',1)"
set sql(9c) "call ttOptUpdateStats('ORDER_LINE_3',1)"
set sql(9d) "call ttOptUpdateStats('ORDER_LINE_4',1)"
set sql(9e) "call ttOptUpdateStats('ORDER_LINE_5',1)"
set sql(9f) "call ttOptUpdateStats('ORDER_LINE_6',1)"
set sql(9g) "call ttOptUpdateStats('ORDER_LINE_7',1)"
set sql(9h) "call ttOptUpdateStats('ORDER_LINE_8',1)"
set sql(9i) "call ttOptUpdateStats('ORDER_LINE_9',1)"
set sql(9j) "call ttOptUpdateStats('ORDER_LINE_10',1)"
set partidx [ list a b c d e f g h i j ]
for { set p 1 } { $p <= 10 } { incr p } {
set idx [ lindex $partidx [ expr $p - 1]]
if {[ catch {orasql $curn1 $sql(9$idx)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
}
} else {
set sql(1) "BEGIN dbms_stats.gather_schema_stats('$tpcc_user'); END;"
if {[ catch {orasql $curn1 $sql(1)} message ] } {
puts "$message $sql(1)"
puts [ oramsg $curn1 all ]
}
}
oraclose $curn1
return
}
proc CreateUser { lda tpcc_user tpcc_pass tpcc_def_tab tpcc_def_temp tpcc_ol_tab partition} {
puts "CREATING USER $tpcc_user"
set stmt_cnt 3
set curn1 [ oraopen $lda ]
set sql(1) "create user $tpcc_user identified by $tpcc_pass default tablespace $tpcc_def_tab temporary tablespace $tpcc_def_temp\n"
set sql(2) "grant connect,resource to $tpcc_user\n"
set sql(3) "alter user $tpcc_user quota unlimited on $tpcc_def_tab\n"
if { $partition eq "true" } {
if { $tpcc_def_tab != $tpcc_ol_tab } {
set stmt_cnt 4
set sql(4) "alter user $tpcc_user quota unlimited on $tpcc_ol_tab\n"
}
}
for { set i 1 } { $i <= $stmt_cnt } { incr i } {
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
oraclose $curn1
return
}
proc CreateTables { lda num_part tpcc_ol_tab timesten hash_clusters count_ware } {
puts "CREATING TPCC TABLES"
set curn1 [ oraopen $lda ]
if { $timesten } {
set sql(1) "create table TPCC.CUSTOMER (C_ID TT_BIGINT, C_D_ID TT_INTEGER, C_W_ID TT_INTEGER, C_FIRST CHAR(16), C_MIDDLE CHAR(2), C_LAST CHAR(16), C_STREET_1 CHAR(20), C_STREET_2 CHAR(20), C_CITY CHAR(20), C_STATE CHAR(2), C_ZIP CHAR(9), C_PHONE CHAR(16), C_SINCE DATE, C_CREDIT CHAR(2), C_CREDIT_LIM BINARY_DOUBLE, C_DISCOUNT BINARY_DOUBLE, C_BALANCE BINARY_DOUBLE, C_YTD_PAYMENT BINARY_DOUBLE, C_PAYMENT_CNT TT_INTEGER, C_DELIVERY_CNT TT_INTEGER, C_DATA VARCHAR2(500))"
set sql(2) "create table TPCC.DISTRICT (D_ID TT_INTEGER, D_W_ID TT_INTEGER, D_YTD BINARY_DOUBLE, D_TAX BINARY_DOUBLE, D_NEXT_O_ID TT_BIGINT, D_NAME CHAR(10), D_STREET_1 CHAR(20), D_STREET_2 CHAR(20), D_CITY CHAR(20), D_STATE CHAR(2), D_ZIP CHAR(9))"
set sql(3) "create table TPCC.HISTORY (H_C_ID TT_BIGINT, H_C_D_ID TT_INTEGER, H_C_W_ID TT_INTEGER, H_D_ID TT_INTEGER, H_W_ID TT_INTEGER, H_DATE DATE, H_AMOUNT BINARY_DOUBLE, H_DATA CHAR(24))"
set sql(4) "create table TPCC.ITEM (I_ID TT_BIGINT, I_IM_ID TT_BIGINT, I_NAME CHAR(24), I_PRICE BINARY_DOUBLE, I_DATA CHAR(50))"
set sql(5) "create table TPCC.NEW_ORDER (NO_W_ID TT_BIGINT, NO_D_ID TT_INTEGER, NO_O_ID TT_INTEGER)"
set sql(6) "create table TPCC.ORDERS (O_ID TT_BIGINT, O_W_ID TT_BIGINT, O_D_ID TT_INTEGER, O_C_ID TT_INTEGER, O_CARRIER_ID TT_INTEGER, O_OL_CNT TT_INTEGER, O_ALL_LOCAL TT_INTEGER, O_ENTRY_D DATE)"
if {$num_part eq 0} {
set sql(7) "create table TPCC.ORDER_LINE (OL_W_ID TT_BIGINT, OL_D_ID TT_INTEGER, OL_O_ID TT_INTEGER, OL_NUMBER TT_INTEGER, OL_I_ID TT_BIGINT, OL_DELIVERY_D DATE, OL_AMOUNT BINARY_DOUBLE, OL_SUPPLY_W_ID TT_INTEGER, OL_QUANTITY TT_INTEGER, OL_DIST_INFO CHAR(24))"
} else {
set partidx [ list a b c d e f g h i j ]
for { set p 1 } { $p <= 10 } { incr p } {
set idx [ lindex $partidx [ expr $p - 1]]
set sql(7$idx) "create table TPCC.ORDER_LINE_$p (OL_W_ID TT_BIGINT, OL_D_ID TT_INTEGER, OL_O_ID TT_INTEGER, OL_NUMBER TT_INTEGER, OL_I_ID TT_BIGINT, OL_DELIVERY_D DATE, OL_AMOUNT BINARY_DOUBLE, OL_SUPPLY_W_ID TT_INTEGER, OL_QUANTITY TT_INTEGER, OL_DIST_INFO CHAR(24))"
}
set idx k
set sql(7$idx) "create view ORDER_LINE AS ("
for { set p 1 } { $p <= 9 } { incr p } {
set sql(7$idx) "$sql(7$idx) SELECT * FROM ORDER_LINE_$p UNION ALL"
}
set p 10
set sql(7$idx) "$sql(7$idx) SELECT * FROM ORDER_LINE_$p )"
}
set sql(8) "create table TPCC.STOCK (S_I_ID TT_BIGINT, S_W_ID TT_INTEGER, S_QUANTITY TT_INTEGER, S_DIST_01 CHAR(24), S_DIST_02 CHAR(24), S_DIST_03 CHAR(24), S_DIST_04 CHAR(24), S_DIST_05 CHAR(24), S_DIST_06 CHAR(24), S_DIST_07 CHAR(24), S_DIST_08 CHAR(24), S_DIST_09 CHAR(24), S_DIST_10 CHAR(24), S_YTD TT_BIGINT, S_ORDER_CNT TT_INTEGER, S_REMOTE_CNT TT_INTEGER, S_DATA CHAR(50))"
set sql(9) "create table TPCC.WAREHOUSE (W_ID TT_INTEGER, W_YTD BINARY_DOUBLE, W_TAX BINARY_DOUBLE, W_NAME CHAR(10), W_STREET_1 CHAR(20), W_STREET_2 CHAR(20), W_CITY CHAR(20), W_STATE CHAR(2), W_ZIP CHAR(9))"
} else {
if { $hash_clusters } {
set blocksize 8000
while 1 { if { ![ expr {$count_ware % 100} ] } { break } else { incr count_ware } }
set ware_hkeys $count_ware
set dist_hkeys [ expr {$ware_hkeys * 10} ]
set cust_hkeys [ expr {$count_ware * 30000} ]
set cust_mult [ expr {$cust_hkeys 3000} ]
set stock_hkeys [ expr {$count_ware * 100000} ]
set stock_mult $count_ware
set sqlclust(1) "CREATE CLUSTER CUSTCLUSTER (C_ID NUMBER(5, 0), C_D_ID NUMBER(2, 0), C_W_ID NUMBER(4, 0)) SINGLE TABLE HASHKEYS $cust_hkeys hash is ((c_id * $cust_mult)+(c_w_id * 10) + c_d_id) size 650 INITRANS 4 PCTFREE 0"
set sqlclust(2) "CREATE CLUSTER DISTCLUSTER (D_W_ID NUMBER(4, 0), D_ID NUMBER(2, 0)) SINGLE TABLE HASHKEYS $dist_hkeys hash is ((d_w_id) * 10 + d_id) size $blocksize INITRANS 4 PCTFREE 0"
set sqlclust(3) "CREATE CLUSTER ITEMCLUSTER (I_ID NUMBER(6, 0)) SINGLE TABLE HASHKEYS 100000 hash is i_id size 120 INITRANS 4 PCTFREE 0"
set sqlclust(4) "CREATE CLUSTER WARECLUSTER (W_ID NUMBER(4, 0)) SINGLE TABLE HASHKEYS $ware_hkeys hash is w_id size $blocksize INITRANS 4 PCTFREE 0"
set sqlclust(5) "CREATE CLUSTER STOCKCLUSTER (S_I_ID NUMBER(6, 0), S_W_ID NUMBER(4, 0)) SINGLE TABLE HASHKEYS $stock_hkeys hash is (s_i_id * $stock_mult + s_w_id) size 350 INITRANS 4 PCTFREE 0"
set sql(1) "CREATE TABLE CUSTOMER (C_ID NUMBER(5, 0), C_D_ID NUMBER(2, 0), C_W_ID NUMBER(4, 0), C_FIRST VARCHAR2(16), C_MIDDLE CHAR(2), C_LAST VARCHAR2(16), C_STREET_1 VARCHAR2(20), C_STREET_2 VARCHAR2(20), C_CITY VARCHAR2(20), C_STATE CHAR(2), C_ZIP CHAR(9), C_PHONE CHAR(16), C_SINCE DATE, C_CREDIT CHAR(2), C_CREDIT_LIM NUMBER(12, 2), C_DISCOUNT NUMBER(4, 4), C_BALANCE NUMBER(12, 2), C_YTD_PAYMENT NUMBER(12, 2), C_PAYMENT_CNT NUMBER(8, 0), C_DELIVERY_CNT NUMBER(8, 0), C_DATA VARCHAR2(500)) CLUSTER CUSTCLUSTER (C_ID, C_D_ID, C_W_ID)"
set sql(2) "CREATE TABLE DISTRICT (D_ID NUMBER(2, 0), D_W_ID NUMBER(4, 0), D_YTD NUMBER(12, 2), D_TAX NUMBER(4, 4), D_NEXT_O_ID NUMBER, D_NAME VARCHAR2(10), D_STREET_1 VARCHAR2(20), D_STREET_2 VARCHAR2(20), D_CITY VARCHAR2(20), D_STATE CHAR(2), D_ZIP CHAR(9)) CLUSTER DISTCLUSTER (D_W_ID, D_ID)"
set sql(3) "CREATE TABLE HISTORY (H_C_ID NUMBER, H_C_D_ID NUMBER, H_C_W_ID NUMBER, H_D_ID NUMBER, H_W_ID NUMBER, H_DATE DATE, H_AMOUNT NUMBER(6, 2), H_DATA VARCHAR2(24)) INITRANS 4 PCTFREE 10"
set sql(4) "CREATE TABLE ITEM (I_ID NUMBER(6, 0), I_IM_ID NUMBER, I_NAME VARCHAR2(24), I_PRICE NUMBER(5, 2), I_DATA VARCHAR2(50)) CLUSTER ITEMCLUSTER(I_ID)"
set sql(5) "CREATE TABLE WAREHOUSE (W_ID NUMBER(4, 0), W_YTD NUMBER(12, 2), W_TAX NUMBER(4, 4), W_NAME VARCHAR2(10), W_STREET_1 VARCHAR2(20), W_STREET_2 VARCHAR2(20), W_CITY VARCHAR2(20), W_STATE CHAR(2), W_ZIP CHAR(9)) CLUSTER WARECLUSTER(W_ID)"
set sql(6) "CREATE TABLE STOCK (S_I_ID NUMBER(6, 0), S_W_ID NUMBER(4, 0), S_QUANTITY NUMBER(6, 0), S_DIST_01 CHAR(24), S_DIST_02 CHAR(24), S_DIST_03 CHAR(24), S_DIST_04 CHAR(24), S_DIST_05 CHAR(24), S_DIST_06 CHAR(24), S_DIST_07 CHAR(24), S_DIST_08 CHAR(24), S_DIST_09 CHAR(24), S_DIST_10 CHAR(24), S_YTD NUMBER(10, 0), S_ORDER_CNT NUMBER(6, 0), S_REMOTE_CNT NUMBER(6, 0), S_DATA VARCHAR2(50)) CLUSTER STOCKCLUSTER(S_I_ID, S_W_ID)"
set sql(7) "CREATE TABLE NEW_ORDER (NO_W_ID NUMBER, NO_D_ID NUMBER, NO_O_ID NUMBER, CONSTRAINT INORD PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID) ENABLE) ORGANIZATION INDEX NOCOMPRESS INITRANS 4 PCTFREE 10"
set sql(8) "CREATE TABLE ORDERS (O_ID NUMBER, O_W_ID NUMBER, O_D_ID NUMBER, O_C_ID NUMBER, O_CARRIER_ID NUMBER, O_OL_CNT NUMBER, O_ALL_LOCAL NUMBER, O_ENTRY_D DATE) INITRANS 4 PCTFREE 10"
set sql(9) "CREATE TABLE ORDER_LINE (OL_W_ID NUMBER, OL_D_ID NUMBER, OL_O_ID NUMBER, OL_NUMBER NUMBER, OL_I_ID NUMBER, OL_DELIVERY_D DATE, OL_AMOUNT NUMBER, OL_SUPPLY_W_ID NUMBER, OL_QUANTITY NUMBER, OL_DIST_INFO CHAR(24), CONSTRAINT IORDL PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER) ENABLE) ORGANIZATION INDEX NOCOMPRESS INITRANS 4 PCTFREE 10 PARTITION BY HASH(OL_W_ID) PARTITIONS $num_part TABLESPACE $tpcc_ol_tab"
} else {
set sql(1) "CREATE TABLE CUSTOMER (C_ID NUMBER(5, 0), C_D_ID NUMBER(2, 0), C_W_ID NUMBER(4, 0), C_FIRST VARCHAR2(16), C_MIDDLE CHAR(2), C_LAST VARCHAR2(16), C_STREET_1 VARCHAR2(20), C_STREET_2 VARCHAR2(20), C_CITY VARCHAR2(20), C_STATE CHAR(2), C_ZIP CHAR(9), C_PHONE CHAR(16), C_SINCE DATE, C_CREDIT CHAR(2), C_CREDIT_LIM NUMBER(12, 2), C_DISCOUNT NUMBER(4, 4), C_BALANCE NUMBER(12, 2), C_YTD_PAYMENT NUMBER(12, 2), C_PAYMENT_CNT NUMBER(8, 0), C_DELIVERY_CNT NUMBER(8, 0), C_DATA VARCHAR2(500)) INITRANS 4 PCTFREE 10"
set sql(2) "CREATE TABLE DISTRICT (D_ID NUMBER(2, 0), D_W_ID NUMBER(4, 0), D_YTD NUMBER(12, 2), D_TAX NUMBER(4, 4), D_NEXT_O_ID NUMBER, D_NAME VARCHAR2(10), D_STREET_1 VARCHAR2(20), D_STREET_2 VARCHAR2(20), D_CITY VARCHAR2(20), D_STATE CHAR(2), D_ZIP CHAR(9)) INITRANS 4 PCTFREE 99 PCTUSED 1"
set sql(3) "CREATE TABLE HISTORY (H_C_ID NUMBER, H_C_D_ID NUMBER, H_C_W_ID NUMBER, H_D_ID NUMBER, H_W_ID NUMBER, H_DATE DATE, H_AMOUNT NUMBER(6, 2), H_DATA VARCHAR2(24)) INITRANS 4 PCTFREE 10"
set sql(4) "CREATE TABLE ITEM (I_ID NUMBER(6, 0), I_IM_ID NUMBER, I_NAME VARCHAR2(24), I_PRICE NUMBER(5, 2), I_DATA VARCHAR2(50)) INITRANS 4 PCTFREE 10"
set sql(5) "CREATE TABLE WAREHOUSE (W_ID NUMBER(4, 0), W_YTD NUMBER(12, 2), W_TAX NUMBER(4, 4), W_NAME VARCHAR2(10), W_STREET_1 VARCHAR2(20), W_STREET_2 VARCHAR2(20), W_CITY VARCHAR2(20), W_STATE CHAR(2), W_ZIP CHAR(9)) INITRANS 4 PCTFREE 99 PCTUSED 1"
set sql(6) "CREATE TABLE STOCK (S_I_ID NUMBER(6, 0), S_W_ID NUMBER(4, 0), S_QUANTITY NUMBER(6, 0), S_DIST_01 CHAR(24), S_DIST_02 CHAR(24), S_DIST_03 CHAR(24), S_DIST_04 CHAR(24), S_DIST_05 CHAR(24), S_DIST_06 CHAR(24), S_DIST_07 CHAR(24), S_DIST_08 CHAR(24), S_DIST_09 CHAR(24), S_DIST_10 CHAR(24), S_YTD NUMBER(10, 0), S_ORDER_CNT NUMBER(6, 0), S_REMOTE_CNT NUMBER(6, 0), S_DATA VARCHAR2(50)) INITRANS 4 PCTFREE 10"
set sql(7) "CREATE TABLE NEW_ORDER (NO_W_ID NUMBER, NO_D_ID NUMBER, NO_O_ID NUMBER, CONSTRAINT INORD PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID) ENABLE ) ORGANIZATION INDEX NOCOMPRESS INITRANS 4 PCTFREE 10"
set sql(8) "CREATE TABLE ORDERS (O_ID NUMBER, O_W_ID NUMBER, O_D_ID NUMBER, O_C_ID NUMBER, O_CARRIER_ID NUMBER, O_OL_CNT NUMBER, O_ALL_LOCAL NUMBER, O_ENTRY_D DATE) INITRANS 4 PCTFREE 10"
if {$num_part eq 0} {
set sql(9) "CREATE TABLE ORDER_LINE (OL_W_ID NUMBER, OL_D_ID NUMBER, OL_O_ID NUMBER, OL_NUMBER NUMBER, OL_I_ID NUMBER, OL_DELIVERY_D DATE, OL_AMOUNT NUMBER, OL_SUPPLY_W_ID NUMBER, OL_QUANTITY NUMBER, OL_DIST_INFO CHAR(24), CONSTRAINT IORDL PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER) ENABLE) ORGANIZATION INDEX NOCOMPRESS INITRANS 4 PCTFREE 10"
} else {
set sql(9) "CREATE TABLE ORDER_LINE (OL_W_ID NUMBER, OL_D_ID NUMBER, OL_O_ID NUMBER, OL_NUMBER NUMBER, OL_I_ID NUMBER, OL_DELIVERY_D DATE, OL_AMOUNT NUMBER, OL_SUPPLY_W_ID NUMBER, OL_QUANTITY NUMBER, OL_DIST_INFO CHAR(24), CONSTRAINT IORDL PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER) ENABLE) ORGANIZATION INDEX NOCOMPRESS INITRANS 4 PCTFREE 10 PARTITION BY HASH(OL_W_ID) PARTITIONS $num_part TABLESPACE $tpcc_ol_tab"
}
}
}
if { $hash_clusters } {
for { set j 1 } { $j <= 5 } { incr j } {
if {[ catch {orasql $curn1 $sqlclust($j)} message ] } {
puts "$message $sql($j)"
puts [ oramsg $curn1 all ]
}
}
}
for { set i 1 } { $i <= 9 } { incr i } {
if { $i eq 7 && $timesten && $num_part eq 10 } {
set partidx [ list a b c d e f g h i j k ]
for { set p 1 } { $p <= 11 } { incr p } {
set idx [ lindex $partidx [ expr $p - 1]]
if {[ catch {orasql $curn1 $sql(7$idx)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}}} else {
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
}
oraclose $curn1
return
}
proc CreateIndexes { lda timesten num_part hash_clusters } {
puts "CREATING TPCC INDEXES"
set curn1 [ oraopen $lda ]
set stmt_cnt 9
if { $timesten } {
if { $num_part eq 0 } {
set stmt_cnt 10
set sql(1) "create unique index TPCC.WAREHOUSE_I1 on TPCC.WAREHOUSE (W_ID)"
set sql(2) "create unique index TPCC.STOCK_I1 on TPCC.STOCK (S_I_ID, S_W_ID)"
set sql(3) "create unique index TPCC.ORDER_LINE_I1 on TPCC.ORDER_LINE (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(4) "create unique index TPCC.ORDERS_I1 on TPCC.ORDERS (O_W_ID, O_D_ID, O_ID)"
set sql(5) "create unique index TPCC.ORDERS_I2 on TPCC.ORDERS (O_W_ID, O_D_ID, O_C_ID, O_ID)"
set sql(6) "create unique index TPCC.NEW_ORDER_I1 on TPCC.NEW_ORDER (NO_W_ID, NO_D_ID, NO_O_ID)"
set sql(7) "create unique index TPCC.ITEM_I1 on TPCC.ITEM (I_ID)"
set sql(8) "create unique index TPCC.DISTRICT_I1 on TPCC.DISTRICT (D_W_ID, D_ID)"
set sql(9) "create unique index TPCC.CUSTOMER_I1 on TPCC.CUSTOMER (C_W_ID, C_D_ID, C_ID)"
set sql(10) "create unique index TPCC.CUSTOMER_I2 on TPCC.CUSTOMER (C_LAST, C_W_ID, C_D_ID, C_FIRST, C_ID)"
} else {
set stmt_cnt 19
set sql(1) "create unique index TPCC.WAREHOUSE_I1 on TPCC.WAREHOUSE (W_ID)"
set sql(2) "create unique index TPCC.STOCK_I1 on TPCC.STOCK (S_I_ID, S_W_ID)"
set sql(3) "create unique index TPCC.ORDER_LINE_I1 on TPCC.ORDER_LINE_1 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(4) "create unique index TPCC.ORDER_LINE_I2 on TPCC.ORDER_LINE_2 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(5) "create unique index TPCC.ORDER_LINE_I3 on TPCC.ORDER_LINE_3 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(6) "create unique index TPCC.ORDER_LINE_I4 on TPCC.ORDER_LINE_4 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(7) "create unique index TPCC.ORDER_LINE_I5 on TPCC.ORDER_LINE_5 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(8) "create unique index TPCC.ORDER_LINE_I6 on TPCC.ORDER_LINE_6 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(9) "create unique index TPCC.ORDER_LINE_I7 on TPCC.ORDER_LINE_7 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(10) "create unique index TPCC.ORDER_LINE_I8 on TPCC.ORDER_LINE_8 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(11) "create unique index TPCC.ORDER_LINE_I9 on TPCC.ORDER_LINE_9 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(12) "create unique index TPCC.ORDER_LINE_I10 on TPCC.ORDER_LINE_10 (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)"
set sql(13) "create unique index TPCC.ORDERS_I1 on TPCC.ORDERS (O_W_ID, O_D_ID, O_ID)"
set sql(14) "create unique index TPCC.ORDERS_I2 on TPCC.ORDERS (O_W_ID, O_D_ID, O_C_ID, O_ID)"
set sql(15) "create unique index TPCC.NEW_ORDER_I1 on TPCC.NEW_ORDER (NO_W_ID, NO_D_ID, NO_O_ID)"
set sql(16) "create unique index TPCC.ITEM_I1 on TPCC.ITEM (I_ID)"
set sql(17) "create unique index TPCC.DISTRICT_I1 on TPCC.DISTRICT (D_W_ID, D_ID)"
set sql(18) "create unique index TPCC.CUSTOMER_I1 on TPCC.CUSTOMER (C_W_ID, C_D_ID, C_ID)"
set sql(19) "create unique index TPCC.CUSTOMER_I2 on TPCC.CUSTOMER (C_LAST, C_W_ID, C_D_ID, C_FIRST, C_ID)"
}
} else {
if { $hash_clusters } {
set stmt_cnt 18
set sql(1) "alter session set sort_area_size=5000000"
set sql(2) "CREATE UNIQUE INDEX CUSTOMER_I1 ON CUSTOMER (C_W_ID, C_D_ID, C_ID) INITRANS 4 PCTFREE 1"
set sql(3) "CREATE UNIQUE INDEX CUSTOMER_I2 ON CUSTOMER (C_LAST, C_D_ID, C_W_ID, C_FIRST) INITRANS 4 PCTFREE 1"
set sql(4) "CREATE UNIQUE INDEX DISTRICT_I1 ON DISTRICT (D_W_ID, D_ID) INITRANS 4 PCTFREE 5"
set sql(5) "CREATE UNIQUE INDEX ITEM_I1 ON ITEM (I_ID) INITRANS 4 PCTFREE 5"
set sql(6) "CREATE UNIQUE INDEX ORDERS_I1 ON ORDERS (O_W_ID, O_D_ID, O_ID) INITRANS 4 PCTFREE 1"
set sql(7) "CREATE UNIQUE INDEX ORDERS_I2 ON ORDERS (O_W_ID, O_D_ID, O_C_ID, O_ID) INITRANS 4 PCTFREE 25"
set sql(8) "CREATE UNIQUE INDEX STOCK_I1 ON STOCK (S_I_ID, S_W_ID) INITRANS 4 PCTFREE 1"
set sql(9) "CREATE UNIQUE INDEX WAREHOUSE_I1 ON WAREHOUSE (W_ID) INITRANS 4 PCTFREE 1"
set sql(10) "ALTER TABLE WAREHOUSE DISABLE TABLE LOCK"
set sql(11) "ALTER TABLE DISTRICT DISABLE TABLE LOCK"
set sql(12) "ALTER TABLE CUSTOMER DISABLE TABLE LOCK"
set sql(13) "ALTER TABLE ITEM DISABLE TABLE LOCK"
set sql(14) "ALTER TABLE STOCK DISABLE TABLE LOCK"
set sql(15) "ALTER TABLE ORDERS DISABLE TABLE LOCK"
set sql(16) "ALTER TABLE NEW_ORDER DISABLE TABLE LOCK"
set sql(17) "ALTER TABLE ORDER_LINE DISABLE TABLE LOCK"
set sql(18) "ALTER TABLE HISTORY DISABLE TABLE LOCK"
} else {
set sql(1) "alter session set sort_area_size=5000000"
set sql(2) "CREATE UNIQUE INDEX CUSTOMER_I1 ON CUSTOMER ( C_W_ID, C_D_ID, C_ID) INITRANS 4 PCTFREE 10"
set sql(3) "CREATE UNIQUE INDEX CUSTOMER_I2 ON CUSTOMER ( C_LAST, C_W_ID, C_D_ID, C_FIRST, C_ID) INITRANS 4 PCTFREE 10"
set sql(4) "CREATE UNIQUE INDEX DISTRICT_I1 ON DISTRICT ( D_W_ID, D_ID) INITRANS 4 PCTFREE 10"
set sql(5) "CREATE UNIQUE INDEX ITEM_I1 ON ITEM (I_ID) INITRANS 4 PCTFREE 10"
set sql(6) "CREATE UNIQUE INDEX ORDERS_I1 ON ORDERS (O_W_ID, O_D_ID, O_ID) INITRANS 4 PCTFREE 10"
set sql(7) "CREATE UNIQUE INDEX ORDERS_I2 ON ORDERS (O_W_ID, O_D_ID, O_C_ID, O_ID) INITRANS 4 PCTFREE 10"
set sql(8) "CREATE UNIQUE INDEX STOCK_I1 ON STOCK (S_I_ID, S_W_ID) INITRANS 4 PCTFREE 10"
set sql(9) "CREATE UNIQUE INDEX WAREHOUSE_I1 ON WAREHOUSE (W_ID) INITRANS 4 PCTFREE 10"
}
}
for { set i 1 } { $i <= $stmt_cnt } { incr i } {
if {[ catch {orasql $curn1 $sql($i)} message ] } {
puts "$message $sql($i)"
puts [ oramsg $curn1 all ]
}
}
oraclose $curn1
return
}
proc SetNLS { lda } {
set curn_nls [oraopen $lda ]
set nls(1) "alter session set NLS_LANGUAGE = AMERICAN"
set nls(2) "alter session set NLS_TERRITORY = AMERICA"
for { set i 1 } { $i <= 2 } { incr i } {
if {[ catch {orasql $curn_nls $nls($i)} message ] } {
puts "$message $nls($i)"
puts [ oramsg $curn_nls all ]
}
}
oraclose $curn_nls
}
proc gettimestamp { } {
set tstamp [ clock format [ clock seconds ] -format %Y%m%d%H%M%S ]
return $tstamp
}
proc TTCustomer { lda d_id w_id CUST_PER_DIST } {
#Single Row Insert Procedure kept distinct in event of OCI Batch Inserts work against TimesTen
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set namearr [list BAR OUGHT ABLE PRI PRES ESE ANTI CALLY ATION EING]
set chalen [ llength $globArray ]
set c_d_id $d_id
set c_w_id $w_id
set c_middle "OE"
set c_balance -10.0
set c_credit_lim 50000
set h_amount 10.0
puts "Loading Customer for DID=$d_id WID=$w_id"
set curn5 [oraopen $lda ]
set sql "INSERT INTO customer (c_id, c_d_id, c_w_id, c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, c_data, c_ytd_payment, c_payment_cnt, c_delivery_cnt) values (:c_id, :c_d_id, :c_w_id, :c_first, :c_middle, :c_last, :c_street_1, :c_street_2, :c_city, :c_state, :c_zip, :c_phone, to_date(:timestamp,'YYYYMMDDHH24MISS'), :c_credit, :c_credit_lim, :c_discount, :c_balance, :c_data, 10.0, 1, 0)"
oraparse $curn5 $sql
set curn6 [oraopen $lda ]
set sql2 "INSERT INTO history (h_c_id, h_c_d_id, h_c_w_id, h_w_id, h_d_id, h_date, h_amount, h_data) values (:c_id, :c_d_id, :c_w_id, :c_w_id, :c_d_id, to_date(:timestamp,'YYYYMMDDHH24MISS'), :h_amount, :h_data)"
oraparse $curn6 $sql2
for {set c_id 1} {$c_id <= $CUST_PER_DIST } {incr c_id } {
set c_first [ MakeAlphaString 8 16 $globArray $chalen ]
if { $c_id <= 1000 } {
set c_last [ Lastname [ expr {$c_id - 1} ] $namearr ]
} else {
set nrnd [ NURand 255 0 999 123 ]
set c_last [ Lastname $nrnd $namearr ]
}
set c_add [ MakeAddress $globArray $chalen ]
set c_phone [ MakeNumberString ]
if { [RandomNumber 0 1] eq 1 } {
set c_credit "GC"
} else {
set c_credit "BC"
}
set disc_ran [ RandomNumber 0 50 ]
set c_discount [ expr {$disc_ran 100.0} ]
set c_data [ MakeAlphaString 300 500 $globArray $chalen ]
orabind $curn5 :c_id $c_id :c_d_id $c_d_id :c_w_id $c_w_id :c_first $c_first :c_middle $c_middle :c_last $c_last :c_street_1 [ lindex $c_add 0 ] :c_street_2 [ lindex $c_add 1 ] :c_city [ lindex $c_add 2 ] :c_state [ lindex $c_add 3 ] :c_zip [ lindex $c_add 4 ] :c_phone $c_phone :timestamp [ gettimestamp ] :c_credit $c_credit :c_credit_lim $c_credit_lim :c_discount $c_discount :c_balance $c_balance :c_data $c_data
if {[ catch {oraexec $curn5} message ] } {
puts "Error in cursor 5:$curn5 $message"
puts [ oramsg $curn5 all ]
}
set h_data [ MakeAlphaString 12 24 $globArray $chalen ]
orabind $curn6 :c_id $c_id :c_d_id $c_d_id :c_w_id $c_w_id :c_w_id $c_w_id :c_d_id $c_d_id :timestamp [ gettimestamp ] :h_amount $h_amount :h_data $h_data
if {[ catch {oraexec $curn6} message ] } {
puts "Error in cursor 6:$curn6 $message"
puts [ oramsg $curn6 all ]
}
}
oracommit $lda
oraclose $curn5
oraclose $curn6
puts "Customer Done"
return
}
proc TTOrders { lda d_id w_id MAXITEMS ORD_PER_DIST num_part } {
#Single Row Insert Procedure kept distinct in event of OCI Batch Inserts work against TimesTen
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading Orders for D=$d_id W=$w_id"
if { $num_part != 0 } {
set mywid $w_id
if { $mywid > 10 } { set mywid [ expr $mywid % 10 ] }
if { $mywid eq 0 } { set mywid 10 }
}
set curn7 [ oraopen $lda ]
set sql "INSERT INTO orders (o_id, o_c_id, o_d_id, o_w_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all_local) values (:o_id, :o_c_id, :o_d_id, :o_w_id, to_date(:timestamp,'YYYYMMDDHH24MISS'), NULL, :o_ol_cnt, 1)"
oraparse $curn7 $sql
set curn8 [ oraopen $lda ]
set sql2 "INSERT INTO new_order (no_o_id, no_d_id, no_w_id) values (:o_id, :o_d_id, :o_w_id)"
oraparse $curn8 $sql2
set curn9 [ oraopen $lda ]
set sql3 "INSERT INTO orders (o_id, o_c_id, o_d_id, o_w_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all_local) values (:o_id, :o_c_id, :o_d_id, :o_w_id, to_date(:timestamp,'YYYYMMDDHH24MISS'), :o_carrier_id, :o_ol_cnt, 1)"
oraparse $curn9 $sql3
if { $num_part eq 0 } {
set curn10 [ oraopen $lda ]
set sql4 "INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) values (:o_id, :o_d_id, :o_w_id, :ol, :ol_i_id, :ol_supply_w_id, :ol_quantity, :ol_amount, :ol_dist_info, NULL)"
oraparse $curn10 $sql4
set curn11 [ oraopen $lda ]
set sql5 "INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) values (:o_id, :o_d_id, :o_w_id, :ol, :ol_i_id, :ol_supply_w_id, :ol_quantity, :ol_amount, :ol_dist_info, to_date(:timestamp,'YYYYMMDDHH24MISS'))"
oraparse $curn11 $sql5
} else {
set curn10 [ oraopen $lda ]
set sql4 "INSERT INTO order_line_$mywid (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) values (:o_id, :o_d_id, :o_w_id, :ol, :ol_i_id, :ol_supply_w_id, :ol_quantity, :ol_amount, :ol_dist_info, NULL)"
oraparse $curn10 $sql4
set curn11 [ oraopen $lda ]
set sql5 "INSERT INTO order_line_$mywid (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) values (:o_id, :o_d_id, :o_w_id, :ol, :ol_i_id, :ol_supply_w_id, :ol_quantity, :ol_amount, :ol_dist_info, to_date(:timestamp,'YYYYMMDDHH24MISS'))"
oraparse $curn11 $sql5
}
set o_d_id $d_id
set o_w_id $w_id
for {set i 1} {$i <= $ORD_PER_DIST } {incr i } {
set cust($i) $i
}
for {set i 1} {$i <= $ORD_PER_DIST } {incr i } {
set r [ RandomNumber $i $ORD_PER_DIST ]
set t $cust($i)
set cust($i) $cust($r)
set $cust($r) $t
}
set e ""
for {set o_id 1} {$o_id <= $ORD_PER_DIST } {incr o_id } {
set o_c_id $cust($o_id)
set o_carrier_id [ RandomNumber 1 10 ]
set o_ol_cnt [ RandomNumber 5 15 ]
if { $o_id > 2100 } {
set e "o1"
orabind $curn7 :o_id $o_id :o_c_id $o_c_id :o_d_id $o_d_id :o_w_id $o_w_id :timestamp [ gettimestamp ] :o_ol_cnt $o_ol_cnt
if {[ catch {oraexec $curn7} message ] } {
puts "Error in cursor 7:$curn7 $message"
puts [ oramsg $curn7 all ]
}
set e "no1"
orabind $curn8 :o_id $o_id :o_d_id $o_d_id :o_w_id $o_w_id
if {[ catch {oraexec $curn8} message ] } {
puts "Error in cursor 8:$curn8 $message"
puts [ oramsg $curn8 all ]
}
} else {
set e "o3"
orabind $curn9 :o_id $o_id :o_c_id $o_c_id :o_d_id $o_d_id :o_w_id $o_w_id :timestamp [ gettimestamp ] :o_carrier_id $o_carrier_id :o_ol_cnt $o_ol_cnt
if {[ catch {oraexec $curn9} message ] } {
puts "Error in cursor 9:$curn9 $message"
puts [ oramsg $curn9 all ]
}
}
for {set ol 1} {$ol <= $o_ol_cnt } {incr ol } {
set ol_i_id [ RandomNumber 1 $MAXITEMS ]
set ol_supply_w_id $o_w_id
set ol_quantity 5
set ol_amount 0.0
set ol_dist_info [ MakeAlphaString 24 24 $globArray $chalen ]
if { $o_id > 2100 } {
set e "ol1"
orabind $curn10 :o_id $o_id :o_d_id $o_d_id :o_w_id $o_w_id :ol $ol :ol_i_id $ol_i_id :ol_supply_w_id $ol_supply_w_id :ol_quantity $ol_quantity :ol_amount $ol_amount :ol_dist_info $ol_dist_info
if {[ catch {oraexec $curn10} message ] } {
puts "Error in cursor 10:$curn10 $message"
puts [ oramsg $curn10 all ]
}
} else {
set amt_ran [ RandomNumber 10 10000 ]
set ol_amount [ expr {$amt_ran 100.0} ]
set e "ol2"
orabind $curn11 :o_id $o_id :o_d_id $o_d_id :o_w_id $o_w_id :ol $ol :ol_i_id $ol_i_id :ol_supply_w_id $ol_supply_w_id :ol_quantity $ol_quantity :ol_amount $ol_amount :ol_dist_info $ol_dist_info :timestamp [ gettimestamp ]
if {[ catch {oraexec $curn11} message ] } {
puts "Error in cursor 11:$curn11 $message"
puts [ oramsg $curn11 all ]
}
}
}
if { ![ expr {$o_id % 50000} ] } {
puts "...$o_id"
oracommit $lda
}
}
oracommit $lda
oraclose $curn7
oraclose $curn8
oraclose $curn9
oraclose $curn10
oraclose $curn11
puts "Orders Done"
return;
}
proc TTStock { lda w_id MAXITEMS } {
#Single Row Insert Procedure kept distinct in event of OCI Batch Inserts work against TimesTen
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading Stock Wid=$w_id"
set curn3 [oraopen $lda ]
set sql "INSERT INTO STOCK (s_i_id, s_w_id, s_quantity, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10, s_data, s_ytd, s_order_cnt, s_remote_cnt) values (:s_i_id, :s_w_id, :s_quantity, :s_dist_01, :s_dist_02, :s_dist_03, :s_dist_04, :s_dist_05, :s_dist_06, :s_dist_07, :s_dist_08, :s_dist_09, :s_dist_10, :s_data, 0, 0, 0)"
oraparse $curn3 $sql
set s_w_id $w_id
for {set i 0} {$i < [ expr {$MAXITEMS/10} ] } {incr i } {
set orig($i) 0
}
for {set i 0} {$i < [ expr {$MAXITEMS/10} ] } {incr i } {
set pos [ RandomNumber 0 $MAXITEMS ]
set orig($pos) 1
}
for {set s_i_id 1} {$s_i_id <= $MAXITEMS } {incr s_i_id } {
set s_quantity [ RandomNumber 10 100 ]
set s_dist_01 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_02 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_03 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_04 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_05 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_06 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_07 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_08 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_09 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_10 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_data [ MakeAlphaString 26 50 $globArray $chalen ]
if { [ info exists orig($s_i_id) ] } {
if { $orig($s_i_id) eq 1 } {
set first [ RandomNumber 0 [ expr {[ string length $s_data]} - 8 ] ]
set last [ expr {$first + 8} ]
set s_data [ string replace $s_data $first $last "original" ]
}
}
orabind $curn3 :s_i_id $s_i_id :s_w_id $s_w_id :s_quantity $s_quantity :s_dist_01 $s_dist_01 :s_dist_02 $s_dist_02 :s_dist_03 $s_dist_03 :s_dist_04 $s_dist_04 :s_dist_05 $s_dist_05 :s_dist_06 $s_dist_06 :s_dist_07 $s_dist_07 :s_dist_08 $s_dist_08 :s_dist_09 $s_dist_09 :s_dist_10 $s_dist_10 :s_data $s_data
if {[ catch {oraexec $curn3} message ] } {
puts "Error in cursor 3:$curn3 $message"
puts [ oramsg $curn3 all ]
}
if { ![ expr {$s_i_id % 50000} ] } {
puts "Loading Stock - $s_i_id"
oracommit $lda
}
}
oracommit $lda
oraclose $curn3
puts "Stock done"
return
}
proc Customer { lda d_id w_id CUST_PER_DIST } {
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set namearr [list BAR OUGHT ABLE PRI PRES ESE ANTI CALLY ATION EING]
set chalen [ llength $globArray ]
set c_d_id $d_id
set c_w_id $w_id
set c_middle "OE"
set c_balance -10.0
set c_credit_lim 50000
set h_amount 10.0
puts "Loading Customer for DID=$d_id WID=$w_id"
set curn5 [oraopen $lda ]
set sql "INSERT INTO customer (c_id, c_d_id, c_w_id, c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, c_data, c_ytd_payment, c_payment_cnt, c_delivery_cnt) values (:c_id, :c_d_id, :c_w_id, :c_first, :c_middle, :c_last, :c_street_1, :c_street_2, :c_city, :c_state, :c_zip, :c_phone, to_date(:timestamp,'YYYYMMDDHH24MISS'), :c_credit, :c_credit_lim, :c_discount, :c_balance, :c_data, 10.0, 1, 0)"
oraparse $curn5 $sql
set curn6 [oraopen $lda ]
set sql2 "INSERT INTO history (h_c_id, h_c_d_id, h_c_w_id, h_w_id, h_d_id, h_date, h_amount, h_data) values (:c_id, :c_d_id, :c_w_id, :c_w_id, :c_d_id, to_date(:timestamp,'YYYYMMDDHH24MISS'), :h_amount, :h_data)"
oraparse $curn6 $sql2
for {set c_id 1} {$c_id <= $CUST_PER_DIST } {incr c_id } {
set c_first [ MakeAlphaString 8 16 $globArray $chalen ]
if { $c_id <= 1000 } {
set c_last [ Lastname [ expr {$c_id - 1} ] $namearr ]
} else {
set nrnd [ NURand 255 0 999 123 ]
set c_last [ Lastname $nrnd $namearr ]
}
set c_add [ MakeAddress $globArray $chalen ]
set c_phone [ MakeNumberString ]
if { [RandomNumber 0 1] eq 1 } {
set c_credit "GC"
} else {
set c_credit "BC"
}
set disc_ran [ RandomNumber 0 50 ]
set c_discount [ expr {$disc_ran 100.0} ]
set c_data [ MakeAlphaString 300 500 $globArray $chalen ]
foreach i {c_id_c5 c_d_id_c5 c_w_id_c5 c_first_c5 c_middle_c5 c_last_c5 c_phone_c5 c_credit_c5 c_credit_lim_c5 c_discount_c5 c_balance_c5 c_data_c5} j {c_id c_d_id c_w_id c_first c_middle c_last c_phone c_credit c_credit_lim c_discount c_balance c_data} {
lappend $i [set $j]
}
foreach i {c_street_1_c5 c_street_2_c5 c_city_c5 c_state_c5 c_zip_c5 timestamp_c5} j "[ lindex $c_add 0 ] [ lindex $c_add 1 ] [ lindex $c_add 2 ] [ lindex $c_add 3 ] [ lindex $c_add 4 ] [ gettimestamp ]" {
lappend $i $j
}
set h_data [ MakeAlphaString 12 24 $globArray $chalen ]
foreach i {h_c_id_c6 h_c_d_id_c6 h_c_w_id_c6 h_w_id_c6 h_d_id_c6 h_amount_c6 h_data_c6} j {c_id c_d_id c_w_id c_w_id c_d_id h_amount h_data} {
lappend $i [set $j]
}
lappend h_date_c6 [ gettimestamp ]
if { ![ expr {$c_id % 1000} ] } {
oraparse $curn5 $sql
orabind $curn5 -arraydml :c_id $c_id_c5 :c_d_id $c_d_id_c5 :c_w_id $c_w_id_c5 :c_first $c_first_c5 :c_middle $c_middle_c5 :c_last $c_last_c5 :c_street_1 $c_street_1_c5 :c_street_2 $c_street_2_c5 :c_city $c_city_c5 :c_state $c_state_c5 :c_zip $c_zip_c5 :c_phone $c_phone_c5 :timestamp $timestamp_c5 :c_credit $c_credit_c5 :c_credit_lim $c_credit_lim_c5 :c_discount $c_discount_c5 :c_balance $c_balance_c5 :c_data $c_data_c5
if {[ catch {oraexec $curn5} message ] } {
puts "Error in cursor 5:$curn5 $message"
puts [ oramsg $curn5 all ]
}
oraparse $curn6 $sql2
orabind $curn6 -arraydml :c_id $h_c_id_c6 :c_d_id $h_c_d_id_c6 :c_w_id $h_c_w_id_c6 :c_w_id $h_w_id_c6 :c_d_id $h_d_id_c6 :timestamp $h_date_c6 :h_amount $h_amount_c6 :h_data $h_data_c6
if {[ catch {oraexec $curn6} message ] } {
puts "Error in cursor 6:$curn6 $message"
puts [ oramsg $curn6 all ]
}
unset c_id_c5 c_d_id_c5 c_w_id_c5 c_first_c5 c_middle_c5 c_last_c5 c_phone_c5 c_credit_c5 c_credit_lim_c5 c_discount_c5 c_balance_c5 c_data_c5 c_street_1_c5 c_street_2_c5 c_city_c5 c_state_c5 c_zip_c5 timestamp_c5 h_c_id_c6 h_c_d_id_c6 h_c_w_id_c6 h_w_id_c6 h_d_id_c6 h_amount_c6 h_data_c6 h_date_c6
}
}
oracommit $lda
oraclose $curn5
oraclose $curn6
puts "Customer Done"
return
}
proc Orders { lda d_id w_id MAXITEMS ORD_PER_DIST } {
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading Orders for D=$d_id W=$w_id"
set curn7 [ oraopen $lda ]
set sql "INSERT INTO orders (o_id, o_c_id, o_d_id, o_w_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all_local) values (:o_id, :o_c_id, :o_d_id, :o_w_id, to_date(:timestamp,'YYYYMMDDHH24MISS'), NULL, :o_ol_cnt, 1)"
oraparse $curn7 $sql
set curn8 [ oraopen $lda ]
set sql2 "INSERT INTO new_order (no_o_id, no_d_id, no_w_id) values (:o_id, :o_d_id, :o_w_id)"
oraparse $curn8 $sql2
set curn9 [ oraopen $lda ]
set sql3 "INSERT INTO orders (o_id, o_c_id, o_d_id, o_w_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all_local) values (:o_id, :o_c_id, :o_d_id, :o_w_id, to_date(:timestamp,'YYYYMMDDHH24MISS'), :o_carrier_id, :o_ol_cnt, 1)"
oraparse $curn9 $sql3
set curn10 [ oraopen $lda ]
set sql4 "INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) values (:o_id, :o_d_id, :o_w_id, :ol, :ol_i_id, :ol_supply_w_id, :ol_quantity, :ol_amount, :ol_dist_info, NULL)"
oraparse $curn10 $sql4
set curn11 [ oraopen $lda ]
set sql5 "INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) values (:o_id, :o_d_id, :o_w_id, :ol, :ol_i_id, :ol_supply_w_id, :ol_quantity, :ol_amount, :ol_dist_info, to_date(:timestamp,'YYYYMMDDHH24MISS'))"
oraparse $curn11 $sql5
set o_d_id $d_id
set o_w_id $w_id
for {set i 1} {$i <= $ORD_PER_DIST } {incr i } {
set cust($i) $i
}
for {set i 1} {$i <= $ORD_PER_DIST } {incr i } {
set r [ RandomNumber $i $ORD_PER_DIST ]
set t $cust($i)
set cust($i) $cust($r)
set $cust($r) $t
}
set e ""
for {set o_id 1} {$o_id <= $ORD_PER_DIST } {incr o_id } {
set o_c_id $cust($o_id)
set o_carrier_id [ RandomNumber 1 10 ]
set o_ol_cnt [ RandomNumber 5 15 ]
if { $o_id > 2100 } {
set e "o1"
foreach i {o_id_c7 o_c_id_c7 o_d_id_c7 o_w_id_c7 o_ol_cnt_c7} j {o_id o_c_id o_d_id o_w_id o_ol_cnt} {
lappend $i [set $j]
}
lappend timestamp_c7 [ gettimestamp ]
set e "no1"
foreach i {o_id_c8 o_d_id_c8 o_w_id_c8} j {o_id o_d_id o_w_id} {
lappend $i [set $j]
}
} else {
set e "o3"
foreach i {o_id_c9 o_c_id_c9 o_d_id_c9 o_w_id_c9 o_carrier_id_c9 o_ol_cnt_c9} j {o_id o_c_id o_d_id o_w_id o_carrier_id o_ol_cnt} {
lappend $i [set $j]
}
lappend timestamp_c9 [ gettimestamp ]
}
for {set ol 1} {$ol <= $o_ol_cnt } {incr ol } {
set ol_i_id [ RandomNumber 1 $MAXITEMS ]
set ol_supply_w_id $o_w_id
set ol_quantity 5
set ol_amount 0.0
set ol_dist_info [ MakeAlphaString 24 24 $globArray $chalen ]
if { $o_id > 2100 } {
set e "ol1"
foreach i {o_id_c10 o_d_id_c10 o_w_id_c10 ol_c10 ol_i_id_c10 ol_supply_w_id_c10 ol_quantity_c10 ol_amount_c10 ol_dist_info_c10} j {o_id o_d_id o_w_id ol ol_i_id ol_supply_w_id ol_quantity ol_amount ol_dist_info} {
lappend $i [set $j]
}
} else {
set amt_ran [ RandomNumber 10 10000 ]
set ol_amount [ expr {$amt_ran 100.0} ]
set e "ol2"
foreach i {o_id_c11 o_d_id_c11 o_w_id_c11 ol_c11 ol_i_id_c11 ol_supply_w_id_c11 ol_quantity_c11 ol_amount_c11 ol_dist_info_c11} j {o_id o_d_id o_w_id ol ol_i_id ol_supply_w_id ol_quantity ol_amount ol_dist_info} {
lappend $i [set $j]
}
lappend timestamp_c11 [ gettimestamp ]
}
}
if { ![ expr {$o_id % 100} ] } {
if { ![ expr {$o_id % 50000} ] } {
puts "...$o_id"
oracommit $lda
}
if { $o_id > 2100 } {
oraparse $curn7 $sql
oraparse $curn8 $sql2
oraparse $curn10 $sql4
orabind $curn7 -arraydml :o_id $o_id_c7 :o_c_id $o_c_id_c7 :o_d_id $o_d_id_c7 :o_w_id $o_w_id_c7 :timestamp $timestamp_c7 :o_ol_cnt $o_ol_cnt_c7
if {[ catch {oraexec $curn7} message ] } {
puts "Error in cursor 7:$curn7 $message"
puts [ oramsg $curn7 all ]
}
orabind $curn8 -arraydml :o_id $o_id_c8 :o_d_id $o_d_id_c8 :o_w_id $o_w_id_c8
if {[ catch {oraexec $curn8} message ] } {
puts "Error in cursor 8:$curn8 $message"
puts [ oramsg $curn8 all ]
}
orabind $curn10 -arraydml :o_id $o_id_c10 :o_d_id $o_d_id_c10 :o_w_id $o_w_id_c10 :ol $ol_c10 :ol_i_id $ol_i_id_c10 :ol_supply_w_id $ol_supply_w_id_c10 :ol_quantity $ol_quantity_c10 :ol_amount $ol_amount_c10 :ol_dist_info $ol_dist_info_c10
if {[ catch {oraexec $curn10} message ] } {
puts "Error in cursor 10:$curn10 $message"
puts [ oramsg $curn10 all ]
}
unset o_id_c7 o_c_id_c7 o_d_id_c7 o_w_id_c7 timestamp_c7 o_ol_cnt_c7 o_id_c8 o_d_id_c8 o_w_id_c8 o_id_c10 o_d_id_c10 o_w_id_c10 ol_c10 ol_i_id_c10 ol_supply_w_id_c10 ol_quantity_c10 ol_amount_c10 ol_dist_info_c10
} else {
oraparse $curn9 $sql3
oraparse $curn11 $sql5
orabind $curn9 -arraydml :o_id $o_id_c9 :o_c_id $o_c_id_c9 :o_d_id $o_d_id_c9 :o_w_id $o_w_id_c9 :timestamp $timestamp_c9 :o_carrier_id $o_carrier_id_c9 :o_ol_cnt $o_ol_cnt_c9
if {[ catch {oraexec $curn9} message ] } {
puts "Error in cursor 9:$curn9 $message"
puts [ oramsg $curn9 all ]
}
orabind $curn11 -arraydml :o_id $o_id_c11 :o_d_id $o_d_id_c11 :o_w_id $o_w_id_c11 :ol $ol_c11 :ol_i_id $ol_i_id_c11 :ol_supply_w_id $ol_supply_w_id_c11 :ol_quantity $ol_quantity_c11 :ol_amount $ol_amount_c11 :ol_dist_info $ol_dist_info_c11 :timestamp $timestamp_c11
if {[ catch {oraexec $curn11} message ] } {
puts "Error in cursor 11:$curn11 $message"
puts [ oramsg $curn11 all ]
}
unset o_id_c9 o_c_id_c9 o_d_id_c9 o_w_id_c9 timestamp_c9 o_carrier_id_c9 o_ol_cnt_c9 o_id_c11 o_d_id_c11 o_w_id_c11 ol_c11 ol_i_id_c11 ol_supply_w_id_c11 ol_quantity_c11 ol_amount_c11 ol_dist_info_c11 timestamp_c11
}
}
}
oracommit $lda
oraclose $curn7
oraclose $curn8
oraclose $curn9
oraclose $curn10
oraclose $curn11
puts "Orders Done"
return;
}
proc LoadItems { lda MAXITEMS } {
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading Item"
set curn1 [oraopen $lda ]
set sql "INSERT INTO item (i_id, i_im_id, i_name, i_price, i_data) values (:i_id, :i_im_id, :i_name, :i_price, :i_data)"
oraparse $curn1 $sql
for {set i 0} {$i < [ expr {$MAXITEMS/10} ] } {incr i } {
set orig($i) 0
}
for {set i 0} {$i < [ expr {$MAXITEMS/10} ] } {incr i } {
set pos [ RandomNumber 0 $MAXITEMS ]
set orig($pos) 1
}
for {set i_id 1} {$i_id <= $MAXITEMS } {incr i_id } {
set i_im_id [ RandomNumber 1 10000 ]
set i_name [ MakeAlphaString 14 24 $globArray $chalen ]
set i_price_ran [ RandomNumber 100 10000 ]
set i_price [ format "%4.2f" [ expr {$i_price_ran 100.0} ] ]
set i_data [ MakeAlphaString 26 50 $globArray $chalen ]
if { [ info exists orig($i_id) ] } {
if { $orig($i_id) eq 1 } {
set first [ RandomNumber 0 [ expr {[ string length $i_data] - 8}] ]
set last [ expr {$first + 8} ]
set i_data [ string replace $i_data $first $last "original" ]
}
}
orabind $curn1 :i_id $i_id :i_im_id $i_im_id :i_name $i_name :i_price $i_price :i_data $i_data
if {[ catch {oraexec $curn1} message ] } {
puts "Error in cursor 1:$curn1 $message"
puts [ oramsg $curn1 all ]
}
if { ![ expr {$i_id % 50000} ] } {
puts "Loading Items - $i_id"
oracommit $lda
}
}
oracommit $lda
oraclose $curn1
puts "Item done"
return
}
proc Stock { lda w_id MAXITEMS } {
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading Stock Wid=$w_id"
set curn3 [oraopen $lda ]
set sql "INSERT INTO STOCK (s_i_id, s_w_id, s_quantity, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10, s_data, s_ytd, s_order_cnt, s_remote_cnt) values (:s_i_id, :s_w_id, :s_quantity, :s_dist_01, :s_dist_02, :s_dist_03, :s_dist_04, :s_dist_05, :s_dist_06, :s_dist_07, :s_dist_08, :s_dist_09, :s_dist_10, :s_data, 0, 0, 0)"
oraparse $curn3 $sql
set s_w_id $w_id
for {set i 0} {$i < [ expr {$MAXITEMS/10} ] } {incr i } {
set orig($i) 0
}
for {set i 0} {$i < [ expr {$MAXITEMS/10} ] } {incr i } {
set pos [ RandomNumber 0 $MAXITEMS ]
set orig($pos) 1
}
for {set s_i_id 1} {$s_i_id <= $MAXITEMS } {incr s_i_id } {
set s_quantity [ RandomNumber 10 100 ]
set s_dist_01 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_02 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_03 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_04 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_05 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_06 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_07 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_08 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_09 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_dist_10 [ MakeAlphaString 24 24 $globArray $chalen ]
set s_data [ MakeAlphaString 26 50 $globArray $chalen ]
if { [ info exists orig($s_i_id) ] } {
if { $orig($s_i_id) eq 1 } {
set first [ RandomNumber 0 [ expr {[ string length $s_data]} - 8 ] ]
set last [ expr {$first + 8} ]
set s_data [ string replace $s_data $first $last "original" ]
}
}
foreach i {s_i_id_c3 s_w_id_c3 s_quantity_c3 s_dist_01_c3 s_dist_02_c3 s_dist_03_c3 s_dist_04_c3 s_dist_05_c3 s_dist_06_c3 s_dist_07_c3 s_dist_08_c3 s_dist_09_c3 s_dist_10_c3 s_data_c3} j {s_i_id s_w_id s_quantity s_dist_01 s_dist_02 s_dist_03 s_dist_04 s_dist_05 s_dist_06 s_dist_07 s_dist_08 s_dist_09 s_dist_10 s_data} {
lappend $i [set $j]
}
if { ![ expr {$s_i_id % 1000} ] } {
oraparse $curn3 $sql
orabind $curn3 -arraydml :s_i_id $s_i_id_c3 :s_w_id $s_w_id_c3 :s_quantity $s_quantity_c3 :s_dist_01 $s_dist_01_c3 :s_dist_02 $s_dist_02_c3 :s_dist_03 $s_dist_03_c3 :s_dist_04 $s_dist_04_c3 :s_dist_05 $s_dist_05_c3 :s_dist_06 $s_dist_06_c3 :s_dist_07 $s_dist_07_c3 :s_dist_08 $s_dist_08_c3 :s_dist_09 $s_dist_09_c3 :s_dist_10 $s_dist_10_c3 :s_data $s_data_c3
if {[ catch {oraexec $curn3} message ] } {
puts "Error in cursor 3:$curn3 $message"
puts [ oramsg $curn3 all ]
}
unset s_i_id_c3 s_w_id_c3 s_quantity_c3 s_dist_01_c3 s_dist_02_c3 s_dist_03_c3 s_dist_04_c3 s_dist_05_c3 s_dist_06_c3 s_dist_07_c3 s_dist_08_c3 s_dist_09_c3 s_dist_10_c3 s_data_c3
}
if { ![ expr {$s_i_id % 50000} ] } {
puts "Loading Stock - $s_i_id"
oracommit $lda
}
}
oracommit $lda
oraclose $curn3
puts "Stock done"
return
}
proc District { lda w_id DIST_PER_WARE } {
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading District"
set curn4 [oraopen $lda ]
set sql "INSERT INTO DISTRICT (d_id, d_w_id, d_name, d_street_1, d_street_2, d_city, d_state, d_zip, d_tax, d_ytd, d_next_o_id) values (:d_id, :d_w_id, :d_name, :d_street_1, :d_street_2, :d_city, :d_state, :d_zip, :d_tax, :d_ytd, :d_next_o_id)"
oraparse $curn4 $sql
set d_w_id $w_id
set d_ytd 30000.0
set d_next_o_id 3001
for {set d_id 1} {$d_id <= $DIST_PER_WARE } {incr d_id } {
set d_name [ MakeAlphaString 6 10 $globArray $chalen ]
set d_add [ MakeAddress $globArray $chalen ]
set d_tax_ran [ RandomNumber 10 20 ]
set d_tax [ string replace [ format "%.2f" [ expr {$d_tax_ran / 100.0} ] ] 0 0 "" ]
orabind $curn4 :d_id $d_id :d_w_id $d_w_id :d_name $d_name :d_street_1 [ lindex $d_add 0 ] :d_street_2 [ lindex $d_add 1 ] :d_city [ lindex $d_add 2 ] :d_state [ lindex $d_add 3 ] :d_zip [ lindex $d_add 4 ] :d_tax $d_tax :d_ytd $d_ytd :d_next_o_id $d_next_o_id
if {[ catch {oraexec $curn4} message ] } {
puts "Error in cursor 4:$curn4 $message"
puts [ oramsg $curn4 all ]
}
}
oracommit $lda
oraclose $curn4
puts "District done"
return
}
proc LoadWare { lda ware_start count_ware MAXITEMS DIST_PER_WARE timesten } {
set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
set chalen [ llength $globArray ]
puts "Loading Warehouse"
set curn2 [oraopen $lda ]
set sql "INSERT INTO WAREHOUSE (w_id, w_name, w_street_1, w_street_2, w_city, w_state, w_zip, w_tax, w_ytd) values (:w_id, :w_name, :w_street_1, :w_street_2, :w_city, :w_state, :w_zip, :w_tax, :w_ytd)"
oraparse $curn2 $sql
set w_ytd 3000000.00
for {set w_id $ware_start } {$w_id <= $count_ware } {incr w_id } {
set w_name [ MakeAlphaString 6 10 $globArray $chalen ]
set add [ MakeAddress $globArray $chalen ]
set w_tax_ran [ RandomNumber 10 20 ]
set w_tax [ string replace [ format "%.2f" [ expr {$w_tax_ran / 100.0} ] ] 0 0 "" ]
orabind $curn2 :w_id $w_id :w_name $w_name :w_street_1 [ lindex $add 0 ] :w_street_2 [ lindex $add 1 ] :w_city [ lindex $add 2 ] :w_state [ lindex $add 3 ] :w_zip [ lindex $add 4 ] :w_tax $w_tax :w_ytd $w_ytd
if {[ catch {oraexec $curn2} message ] } {
puts "Error in cursor 2:$curn2 $message"
puts [ oramsg $curn2 all ]
}
if { $timesten } {
TTStock $lda $w_id $MAXITEMS
} else {
Stock $lda $w_id $MAXITEMS
}
District $lda $w_id $DIST_PER_WARE
oracommit $lda
}
oraclose $curn2
}
proc LoadCust { lda ware_start count_ware CUST_PER_DIST DIST_PER_WARE timesten } {
for {set w_id $ware_start} {$w_id <= $count_ware } {incr w_id } {
for {set d_id 1} {$d_id <= $DIST_PER_WARE } {incr d_id } {
if { $timesten } {
TTCustomer $lda $d_id $w_id $CUST_PER_DIST
} else {
Customer $lda $d_id $w_id $CUST_PER_DIST
}
}
}
oracommit $lda
return
}
proc LoadOrd { lda ware_start count_ware MAXITEMS ORD_PER_DIST DIST_PER_WARE timesten num_part } {
for {set w_id $ware_start} {$w_id <= $count_ware } {incr w_id } {
for {set d_id 1} {$d_id <= $DIST_PER_WARE } {incr d_id } {
if { $timesten } {
TTOrders $lda $d_id $w_id $MAXITEMS $ORD_PER_DIST $num_part
} else {
Orders $lda $d_id $w_id $MAXITEMS $ORD_PER_DIST
}
}
}
oracommit $lda
return
}
proc do_tpcc { system_user system_password instance count_ware tpcc_user tpcc_pass tpcc_def_tab tpcc_ol_tab tpcc_def_temp partition timesten hash_clusters num_vu } {
set MAXITEMS 100000
set CUST_PER_DIST 3000
set DIST_PER_WARE 10
set ORD_PER_DIST 3000
if { [ string toupper $timesten ] eq "TRUE"} { set timesten 1 } else { set timesten 0 }
if { $num_vu > $count_ware } { set num_vu $count_ware }
if { $num_vu > 1 && [ chk_thread ] eq "TRUE" } {
set threaded "MULTI-THREADED"
set rema [ lassign [ findvuposition ] myposition totalvirtualusers ]
switch $myposition {
1 {
puts "Monitor Thread"
if { $threaded eq "MULTI-THREADED" } {
tsv::lappend common thrdlst monitor
for { set th 1 } { $th <= $totalvirtualusers } { incr th } {
tsv::lappend common thrdlst idle
}
tsv::set application load "WAIT"
}
}
default {
puts "Worker Thread"
if { [ expr $myposition - 1 ] > $count_ware } { puts "No Warehouses to Create"; return }
}
}
} else {
set threaded "SINGLE-THREADED"
set num_vu 1
}
if { $threaded eq "SINGLE-THREADED" || $threaded eq "MULTI-THREADED" && $myposition eq 1 } {
puts "CREATING [ string toupper $tpcc_user ] SCHEMA"
if { $timesten } {
puts "TimesTen expects the Database [ string toupper $instance ] and User [ string toupper $tpcc_user ] to have been created by the instance administrator in advance and be granted create table, session, procedure, view (and admin for checkpoints) privileges"
} else {
set connect $system_user/$system_password@$instance
set lda [ oralogon $connect ]
SetNLS $lda
CreateUser $lda $tpcc_user $tpcc_pass $tpcc_def_tab $tpcc_def_temp $tpcc_ol_tab $partition
oralogoff $lda
}
set connect $tpcc_user/$tpcc_pass@$instance
set lda [ oralogon $connect ]
if { $timesten } {
if { $partition eq "true" } {
set num_part 10
} else {
set num_part 0
}
} else {
SetNLS $lda
if { $partition eq "true" } {
if {$count_ware < 200} {
set num_part 0
set hash_clusters "false"
} else {
set num_part [ expr round($count_ware/100) ]
}
} else {
set num_part 0
set hash_clusters "false"
}}
CreateTables $lda $num_part $tpcc_ol_tab $timesten $hash_clusters $count_ware
if { $threaded eq "MULTI-THREADED" } {
tsv::set application load "READY"
LoadItems $lda $MAXITEMS
puts "Monitoring Workers..."
set prevactive 0
while 1 {
set idlcnt 0; set lvcnt 0; set dncnt 0;
for {set th 2} {$th <= $totalvirtualusers } {incr th} {
switch [tsv::lindex common thrdlst $th] {
idle { incr idlcnt }
active { incr lvcnt }
done { incr dncnt }
}
}
if { $lvcnt != $prevactive } {
puts "Workers: $lvcnt Active $dncnt Done"
}
set prevactive $lvcnt
if { $dncnt eq [expr $totalvirtualusers - 1] } { break }
after 10000
}} else {
LoadItems $lda $MAXITEMS
}}
if { $threaded eq "SINGLE-THREADED" || $threaded eq "MULTI-THREADED" && $myposition != 1 } {
if { $threaded eq "MULTI-THREADED" } {
puts "Waiting for Monitor Thread..."
set mtcnt 0
while 1 {
incr mtcnt
if { [ tsv::get application load ] eq "READY" } { break }
if { [ tsv::get application abort ] } { return }
if { $mtcnt eq 48 } {
puts "Monitor failed to notify ready state"
return
}
after 5000
}
set connect $tpcc_user/$tpcc_pass@$instance
set lda [ oralogon $connect ]
if { $timesten } {
;
} else {
SetNLS $lda
}
set remb [ lassign [ findchunk $num_vu $count_ware $myposition ] chunk mystart myend ]
puts "Loading $chunk Warehouses start:$mystart end:$myend"
tsv::lreplace common thrdlst $myposition $myposition active
} else {
set mystart 1
set myend $count_ware
}
puts "Start:[ clock format [ clock seconds ] ]"
if { $timesten } { if { $partition eq "true" } { set num_part 10 } else { set num_part 0 }} else { set num_part 0 }
LoadWare $lda $mystart $myend $MAXITEMS $DIST_PER_WARE $timesten
LoadCust $lda $mystart $myend $CUST_PER_DIST $DIST_PER_WARE $timesten
LoadOrd $lda $mystart $myend $MAXITEMS $ORD_PER_DIST $DIST_PER_WARE $timesten $num_part
puts "End:[ clock format [ clock seconds ] ]"
oracommit $lda
if { $threaded eq "MULTI-THREADED" } {
tsv::lreplace common thrdlst $myposition $myposition done
}
}
if { $threaded eq "SINGLE-THREADED" || $threaded eq "MULTI-THREADED" && $myposition eq 1 } {
CreateIndexes $lda $timesten $num_part $hash_clusters
if { $timesten } { TTPLSQLSettings $lda }
CreateStoredProcs $lda $timesten $num_part
GatherStatistics $lda [ string toupper $tpcc_user ] $timesten $num_part
puts "[ string toupper $tpcc_user ] SCHEMA COMPLETE"
oralogoff $lda
return
}
}
do_tpcc system manager oracle 1 tpcc tpcc tpcctab tpcctab temp false false false 1
