[top]
[prev]
[next]

Documentation Top
Global Index
Reference Manual
TOC Index
Grammar Index
Developer’s Guide
TOC Index
Tuning Cloudscape
TOC Index
|
Programming VTIs
Cloudscape provides a construct for presenting external data--from a flat file, from another vendor's database, from a news feed--as a "virtual" table. Access to external data presented in this way constitutes an external virtual table that can be used within the scope of an SQL-J statement in the same way as other virtual or derived tables, and in some cases, in the same way as other actual tables. Within the context of an SQL-J statement, instances of these VTIs are called external virtual tables (see ExternalVirtualTable of the Cloudscape Reference Manual).
Cloudscape supports two types of VTI classes:
NEW: Read-write VTIs are new in Version 3.6. Cloudscape also continues to support its original, read-only version of VTIs. Unless you have already started working from read-only VTIs or are developing VTIs that will always be read-only, it is a good idea work with use read-write VTIs, because they provide a superset of the read-only VTI functionality.
SQL-J and VTI Classes
Like most other Java classes, it is possible to instantiate a VTI class within an SQL-J statement or to make a static reference to it. For example:
VALUES NEW COM.cloudscape.vti.ExternalQuery( 'jdbc:cloudscape:;current=true', 'SELECT * FROM SYS.SYSTABLES')
CALL (CLASS myVTIClass).staticMethod()
However, even though it is possible to do this, doing so won't make the instances of these classes ExternalVirtualTables; you won't be selecting from them or inserting into them. These classes are designed to be used in INSERT, SELECT, and DELETE statements, as documented in the Cloudscape Reference Manual. For example:
SELECT cn FROM NEW ExternalQuery( 'jdbc:cloudscape:toursDB', 'SELECT CONGLOMERATENUMBER FROM SYS.SYSCONGLOMERATES') AS EQ (cn)
CREATE CLASS ALIAS FOR JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable
INSERT INTO NEW JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable( 'jdbc:cloudscape:history', 'hotelbookings') VALUES (1000, 500, current_date, current_date, 10, 10.00)
|
Read-Write VTIs
A read-write external virtual table is created by an instance of any class that fulfills the requirements of Cloudscape's read-write virtual table interface. It is valid only within SELECT, INSERT, and DELETE statements.
This section discusses how to create classes of that type.
Requirements and Options for Read-Write VTI Classes
To qualify as a read-write VTI class, a Java class must fulfill these requirements:
- It must implement the java.sql.PreparedStatement interface.
Even if you are working in a JDBC 1.2 environment (JDK 1.1), you must also implement two methods on PreparedStatement that are new in JDBC 2.0:
- Since PreparedStatements return a ResultSet in the executeQuery method, you sometimes may also have to write a class that implements java.sql.ResultSet.
The ResultSet must be updatable.
See also Implementing ResultSet.
An exception is thrown if a problem is found with any of the above.
- The parameters to the VTI must follow certain rules, and your VTI class must be able to handle default values for parameters.
See Rules for Parameters to VTIs.
Optionally, the class can provide costing information to the Cloudscape optimizer or prevent Cloudscape from instantiating it more than once. See Providing Costing Information.
NOTE: There is no transaction support for inserts into and deletes from external virtual tables.
Notes on Implementing java.sql.PreparedStatement
- Releasing resources
Prepared statements are compiled once and executed many times. For this reason, Cloudscape will close a read-write VTI class only if it decides that the class must be instantiated anew for each execution. Thus, the read-write VTI class should clean up its resources as soon as possible and avoid waiting until its close() or finalizer methods are called to release the resources. In fact, Cloudscape calls close only for select statements and not for inserts or deletes.
- Committing transactions
If the underlying datastore has transactional capability, it is a good idea to commit the underlying transaction in the close() method of the ResultSet returned by executeQuery to avoid committing each row change independently.
NOTE: For information on examples provided in the tours sample application, see Example VTIs.
What Happens Behind the Scenes During Compilation and Execution of a Read-Write VTI
INSERT Statements
When Cloudscape compiles an INSERT statement that includes a read-write VTI, here's what happens:
- Cloudscape verifies that the VTI class meets the requirements as listed in Requirements and Options for Read-Write VTI Classes and throws an exception if the requirements are not met.
- It creates an instance of the class, then calls getResultSetConcurrency() and getMetaData() on this instance.
When Cloudscape executes the statement, here's what happens:
- It does one of the following:
- If the statement is not a stored prepared statement, Cloudscape uses the instance of the VTI created during compilation; it does not instantiate a new object unless one or more of the parameters to the VTI is or contains a ? (dynamic parameter). If that is the case, it does instantiate a new object upon each execution of the statement.
- If the statement is a stored prepared statement, Cloudscape will have tried to serialize the object when the stored prepared statement was created. (If the class was serializable, Cloudscape would have been successful. For information about serializable classes, see Programming Serializable Classes.) Then at statement execution Cloudscape de-serializes the VTI instance unless one or more of the parameters to the VTI is or contains a ? (dynamic parameter). If that is the case, it does instantiate a new object upon each execution of the statement.
- Cloudscape calls executeQuery() on the class to get the ResultSet returned by the class.
- For each row being inserted, Cloudscape calls moveToInsertRow() on the ResultSet.
- Cloudscape calls updateObject() for each column being inserted into. Then it calls insertRow() to insert the row.
- When the statement is done executing or if an error occurs, Cloudscape calls close() on the ResultSet.
NOTE: It does not call close() on the PreparedStatement class.
DELETE Statements
When Cloudscape compiles a DELETE statement that includes a read-write VTI, here's what happens:
- Cloudscape verifies that the VTI class meets the requirements as listed in Requirements and Options for Read-Write VTI Classes and throws an exception if the requirements are not met.
- It creates an instances of the class, then calls getResultSetConcurrency and getMetaData on the instance.
- It tries to serialize this instance. (If the class is serializable, Cloudscape is successful. For information about serializable classes, see Programming Serializable Classes.)
When Cloudscape executes the statement, here's what happens:
- If Cloudscape serialized the instance of the class during compilation, it tries to de-serialize the instance of the class unless one or more of the parameters to the VTI is or contains a ? (dynamic parameter). If that is the case or if the class was not serializable, it instantiates a new object upon each execution of the statement.
- Cloudscape calls executeQuery() on the class to get the ResultSet returned by the class.
- Cloudscape iterates through the rows in the ResultSet applying any qualification, and for each deleted row, Cloudscape calls deleteRow() on the ResultSet.
- Cloudscape calls close() on the ResultSet at the end of execution or if an error occurs.
NOTE: It does not call close() on the PreparedStatement class.
SELECT Statements
When Cloudscape compiles a SELECT statement that includes a read-write VTI, here's what happens:
- Cloudscape checks to see if the VTI class is a read-write VTI or a read-only VTI. The class is a read-write VTI if the class implements java.sql.PreparedStatement and not java.sql.ResultSet. Cloudscape then checks to see if the class meets all the requirements as listed in Requirements and Options for Read-Write VTI Classes and throws an exception if the requirements are not met.
- It creates an instance of the class, then calls getResultSetConcurrency and getMetaData on the instance.
- It tries to serialize the instance of the class . (If the class is serializable, Cloudscape is successful. For information about serializable classes, see Programming Serializable Classes.)
- It calls close() on the instance of the class.
When Cloudscape executes the statement, here's what happens:
- If Cloudscape was able to serialize the instance of the class at compilation, at statement execution Cloudscape de-serializes the VTI instance unless:
- Cloudscape calls executeQuery() on the class to get the ResultSet returned by the class.
- At the end of execution or if an error occurs, Cloudscape calls close() on the ResultSet.
Read-Only VTIs
A read only external virtual table is created by an instance of any class that fulfills the requirements of Cloudscape's read-only virtual table interface (VTI). It is valid only within a SELECT statement
This section discusses how to create classes of that type. This section is for customers who are working with VTI classes they may have already developed or started developing. New customers should work with read-write VTI classes.
Requirements and Options for Read-Only VTI Classes
To qualify as a VTI class, Java class must fulfill the VTI requirements:
Optionally, the class can provide costing information to the Cloudscape optimizer or prevent Cloudscape from instantiating it more than once. See Providing Costing Information.
Deprecation of the getResultSetMetaData Method
NEW: In previous releases, Cloudscape looked for an optional method, public static java.sql.ResultSetMetaData getResultSetMetaData(parameterList). Cloudscape used the ResultSetMetaData object returned by this method to avoid having to instantiate the class at compile time; it was sometimes able to instantiate the class only at execution time. Beginning in Version 3.6, Cloudscape no longer looks for this optional method; it always creates an instance of the class at compilation time.
Compile Time vs. Execution Time
When Cloudscape compiles the SQL-J statement that refers to the VTI class, Cloudscape needs to instantiate the VTI to determine the shape of its ResultSet. This means that the class will be instantiated twice--once at compile time and once at runtime. You may want the VTI class to be able to distinguish between those two different situations. For example, for performance reasons, when SQL-J executes the statement (instead of just compiling it) you may want the class to begin some external actions such as reading files, connecting to a mainframe, and so on, when it is constructed. When Cloudscape is just compiling the statement, you may not want it to carry out those actions.
Here's how the VTI class can determine the situation (compile time or runtime):
- Add an object parameter (such as a string) to the constructor.
- When invoking it within SQL-J, pass in a valid parameter.
- Inside the VTI class, check the value of the parameter. If the object parameter is null, it is compile time (the statement isn't being executed yet).
NOTE: For information on examples provided in the tours sample application, see Example VTIs.
Information Applicable to Both Read-Write VTIs and Read-Only VTIs
Implementing ResultSet
A VTI class implements java.sql.ResultSet. A ResultSet consists of rows and columns of data, the methods for stepping through them, and a method for getting the ResultSetMetaData (getMetaData()).
NOTE: Defer as much work as possible until the first call to the next() method, since Cloudscape will have to instantiate the class when it compiles that statement that references the VTI class, not just when executing that statement. (The next() method gets called only during execution, not compilation).
ResultSet and ResultSetMetaData Methods Used by Cloudscape
Both the java.sql.ResultSet and java.sql.ResultSetMetaData interfaces have a lot of methods. Cloudscape calls only a small subset of these methods within an SQL-J statement that uses ExternalVirtualTable. The list of these methods follows.
NOTE: This list is subject to change.
- ResultSet
- ResultSetMetaData
- getColumnCount()
- isNullable(int column)
- getColumnDisplaySize(int column)
(This method call is meaningful only for Types that can vary in size--Types.BIT, Types.NUMERIC, Types.DECIMAL, Types.CHAR, Types.VARCHAR, Types.LONGVARCHAR, Types.BINARY, Types.VARBINARY, Types.LONGVARBINARY, and Types.OTHER.)
- getColumnName(int column)
- getPrecision(int column) (for NUMERIC or DECIMAL columns)
- getScale(int column)
- getColumnType(int column)
- getXXX method calls
The getXXX method called on a column returned by a VTI is determined by the JDBC type of the column. Table 5-1 shows which getXXX method is called for each type.
If the statement selects data from the ExternalVirtualTable to insert it into a table, Cloudscape does not need to call the appropriate getXXX method based on the type of the target table; Cloudscape handles the conversion.
In addition, there is nothing to prevent application code from using an entirely different getXXX call on the column when stepping through the ResultSet returned by Cloudscape.
For example, a VTI could return a column of JDBC type BIGINT:
SELECT cn FROM NEW ExternalQuery( 'jdbc:cloudscape:toursDB', 'SELECT CONGLOMERATENUMBER FROM SYS.SYSCONGLOMERATES') AS EQ (cn)
When evaluating the statement, Cloudscape calls getLong on the ResultSet returned by the VTI (the getLong method in the VTI's ResultSet class) to create the ResultSet returned to the user when the query is executed.
However, the user's application could call a getInt on that column instead:
ResultSet rs = s.executeQuery( "SELECT * FROM NEW ExternalQuery" + "('jdbc:cloudscape:toursDB', " + "'SELECT CONGLOMERATENUMBER FROM SYS.SYSCONGLOMERATES') " + "AS EQ"); while (rs.next()) { System.out.println(rs.getInt(1)); }
In this case, the getInt method is the one implemented by Cloudscape, not by your VTI class. Cloudscape only calls the getLong method on the VTI.
Table 5-1 getXXX Methods Called for JDBC Types
ExternalVirtualTable's Column JDBC Data Type |
getXXX Methods Called |
BIGINT
|
getLong
|
BINARY
|
getBytes
|
BIT
|
getBoolean
|
CHAR
|
getString
|
DATE
|
getDate
|
DECIMAL
|
getBigDecimal
|
DOUBLE
|
getDouble
|
FLOAT
|
getDouble
|
INTEGER
|
getInt
|
LONGVARBINARY
|
getBytes
|
LONGVARCHAR
|
getString
|
NUMERIC
|
getBigDecimal
|
OTHER
|
getObject
|
REAL
|
getFloat
|
SMALLINT
|
getShort
|
TIME
|
getTime
|
TIMESTAMP
|
getTimestamp
|
TINYINT
|
getByte
|
VARBINARY
|
getBytes
|
VARCHAR
|
getString
|
Rules for Parameters to VTIs
Constant Parameters
The currently supported constant expressions for compilation time are:
- GETCURRENTCONNECTION()
- literals (for example, 1, 'asdf', 1.1, or DATE'1996-09-09')
- CAST of a constant expression--for example, CAST (1.1 AS REAL)
Non-Constant Parameters
SQL-J statements, as you know, often include non-literals. For example, an SQL-J statement may include dynamic parameters or column references. For example:
SELECT * FROM a, b WHERE a.col1 = b.col1 AND a.col1 = ?
In this example, the ? is a dynamic parameter that the application fills in at runtime, and a.col1 and b.col1 are column references that Cloudscape evaluates at runtime.
Your VTI class must be able to handle such non-literal expressions. For example:
SELECT COUNT(*) FROM NEW jarvti(jar) AS jars, myjars WHERE myjars.jar = ?
SELECT name FROM NEW jarvti(?) AS jars
As discussed elsewhere, Cloudscape instantiates the VTI class at compile time. What value does it pass for these parameters at compile time (before literal values are substituted for the non-literals)? That depends on the data type of the parameter. In the above examples, the data type of the parameter is a String, which is a Java object, so Cloudscape passes in null. You must program your VTI class to handle a null value for Object parameters.
For Java primitives, Cloudscape passes in arbitrary default values. The default values are shown in Table 5-2.
Table 5-2 Default Values for Parameter Data Types
Data Type |
Default Value |
Object
|
null
A VTI must be prepared to handle a null parameter for an Object in its constructor and in the optional getResultSetMetaData method, if implemented.
|
byte
|
0
|
short
|
0
|
int
|
0
|
long
|
0L
|
float
|
0.0
|
double
|
0.0
|
boolean
|
false
|
char
|
'\u0000'
|
NOTE: You may want to read Method Resolution and Type Correspondence for information on working with parameters that are Java primitives.
Providing Costing Information
If a VTI class implements COM.cloudscape.vti.VTICosting, Cloudscape's optimizer can use the information it provides when optimizing an SQL-J statement that references it. This may be useful in determining join order if the SQL-J statement involves a join.
For example, if it is very expensive to iterate through the rows in a VTI, Cloudscape would probably not choose it as an outer table in a join. If on the contrary it is very cheap to iterate through the rows in the VTI, Cloudscape would definitely choose it as the inner table in a join if it has a choice.
By default, Cloudscape assumes that it is very expensive to instantiate a VTI and to iterate through its rows. If you know more about a VTI's cost than Cloudscape does, provide that information by implementing this interface.
This interface also provides a way for you to tell Cloudscape that the class cannot be instantiated more than once.
NOTE: For read-write VTIs, implement this interface in the class that implements java.sql.PreparedStatement. For read-only VTIs, implement this interface in the class implements java.sql.ResultSet.
To implement the interface, you must provide three methods. (The methods take a parameter of type COM.cloudscape.database.VTIEnvironment, which you can ignore for the current release.)
- public double getEstimatedRowCount(
COM.cloudscape.database.VTIEnvironment)
The estimated number of rows returned by a particular instance of the VTI.
- public double getEstimatedCostPerInstantiation(
COM.cloudscape.database.VTIEnvironment)
The estimated cost of instantiating the VTI and iterating through the rows of that instantiation. Usually the cost of iterating through the rows constitutes the greatest part of the cost. (For more information, see Estimating the Cost).
- public boolean supportsMultipleInstantiations(
COM.cloudscape.database.VTIEnvironment)
Whether the VTI can be instantiated more than once during execution. (most VTIs do not have this limitation.) Some unusual VTIs may open read-once streams; those VTIs should be instantiated only once by Cloudscape. Otherwise, Cloudscape may instantiate the VTI more than once in evaluating the statement.
When this method returns false, if Cloudscape chooses the VTI instantiation as the inner table of a join, it must materialize the VTI instantiation into a temporary table.
NOTE: You may wish only to inform Cloudscape that the VTI class can be instantiated only once and have no need to provide costing information. Unfortunately, this interface is all or nothing; you have to implement the cost-related methods anyway. If so, simply have the cost-related methods return the pre-defined variables defaultEstimatedRowCount or the defaultEstimatedCost.
Estimating the Cost
To estimate a VTI's "cost," your best bet is to work with RunTimeStatistics. (For more information about RunTimeStatistics, see Tuning Cloudscape.) This is done most easily from Cloudview, which provides an interface for viewing RunTimeStatistics.
- In the SQL window, check the Use Statistics box.
- Run a query that does a table scan.
- Look at the Statistics tab, and select that node that begins with the words Table Scan (this is the TableScanResultSet). Right-click and select Inspect to display the TableScanResultSet object.
- Write down the following values for the TableScanResultSet, which are displayed in the left-hand window:
optimizer's estimated cost
total time spent in the result set (inspectOverall time, which is displayed in milliseconds)
- Calculate the value of an "optimizer unit" for your environment:
(optimizer's estimated cost)/ (total time spent in the result set) - Run the query again twice, re-calculating the value of an optimizer unit for your environment each time.
- Calculate the average of the three calculations and use this as the value of an optimizer unit. This value does not have to be precise. It is used to provide a rough estimate only.
- Run a query that instantiates and selects from your VTI class.
- Look at the Statistics tab for the statement.
- Select the node that begins VTI, and right-click to inspect the VTIResultSet.
- Look through the text and write down:
total time spent in the result set (inspectOverall time, which is displayed in milliseconds)
the number of rows seen
- Then calculate:
(optimizer unit) * (total time spent in the result set) / (number of rows)
This figure is the inexact "cost" per row for your VTI class. If your class is able to determine or estimate the number of rows in a particular instantiation, it should be able to return an estimated cost per instantiation ("cost" per row * number of rows).
- Estimate whether your VTI has an additional significant cost per instantiation over and above cost per row. For example, your VTI class may have a cost even if it returns 0 or 1 rows. If such a cost is significant, add this cost to (cost per row * number of rows) when returning an estimate of total cost. To determine whether such a cost is significant is to select from an instantiation of the VTI that returns 0 or 1 rows, then inspect the statistics. If the statistics show any time at all in the inspectOverall field, the cost is significant. In such a case, the VTI class should use the following slightly different formula to estimate cost:
((optimizer unit) * ((total time spent in the result set) - (total time spent in empty result set)) / (number of rows)) + (total time spent in empty result set)
For an example of a VTI that provides costing information, see VTI class JBMSTours.vti.jdbc1_2.jarvti provided in the sample application.
Templates for Creating VTIs
The requirements for a VTI class is that it implement java.sql.PreparedStatement and return a class that implements java.sql.ResultSet, or, in the case of read-only VTIs, that it implement java.sql.ResultSet. Both classes contain a lot of methods. Often a class uses only three or four of those methods but it must implement the methods for them to compile correctly. Cloudscape has provided the following template classes to make it easier to develop VTI PreparedStatement and ResultSet classes:
- COM.cloudscape.vti20.UpdatableVTITemplate
This class implements most of the methods of the JDBC20 version PreparedStatement, each one throwing an SQLException with the name of the method being called. You can use this class as a template for a read-write VTI class. A class that extends this template can simply implement the methods it needs that are not implemented in the template and override any methods it needs to implement for correct functionality. Use COM.cloudscape.vti20.VTITemplate as the superclass for the ResultSet returned by the VTI if you need to create such a class.
This one requires a JDBC 2.0 (JDK 1.2 or higher) environment.
- COM.cloudscape.vti.UpdatableVTITemplate
Identical to COM.cloudscape.vti20.UpdatableVTITemplate, except it works in a JDBC1.2 (JDK1.1) environment.
- COM.cloudscape.vti20.VTITemplate
This class implements most of the methods of ResultSet, each one throwing an SQLException with the name of the method being called. You can use this class as a template for a read-only VTI class, or as the template for the ResultSet returned by a read-write VTI class. A class that extends this template can simply implement the methods it needs that are not implemented in the template and override any methods it needs to implement for correct functionality.
This one requires a JDBC 2.0 (JDK 1.2 or higher) environment.
- COM.cloudscape.vti.VTITemplate
Identical to COM.cloudscape.vti.VTITemplate, except it works in a JDBC1.2 (JDK1.1) environment.
- COM.cloudscape.vti.VTIMetaDataTemplate
An abstract implementation of ResultSetMetaData (JDK1.1/JDBC 1.2). This class implements most of the methods of ResultSetMetaData, each one throwing an SQLException with the name of the method. A class that extends this template can simply implement the methods not implemented here and override any methods it needs to implement for correct functionality. (Compiles in JDBC 1.2 only.)
- COM.cloudscape.vti20.VTIMetaDataTemplate
An abstract implementation of ResultSetMetaData (JDK1.1/JDBC 1.2). This class implements most of the methods of ResultSetMetaData, each one throwing an SQLException with the name of the method. A class that extends this template can simply implement the methods not implemented here and override any methods it needs to implement for correct functionality. (Compiles in JDBC 2.0.)
Cloudscape provides the source for these classes in demo/programs/tours/JBMSTours/vti, in /jdbc2_0 or jdbc1_2.
Built-In VTIs and Example VTIs
Built-In VTIs
The Cloudscape engineers have found VTI classes an extremely useful way of presenting information. Whereas another DBMS may come with clunky system functions, Cloudscape comes with a number of built-in VTIs that provide internal system information to the end-user (you) in an elegant way. For example:
Example VTIs
The JBMSTours sample application comes with three sample VTI classes:
|