Hi,
I'm trying to run HammerDB TPC-C from this tutorial
http://hammerora.sourceforge.net/hammerdb_quickstart_trafodion.pdf
I have this environment:
*CentOS release 6.7 (Final)*
*Ambari 2.1.1*
*HDP 2.2*
*trafodion-20150828_0830*
*HammerDB-2.18*
*java version "1.7.0_79"*
On *Schema Build* I get errors when stored procedures are created.
*This is from the hammerdb logs:*
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:Failed to create library /home/trafodion/HammerDB-2.18/NEWORDER.jar
*This is what I see in the UI (attached is also a screenshot):*
loading history file ... 0 events added
Main console display active (Tcl8.6.0 / Tk8.6.0)
The xml in config.xml is well-formed, applying variables
Error in Virtual User 1: [Trafodion ODBC Driver][Trafodion Database] SQL
ERROR:*** ERROR[1382] JAR or DLL file
/home/trafodion/HammerDB-2.18/NEWORDER.jar not found. [2015-09-11
07:50:53]Àç¿Àéç»ç¿Àéç»ç¿Àéç»ç¿Àé
ç»ç¿Àéç»ç¿Àé¸ç»ç¿Àå¦èç¿ÀìªÊÀÀì¹°ÊÀÀé
ç»ç¿ÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÕË®ÀÀì
Ì ÀÀÀÀÀÀÀÀÀÀï¿¿ï¿¿ÀÀì« ï¿¿
ÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀé°ÀÀÀì²ÊÀÀî°Ò»ÀÀé¼ç«ç¿Àë¾°Ç´ÀÀåÒÀÀç½ËÀÀì²ÊÀÀéç»ç¿Àãµç«ç¿Àé
ç»ç¿ÀìµÊÀÀé°ç»
(executing the statement)
(HammerDB-2.18) 1 %
By looking in the tpcc schema I see some tables are created and are
populated with data:
>>set schema tpcc;
--- SQL operation complete.
>>get tables;
Tables in Schema TRAFODION.TPCC
===============================
CUSTOMER
DISTRICT
HISTORY
ITEM
NEW_ORDER
ORDERS
ORDER_LINE
STOCK
WAREHOUSE
The file '*/home/trafodion/HammerDB-2.18/NEWORDER.jar*' exists, attached is
the NEWORDER.java
*When trying to create the stored procedure from sqlci I get this:*
set schema tpcc;
create library testrs file '/home/trafodion/HammerDB-2.18/NEWORDER.jar';
create procedure NEWORD()
language java
parameter style java
external name 'NEWORDER.NEWORD'
dynamic result sets 1
library testrs;
*** ERROR[11239] No compatible Java methods named 'NEWORD' were found in
Java class 'NEWORDER'.
*** ERROR[1231] User-defined routine TRAFODION.TPCC.NEWORDER could not be
created.
Is there an API change on methods for stored procedures in the latest
trafodion and hammerdb uses the older syntax? This is the method:
public static void NEWORD (int no_w_id, int no_max_w_id, int no_d_id, int
no_c_id, int no_o_ol_cnt, BigDecimal[] no_c_discount, String[] no_c_last,
String[] no_c_credit, BigDecimal[] no_d_tax, BigDecimal[] no_w_tax, int[]
no_d_next_o_id, Timestamp tstamp, ResultSet[] opres)
throws SQLException
Also If I disable "*Build Java Stored Procedures Locally*" from hammerdb I
get:
Vuser 1:CREATING TPCC INDEXES
Vuser 1:Creating Index CUSTOMER_I2...
Vuser 1:Failed to Create Index
--
And in the end, it's not the years in your life that count. It's the life
in your years.
import java.sql.*;
import java.math.*;
import java.util.Random;
public class NEWORDER {
public static int randInt(int min, int max) {
Random rand = new Random();
int randomNum = rand.nextInt((max - min) + 1) + min;
return randomNum;
}
public static void NEWORD (int no_w_id, int no_max_w_id, int no_d_id, int no_c_id, int no_o_ol_cnt, BigDecimal[] no_c_discount, String[] no_c_last, String[] no_c_credit, BigDecimal[] no_d_tax, BigDecimal[] no_w_tax, int[] no_d_next_o_id, Timestamp tstamp, ResultSet[] opres)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("set schema trafodion.tpcc");
BigDecimal no_o_all_local = new BigDecimal(0);
PreparedStatement getDiscTax =
conn.prepareStatement("SELECT c_discount, c_last, c_credit, w_tax " +
"FROM customer, warehouse " +
"WHERE warehouse.w_id = ? AND customer.c_w_id = ? AND " +
"customer.c_d_id = ? AND customer.c_id = ?");
getDiscTax.setInt(1, no_w_id);
getDiscTax.setInt(2, no_w_id);
getDiscTax.setInt(3, no_d_id);
getDiscTax.setInt(4, no_c_id);
ResultSet rs = getDiscTax.executeQuery();
rs.next();
no_c_discount[0] = rs.getBigDecimal(1);
no_c_last[0] = rs.getString(2);
no_c_credit[0] = rs.getString(3);
no_w_tax[0] = rs.getBigDecimal(4);
rs.close();
PreparedStatement getOidTax =
conn.prepareStatement("SELECT d_next_o_id, d_tax " +
"FROM district " +
"WHERE d_id = ? AND d_w_id = ? FOR UPDATE");
getOidTax.setInt(1, no_d_id);
getOidTax.setInt(2, no_w_id);
ResultSet rs1 = getOidTax.executeQuery();
rs1.next();
no_d_next_o_id[0] = rs1.getInt(1);
no_d_tax[0] = rs1.getBigDecimal(2);
rs1.close();
PreparedStatement UpdDisc =
conn.prepareStatement("UPDATE district SET d_next_o_id = d_next_o_id + 1 " +
"WHERE d_id = ? AND d_w_id = ?");
UpdDisc.setInt(1, no_d_id);
UpdDisc.setInt(2, no_w_id);
UpdDisc.executeUpdate();
int o_id = no_d_next_o_id[0];
PreparedStatement InsOrd =
conn.prepareStatement("INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)");
InsOrd.setInt(1, o_id);
InsOrd.setInt(2, no_d_id);
InsOrd.setInt(3, no_w_id);
InsOrd.setInt(4, no_c_id);
InsOrd.setTimestamp(5, tstamp);
InsOrd.setInt(6, no_o_ol_cnt);
InsOrd.setBigDecimal(7, no_o_all_local);
InsOrd.executeUpdate();
PreparedStatement InsNewOrd =
conn.prepareStatement("INSERT INTO new_order (no_o_id, no_d_id, no_w_id) " +
"VALUES (?, ?, ?)");
InsNewOrd.setInt(1, o_id);
InsNewOrd.setInt(2, no_d_id);
InsNewOrd.setInt(3, no_w_id);
InsNewOrd.executeUpdate();
int rbk = randInt(1,100);
int no_ol_i_id;
int no_ol_supply_w_id;
/* In Loop Statements Prepared Outside of Loop */
PreparedStatement SelNameData =
conn.prepareStatement("SELECT i_price, i_name, i_data FROM item " +
"WHERE i_id = ?");
PreparedStatement SelStock =
conn.prepareStatement("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 FROM stock " +
"WHERE s_i_id = ? AND s_w_id = ?");
PreparedStatement UpdStock =
conn.prepareStatement("UPDATE stock SET s_quantity = ? " +
"WHERE s_i_id = ? " +
"AND s_w_id = ?");
PreparedStatement InsLine =
conn.prepareStatement("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 (?, ?, ?, ?, ?, ?, ?, ?, ?)");
/* In Loop Statements Prepared Outside of Loop */
for (int loop_counter=1; loop_counter<=no_o_ol_cnt; loop_counter++) {
if ((loop_counter == no_o_ol_cnt) && (rbk == 1)) {
no_ol_i_id = 100001;
}
else {
no_ol_i_id = randInt(1,100000);
}
int x = randInt(1,100);
if (x > 1) {
no_ol_supply_w_id = no_w_id;
}
else
{
no_ol_supply_w_id = no_w_id;
//NOTE IN SPECIFICATION no_o_all_local set to 0 here but not needed
while ((no_ol_supply_w_id == no_w_id) && (no_max_w_id != 1)) {
no_ol_supply_w_id = randInt(1,no_max_w_id);
}
}
int no_ol_quantity = randInt(1,10);
SelNameData.setInt(1, no_ol_i_id);
ResultSet rs2 = SelNameData.executeQuery();
rs2.next();
BigDecimal no_i_price = rs2.getBigDecimal(1);
String no_i_name = rs2.getString(2);
String no_i_data = rs2.getString(3);
rs2.close();
SelStock.setInt(1, no_ol_i_id);
SelStock.setInt(2, no_ol_supply_w_id);
ResultSet rs3 = SelStock.executeQuery();
rs3.next();
int no_s_quantity = rs3.getInt(1);
String no_s_data = rs3.getString(2);
String no_s_dist_01 = rs3.getString(3);
String no_s_dist_02 = rs3.getString(4);
String no_s_dist_03 = rs3.getString(5);
String no_s_dist_04 = rs3.getString(6);
String no_s_dist_05 = rs3.getString(7);
String no_s_dist_06 = rs3.getString(8);
String no_s_dist_07 = rs3.getString(9);
String no_s_dist_08 = rs3.getString(10);
String no_s_dist_09 = rs3.getString(11);
String no_s_dist_10 = rs3.getString(12);
rs3.close();
if (no_s_quantity > no_ol_quantity) {
no_s_quantity = (no_s_quantity - no_ol_quantity);
}
else {
no_s_quantity = (no_s_quantity - no_ol_quantity + 91);
}
UpdStock.setInt(1, no_s_quantity);
UpdStock.setInt(2, no_ol_i_id);
UpdStock.setInt(3, no_ol_supply_w_id);
UpdStock.executeUpdate();
BigDecimal onebd, disc, tax, no_ol_amount, quant, quant2;
onebd = new BigDecimal("1.0");
disc = onebd.subtract(no_c_discount[0]);
tax = onebd.add(no_w_tax[0].add(no_d_tax[0]));
quant = new BigDecimal(String.valueOf(no_ol_quantity));
quant2 = quant.multiply(no_i_price.multiply(tax.multiply(disc)));
no_ol_amount = quant2.setScale(2, RoundingMode.HALF_UP);
String no_ol_dist_info = "";
switch(no_d_id) {
case 1: no_ol_dist_info = no_s_dist_01;
break;
case 2: no_ol_dist_info = no_s_dist_02;
break;
case 3: no_ol_dist_info = no_s_dist_03;
break;
case 4: no_ol_dist_info = no_s_dist_04;
break;
case 5: no_ol_dist_info = no_s_dist_05;
break;
case 6: no_ol_dist_info = no_s_dist_06;
break;
case 7: no_ol_dist_info = no_s_dist_07;
break;
case 8: no_ol_dist_info = no_s_dist_08;
break;
case 9: no_ol_dist_info = no_s_dist_09;
break;
case 10:no_ol_dist_info = no_s_dist_10;
break;
}
InsLine.setInt(1, o_id);
InsLine.setInt(2, no_d_id);
InsLine.setInt(3, no_w_id);
InsLine.setInt(4, loop_counter);
InsLine.setInt(5, no_ol_i_id);
InsLine.setInt(6, no_ol_supply_w_id);
InsLine.setInt(7, no_ol_quantity);
InsLine.setBigDecimal(8, no_ol_amount);
InsLine.setString(9, no_ol_dist_info);
InsLine.executeUpdate();
}
conn.commit();
/* Return output parameters as a result set */
PreparedStatement getOutput =
conn.prepareStatement("SELECT ? AS NO_C_DISCOUNT,? AS NO_C_LAST,? AS NO_C_CREDIT,? AS NO_D_TAX,? AS NO_W_TAX,? AS NO_D_NEXT_O_ID from (values(1))x");
getOutput.setBigDecimal(1,no_c_discount[0]);
getOutput.setString(2,no_c_last[0]);
getOutput.setString(3,no_c_credit[0]);
getOutput.setBigDecimal(4,no_d_tax[0]);
getOutput.setBigDecimal(5,no_w_tax[0]);
getOutput.setInt(6,no_d_next_o_id[0]);
opres[0] = getOutput.executeQuery();
/* Do not close to retrieve result set, database engine will close
conn.close();
*/
}
catch (SQLException se)
{
System.out.println("ERROR: SQLException");
se.printStackTrace();
System.out.println(se.getMessage());
try {
conn.rollback();
} catch (SQLException se2) {
se2.printStackTrace();
}
conn.close();
return;
}
catch (Exception e)
{
System.out.println("ERROR: Exception");
e.printStackTrace();
System.out.println(e.getMessage());
System.exit(1);
}
}
}
