Section 1: Teradata ODBC Overview
There are multiple levels of ODBC conformance so not all
applications and ODBC drivers support the same functionality. Under ODBC SDK
2.0/2.5 relational database ODBC drivers are expected to be at least ODBC API
Level 1 compliant. (Currently, the Teradata ODBC Driver is ODBC API Level 1,
but most functions in Level 2 are supported.
Please refer to Unsupported
Functions below for specific Level 2 functions not supported).In ODBC SDK 3.0 the old Level 1 has actually been redefined
as Level 0.
Supported
Environments
·
Microsoft: Windows 9x, Windows NT, and Windows
2000
·
UNIX: NCR MP-RAS, HP-UX, AIX, Sun Solaris Sparc
and Intel
·
English and Japanese
ODBC SDK Support
·
ODBC SDK 2.5 for Microsoft Windows, MP-RAS, and
Sun environments
·
ODBC SDK 3.5 for HP-UX and AIX environments
Teradata ODBC Driver
Supported Features:
·
ODBC API Level 1 compliant, but most functions
in Level 2 are supported.
·
TCP socket communication based
·
Connection Pooling (2.8.0+ on Windows platforms
/ 2.4.0+ on UNIX platforms)
·
Multiple connection support
·
Multi-statement request
·
ODBC Asynchronous processing
·
ODBC Parameter binding
·
Multi-threaded applications
·
Thread Safe (2.8.0+ on Windows platforms /
2.4.0+ on UNIX platforms)
·
Supports Teradata specific SQL grammar
·
User configurable options
·
Session recovery (reconnect)
·
Support for MBCS (Kanji)
·
Teradata Password Expiration security feature
·
Support for XWindows based applications on UNIX
·
Additional Trace and Logging features in ODBC
driver for UNIX
·
Single Sign On (SSO) - (2.8.2+ on Windows
platforms only)
Unsupported Functions
The following is a list of the Level 2 functions that are
not supported:
|
SQLDescribeParam
|
SQLExtendedFetch
|
|
SQLParamOptions
|
SQLSetPos
|
|
SQLSetScrollOptions
|
|
Limitations
·
Teradata WITH…BY clauses on SELECT statements
are not supported.
·
UNICODE is currently not supported
·
Please refer to the Restrictions section in the Teradata ODBC Drivers User’s Guide for
details
Architecture
·
No requirement for Teradata CLI
·
ODBC driver is only a client side component, no
server side component required (only the DBMS).
·
In general 2 libraries make up the ODBC driver.
A parser library used for parsing and converting SQL grammar, and the main
driver library.
·
On UNIX the parser component of the ODBC driver
temporarily writes to the /var/tmp directory.
·
ODBC driver communicates across a LAN directly
with the Teradata Gateway software running on nodes
ODBC Extensions not
supported
·
The Teradata ODBC driver only supports the
forward fetch direction, one row at a time (i.e. rowset size = 1). Scrollable Cursors are not supported.
The ODBC cursor library may be used
if an application requires support for functions like SQLExtendedFetch or
additional cursor support.
·
Only column-wise binding is supported, no
row-wise binding
·
Rowids or Bookmarks
·
Use of the Qualifier argument in the catalog
functions
·
There are other implementation notes and
restrictions listed in the Installation
and User Guide.
·
No BLOB data
Section 2: General Good Practices
The following section mentions general practices that are
recommended for obtaining better performance in ODBC enabled applications. Some
of these recommendations may only apply to the use of the Teradata ODBC driver.
Adaptive Programming
Applications should call the ODBC functions SQLGetInfo,
SQLGetTypeInfo, and SQLGetFunctions to determine the features, data types, and
functions supported by the ODBC driver and the DBMS. Once the application
“interrogates” the ODBC driver it adapts
to the capabilities supported by the driver/DBMS combination. Applications can
NOT assume the driver/DBMS combination support all ODBC features.
Persistent
Connections
Efficient applications will establish a connection and the
application will reuse this connection for sending many requests. Ideally one
or more connections will be opened by an application at startup and those
connections will be closed when the application exits. If possible applications
should avoid constantly opening and closing connections to service requests
since this is inefficient, EXCEPT if the Connection Pooling feature is
utilized.
Columns in a Result
Applications should only select the columns of data needed
to perform the required task. This will reduce the amount of data sent across
the network. It also reduces the impact to database resources and resources
that the application and the ODBC driver may need to handle the results.
Result Buffers
If large result set is expected in a LAN environment, the MaxRespSize option should be set to
65477 (for Teradata RDBMS V2R3 and above). This option is defined through a DSN
entry in the ODBC.INI and it specifies the size for Teradata response buffers
to use. Please refer to the Installation
and User Guide for complete information.
Teradata Specific SQL
Custom Teradata applications which only generate Teradata specific SQL syntax should disable
the ODBC driver from parsing SQL requests by calling
SQLSetConnectOption(SQL_NOSCAN). This tells the Teradata ODBC driver to bypass
the parsing of SQL requests, thus improving performance. Be aware that once
this option is set the ODBC driver will not convert any ODBC SQL syntax and
Teradata may not handle this SQL syntax.
Freeing Resources
If an application only needs a subset of rows from the
result set, once those rows are obtained the application should call
SQLFreeStmt(CLOSE/DROP) to release the spool file held by Teradata.
If an application is NOT issuing multi-statement requests
and it only needs a subset of rows form the result set then it should avoid
calling SQLMoreResults and instead it should call SQLFreeStmt(C/D).
Calling SQLFreeStmt allows the driver to immediately close
the spool file. Calling SQLMoreResults forces the ODBC driver to look through
the remaining parcels until an EndStatement parcel is detected.
Use of ODBCINI
(UNIX only)
Set the ODBCINI environment variable specifying where the
.odbc.ini file can be found, this avoids a search into the /etc/passwd file to
get the user’s HOME directory.
Environment Variables
(UNIX only)
Environment Variables must be set to reflect the location of
the ODBC libraries.
|
Operating System(s)
|
Environment
Variable
|
|
HP-UX
|
SHLIB_PATH
|
|
Solaris (Sparc / Intel), MP-RAS
|
LD_LIBRARY_PATH
|
|
AIX
|
LIBPATH
|
So,
if the ODBC driver package was installed in /usr/odbc, then SHLIB_PATH should be set to /usr/odbc/lib.
Retrieving Data
ODBC provides 2 different mechanisms for retrieving data
from the result set.
Applications can call SQLGetData to retrieve data on a
column by column basis. The application calls SQLGetData for each column in the
row it needs to retrieve. If the result set contains a large number of rows
using SQLGetData is very inefficient because the application has to make a call
to SQLGetData for every column in every row.
Binding is the other method. By calling SQLBindCol an
application can bind or specify a buffer to a specific column in the result. At
fetch time the driver automatically transfers the data into the variable buffer
specified by the application. This logic allows applications to effectively
deal with situations where they may execute the same data returning SQL
statement (i.e. SELECT) several times. The application can bind the result set
columns once and then issue the SQL requests to process. Every time a row is
fetched the driver updates the buffers with the values for the columns.
Excessive use of SQLBindCol to bind result set columns maybe expensive because
it causes the driver to allocate memory. The application is also responsible
for releasing or freeing any binding specified, as bindings are not
automatically undone.
Batches
Network traffic may be reduced by issuing a multi-statement
request. By sending multiple SQL statements through a single request you reduce
the number of roundtrips the application makes to perform the same work.
Data Conversion
An ODBC driver may be asked to perform data conversion by
the application when moving data into and out of program variables. This may
occur when servicing calls like
SQLGetData, SQLBindParameter, and SQLBindCol. For example an application that
displays numeric columns may ask the driver to convert the data from a numeric
type to a character.
The driver normally transfers data from the network buffers
to the application variables. Requesting the driver to carry out data conversion
forces the driver to buffer the data and use CPU cycles for the conversion.
Therefore applications may want to minimize data conversion through the ODBC
driver.
Data Truncation
If an application tries to retrieve data into a variable
that is too small to hold it, a warning may be generated by the ODBC driver.
The driver will then need to use CPU cycles to allocate resources so the
warning generated can be serviced.
Cache Metadata
Certain applications should consider caching results from
calls to ODBC catalog functions (i.e. SQLTables, SQLColumns, SQLSpecialColumns,
etc.) to avoid repetitive calls and roundtrips to the DBMS for the same
information.
Multiple vs. Single Execution:
Use SQLPrepare and SQLExecute combination for multiple
execution. Use SQLExecDirect for single
execution.
<code snippet example here>
SQLPrepare
SQLExecute
Loop INSERT INTO table VALUES
SQLDescribeCol vs. SQLColAttributes
Use SQLDescribeCol to retrieve all
the metadata information for a column instead of SQLColAttributes.
Quiet
Mode (3-tier applications)
‘Quiet Mode’ must be set when using
the ODBC Driver in a 3-tier architecture.
DateTime
Format Switching
Do not switch DateTime Format (e.g.,
AAA/III) settings haphazardly.
Understand the implication of changing these in the context of existing
DDL, datatypes and date import/export settings.
OLE-DB
to ODBC Bridge using MSDASQL and ADO Considerations
For DSN-less connections, the connection string must be:
Driver=Teradata;
DBCName=xxx.xxx.xxx.xxx; uid=abcd; pwd=abcd; database=abcd
Use ‘DBCName=’ instead of ‘Server=’.
Internet Applications
In a web-based application, the application might get better
performance when not caching the connection, i.e.; the application can connect
and disconnect for each page instead of having the connection open for the
entire set of pages. The application can
use ODBC Connection Pooling.
Example of Multi-Statement Select
After the result set of the first select in the
multistatement is retrieved using SQLFetch() a call to SQLMoreResults() is
required to retrieve the result set of the second select. So the order of calls is as below:
SQLFetch()
SQLFetch()
....
....<End of result set of statement 1 in
multistatement)
SQLMoreResults()
SQLFetch()
SQLFetch()
.....
....<End of result set of statement 2 in
multistatement)
SQLMoreResults()
SQLFetch()
SQLFetch()
.....
soon...
Its a better ODBC programming approach to check for the
return value of SQLMoreResults() even after SQLFetch() returns SQL_NO_DATA.
Only when SQLMoreResults() returns SQL_NO_DATA it means that the whole result
set has been retrieved.
What level of ODBC API and ODBC SQL conformance do you
require?
Does the application use parameters?
Does the application require data conversion?
How does the application retrieve results? Does it bind columns?
Does the application use functions like SQLGetInfo to find
out what is supported?
Does the application require SQLExtendedFetch or rich
cursors? Does it use the Cursor library?
Does the application support all the SQL_C data types
specified by ODBC? Limitations?
Does the application take advantage of the connection and
statement options defined by ODBC?
What form of SQL does the application generate, does it have
a means for providing pass-through
SQL?
Does the application service a single user or multiple users
?
Does the application reside in the middle tier of a 3-tier
architecture? (executing as a server)
Is the application multi-threaded?
Does it execute in ODBC asynchronous mode?
Section 4: Considerations with a Teradata
3.51-compliant driver (Future)
Parameter Arrays (3.5.1 Enhancement – Not available today)
Use Parameter Arrays instead of
binding single values to SQLBindParameter.
Multiple Environments (3.5.1 Enhancement – Not available
today)
Mulitple Environments can be
allocated in an ODBC 3.51-compliant driver.
SQLSetEnvAttr (2.x Applications migrating to a 3.x ODBC
Driver)
Set the SQL_ATTR_ODBC_VERSION environment attribute to
SQL_OV_ODBC2 for ODBC 2.x applications migrating to ODBC 3.x if no additional
3.x features are being enabled or if no conditional code is being incorporated
to facilitate ODBC 2.x and ODBC 3.x co-existence.
No comments:
Post a Comment