001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.maxMemPerTab
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.Connection;
025: import java.sql.PreparedStatement;
026: import java.sql.Statement;
027: import java.sql.ResultSet;
028: import java.sql.SQLException;
029:
030: import org.apache.derby.tools.JDBCDisplayUtil;
031: import org.apache.derby.tools.ij;
032: import org.apache.derbyTesting.functionTests.util.Formatters;
033: import org.apache.derbyTesting.functionTests.util.TestUtil;
034:
035: public class maxMemPerTab {
036:
037: public static void main(String[] args) {
038: try {
039: ij.getPropertyArg(args);
040: Connection conn = ij.startJBMS();
041: conn.setAutoCommit(false);
042:
043: createTablesAndInsertData(conn);
044: getStatistics(conn);
045:
046: conn.rollback();
047: conn.close();
048: } catch (SQLException e) {
049: TestUtil.dumpSQLExceptions(e);
050: } catch (Throwable e) {
051: System.out.println("FAIL -- unexpected exception:"
052: + e.toString());
053: }
054: }
055:
056: private static void createTablesAndInsertData(Connection conn)
057: throws SQLException {
058:
059: System.out
060: .println("Start creating tables and inserting data ...");
061:
062: //create tables
063: PreparedStatement ps = conn
064: .prepareStatement("create table tab1 (c1 int, c2 varchar(20000))");
065: ps.execute();
066: ps = conn
067: .prepareStatement("create table tab2 (c1 int, c2 varchar(20000))");
068: ps.execute();
069: ps = conn
070: .prepareStatement("create table tab3 (c1 int, c2 varchar(2000))");
071: ps.execute();
072: ps = conn
073: .prepareStatement("create table tab4 (c1 int, c2 varchar(2000))");
074: ps.execute();
075:
076: //insert data
077: String largeStringA20000 = new String(Formatters.repeatChar(
078: "a", 20000));
079: String largeStringA2000 = new String(Formatters.repeatChar("a",
080: 2000));
081: String largeStringB20000 = new String(Formatters.repeatChar(
082: "b", 20000));
083: String largeStringB2000 = new String(Formatters.repeatChar("b",
084: 2000));
085: String largeStringC20000 = new String(Formatters.repeatChar(
086: "c", 20000));
087: String largeStringC2000 = new String(Formatters.repeatChar("c",
088: 2000));
089: String largeStringD20000 = new String(Formatters.repeatChar(
090: "d", 20000));
091: String largeStringD2000 = new String(Formatters.repeatChar("d",
092: 2000));
093:
094: ps = conn.prepareStatement("insert into tab1 values (?, ?)");
095: ps.setInt(1, 1);
096: ps.setString(2, largeStringA20000);
097: ps.executeUpdate();
098: ps.setInt(1, 2);
099: ps.setString(2, largeStringB20000);
100: ps.executeUpdate();
101: ps.setInt(1, 3);
102: ps.setString(2, largeStringC20000);
103: ps.executeUpdate();
104: ps.close();
105: ps = conn.prepareStatement("insert into tab2 values (?, ?)");
106: ps.setInt(1, 1);
107: ps.setString(2, largeStringA20000);
108: ps.executeUpdate();
109: ps.setInt(1, 2);
110: ps.setString(2, largeStringC20000);
111: ps.executeUpdate();
112: ps.setInt(1, 3);
113: ps.setString(2, largeStringD20000);
114: ps.executeUpdate();
115: ps.close();
116: ps = conn.prepareStatement("insert into tab3 values (?, ?)");
117: ps.setInt(1, 1);
118: ps.setString(2, largeStringA2000);
119: ps.executeUpdate();
120: ps.setInt(1, 2);
121: ps.setString(2, largeStringB2000);
122: ps.executeUpdate();
123: ps.setInt(1, 3);
124: ps.setString(2, largeStringC2000);
125: ps.executeUpdate();
126: ps.close();
127: ps = conn.prepareStatement("insert into tab4 values (?, ?)");
128: ps.setInt(1, 1);
129: ps.setString(2, largeStringA2000);
130: ps.executeUpdate();
131: ps.setInt(1, 2);
132: ps.setString(2, largeStringC2000);
133: ps.executeUpdate();
134: ps.setInt(1, 3);
135: ps.setString(2, largeStringD2000);
136: ps.executeUpdate();
137: ps.close();
138:
139: System.out
140: .println("... done creating tables and inserting data.");
141: }
142:
143: private static void getStatistics(Connection conn)
144: throws SQLException {
145:
146: Statement stmt = conn.createStatement();
147: stmt.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
148: System.out
149: .println("Called SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
150:
151: JDBCDisplayUtil.setMaxDisplayWidth(2500);
152:
153: //should use nested loop join due to maxMemoryPerTable property setting
154: executeQuery(stmt, conn,
155: "select * from tab1, tab2 where tab1.c2 = tab2.c2");
156: executeQuery(stmt, conn,
157: "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
158:
159: //should use hash join, maxMemoryPerTable property value is big enough
160: executeQuery(stmt, conn,
161: "select * from tab3, tab4 where tab3.c2 = tab4.c2");
162: executeQuery(stmt, conn,
163: "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
164:
165: stmt.close();
166: }
167:
168: private static void executeQuery(Statement stmt, Connection conn,
169: String query) throws SQLException {
170: System.out.println("#### Executing \"" + query + "\"");
171: //Display results for select statements
172: ResultSet rs = stmt.executeQuery(query);
173: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
174: rs.close();
175: }
176: }
|