Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

3.7. DDL Commands

3.7.1. DDL Commands

JBoss Data Virtualization supports a subset of DDL to create/drop temporary tables and to manipulate procedure and view definitions at runtime. It is not currently possible to arbitrarily drop/create non-temporary metadata entries. See Section 12.1, “DDL Metadata” for DDL usage to define schemas within a VDB.

Note

A MetadataRepository must be configured to make a non-temporary metadata update persistent. See Runtime Metadata Updates in Red Hat JBoss Data Virtualization Development Guide: Server Development for more information.

3.7.2. Temporary Tables

JBoss Data Virtualization supports creating temporary tables. Temporary tables are dynamically created, but are treated as any other physical table.
Temporary tables can be defined implicitly by referencing them in a INSERT statement or explicitly with a CREATE TABLE statement. Implicitly created temporary tables must have a name that starts with '#'.

Creation syntax:

  • Temporary tables can be defined explicitly with a CREATE TABLE statement: 
    CREATE LOCAL TEMPORARY TABLE name (column type [NOT NULL], ... [PRIMARY KEY (column, ...)])
    Alternatively, temporary tables can be defined implicitly by referencing them in a INSERT statement. Implicitly created temporary tables must have a name that starts with '#': 
    INSERT INTO #name (column, ...) VALUES (value, ...)

    Note

    If #name does not exist, it will be defined using the given column names and types from the value expressions. 
    INSERT INTO #name [(column, ...)] select c1, c2 from t

    Note

    If #name does not exist, it will be defined using the target column names and the types from the query derived columns. If target columns are not supplied, the column names will match the derived column names from the query.
    Use the SERIAL data type to specify a NOT NULL and auto-incrementing INTEGER column. The starting value of a SERIAL column is 1.

Drop syntax:

  • DROP TABLE name
Primary Key Support
  • All key columns must be comparable.
  • Use of a primary key creates a clustered index that supports search improvements for comparison, in, like, and order by.
  • Null is an allowable primary key value, but there must be only 1 row that has an all null key. 
Limitations:
  • With the CREATE TABLE syntax only basic table definition (column name and type information) and an optional primary key are supported.
  • The "ON COMMIT" clause is not supported in the CREATE TABLE statement.
  • "drop behavior" option is not supported in the drop statement.
  • Only local temporary tables are supported. This implies that the scope of temp table will be either to the session or the block of a virtual procedure that creates it.
  • Session level temporary tables are not fail-over safe.
  • Temp tables support a READ_UNCOMMITED transaction isolation level. There are no locking mechanisms available to support higher isolation levels and the result of a rollback may be inconsistent across multiple transactions. If concurrent transactions are not associated with the same local temporary table or session, then the transaction isolation level is effectively SERIALIZABLE. If you want full consistency with local temporary tables, then only use a connection with 1 transaction at a time. This mode of operation is ensured by connection pooling that tracks connections by transaction.
  • LOB values (XML, CLOB, BLOB) are tracked by reference rather than by value in a temporary table. LOB values from external sources that are inserted in a temporary table may become unreadable when the associated statement or connection is closed.
The following example is a series of statements that loads a temporary table with data from 2 sources, and with a manually inserted record, and then uses that temp table in a subsequent query. 
...
CREATE LOCAL TEMPORARY TABLE TEMP (a integer, b integer, c integer);
INSERT * INTO temp FROM Src1; INSERT * INTO temp FROM Src2;
INSERT INTO temp VALUES (1,2,3);
SELECT a,b,c FROM Src3, temp WHERE Src3.a = temp.b;
...

3.7.3. Foreign Temporary Tables

Unlike a local temporary table, a foreign temporary table is a reference to an actual source table that is created at runtime rather than during the metadata load.
A foreign temporary table requires explicit creation syntax: 
CREATE FOREIGN TEMPORARY TABLE name ... ON schema
Where the table creation body syntax is the same as a standard CREATE FOREIGN TABLE DDL statement (see Section 12.1, “DDL Metadata”). In general usage of DDL OPTION, clauses may be required to properly access the source table, including setting the name in source, updatability, native types, etc.
The schema name must specify an existing schema/model in the VDB. The table will be accessed as if it is on that source, however within JBoss Data Virtualization the temporary table will still be scoped the same as a non-foreign temporary table. This means that the foreign temporary table will not belong to a JBoss Data Virtualization schema and will be scoped to the session or procedure block where created.
The DROP syntax for a foreign temporary table is the same as for a non-foreign temporary table.
Neither a CREATE nor a corresponding DROP of a foreign temporary table issue a pushdown command, rather this mechanism simply exposes a source table for use within JBoss Data Virtualization on a temporary basis.
There are two usage scenarios for a FOREIGN TEMPORARY TABLE. The first is to dynamically access additional tables on the source. The other is to replace the usage of a JBoss Data Virtualization local temporary table for performance reasons. The usage pattern for the latter case would look like: 
//- create the source table
call source.native("CREATE GLOBAL TEMPORARY TABLE name IF NOT EXISTS ON COMMIT DELETE ROWS");
//- bring the table into JBoss Data Virtualization
CREATE FOREIGN TEMPORARY TABLE name ... OPTIONS (UPDATABLE true)
//- use the table
...
//- forget the table
DROP TABLE name
Note the usage of the native procedure to pass source specific CREATE ddl to the source. JBoss Data Virtualization does not currently attempt to pushdown a source creation of a temporary table based upon the CREATE statement. Some other mechanism, such as the native procedure shown above, must be used to first create the table. Also note the table is explicitly marked as updatable, since DDL defined tables are not updatable by default.
The source's handling of temporary tables must also be understood to make this work as intended. Sources that use the same GLOBAL table definition for all sessions while scoping the data to be session specific (such as Oracle) or sources that support session scoped temporary tables (such as PostgreSQL) will work if accessed under a transaction. A transaction is necessary because:
  • the source on commit behavior (most likely DELETE ROWS or DROP) will ensure clean-up. Keep in mind that a JBoss Data Virtualization DROP does not issue a source command and is not guaranteed to occur (in some exception cases, loss of DB connectivity, hard shutdown, etc.).
  • the source pool when using track connections by transaction will ensure that multiple uses of that source by JBoss Data Virtualization will use the same connection/session and thus the same temporary table and data.

Note

Since the ON COMMIT clause is not yet supported by JBoss Data Virtualization, it is important to consider that the source table ON COMMIT behavior will likely be different that the default, PRESERVE ROWS, for JBoss Data Virtualization local temporary tables.

3.7.4. Alter View

Usage: 
ALTER VIEW name AS queryExpression

Syntax Rules:

  • The alter query expression may be prefixed with a cache hint for materialized view definitions. The hint will take effect the next time the materialized view table is loaded.

3.7.5. Alter Procedure

Usage: 
ALTER PROCEDURE name AS block

Syntax Rules:

  • The alter block should not include 'CREATE VIRTUAL PROCEDURE'
  • The alter block may be prefixed with a cache hint for cached procedures.

3.7.6. Create Trigger

Usage: 
CREATE TRIGGER ON name INSTEAD OF INSERT|UPDATE|DELETE AS FOR EACH ROW block

Syntax Rules:

  • The target, name, must be an updatable view.
  • An INSTEAD OF TRIGGER must not yet exist for the given event.
  • Triggers are not yet true schema objects. They are scoped only to their view and have no name.

Limitations:

3.7.7. Alter Trigger

Usage: 
ALTER TRIGGER ON name INSTEAD OF INSERT|UPDATE|DELETE (AS FOR EACH ROW block) | (ENABLED|DISABLED)

Syntax Rules:

  • The target, name, must be an updatable view.
  • Triggers are not yet true schema objects. They are scoped only to their view and have no name.
  • Update Procedures must already exist for the given trigger event. See Section 3.10.6, “Update Procedures”.

Note

If the default inherent update is chosen in Teiid Designer, any SQL associated with update (shown in a greyed out text box) is not part of the VDB and cannot be enabled with an alter trigger statement.