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) SELECT * FROM OPERATOR (ODBCOperator); );
--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.
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
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.