Perform cross-database operations on opaque data types with Informix Dynamic Server 10.00

This article describes a new feature of IBM Informix® Dynamic Server 10.00 that extends support for opaque data types to distributed operations on multiple databases of the same server instance. Cross-database operations such as SELECT queries, CREATE VIEW statements, and calls to UDRs can reference and manipulate values of built-in opaque data types and of opaque or distinct UDTs that can be explicitly cast to built-in data types.

Share:

Joaquim Zuzarte (joaquim@us.ibm.com), Software Engineer, Informix/DB2 Products, IBM, Software Group

Joaquim Zuzarte has been working on the Informix Server SQL Engine for over eight years. Joaquim works in the Query Optimizer area, and his major focus for the past year has been enhancing ANSI outer join query performance.



Tom Houston (thouston@us.ibm.com), Advisory Software Engineer, IBM, Software Group

Tom Houston has been documenting Informix software products since 1986. He currently maintains the IBM Informix Guide to SQL:Syntax and Guide to SQL:Reference, and monitors the docinf@us.ibm.com email alias for receiving reports on errata in IBM Informix user manuals. Tom has a Doctorate from the University of Wisconsin at Madison.



25 August 2005

Also available in Russian

Background and Terminology

Among its extensibility features, the Informix Dynamic Server (IDS) 9.x server introduced built-in opaque data types. This term refers to types such as BOOLEAN, LVARCHAR, BLOB, and CLOB that are implemented internally as server-defined opaque data types. You can also use the CREATE OPAQUE TYPE and CREATE DISTINCT TYPE statements to define your own data types, which this article abbreviates as UDTs (user-defined types).

Some system catalog tables that use built-in SQL types in IDS 7.x were redesigned for IDS 9.x to use built-in opaque data types. IDS 7.x customers typically have applications and tools that need to access system catalog tables of other databases. Because IDS 9.x does not allow access to columns of built-in opaque data types across databases, some IDS 7.x applications and tools do not work properly after server migration to IDS 9.x.

This article uses the following terminology conventions:

  • Distributed operations refer to operations on objects in more than one database.
  • Cross-database operations are distributed operations in which all of the participating databases are databases of the same Dynamic Server instance.
  • Local database is the client-connected database.
  • Remote database is a database of the same IDS instance as the local database.

The last term conflicts with the standard use of remote in IDS user documentation, where it normally refers to a database of a different Dynamic Server instance.

Overview of the IDS 9.40 distributed query model

IBM Informix Dynamic Server (IDS) supports distributed queries and other distributed operations. This feature enables data manipulation language (DML) statements, data definition language (DDL) statements, and certain other SQL statements to access data or to create database objects across databases on one or more IDS instances. These multiple server instances can be on the same machine, or on different machines in a network.

Transaction logging restrictions

All databases that are accessed by a distributed query must have the same transaction logging mode:

  • All must use implicit transactions (as ANSI/ISO-compliant databases)
  • or else all must use explicit transaction logging,
  • or else all must not support transaction logging.

The IDS 9.40 distributed query model supports the following SQL statements.

DML operations:

  • SELECT
  • DELETE
  • INSERT
  • UPDATE

DDL operations:

  • CREATE VIEW with remote table/view references
  • CREATE SYNONYM with remote table/view references
  • CREATE DATABASE with remote server references

Miscellaneous operations:

  • CONNECT and SET CONNECTION
  • DATABASE
  • CALL (in SPL routines)
  • EXECUTE PROCEDURE and EXECUTE FUNCTION
  • INFO, LOAD, and UNLOAD (in DB-Access sessions)
  • LOCK and UNLOCK

Data types supported by IDS 9.40 in distributed queries

The IDS 9.40 distributed query model supports the following SQL data types:

  • INT
  • INT8
  • SMALLINT
  • FLOAT
  • SMALLFLOAT
  • DATE
  • DATETIME
  • INTERVAL
  • CHAR
  • VARCHAR
  • NCHAR
  • NVARCHAR
  • DECIMAL (p)
  • DECIMAL (p, s)
  • MONEY
  • SERIAL
  • SERIAL8
  • BYTE
  • TEXT

All these SQL data types are called non-opaque built-in data types. Not all distributed operations support the last four (serial and simple large object) types.


Enhanced support for extended data types in IDS 10.00

Besides the non-opaque built-in data types, Dynamic Server 10.00 extends data type support in cross-database operations to the following additional categories of data types:

  • Built-in opaque data types
  • Distinct types that are based on built-in data types
  • User-defined types (UDTs) that have explicit casts to built-in data types

In the last two categories, the built-in types can be either non-opaque or opaque.

Cross-database operations can now support the following built-in opaque data types:

  • BLOB
  • BOOLEAN
  • CLIENTBINVAL
  • CLOB
  • IFX_LO_SPEC
  • IFX_LO_STAT
  • INDEXKEYARRAY
  • LVARCHAR
  • POINTER
  • RTNPARAMTYPES
  • SELFUNCARGS
  • STAT
  • XID

Note: The following built-in opaque data types are not valid in cross-database operations, because they lack required support functions:

  • IMPEX
  • IMPEXBIN
  • LOLIST
  • SENDRECV

From now on, when this article refers to "all built-in opaque data types," these four unsupported data types are excluded.

Cross-database operations of IDS 10.00 also support user-defined data types that the CREATE DISTINCT TYPE or CREATE OPAQUE TYPE statements of SQL define, if both of the following conditions are satisfied for each UDT:

  • All the databases are databases of the same Dynamic Server instance.
  • The UDT and an explicit cast to LVARCHAR (or to some other built-in type) exists in each database in which the query accesses the didtinct or opaque UDT.

Note: The opaque data types of IDS 10.00 are valid only in local and cross-database operations. Operations that access the databases of more than one IDS 10.00 server instance (called cross-server operations) are restricted to built-in non-opaque types.

The following sections provide examples of distributed operations that can support extended data types in databases of a single instance of Dynamic Server 10.00.

The following SQL statements (and pseudo-code within comments) show how to create two databases (localdb and remotedb) of the same IDS 10.00 instance. Each database contains a table (localtab and remtab respectively) with columns of built-in opaque data types and of a user-defined data type. Both databases also define explicit casts of the user-defined data type to VARCHAR and LVARCHAR.

The localdb database also stores user-defined functions that accept a built-in opaque argument and return a built-in opaque value, and also a user-defined procedure that performs an INSERT operation on a table in remotedb.

Schema for examples of cross-database operations on opaque data types

    CREATE DATABASE localdb WITH LOG;
    {
    create UDT type user_udt
    create explicit cast of user_udt to built-in  
    type varchar and lvarchar
     }
    CREATE TABLE localtab( bool_col    boolean, 
                           b_col       blob, 
                           c_col       clob,  	
                           lvar_col    lvarchar(10),
                           udt_col     user_udt,
                           udt2_col    user_udt
                         );
    INSERT INTO localtab VALUES 
       ('t', filetoblob('/tmp/info.txt', 'client'), 
	filetoclob('/tmp/info.txt', 'client'), 
	'first row', 'ldata1row1', 'ldata2row1');

    INSERT INTO localtab VALUES 
       ('f', filetoblob('/tmp/info.txt', 'client'),
        filetoclob('/tmp/info.txt', 'client'),
        'second row', 'ldata1row2', 'ldata2row2');     


    CREATE DATABASE remotedb WITH LOG;
    {
     create user UDT type user_udt
     create explicit casts of user_udt to built-in 
     types VARCHAR and LVARCHAR
     }

    CREATE TABLE remtab( boolean_col   BOOLEAN, 
                         blob_col      BLOB, 
                         clob_col      CLOB,  	
                         lvarchar_col  LVARCHAR(10),
                         udt1_col      user_udt,
                         udt2_col      user_udt,
                         udt3_col      user_udt
                       );
   
   INSERT INTO remtab VALUES 
       ('t', filetoblob('/tmp/info.txt', 'client'), 
	filetoclob('/tmp/info.txt', 'client'), 
	'first row', 'rdata1row1', 'rdata2row1', 'rdata3row1');

   INSERT INTO remtab VALUES 
       ('f', filetoblob('/tmp/info.txt', 'client'), 
	filetoclob('/tmp/info.txt', 'client'), 
	'second row', 'rdata1row2', 'rdata2row2', 'rdata3row2');


    CREATE FUNCTION remfunc1(a1 BOOLEAN) 
	  RETURNING BOOLEAN
	  RETURN a1;
    END FUNCTION; 
    
    CREATE FUNCTION remfunc2(a1 LVARCHAR)  
       RETURNING lvarchar
	  RETURN a1;
    END FUNCTION;
    
    CREATE PROCEDURE remproc(a1 BOOLEAN) 
	  INSERT INTO remtab(boolean_col) 
          VALUES (a1);
    END PROCEDURE;

The code examples in the following sections use database objects in the localdb and remotedb databases to illustrate cross-database operations.


Data manipulation language support

Dynamic Server 10.00 introduces support for cross-database DML statements that reference remote tables with columns of built-in opaque types, or of distinct or opaque UDTs that can be explicitly cast to built-in data types.

SELECT statements

All built-in opaque data types are supported for cross-database SELECT queries. Cross-database references of columns in column lists, predicates, subqueries, and parameters, and return values of functions called within cross-database queries can be built-in opaque data types.

Direct references to UDTs in other databases are not supported, but cross-database queries can use UDTs that you explicitly cast to some built-in type. The UDTs, explicit casts, and cast functions must exist in each participating database.

Examples of cross-database SELECT operations on opaque columns

Simple query on a remote table with built-in opaque data types:

    DATABASE localdb;
    SELECT et.boolean_col, et.blob_col,
       et.clob_col, et.lvarchar_col 
       FROM remotedb:remtab et 
	  WHERE et.boolean_col = 't';

Simple query on a remote table with user-defined opaque data types:

       SELECT et.udt1_col::lvarchar, 
            et.udt2_col::varchar 
       FROM remotedb:remtab et
	  WHERE et.udt3_col::lvarchar = 'rdata3row2';

INSERT statements

Among the built-in opaque data types, only BOOLEAN, LVARCHAR, BLOB, and CLOB are supported in cross-database INSERT statements.

For smart large objects, you can insert into BLOB and CLOB columns of a remote table of the same server instance the same way that you would insert into BLOB and CLOB columns of a local table. The BLOB or CLOB value is created in a sbspace (smart blob storage space) and the corresponding row in the tblspace contains a pointer to the BLOB or CLOB.

Cross-database inserts of UDTs require explicit casting. The UDTs, casts, and cast functions must exist in all databases that the INSERT statement references, as in the localdb and remotedb databases on which these examples are based.

Examples of cross-database INSERT operations on opaque columns

Insert built-in opaque data types into a remote table:

INSERT INTO remotedb:remtab 
   (boolean_col, lvarchar_col, blob_col, clob_col) 
    VALUES ('t', 'lvarchar_data',  
            filetoblob('blobfile', 'client'),
	    filetoclob('clobfile', 'client'));

Insert built-in opaque types into a remote table with an embedded query:

INSERT INTO remotedb:remtab 
   ( boolean_col, lvarchar_col, blob_col, clob_col)
   SELECT bool_col, lvar_col, b_col, c_col 
      FROM localtab
      WHERE bool_col = 't';

Insert a user-defined opaque data type into a remote table:

INSERT INTO remotedb:remtab (udt1_col)
   SELECT udt_col::LVARCHAR 
      FROM localtab
      WHERE udt2_col::LVARCHAR = 'ldata2row1';

UPDATE statements

Among built-in opaque types, only BOOLEAN, LVARCHAR, BLOB, and CLOB are supported in cross-database UPDATE statements. Cross-database updates to BLOB and CLOB columns are reflected immediately in the remote tables.

Updating UDTs requires explicit casting. The UDTs, their casts, and their cast functions must exist in all databases that participate in the UPDATE statement.

Examples of cross-database UPDATE operations on opaque columns

Update built-in opaque types in a remote table:

UPDATE remotedb:remtab  
   SET (boolean_col, lvarchar_col, blob_col, clob_col) =
     ('f', 'lvarchar_data', filetoblob('blobfile', 
      'client'), filetoclob('clobfile', 'client'))
   WHERE boolean_col = 't';

Update a UDT in a remote table with an embedded query:

UPDATE remotedb:remtab 
   SET (udt1_col) = 
     ((SELECT udt_col::LVARCHAR 
          FROM localtab
          WHERE udt2_col::LVARCHAR = 'ldata2row1'))
   WHERE boolean_col = 'f';

DELETE Statements

All built-in opaque types are valid in cross-database DELETE statements. Deletion of UDTs is also supported through explicit casting, if the UDTs, their casts, and their cast functions exist in all of the participating databases.

Deletion from BLOB and CLOB columns of a remote table of the same server instance is accomplished the same way as DELETE operations on BLOB and CLOB columns of a local table:

  1. The handle to the BLOB or CLOB data value is deleted.
  2. When the transaction is committed, the database server deletes the BLOB or CLOB value if the reference and the open count to it are zero.

Examples of cross-database DELETE operations on opaque columns

Delete from a remote table with built-in opaque types:

DELETE FROM remotedb:remtab
   WHERE boolean_col = 't' 
      AND lvarchar_col = 'first row'
      AND blob_col IS NOT NULL 
      AND clob_col IS NULL;

Delete from a remote table with UDTs:

DELETE FROM remotedb:remtab
   WHERE udt2_col::lvarchar = 'rdata2row2';

Data definition language support

Dynamic Server 10.00 introduces support for DDL statements that create views or synonyms using cross-database references to remote tables with columns of built-in opaque types, or of distinct or opaque UDTs that can be explicitly cast to built-in data types.

CREATE VIEW statements

Now built-in opaque types are supported in CREATE VIEW statements that create views on tables in other databases of the same Dynamic Server instance.

Columns with UDTs in remote tables must be explicitly cast to built-in types in order to support the creation of views. The UDTs and their casts must exist in all of the referenced databases. The new view exists only in the local database.

Examples of creating views on remote tables with opaque data types

Create a view on a remote table with built-in opaque columns:

CREATE VIEW local_view1 AS
   SELECT et.boolean_col, et.lvarchar_col, 
          et.blob_col, et.clob_col
                FROM remotedb:remtab et;

Create a view on a remote table with UDT columns:

CREATE VIEW local_view2(vcol1, vcol2) AS
   SELECT et.udt1_col::LVARCHAR,      
          et.udt2_col::VARCHAR
      FROM remotedb:remtab et
      WHERE et.udt3_col::VARCHAR MATCHES 'rdata3*';

CREATE SYNONYM statements

Dynamic Server 9.40 supported the creation of synonyms in the local database for tables in other databases, only if all columns of the tables were non-opaque built-in data types. Now the CREATE SYNONYM statement supports synonyms for remote tables that include columns of built-in opaque data types in databases of the same server instance. The new synonym exists only in the local database.

Example of creating a synonym for a remote table with opaque data types

Create a synonym for a remote table with built-in opaque data types:

DATABASE remotedb;
CREATE TABLE remtab2(col_bool    BOOLEAN, 
                     col_lvar    LVARCHAR);
DATABASE localdb;
CREATE SYNONYM syn1 FOR remotedb:remtab2;

Executing user-defined procedures and functions

Dynamic Server 10.00 supports implicit and explicit execution of user-defined routines that use built-in opaque data types as parameters or as return types. If the UDR has UDT parameters or a UDT return type, the calling context must invoke the UDR with its return type and parameters explicitly cast to built-in data types. IDS 10.00 also supports execution of UDRs that have OUT parameters (and for Java language UDRs, also INOUT parameters) of built-in opaque data types.

Implicit execution of UDRs

A UDR can be invoked implicitly by the database server in these contexts:

  • UDRs referenced in the projection list or predicates of a query
  • UDRs called to convert a function argument to another data type
  • UDRs executing an operator function for a built-in opaque type

This is called implicit execution. Dynamic Server 10.00 supports implicit execution of UDRs with built-in opaque data types. UDTs that you cast explicitly to built-in types are also supported in implicit UDR execution.

Examples of implicit execution of UDRs

Implicit execution in SELECT queries:

SELECT remotedb:remfunc1(bool_col) 
   FROM localtab;

SELECT remotedb:remfunc1(bool_col) 
   FROM localtab 
   WHERE remotedb:remfunc2(lvar_col) = lvar_col;

Implicit execution in UPDATE, DELETE, and INSERT statements:

UPDATE localtab 
   SET lvar_col  = remotedb:remfunc2(lvar_col) 
   WHERE remotedb:remfunc1(bool_col) = 't';

INSERT INTO localtab (bool_col, lvar_col) 
   VALUES (remotedb:remfunc1('t'), 
           remotedb:remfunc2('lchar data'));

DELETE FROM localtab 
   WHERE remotedb:remfunc1(bool_col) = 'f';

Implicit execution of the ifx_boolean_equal operator function:

SELECT * from localtab, remotedb:remtab et 
   WHERE  bool_col = et.boolean_col;

Explicit execution of UDRs

When you invoke a function or procedure by issuing the EXECUTE FUNCTION or EXECUTE PROCEDURE statement of SQL, or the CALL statement of SPL, the resulting execution is called explicit execution. Built-in opaque data types of UDR parameters or of a return value are now supported in the explicit execution of UDRs. UDTs that are cast explicitly to built-in types are also supported.

Examples of explicit execution of UDRs

Explicit execution with EXECUTE PROCEDURE:

EXECUTE PROCEDURE remotedb:remproc('t');

Explicit execution with EXECUTE FUNCTION:

EXECUTE FUNCTION remotedb:remfunc1('t');

Triggered actions

The triggering event that the CREATE TRIGGER statement specifies must be a DML operation on a table or view in the local database. IDS 10.00 extends the functionality of triggers, however, by enabling you to reference built-in opaque data types (and UDTs that can be cast to built-in data types) across databases of the same server. These references can appear in the Triggered Action list and in the WHEN condition when you define a trigger on a table, or in the Triggered Action list when you define an INSTEAD OF trigger on a view.

The Triggered Action list of the CREATE TRIGGER statement can now include the following previously unsupported cross-database operations:

  • DML operations on built-in opaque types, or on UDTs that can be cast explicitly to built-in data types, as this article has described for cross-database SELECT, INSERT, DELETE, and UPDATE statements.
  • EXECUTE PROCEDURE or EXECUTE FUNCTION statements that invoke remote UDRs whose arguments or returned values are built-in opaque types, or that are UDTs that can be cast explicitly to built-in types.

Examples of triggers with cross-database actions

Simple INSERT trigger that calls a remote UDR with an opaque argument:

CREATE TRIGGER ins_trig 
   INSERT ON localtab REFERENCING NEW AS post
   FOR EACH ROW 
     (EXECUTE PROCEDURE remotedb:remproc(post.bool_col));

Simple UPDATE trigger that updates a remote opaque column:

CREATE TRIGGER upd_trig 
   UPDATE OF bool_col ON localtab
   REFERENCING OLD AS pre NEW AS post
   FOR EACH ROW WHEN ( pre.bool_col = 't' )
     (UPDATE remotedb:remtab 
         SET boolean_col = pre.bool_col);

Conclusion

With IDS 10.00 enhanced data type support in distributed DML operations, most built-in opaque data types can now be treated like other valid built-in data types in cross-database SELECT and DELETE statements. The BOOLEAN, BLOB, CLOB, and LVARCHAR data types are now valid in cross-database INSERT and UPDATE statements.

In addition, user-defined distinct and opaque types that are cast explicitly to built-in data types are now valid in cross-database operations. This extended support for built-in opaque types and for UDTs can be used in many different scenarios, such as subqueries and joins, besides the contexts that this article presented. This feature requires no change in SQL transaction semantics like BEGIN or COMMIT WORK, and extends the functionality of Dynamic Server 10.00 in distributed queries that use opaque data types.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=92556
ArticleTitle=Perform cross-database operations on opaque data types with Informix Dynamic Server 10.00
publish-date=08252005