Duplicate Select statements in history using ODBC Operator

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Duplicate Select statements in history using ODBC Operator

Hello:

 

I'm using TTU 16.20 and moving data from Netezza to Teradata using the ODBC Operator. The tool we've created is able to move a lot of data and is not experiencing any conversion issues (just a warning saying the driver is not supported). That being said, the Netezza history database is showing two identical SELECT statements being run in the history database after the script is run.

 

Would the script below need to run two SELECTs on the Netezza system for any reason? I guess what I'm trying to figure out if this is expected behavior. This is a small table, but if a second select can be avoided it woudl be preferred especially on large tables. Just not sure if this is needed or not.

 

 

 

DEFINE JOB "Load To Teradata"
(
                DEFINE SCHEMA SourceTable
                (
                COL1 BIGINT
                );

                DEFINE OPERATOR ODBCOperator
                TYPE ODBC
                SCHEMA SourceTable
                ATTRIBUTES (
                                                VARCHAR SelectStmt = 'SELECT COL1 FROM SQL_CLASS."ADMIN".BIGINT_TBL;'
                                                ,VARCHAR DSNName = 'Netezza'
                                                ,VARCHAR UserName = 'admin'
                                                ,VARCHAR UserPassword = '<SOURCE_PASSWORD>'
                );

                 DEFINE OPERATOR StreamOperator
                TYPE Stream
                SCHEMA *
                ATTRIBUTES (
                                                VARCHAR Tdpid = 'td1510'
                                                ,VARCHAR UserName = 'dbc'
                                                ,VARCHAR UserPassword = '<TARGET_PASSWORD>'
                                                ,VARCHAR WorkingDatabase = '"SQL_SANDBOX"'
                                                ,VARCHAR MacroDatabase = '"SQL_SANDBOX"'
                                                ,VARCHAR ErrorTable = '"SQL_SANDBOX"."BIGINT_TBL_ET"'
                                                ,VARCHAR LogTable = '"SQL_SANDBOX"."BIGINT_TBL_tlog"'
                                                ,VARCHAR DateForm = 'INTEGERDATE'
                );

                 APPLY 'INSERT INTO SQL_SANDBOX.BIGINT_TBL(:COL1);'
                TO OPERATOR (StreamOperator[1])
                SELECT * FROM OPERATOR (ODBCOperator[1]);
);


 

--Netezza table

CREATE TABLE SQL_CLASS.ADMIN.BIGINT_TBL 
(COL1 BIGINT)
DISTRIBUTE ON RANDOM;

--Only one row is needed for the test
INSERT INTO SQL_CLASS.ADMIN.BIGINT_TBL VALUES(123);

--Teradata Target Table
CREATE MULTISET TABLE SQL_SANDBOX.BIGINT_TBL
(COL1 BIGINT)
NO PRIMARY INDEX;

 

 

 

 

The driver being used is the Netezza ODBC driver as there are no "official" drivers for Netezza to stream data to Teradata. Netezza version is 7.2.1 and the ODBC Driver is 7.02.00.40845. Anther user reported this issue using TTU 15.10 and TD 15.10. Not 100% sure what Netezza version or driver they were using, but they are probably slightly different than the above.

 

Thank you for your help.

 

2 REPLIES
Highlighted
Enthusiast

Re: Duplicate Select statements in history using ODBC Operator

Also, is the private log under Select Request we are seeing 2 fetches.

 

It looks like the DataBlockSize attribute of the ODBC operator should be adjusted to fine tune for a single fetch. Does this sound about right?  Default is 64KB from what I understand...

 

     ===================================================================

     =                                                                 =

     =                         Select Request                          =

     =                                                                 =

     ===================================================================

**** 03:14:26 This job is submitting the following request:

 

     SELECT COL1 FROM SQL_CLASS."ADMIN".BIGINT_TBL;

 

     RowSet size per fetched data block:  262144

 

ODBCOperator: sending SELECT request

     Total number of fetches: 2

Teradata Employee

Re: Duplicate Select statements in history using ODBC Operator

I would expect an ODBC SQLPrepare request to obtain metadata for the SELECT statement, followed by a SQLExecute of the (same) prepared statement. Not sure how that is reflected in NZ history.

 

And since ODBC doesn't indicate "end of data" until you try to fetch more, I would expect fetch count to be one more than block count.