Sample usage

Query optimization

Following is a sample usage scenario of Fluid Query functionality for fetching remote data from external Oracle database, and an example of query optimization.

1. Connector is registered:

   ./fqRegister.sh --udtf read_from_oracle --db my_nps_db --config my_oracleDB --remote

2. A view is created to simplify queries (or because of limitation of customer tools):

   CREATE OR REPLACE VIEW oracle_remote_view AS SELECT * FROM TABLE WITH FINAL(read_from_oracle('','', 'SELECT * FROM orc_table');

3. Queries are executed against the view:

   

   A = SELECT * FROM oracle_remote_view WHERE col1=1234

   B = SELECT * FROM TABLE WITH FINAL (read_from_oracle('','', 'SELECT * FROM orc_table')) WHERE col1=1234

   C = SELECT * FROM orc_table, executed by Fluid Query connector via Oracle JDBC driver

   D = Results from Oracle database = returned rows from table orc_table

   E = Results from customer query “A”, with filtered results from Oracle database to meet the condition col1=1234

In order to execute a sample query (A) the Netezza engine needs to expand the view (B), fetch all data from external source (C, D) and then limit the results to meet the condition col1=1234 (E).

Query:

SELECT * FROM TABLE WITH FINAL (read_from_oracle('','', 'SELECT * FROM orc_table')) WHERE col1=1234

This query can be executed much more efficiently if the condition col1=1234 is checked on the external source, and only the filtered results are returned to the NPS, like in this optimized query:

SELECT * FROM TABLE WITH FINAL (read_from_oracle('','', 'SELECT * FROM orc_table WHERE col1=1234'))

This optimization operation of detecting predicates such as col1=1234 and pushing them into the connector is now done automatically by IBM Fluid Query Accelerator.

Usage with other tools

Fluid Query Accelerator is a fully featured ODBC driver, so it can be used with any tool or program that is capable of loading ODBC drivers. Following are examples.

Advanced Query Tool

Following are sample screenshots for Advanced Query Tool, which automatically detects configured ODBC connections in the system.

Note: In Advanced Query Tool you must enable parameter Use SQLExecDirect in options > advanced to properly execute queries.

Python

Following is the sample Python code that uses Fluid Query Accelerator to execute a sample query:

import pyodbc
import platform
def main():
    platf, linkage = platform.architecture()
    uname = platform.uname()
    print "platform: '%s', linkage: '%s', uname: %s" % (platf, linkage, uname)
    #cnxn =
pyodbc.connect("DRIVER={NetezzaSQLAccelerator};server=127.0.0.1;database=testdb;uid=admin;pwd=password;
")
    cnxn = pyodbc.connect("DSN=NZSQLACC;")
    print "Connected"
    dataSrcName = cnxn.getinfo(pyodbc.SQL_DATA_SOURCE_NAME)
    driverName = cnxn.getinfo(pyodbc.SQL_DRIVER_NAME)
    serverName = cnxn.getinfo(pyodbc.SQL_SERVER_NAME)
    userName = cnxn.getinfo(pyodbc.SQL_USER_NAME)
    print "dataSrcName: '%s', driverName: '%s', serverName: '%s', userName: '%s'" % (dataSrcName,
driverName, serverName, userName)
    cursor = cnxn.cursor()
    queryStr = "select a, b from sqlite_view where a < 5 limit 10;"
    cursor.execute(queryStr)
    print ("executed: %s" % (queryStr, ))
    print "fetching data..."
    rows = cursor.fetchall()
    print "data: "
    for row in rows:
        print("\t a: %s , b: %s" % (row.a, row.b))
if __name__ == "__main__":
    main()