Contents: Introduction Accessing an External File Directory content Installation Testing Tracing Download About the author Rate this article Subscriptions: dW newsletters dW Subscription (CDs and downloads)

Jacques Roy
IBM Worldwide Sales Support, IBM Corporation
27 Apr 2001

This demo creates an Informix DataBlade that implements an access method for building virtual tables based on operating system files. Includes source code.

Introduction
This primary access method (FFAccess) implements a read-only interface to make external files look like tables in the server. The types supported are:

boolean, char, date, datetime, decimal, float, int8, integer, interval, lvarchar, money, nchar, nvarchar, smallfloat, smallint, varchar

This includes any distinct types based on these types.

Accessing an External File
An external file is identified in a CREATE TABLE statement. For example:

 CREATE TABLE mytab (
   a BOOLEAN,
   b VARCHAR(20),
   c DATE,
   d DATETIME year to second,
   e DECIMAL,
   f DOUBLE PRECISION,
   g SMALLFLOAT,
   h INT8
 ) USING FFAccess (path='/tmp/mytab.txt', delim=';');
  

The last part of the statement identifies the access method used, followed by arguments to the access method that includes the path to the external file and the delimiter used in the file to separate columns. The default separator is ':'.

An external dbspace could also be used as the external file by using the "IN" keyword in the CREATE TABLE statement.

Once the table is created it can be used in any SELECT statements including joining with existing tables.

Directory content

Installation
You should install the content of this directory under $INFORMIXDIR/extend/ffvti

Once the shared library (or DLL) is created, move it from the bin directory to the top directory ($INFORMIXDIR/extend/ffvti). Then, execute ffvti.sql in the database that requires this access method. At this point you can create tables that access external files as shown above.

Testing
To test the access method, you can use the tryit.sql script. You first need to copy the tab.txt file to the /tmp directory.

Tracing
The source code contains tracing statement. To enable tracing in your current session, execute the following statement:

 EXECUTE FUNCTION set_tracing("ffvti", 50, "
 $INFORMIXDIR/extend/ffvti/trace.out");
   

For trace to be active, you must compile the source without the MITRACE_OFF flag

IBM, DB2, Informix, and WebSphere are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Windows and Windows NT are registered trademarks of Microsoft Corporation in the United States, other countries, or both.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

IBM copyright and trademark information

	Name				Size				Download method
	ffvti.zip				12 KB				FTP|HTTP

Information about download methods

Rate this article This content was helpful to me: Strongly disagree (1) Disagree (2) Neutral (3) Agree (4) Strongly agree (5) Comments?

developerWorks > DB2 >