IBM InfoSphere Federation Server, Version 10.1

Excel data source - sample scenario

This scenario shows the SQL statements that are necessary to register the federated objects that are used to access an Excel worksheet. Included in this scenario are several queries that you can run using the nickname that you create.

Excel worksheet information

This scenario starts with a worksheet that contains information about various compounds. The name of the workbook that the worksheet is in is Compound_Master.xls and the workbook was created in Excel. The fully-qualified path name to the workbook is C:\Data\Compound_Master.xls.

The first worksheet in the workbook contains four columns and nine rows of data. The columns list the names of the compounds, the weight of the compounds, the molecular count of the compound, and if the compound has been tested.

The contents of the worksheet are shown in the following table.

Table 1. Sample worksheet Compound_Master.xls
Â	A	B	C	D
1	COMPOUND_NAME	WEIGHT	MOL_COUNT	WAS_TESTED
2	compound_A	1.23	367	tested
3	compound_G	Â	210	Â
4	compound_F	0.000425536	174	tested
5	compound_Y	1.00256	Â	tested
6	compound_Q	Â	1024	Â
7	compound_B	33.5362	Â	Â
8	compound_S	0.96723	67	tested
9	compound_O	1.2	Â	tested

Register the federated objects

To access the worksheet using the Excel wrapper you must register the objects on the federated server:

For example:

CREATE WRAPPER Excel LIBRARY 'db2lsxls.dll';

For example:

CREATE SERVER biochem_lab WRAPPER Excel;

For example:

CREATE NICKNAME Compound_Master
     (compound_name VARCHAR(40),
      weight        FLOAT, 
      mol_count     INTEGER, 
      was_tested    VARCHAR(20))
    FOR SERVER biochem_lab 
    OPTIONS (FILE_PATH 'C:\Data\Compound_Master.xls');

The registration process is complete. The Excel worksheet is now part of the federated system, and can be used in SQL queries.

The following examples show the SQL queries and the results that arereturned from the Compound_Master nickname.

A query that returns all data that matches a specific WHERE clause condition

To return all of the data for the compounds that have a molecular count that is greater than 100, issue this query:

SELECT * FROM Compound_Master 
    WHERE mol_count > 100;

A query that returns specific columns from the worksheet

To return the names and molecular counts for all of the compounds where the molecular count has not yet been determined, issue this query:

SELECT compound_name, mol_count FROM Compound_Master 
    WHERE mol_count IS NULL;

All of the columns from rows 2, 3, 4, 6, and 8 are returned.

The compound_name and mol_count columns from rows 5, 7, and 10 are returned.

A query that counts the number of rows that match specific WHERE clause conditions

To return the number of compounds that have a weight that is greater than 1 and that have not been tested, issue this query:

SELECT count(*) FROM Compound_Master 
    WHERE was_tested IS NULL AND weight > 1

The record count of 1 is returned. The compound in row 7 matches the query criteria.

A query that returns specific columns from the worksheet and includes a subselect statement

To return the names and molecular counts for all of the compound where the molecular count has been determined and the molecular count is less than the average molecular count, issue this query:

SELECT compound_name, mol_count FROM Compound_Master 
    WHERE mol_count IS NOT NULL 
    AND mol_count < 
    (SELECT AVG(mol_count) FROM Compound_Master 
         WHERE mol_count IS NOT NULL AND was_tested  IS NOT NULL);

The subquery returns 368 for the molecular count average. The main query uses the average to return the query results that are shown in the following table:

Table 2. Query results
COMPOUND_NAME	MOL_COUNT
compound_A	367
compound_G	210
compound_F	174
compound_S	67

Feedback

Last updated: 2012-05-18