All Forums Tools
kmchelvam 2 posts Joined 09/07
31 Jul 2012
TPT API : Setting DSNName attribute through TPT API script for a source database

 

Hi,

 

        I'm new to Teradata PT API and am using PT API script to intract (Load/Unload) with Teradata.

I am using TD_LOAD operator to load the data in Teradata table and the TPT API script is working 

fine with flat file as a source.

 

Now I have to load the data directly from other RDBMS such as Oracle or SQL Server. 

I generated the TPT script using TPT wizard with SQL Server as source and Teradata as target.

It has generated the TPT script with DSNName attribute. 

How can I set the ODBC connection (DSNName, Password and etc.) string through TPT API script for source connection.

 

Appreciate your help on this.

 

Thanks,

feinholz 1234 posts Joined 05/08
09 Aug 2012

TPTAPI does not use a script, and so referring to TPTAPI in this context is not correct.

TPTAPI is an "API" interface in which you write a C++ program to have direct control over the TPT operators.

So, I am assuming you are using script-based TPT.

What version of TPT are you using?

TPT has the ability to interface to Oracle and SQLServer by using the ODBC operator.

And the ODBC Operator has attributes for specifying the DSN name, username and password.

Please refer to TPT documentation for more information.

 

--SteveF

kmchelvam 2 posts Joined 09/07
22 Aug 2012

Hi Feinholz,

 

Thanks for your reply on this.

 

Sorry I shouldn't have mentioned TPT API script and I'm trying to connect RDBMS (Oracle/SQL Server) from TPT API interface.

You have mentioned that we have to write a C++ program, that means we have to write C++ code using ODBC driver to connect any RDBMS.

 

Thanks,

feinholz 1234 posts Joined 05/08
23 Aug 2012

The TPTAPI interface only connects to Teradata.

It does not connect to Oracle.

If you would like to move data from Oracle to Teradata, then use the script-based TPT, and use our ODBC operator to retrieve data from Oracle and feed the data to a loading operator to load into Teradata.

 

--SteveF

vetriselvan 9 posts Joined 04/14
15 May 2014

Hi Feinholz,
 
I am trying to load data from Oracle to Teradata using ODBC operator but its failing with the below error.
 
If the below format is wrong. Can you please give me the sample variable file for dsnname and connection string
 
Variable File:
-------------
ODBCInstances            = 1,
ODBCPrivateLogName  = 'ABC.txt',
ConnectString               ='test'
ODBCUserName             = 'test',
ODBCPassword             = 'password',
SelectStmt               = 'SQL'
Note: ODBC file has the details of the connectionstring 'test'
TPT script (only ODBC section)
-------------------------------------

        DEFINE OPERATOR EXPORT_OPERATOR
        DESCRIPTION 'TPT ODBC Operator'
        TYPE ODBC
        SCHEMA TABLE_STRUCTURE
        ATTRIBUTES
        (
                VARCHAR TraceLevel='ALL',
                VARCHAR  PrivateLogName   = @ODBCPrivateLogName,
                VARCHAR  DSNname             = @DSNname,
                VARCHAR  UserName             = @ODBCUserName,
                VARCHAR  UserPassword       = @ODBCPassword,
                VARCHAR  SelectStmt             = @SelectStmt
        );
 
 
Log:
 

Using memory mapped file for IPC
TPT_INFRA: TPT04101: Warning: TMSM failed to initialize
Teradata Parallel Transporter Coordinator Version 14.00.00.08
Teradata Parallel Transporter Executor Version 14.00.00.08
Teradata Parallel Transporter Executor Version 14.00.00.08
Teradata Parallel Transporter Load Operator Version 14.00.00.08
LOAD_OPERATOR: private log specified: loadoper_privatelog
LOAD_OPERATOR: connecting sessions
TPT_INFRA: TPT01036: Error: Task (TaskID: 5, Task Name: SELECT_2[0001]) terminated due to the receipt of signal number 11
TPT_INFRA: TPT01037: Error: Task (TaskID: 5, Task Name: SELECT_2[0001]) core dumped
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
Job is running in Buffer Mode
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '19.48 Second(s)'
LOAD_OPERATOR: Start : Thu May 8 11:21:01 2014
LOAD_OPERATOR: End : Thu May 8 11:21:27 2014
Job step MAIN_STEP terminated (status 8)
Job <user_name> terminated (status 8)
Total available memory: 10002340
Largest allocable area: 10002340
Memory use high water mark: 43004
Free map size: 1024
Free map use high water mark: 16
Free list use high water mark: 0
Private log PXCRM:
CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.
Private log TWB_SRCTGT:
MAIN_STEP APPLY_1[0001] LOAD_OPERATOR DBS TDPID_NAME database.tablename
Private log TWB_STATUS:
MAIN_STEP APPLY_1[0001] Success LOAD_OPERATOR 1 1 INITIATE-Started 11:21:01 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP APPLY_1[0001] Success LOAD_OPERATOR 1 1 INITIATE-Ended 11:21:25 24.0000 19.4800 65000 0 0 0 0 0 N Y
MAIN_STEP APPLY_1[0001] Success LOAD_OPERATOR 1 1 TERMINATE-Started 11:21:25 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP APPLY_1[0001] Success LOAD_OPERATOR 1 1 TERMINATE-Ended 11:21:27 2.0000 0.0200 65000 0 0 0 0 0 N Y
Private log TWB_EVENTS:
<user_name>-132,17,0,OperatorEnter,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,,1,0
<user_name>-132,116,5,UtilityName,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TPT Load Operator,1,0
<user_name>-132,0,5,LoadVersionId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,14.00.00.08,1,0
<user_name>-132,115,1,UtilityId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,1,1,0
<user_name>-132,132,5,LoadTdpId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TDPID_NAME,1,0
<user_name>-132,1,5,LoadUserName,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TERADATA_USER_NAME,1,0
<user_name>-132,128,5,LoadDbase,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TERADATA_DB_NAME,1,0
<user_name>-132,21,1,LoadTableId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,1,1,1
<user_name>-132,22,5,LoadTableName,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,database.tablename,1,1
<user_name>-132,19,0,LoadPhase1Begin,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,,1,1
<user_name>-132,135,0,LoadSessEnd,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,,1,0
<user_name>-132,18,1,OperatorExit,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,0,1,0
Private log loadoper_privatelog:
===================================================================
= =
= TERADATA PARALLEL TRANSPORTER =
= =
= LOAD OPERATOR VERSION 14.00.00.08 =
= =
= OPERATOR SUPPORT LIBRARY VERSION 14.00.00.08 =
= =
= COPYRIGHT 2001-2012, TERADATA CORPORATION. ALL RIGHTS RESERVED. =
= =
===================================================================
**** 11:21:01 Processing starting at: Thu May 8 11:21:01 2014
===================================================================
= =
= Module Identification =
= =
===================================================================
Load Operator for SunOS release 5.10 on HOST-NAME-XX.com
LoadMain : 14.00.00.10
LoadCLI : 14.00.00.07
LoadUtil : 14.00.00.02
PcomCLI : 14.00.00.24
PcomMBCS : 14.00.00.01
PcomMsgs : 14.00.00.04
PcomNtfy : 14.00.00.01
PcomPx : 14.00.00.11
PcomUtil : 14.00.00.09
PXICU : 14.00.00.08
TDICU : 14.00.00.02
CLIv2 : 14.00.00.04
===================================================================
= =
= Attribute Definitions =
= =
===================================================================
**** 11:21:02 Options in effect for this job:
OperatorType: Consumer
Instances: 1
Character set: Not specified; will use default
Checkpoint: No checkpoint in effect
Notify: Not enabled
Buffer size: Maximum allowable
Error limit: No limit in effect
Tenacity: 4 hour limit to successfully connect
Sleep: 6 minute(s) between connect retries
Pause Acq: Not in effect
===================================================================
= =
= Column/Field Definition =
= =
===================================================================
Column Name Offset Length Type
============================== ====== ====== ========================
COL_1 0 8 DECIMAL(15,0)
COL_2 8 30 VARCHAR
============================== ====== ====== ========================
INDICATOR BYTES NEEDED: 1
EXPECTED RECORD LENGTH: 41
===================================================================
= =
= Control Session Connection =
= =
===================================================================
**** 11:21:02 Connecting to RDBMS: 'TDPID_NAME'
**** 11:21:02 Connecting with UserId: 'TERADATA_USER_NAME'
===================================================================
= =
= Teradata Database Information =
= =
===================================================================
**** 11:21:04 Teradata Database Version: '14.00.04.10 '
**** 11:21:04 Teradata Database Release: '14.00.04.10 '
**** 11:21:04 Maximum request size supported: 1MB
**** 11:21:04 Session character set: 'ASCII'
**** 11:21:04 Total AMPs available: 60
**** 11:21:04 Data Encryption: supported
**** 11:21:04 Restart log table 'database.LOADOPER_LOGTABLE' exists from previous job
**** 11:21:04 This job is restarting
===================================================================
= =
= Special Session Connection =
= =
===================================================================
**** 11:21:25 Number of sessions adjusted due to TASM: 23
Instance Assigned Connected Result
======== ======== ========= ======================
1 23 23 Successful
======== ======== ========= ======================
Total 23 23 Successful
===================================================================
= =
= Target/Error Table Information =
= =
===================================================================
**** 11:21:25 This job will use the following tables:
Target Table: 'database.tablename'
Error Table 1: 'database.LOADOPER_ERRTABLE1'
Error Table 2: 'database.LOADOPER_ERRTABLE2'
**** 11:21:25 Number of rows sent, last job: 0
**** 11:21:25 Number of bytes sent, last job: 0
===================================================================
= =
= Acquisition Phase =
= =
===================================================================
**** 11:21:25 DML statement for DML Group: 1
INSERT INTO database.tablename ( :COL_1, :COL_2 );
**** 11:21:25 Number of records per buffer for this job: 1567
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================
**** 11:21:25 Logging off all sessions
Instance Cpu Time
======== ================
1 19.48 Seconds
**** 11:21:27 Total processor time used = '19.48 Second(s)'
. Start : Thu May 8 11:21:01 2014
. End : Thu May 8 11:21:27 2014
. Highest return code encountered = '0'.
**** 11:21:27 This job terminated
 
 

feinholz 1234 posts Joined 05/08
16 May 2014

Please check the documentation.
You specified:
ODBCInstances            = 1,
ODBCPrivateLogName  = 'ABC.txt',
ConnectString               ='test'
ODBCUserName             = 'test',
ODBCPassword             = 'password',
SelectStmt               = 'SQL'
And those are not correct names.
(Also, the "SelectStmt" has to actually be a SELECT statement, not just the word "SQL". I am not sure from where you received this information.)
 

--SteveF

vetriselvan 9 posts Joined 04/14
19 May 2014

Hi Fienholz,
The above values are just for sample . But in the script I have mentioned the valid Connect string/User Name and pwd. Also SelectStmt  contains proper SQL  like 'Select EMP_ID from EMP;' So scripts looks fine in the format standpoint. Can you pls tell me whether we can give all the log on information of oracle database in the variable file instead of refering to ODBC file?
 
Thanks!!!
 

feinholz 1234 posts Joined 05/08
19 May 2014

YOu either provide the DSNName, UserName and UserPassword, or you provide the entire connection string (with proper syntax and options) in the ConnectString attribute. Do not do both. I believe the ConnectString information can bypass the information in the odbc.ini file.

--SteveF

vetriselvan 9 posts Joined 04/14
20 May 2014

Correct. If you look at the below TPT script, it has only DSNName, Username and Password. Still I am facing issue due to ODBC setup has not done properly. So I am trying to use ConnectString to bypass the odbc.ini file. Can you help to fix the issue and also with sample TPT variable file with proper ConnectString values?
 
TPT script (only ODBC section)
-------------------------------------
        DEFINE OPERATOR EXPORT_OPERATOR
        DESCRIPTION 'TPT ODBC Operator'
        TYPE ODBC
        SCHEMA TABLE_STRUCTURE
        ATTRIBUTES
        (
                VARCHAR TraceLevel='ALL',
                VARCHAR  PrivateLogName   = @ODBCPrivateLogName,
                VARCHAR  DSNname             = @DSNname,
                VARCHAR  UserName             = @ODBCUserName,
                VARCHAR  UserPassword       = @ODBCPassword,
                VARCHAR  SelectStmt             = @SelectStmt
        );
 
Thank you

feinholz 1234 posts Joined 05/08
20 May 2014

The error I see is this:
 
TPT_INFRA: TPT01036: Error: Task (TaskID: 5, Task Name: SELECT_2[0001]) terminated due to the receipt of signal number 11
TPT_INFRA: TPT01037: Error: Task (TaskID: 5, Task Name: SELECT_2[0001]) core dumped
 
That would not be related to the DSNName, UserName or UserPassord settings, or the ConnectString, or the odbc.ini file.
I cannot provide you with a sample connection string because it is driver dependent.
What ODBC drivers are you using?
We are only certified with the drivers from DataDirect.
Also, on which platform are you running?
The next step (before I can help any further) is for you to download and install the latest patches for TPT 14.0. You are using 14.00.00.08, which is pretty old. A newer version might have fixed the coredump issue.
 

--SteveF

vetriselvan 9 posts Joined 04/14
28 May 2014

My core dump issue got resolved. I am using DataDirect 7.0 Oracle Wire Protocol driver and my OS is "SunOS 5.10 Generic_141444-09 sun4v sparc SUNW,SPARC-Enterprise-T5220". When I execute the script am getting the below error:-

Teradata Parallel Transporter Load Operator Version 14.00.00.08
LOAD_OPERATOR: private log specified: loadoper_privatelog
LOAD_OPERATOR: connecting sessions
Teradata Parallel Transporter ODBC Operator Version 14.00.00.08
EXPORT_OPERATOR: private log specified: odbclog.txt-1
EXPORT_OPERATOR: connecting sessions
EXPORT_OPERATOR: TPT17122: Error: unable to connect to data source
EXPORT_OPERATOR: TPT17101: Fatal error received from ODBC driver:
STATE=IM003, CODE=0,
MSG='[DataDirect][ODBC lib] Specified driver could not be loaded'
EXPORT_OPERATOR: disconnecting sessions
EXPORT_OPERATOR: TPT17124: Error: unable to disconnect from data source
EXPORT_OPERATOR: TPT17101: Fatal error received from ODBC driver:
STATE=08003, CODE=0,
MSG='[DataDirect][ODBC lib] Connection not open'
EXPORT_OPERATOR: Total processor time used = '0.11 Second(s)'
EXPORT_OPERATOR: Start : Wed May 28 10:04:13 2014
EXPORT_OPERATOR: End : Wed May 28 10:04:13 2014
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '19.33 Second(s)'
LOAD_OPERATOR: Start : Wed May 28 10:04:13 2014
LOAD_OPERATOR: End : Wed May 28 10:04:41 2014

Could you please help me on this?

feinholz 1234 posts Joined 05/08
28 May 2014

SQLSTATE 8003 means "connection does not exist".

--SteveF

You must sign in to leave a comment.