TPT API : Setting DSNName attribute through TPT API script for a source database

Tools

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,

11 REPLIES
Teradata Employee

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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,

Teradata Employee

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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
Enthusiast

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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

Teradata Employee

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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
Enthusiast

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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!!!

Teradata Employee

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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
Enthusiast

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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

Teradata Employee

Re: TPT API : Setting DSNName attribute through TPT API script for a source database

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