Here is the table function that is being used in case it might be obvious that
I am doing something wrong. Basically there are 53 tables named
NPA_RESULTS_WEEK_1, NPA_RESULTS_WEEK_2, ..., NPA_RESULTS_WEEK_53. There is a
customer defined setting of the number of weeks of data to retain (disk space
is an issue as 2 weeks of data at one customer is about 28G). As data is to
be inserted, the week ordinal number is computed and the correct week table is
inserted into. At a scheduled time (usually Sunday at 2AM), a purge process
is performed by truncating the proper week table which quickly removes all of
the data and reclaims the disk space.
As data is extracted for analysis, a view is used which is based on this table
function. The table function dynamically creates a query of the correct week
tables by determining the current week ordinal and using the customer setting
of the number of weeks and a UNION is created of just these tables and a result
set is returned.
I know I have not take into consideration the table functions column name
restriction in the initScan which I will eventually get to. What would be
useful I think would be to also possibly pass in the ORDER BY restriction and
have the table function to be able to signal that it can return an ordered
result set. This might make it possible to optimize any sorting that might be
required if the returned result set were known to be ordered.
Using the View based on the table function also affords me the ability to place
a shared lock on a table to act as a semaphore. The purge process waits to
exclusively lock this same table before performing it TRUNCATE TABLE. So the
truncate will not happen while there is an open result set looking at the
unioned data and the table function will wait while the truncate is being
performed. Using just a straight View does not allow me to such.
I do have a question, however, and it is "is it more performant to have a View
that is a union of 53 tables or have a View based on a table function that
dynamically creates a query?" I was wondering if there is some internal
knowledge that might sway one way or other?
Aslo it is interesting that modifying the query to not use the View but rather
just use the table function directly, the class loaded count does not
increased. So one might ask, "why not use the table function in the query
instead of the View". Well this is part of a larger Java EE application that
is designed in a component fashion and built and released and installed on the
customers system. The part that has the query was built and released years
ago, so by using the View, I was able to maintain the data structure appearance
as it was at that time which was a single NPA_RESULTS table. So the code
generating the query did not have to be rev'ed, Q/A'ed, scheduled for an
install into a system that is up 24/7, etc. On the next release, it will be
changed to used the table function directly.
Anyways, here is the table function details.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.canoga.derby.fcn;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Map;
import org.apache.derby.vti.Restriction;
import org.apache.derby.vti.RestrictedVTI;
import org.apache.derby.vti.VTIEnvironment;
/**
*
* @author root
*/
public class NpaResultsTableFunction implements ResultSet, /*VTICosting,*/
RestrictedVTI {
/**
* The internal connection that we will use
*/
private Connection conn;
/**
* The template of the statement that will be created
*/
private static String template = "SELECT " +
"ID, " +
"REPORTKEY, " +
"MASTERIP, " +
"BOOTCOUNT, " +
"TESTRESULTID, " +
"PROFILEREFID, " +
"ADDRESSREFID, " +
"STARTDATETIME, " +
"ACCURACYLEVEL, " +
"RESULTFLAG, " +
"PACKETSSENT, " +
"ROUNDTRIPPACKETS, " +
"DROPPEDPACKETS, " +
"OUTOFORDERPACKETS, " +
"MINROUNDTRIPLATENCY, " +
"MAXROUNDTRIPLATENCY, " +
"TOTALROUNDTRIPLATENCY, " +
"AVGROUNDTRIPLATENCY, " +
"LATENCYBUCKETVALUE1, " +
"LATENCYBUCKETVALUE2, " +
"LATENCYBUCKETVALUE3, " +
"LATENCYBUCKETVALUE4, " +
"LATENCYBUCKETVALUE5, " +
"LATENCYBUCKETVALUE6, " +
"LATENCYBUCKETVALUE7, " +
"LATENCYBUCKETVALUE8, " +
"LATENCYBUCKETVALUE9, " +
"LATENCYBUCKETVALUE10, " +
"JITTERMEASUREMENT, " +
"MINLOCALREMOTEJITTER, " +
"MAXLOCALREMOTEJITTER, " +
"TOTALLOCALREMOTEJITTER, " +
"AVGLOCALREMOTEJITTER, " +
"LOCALREMOTEJITTERBUCKETVALUE1, " +
"LOCALREMOTEJITTERBUCKETVALUE2, " +
"LOCALREMOTEJITTERBUCKETVALUE3, " +
"LOCALREMOTEJITTERBUCKETVALUE4, " +
"LOCALREMOTEJITTERBUCKETVALUE5, " +
"LOCALREMOTEJITTERBUCKETVALUE6, " +
"LOCALREMOTEJITTERBUCKETVALUE7, " +
"LOCALREMOTEJITTERBUCKETVALUE8, " +
"LOCALREMOTEJITTERBUCKETVALUE9, " +
"MINREMOTELOCALJITTER, " +
"MAXREMOTELOCALJITTER, " +
"TOTALREMOTELOCALJITTER, " +
"AVGREMOTELOCALJITTER, " +
"REMOTELOCALJITTERBUCKETVALUE1, " +
"REMOTELOCALJITTERBUCKETVALUE2, " +
"REMOTELOCALJITTERBUCKETVALUE3, " +
"REMOTELOCALJITTERBUCKETVALUE4, " +
"REMOTELOCALJITTERBUCKETVALUE5, " +
"REMOTELOCALJITTERBUCKETVALUE6, " +
"REMOTELOCALJITTERBUCKETVALUE7, " +
"REMOTELOCALJITTERBUCKETVALUE8, " +
"REMOTELOCALJITTERBUCKETVALUE9, " +
"CIRCUIT1REFID, " +
"CIRCUIT2REFID, " +
"UNAVAILABLEEXCLUDED " +
"FROM PCS_V1.NPARESULTS_WEEK_";
/**
* The statement that will be executed
*/
private Statement stmt = null;
/**
* The underlying result set
*/
private ResultSet resultSet;
/**
* The constraint clause
*/
private String whereConstraint;
/**
* Invoked by the databae engine to read the table
* @return An instance of this class used to read the table
* @throws java.sql.SQLException
*/
public static NpaResultsTableFunction instance() throws SQLException {
return new NpaResultsTableFunction();
}
/**
* Creates a new instance of this class.
* @throws java.sql.SQLException
*/
public NpaResultsTableFunction() throws SQLException {
conn = DriverManager.getConnection("jdbc:default:connection");
stmt = conn.createStatement();
}
private ResultSet getResultSet() throws SQLException {
if (!isClosed()) {
if (null == this.resultSet) {
pcsPmLock();
this.resultSet = stmt.executeQuery(prepareStatement());
}
return this.resultSet;
} else {
throw new SQLException("Already closed");
}
}
private void pcsPmLock() throws SQLException {
stmt.execute("LOCK TABLE PCS_V1.PCS_PM_LOCK IN SHARE MODE");
}
private String prepareStatement() throws SQLException {
int keepWeeks = getKeepWeeks();
int weekOfYear = getThisWeek();
StringBuilder sb = new StringBuilder();
String unionAll = "";
while (keepWeeks > 0) {
sb.append(unionAll);
sb.append(template);
sb.append(weekOfYear);
if (null != whereConstraint) {
sb.append(" WHERE ");
sb.append(whereConstraint);
}
keepWeeks -= 1;
if (--weekOfYear == 0) {
weekOfYear = 53;
}
unionAll = " UNION ALL ";
}
String s = sb.toString();
return s;
}
private int getKeepWeeks() throws SQLException {
int keepWeeks = 1;
ResultSet weeks = stmt.executeQuery("SELECT PM_PURGE_KEEP_WEEKS FROM
PCS_V1.PCS_PROPERTIES");
if (weeks.next()) {
keepWeeks = weeks.getInt(1);
}
// See if we need to keep one more week. This will be the case for
example
// where we are keeping 4 weeks: the current week and the previous 4
weeks
// and if the current week is the first week of the year and the
previous
// year had 52 weeks, then we need to keep weeks 1, 53, 52, 51, and 50
// since week 53 of the previous year never had any data
if (52 == getLastWeekOfYear()) {
keepWeeks += 1;
}
return keepWeeks;
}
private int getThisWeek() {
java.util.Date date = new java.util.Date();
Calendar cal = GregorianCalendar.getInstance();
cal.setMinimalDaysInFirstWeek(7);
cal.setTime(date);
int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);
return weekOfYear;
}
private int getLastWeekOfYear() {
java.util.Date date = new java.util.Date();
Calendar cal = GregorianCalendar.getInstance();
cal.setMinimalDaysInFirstWeek(7);
cal.setTime(date);
cal.roll(Calendar.YEAR, -1);
cal.set(Calendar.MONTH, Calendar.DECEMBER);
cal.set(Calendar.DAY_OF_MONTH, 31);
int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);
return weekOfYear;
}
public void initScan(String[] columNames, Restriction restriction) throws
SQLException {
if (null != restriction) {
String s = restriction.toSQL();
if (null != s && 0 != s.length()) {
whereConstraint = s;
}
}
}
public double getEstimatedRowCount(VTIEnvironment arg0) throws SQLException
{
return 1000.0;
}
public double getEstimatedCostPerInstantiation(VTIEnvironment arg0) throws
SQLException {
return 1.0;
}
public boolean supportsMultipleInstantiations(VTIEnvironment arg0) throws
SQLException {
return false;
}
@Override
public void close() throws SQLException {
if (!isClosed()) {
if (null != this.resultSet) {
this.resultSet.close();
this.resultSet = null;
}
if (null != this.stmt) {
this.stmt.close();
this.stmt = null;
}
this.conn = null;
}
}
@Override
public ResultSetMetaData getMetaData() throws SQLException {
return getResultSet().getMetaData();
}
@Override
public boolean next() throws SQLException {
return getResultSet().next();
}
public RowId getRowId(int columnIndex) throws SQLException {
return getResultSet().getRowId(columnIndex);
}
public RowId getRowId(String columnLabel) throws SQLException {
return getResultSet().getRowId(columnLabel);
}
public void updateRowId(int columnIndex, RowId x) throws SQLException {
getResultSet().updateRowId(columnIndex, x);
}
public void updateRowId(String columnLabel, RowId x) throws SQLException {
getResultSet().updateRowId(columnLabel, x);
}
public int getHoldability() throws SQLException {
return getResultSet().getHoldability();
}
public boolean isClosed() throws SQLException {
return null == this.conn ? true : false;
}
public void updateNString(int columnIndex, String nString) throws
SQLException {
getResultSet().updateNString(columnIndex, nString);
}
public void updateNString(String columnLabel, String nString) throws
SQLException {
getResultSet().updateNString(columnLabel, nString);
}
public void updateNClob(int columnIndex, NClob nClob) throws SQLException {
getResultSet().updateNClob(columnIndex, nClob);
}
public void updateNClob(String columnLabel, NClob nClob) throws
SQLException {
getResultSet().updateNClob(columnLabel, nClob);
}
public NClob getNClob(int columnIndex) throws SQLException {
return getResultSet().getNClob(columnIndex);
}
public NClob getNClob(String columnLabel) throws SQLException {
return getResultSet().getNClob(columnLabel);
}
public SQLXML getSQLXML(int columnIndex) throws SQLException {
return getResultSet().getSQLXML(columnIndex);
}
public SQLXML getSQLXML(String columnLabel) throws SQLException {
return getResultSet().getSQLXML(columnLabel);
}
public void updateSQLXML(int columnIndex, SQLXML xmlObject) throws
SQLException {
getResultSet().updateSQLXML(columnIndex, xmlObject);
}
public void updateSQLXML(String columnLabel, SQLXML xmlObject) throws
SQLException {
getResultSet().updateSQLXML(columnLabel, xmlObject);
}
public String getNString(int columnIndex) throws SQLException {
return getResultSet().getNString(columnIndex);
}
public String getNString(String columnLabel) throws SQLException {
return getResultSet().getNString(columnLabel);
}
public Reader getNCharacterStream(int columnIndex) throws SQLException {
return getResultSet().getNCharacterStream(columnIndex);
}
public Reader getNCharacterStream(String columnLabel) throws SQLException {
return getResultSet().getNCharacterStream(columnLabel);
}
public void updateNCharacterStream(int columnIndex, Reader x, long length)
throws SQLException {
getResultSet().updateNCharacterStream(template, x, length);
}
public void updateNCharacterStream(String columnLabel, Reader reader, long
length) throws SQLException {
getResultSet().updateNCharacterStream(columnLabel, reader, length);
}
public void updateAsciiStream(int columnIndex, InputStream x, long length)
throws SQLException {
getResultSet().updateAsciiStream(columnIndex, x, length);
}
public void updateBinaryStream(int columnIndex, InputStream x, long length)
throws SQLException {
getResultSet().updateBinaryStream(columnIndex, x, length);
}
public void updateCharacterStream(int columnIndex, Reader x, long length)
throws SQLException {
getResultSet().updateCharacterStream(columnIndex, x, length);
}
public void updateAsciiStream(String columnLabel, InputStream x, long
length) throws SQLException {
getResultSet().updateAsciiStream(columnLabel, x, length);
}
public void updateBinaryStream(String columnLabel, InputStream x, long
length) throws SQLException {
getResultSet().updateBinaryStream(columnLabel, x, length);
}
public void updateCharacterStream(String columnLabel, Reader reader, long
length) throws SQLException {
getResultSet().updateCharacterStream(columnLabel, reader, length);
}
public void updateBlob(int columnIndex, InputStream inputStream, long
length) throws SQLException {
getResultSet().updateBlob(columnIndex, inputStream, length);
}
public void updateBlob(String columnLabel, InputStream inputStream, long
length) throws SQLException {
getResultSet().updateBlob(columnLabel, inputStream, length);
}
public void updateClob(int columnIndex, Reader reader, long length) throws
SQLException {
getResultSet().updateClob(columnIndex, reader, length);
}
public void updateClob(String columnLabel, Reader reader, long length)
throws SQLException {
getResultSet().updateClob(columnLabel, reader, length);
}
public void updateNClob(int columnIndex, Reader reader, long length) throws
SQLException {
getResultSet().updateNClob(columnIndex, reader, length);
}
public void updateNClob(String columnLabel, Reader reader, long length)
throws SQLException {
getResultSet().updateNClob(columnLabel, reader, length);
}
public void updateNCharacterStream(int columnIndex, Reader x) throws
SQLException {
getResultSet().updateNCharacterStream(columnIndex, x, columnIndex);
}
public void updateNCharacterStream(String columnLabel, Reader reader)
throws SQLException {
getResultSet().updateNCharacterStream(columnLabel, reader,
FETCH_FORWARD);
}
public void updateAsciiStream(int columnIndex, InputStream x) throws
SQLException {
getResultSet().updateAsciiStream(columnIndex, x);
}
public void updateBinaryStream(int columnIndex, InputStream x) throws
SQLException {
getResultSet().updateBinaryStream(columnIndex, x);
}
public void updateCharacterStream(int columnIndex, Reader x) throws
SQLException {
getResultSet().updateCharacterStream(columnIndex, x);
}
public void updateAsciiStream(String columnLabel, InputStream x) throws
SQLException {
getResultSet().updateAsciiStream(columnLabel, x);
}
public void updateBinaryStream(String columnLabel, InputStream x) throws
SQLException {
getResultSet().updateBinaryStream(columnLabel, x);
}
public void updateCharacterStream(String columnLabel, Reader reader) throws
SQLException {
getResultSet().updateCharacterStream(columnLabel, reader);
}
public void updateBlob(int columnIndex, InputStream inputStream) throws
SQLException {
getResultSet().updateBlob(columnIndex, inputStream);
}
public void updateBlob(String columnLabel, InputStream inputStream) throws
SQLException {
getResultSet().updateBlob(columnLabel, inputStream);
}
public void updateClob(int columnIndex, Reader reader) throws SQLException {
getResultSet().updateClob(columnIndex, reader);
}
public void updateClob(String columnLabel, Reader reader) throws
SQLException {
getResultSet().updateClob(columnLabel, reader);
}
public void updateNClob(int columnIndex, Reader reader) throws SQLException
{
getResultSet().updateNClob(columnIndex, reader);
}
public void updateNClob(String columnLabel, Reader reader) throws
SQLException {
getResultSet().updateNClob(columnLabel, reader);
}
public <T> T unwrap(Class<T> iface) throws SQLException {
return getResultSet().unwrap(iface);
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return getResultSet().isWrapperFor(iface);
}
public boolean wasNull() throws SQLException {
return getResultSet().wasNull();
}
public String getString(int columnIndex) throws SQLException {
return getResultSet().getString(columnIndex);
}
public boolean getBoolean(int columnIndex) throws SQLException {
return getResultSet().getBoolean(columnIndex);
}
public byte getByte(int columnIndex) throws SQLException {
return getResultSet().getByte(columnIndex);
}
public short getShort(int columnIndex) throws SQLException {
return getResultSet().getShort(columnIndex);
}
public int getInt(int columnIndex) throws SQLException {
return getResultSet().getInt(columnIndex);
}
public long getLong(int columnIndex) throws SQLException {
return getResultSet().getLong(columnIndex);
}
public float getFloat(int columnIndex) throws SQLException {
return getResultSet().getFloat(columnIndex);
}
public double getDouble(int columnIndex) throws SQLException {
return getResultSet().getDouble(columnIndex);
}
public BigDecimal getBigDecimal(int columnIndex, int scale) throws
SQLException {
return getResultSet().getBigDecimal(columnIndex);
}
public byte[] getBytes(int columnIndex) throws SQLException {
return getResultSet().getBytes(columnIndex);
}
public Date getDate(int columnIndex) throws SQLException {
return getResultSet().getDate(columnIndex);
}
public Time getTime(int columnIndex) throws SQLException {
return getResultSet().getTime(columnIndex);
}
public Timestamp getTimestamp(int columnIndex) throws SQLException {
return getResultSet().getTimestamp(columnIndex);
}
public InputStream getAsciiStream(int columnIndex) throws SQLException {
return getResultSet().getAsciiStream(columnIndex);
}
public InputStream getUnicodeStream(int columnIndex) throws SQLException {
return getResultSet().getUnicodeStream(columnIndex);
}
public InputStream getBinaryStream(int columnIndex) throws SQLException {
return getResultSet().getBinaryStream(columnIndex);
}
public String getString(String columnLabel) throws SQLException {
return getResultSet().getString(columnLabel);
}
public boolean getBoolean(String columnLabel) throws SQLException {
return getResultSet().getBoolean(columnLabel);
}
public byte getByte(String columnLabel) throws SQLException {
return getResultSet().getByte(columnLabel);
}
public short getShort(String columnLabel) throws SQLException {
return getResultSet().getShort(columnLabel);
}
public int getInt(String columnLabel) throws SQLException {
return getResultSet().getInt(columnLabel);
}
public long getLong(String columnLabel) throws SQLException {
return getResultSet().getLong(columnLabel);
}
public float getFloat(String columnLabel) throws SQLException {
return getResultSet().getFloat(columnLabel);
}
public double getDouble(String columnLabel) throws SQLException {
return getResultSet().getDouble(columnLabel);
}
public BigDecimal getBigDecimal(String columnLabel, int scale) throws
SQLException {
return getResultSet().getBigDecimal(columnLabel);
}
public byte[] getBytes(String columnLabel) throws SQLException {
return getResultSet().getBytes(columnLabel);
}
public Date getDate(String columnLabel) throws SQLException {
return getResultSet().getDate(columnLabel);
}
public Time getTime(String columnLabel) throws SQLException {
return getResultSet().getTime(columnLabel);
}
public Timestamp getTimestamp(String columnLabel) throws SQLException {
return getResultSet().getTimestamp(columnLabel);
}
public InputStream getAsciiStream(String columnLabel) throws SQLException {
return getResultSet().getAsciiStream(columnLabel);
}
public InputStream getUnicodeStream(String columnLabel) throws SQLException
{
return getResultSet().getUnicodeStream(columnLabel);
}
public InputStream getBinaryStream(String columnLabel) throws SQLException {
return getResultSet().getBinaryStream(columnLabel);
}
public SQLWarning getWarnings() throws SQLException {
return getResultSet().getWarnings();
}
public void clearWarnings() throws SQLException {
getResultSet().clearWarnings();
}
public String getCursorName() throws SQLException {
return getResultSet().getCursorName();
}
public Object getObject(int columnIndex) throws SQLException {
return getResultSet().getObject(columnIndex);
}
public Object getObject(String columnLabel) throws SQLException {
return getResultSet().getObject(columnLabel);
}
public int findColumn(String columnLabel) throws SQLException {
return getResultSet().findColumn(columnLabel);
}
public Reader getCharacterStream(int columnIndex) throws SQLException {
;
return getResultSet().getCharacterStream(columnIndex);
}
public Reader getCharacterStream(String columnLabel) throws SQLException {
return getResultSet().getCharacterStream(columnLabel);
}
public BigDecimal getBigDecimal(int columnIndex) throws SQLException {
return getResultSet().getBigDecimal(columnIndex);
}
public BigDecimal getBigDecimal(String columnLabel) throws SQLException {
return getResultSet().getBigDecimal(columnLabel);
}
public boolean isBeforeFirst() throws SQLException {
return getResultSet().isBeforeFirst();
}
public boolean isAfterLast() throws SQLException {
return getResultSet().isAfterLast();
}
public boolean isFirst() throws SQLException {
return getResultSet().isFirst();
}
public boolean isLast() throws SQLException {
return getResultSet().isLast();
}
public void beforeFirst() throws SQLException {
getResultSet().beforeFirst();
}
public void afterLast() throws SQLException {
getResultSet().afterLast();
}
public boolean first() throws SQLException {
return getResultSet().first();
}
public boolean last() throws SQLException {
return getResultSet().last();
}
public int getRow() throws SQLException {
return getResultSet().getRow();
}
public boolean absolute(int row) throws SQLException {
return getResultSet().absolute(row);
}
public boolean relative(int rows) throws SQLException {
return getResultSet().relative(rows);
}
public boolean previous() throws SQLException {
return getResultSet().previous();
}
public void setFetchDirection(int direction) throws SQLException {
getResultSet().setFetchDirection(direction);
}
public int getFetchDirection() throws SQLException {
return getResultSet().getFetchDirection();
}
public void setFetchSize(int rows) throws SQLException {
getResultSet().setFetchSize(rows);
}
public int getFetchSize() throws SQLException {
return getResultSet().getFetchSize();
}
public int getType() throws SQLException {
return getResultSet().getType();
}
public int getConcurrency() throws SQLException {
return getResultSet().getConcurrency();
}
public boolean rowUpdated() throws SQLException {
return getResultSet().rowUpdated();
}
public boolean rowInserted() throws SQLException {
return getResultSet().rowInserted();
}
public boolean rowDeleted() throws SQLException {
return getResultSet().rowDeleted();
}
public void updateNull(int columnIndex) throws SQLException {
getResultSet().updateNull(columnIndex);
}
public void updateBoolean(int columnIndex, boolean x) throws SQLException {
getResultSet().updateBoolean(columnIndex, x);
}
public void updateByte(int columnIndex, byte x) throws SQLException {
getResultSet().updateByte(columnIndex, x);
}
public void updateShort(int columnIndex, short x) throws SQLException {
getResultSet().updateShort(columnIndex, x);
}
public void updateInt(int columnIndex, int x) throws SQLException {
getResultSet().updateInt(columnIndex, x);
}
public void updateLong(int columnIndex, long x) throws SQLException {
getResultSet().updateLong(columnIndex, x);
}
public void updateFloat(int columnIndex, float x) throws SQLException {
getResultSet().updateFloat(columnIndex, x);
}
public void updateDouble(int columnIndex, double x) throws SQLException {
getResultSet().updateDouble(columnIndex, x);
}
public void updateBigDecimal(int columnIndex, BigDecimal x) throws
SQLException {
getResultSet().updateBigDecimal(columnIndex, x);
}
public void updateString(int columnIndex, String x) throws SQLException {
getResultSet().updateString(columnIndex, x);
}
public void updateBytes(int columnIndex, byte[] x) throws SQLException {
getResultSet().updateBytes(columnIndex, x);
}
public void updateDate(int columnIndex, Date x) throws SQLException {
getResultSet().updateDate(columnIndex, x);
}
public void updateTime(int columnIndex, Time x) throws SQLException {
getResultSet().updateTime(columnIndex, x);
}
public void updateTimestamp(int columnIndex, Timestamp x) throws
SQLException {
getResultSet().updateTimestamp(columnIndex, x);
}
public void updateAsciiStream(int columnIndex, InputStream x, int length)
throws SQLException {
getResultSet().updateAsciiStream(columnIndex, x);
}
public void updateBinaryStream(int columnIndex, InputStream x, int length)
throws SQLException {
getResultSet().updateBinaryStream(columnIndex, x);
}
public void updateCharacterStream(int columnIndex, Reader x, int length)
throws SQLException {
getResultSet().updateCharacterStream(columnIndex, x);
}
public void updateObject(int columnIndex, Object x, int scaleOrLength)
throws SQLException {
getResultSet().updateObject(columnIndex, x);
}
public void updateObject(int columnIndex, Object x) throws SQLException {
getResultSet().updateObject(columnIndex, x);
}
public void updateNull(String columnLabel) throws SQLException {
getResultSet().updateNull(columnLabel);
}
public void updateBoolean(String columnLabel, boolean x) throws
SQLException {
getResultSet().updateBoolean(columnLabel, x);
}
public void updateByte(String columnLabel, byte x) throws SQLException {
getResultSet().updateByte(columnLabel, x);
}
public void updateShort(String columnLabel, short x) throws SQLException {
getResultSet().updateShort(columnLabel, x);
}
public void updateInt(String columnLabel, int x) throws SQLException {
getResultSet().updateInt(columnLabel, x);
}
public void updateLong(String columnLabel, long x) throws SQLException {
getResultSet().updateLong(columnLabel, x);
}
public void updateFloat(String columnLabel, float x) throws SQLException {
getResultSet().updateFloat(columnLabel, x);
}
public void updateDouble(String columnLabel, double x) throws SQLException {
getResultSet().updateDouble(columnLabel, x);
}
public void updateBigDecimal(String columnLabel, BigDecimal x) throws
SQLException {
getResultSet().updateBigDecimal(columnLabel, x);
}
public void updateString(String columnLabel, String x) throws SQLException {
getResultSet().updateString(columnLabel, x);
}
public void updateBytes(String columnLabel, byte[] x) throws SQLException {
getResultSet().updateBytes(columnLabel, x);
}
public void updateDate(String columnLabel, Date x) throws SQLException {
getResultSet().updateDate(columnLabel, x);
}
public void updateTime(String columnLabel, Time x) throws SQLException {
getResultSet().updateTime(columnLabel, x);
}
public void updateTimestamp(String columnLabel, Timestamp x) throws
SQLException {
getResultSet().updateTimestamp(columnLabel, x);
}
public void updateAsciiStream(String columnLabel, InputStream x, int
length) throws SQLException {
getResultSet().updateAsciiStream(columnLabel, x);
}
public void updateBinaryStream(String columnLabel, InputStream x, int
length) throws SQLException {
getResultSet().updateBinaryStream(columnLabel, x);
}
public void updateCharacterStream(String columnLabel, Reader reader, int
length) throws SQLException {
getResultSet().updateCharacterStream(columnLabel, reader, length);
}
public void updateObject(String columnLabel, Object x, int scaleOrLength)
throws SQLException {
getResultSet().updateObject(columnLabel, x, scaleOrLength);
}
public void updateObject(String columnLabel, Object x) throws SQLException {
getResultSet().updateObject(columnLabel, x);
}
public void insertRow() throws SQLException {
getResultSet().insertRow();
}
public void updateRow() throws SQLException {
getResultSet().updateRow();
}
public void deleteRow() throws SQLException {
getResultSet().deleteRow();
}
public void refreshRow() throws SQLException {
getResultSet().refreshRow();
}
public void cancelRowUpdates() throws SQLException {
getResultSet().cancelRowUpdates();
}
public void moveToInsertRow() throws SQLException {
getResultSet().moveToInsertRow();
}
public void moveToCurrentRow() throws SQLException {
getResultSet().moveToCurrentRow();
}
public Statement getStatement() throws SQLException {
return getResultSet().getStatement();
}
public Object getObject(int columnIndex, Map<String, Class<?>> map) throws
SQLException {
return getResultSet().getObject(columnIndex, map);
}
public Ref getRef(int columnIndex) throws SQLException {
return getResultSet().getRef(columnIndex);
}
public Blob getBlob(int columnIndex) throws SQLException {
return getResultSet().getBlob(columnIndex);
}
public Clob getClob(int columnIndex) throws SQLException {
return getResultSet().getClob(columnIndex);
}
public Array getArray(int columnIndex) throws SQLException {
return getResultSet().getArray(columnIndex);
}
public Object getObject(String columnLabel, Map<String, Class<?>> map)
throws SQLException {
return getResultSet().getObject(columnLabel, map);
}
public Ref getRef(String columnLabel) throws SQLException {
return getResultSet().getRef(columnLabel);
}
public Blob getBlob(String columnLabel) throws SQLException {
return getResultSet().getBlob(columnLabel);
}
public Clob getClob(String columnLabel) throws SQLException {
return getResultSet().getClob(columnLabel);
}
public Array getArray(String columnLabel) throws SQLException {
return getResultSet().getArray(columnLabel);
}
public Date getDate(int columnIndex, Calendar cal) throws SQLException {
return getResultSet().getDate(columnIndex, cal);
}
public Date getDate(String columnLabel, Calendar cal) throws SQLException {
return getResultSet().getDate(columnLabel, cal);
}
public Time getTime(int columnIndex, Calendar cal) throws SQLException {
return getResultSet().getTime(columnIndex, cal);
}
public Time getTime(String columnLabel, Calendar cal) throws SQLException {
return getResultSet().getTime(columnLabel, cal);
}
public Timestamp getTimestamp(int columnIndex, Calendar cal) throws
SQLException {
return getResultSet().getTimestamp(columnIndex, cal);
}
public Timestamp getTimestamp(String columnLabel, Calendar cal) throws
SQLException {
return getResultSet().getTimestamp(columnLabel, cal);
}
public URL getURL(int columnIndex) throws SQLException {
return getResultSet().getURL(columnIndex);
}
public URL getURL(String columnLabel) throws SQLException {
return getResultSet().getURL(columnLabel);
}
public void updateRef(int columnIndex, Ref x) throws SQLException {
getResultSet().updateRef(columnIndex, x);
}
public void updateRef(String columnLabel, Ref x) throws SQLException {
getResultSet().updateRef(columnLabel, x);
}
public void updateBlob(int columnIndex, Blob x) throws SQLException {
getResultSet().updateBlob(columnIndex, x);
}
public void updateBlob(String columnLabel, Blob x) throws SQLException {
getResultSet().updateBlob(columnLabel, x);
}
public void updateClob(int columnIndex, Clob x) throws SQLException {
getResultSet().updateClob(columnIndex, x);
}
public void updateClob(String columnLabel, Clob x) throws SQLException {
getResultSet().updateClob(columnLabel, x);
}
public void updateArray(int columnIndex, Array x) throws SQLException {
getResultSet().updateArray(columnIndex, x);
}
public void updateArray(String columnLabel, Array x) throws SQLException {
getResultSet().updateArray(columnLabel, x);
}
}
________________________________________
From: Knut Anders Hatlen [[email protected]]
Sent: Thursday, November 22, 2012 4:57 AM
To: [email protected]
Subject: Re: Have Derby Network Server having an out of memory (PermGen)
Mike Matrigali <[email protected]> writes:
> On 11/21/2012 6:58 AM, Knut Anders Hatlen wrote:
>> "Bergquist, Brett" <[email protected]> writes:
>>
>>> Yes, the statement cache size has been increased to 50K statements so
>>> that might be an issue. Maybe the PermGen space will need to be
>>> increased because of that. The documentation is not clear which type
> I am not an expert in this area, is there any case where we expect the
> re-execution of the same query to need to generate a different entry
> in the statement cache?
I think what's flooding the statement cache here is whatever gets
executed by the table function, which I understand is some dynamically
generated SQL statements.
This is also why I don't understand how changing from a view to a direct
table function call should change anything, as the top-level statement
should only have one entry in the cache, and the statements executed
inside the table function should be the same.
Two possible explanations:
1) Changing between view and direct call changes the plan picked by the
optimizer, so that the table function call one time ends up as the inner
table in a join, and another time as the outer table. This could change
the number of times the table function is called per query. If each call
to the table function generates truly unique SQL statements, calling it
more often will fill the cache quicker.
2) If it is a restricted table function, the actual
restriction/projection pushed down to the table function may vary
depending on which plan the optimizer picks. And this could affect what
kind of SQL is generated by the table function. Perhaps sometimes it
generates statements that are likely to be identical across invocations,
needing fewer entries in the cache, and other times it generates
statements that are less likely to be identical.
Following up on that last thought, if the queries generated by the table
function would be something like
select * from t where x < N
where N varies between invocations, it's better for the statement cache
if a parameter marker is used, like
select * from t where x < ?
rather than inlining the actual constant
select * from t where x < 5
select * from t where x < 42
...
Even though the table function itself doesn't execute the query more
than once, using parameter markers increases the likelihood of finding a
match in the statement cache.
Not sure if this affects Brett's table function. Just throwing out
ideas...
--
Knut Anders
