Using Teradata PT To copy from other databases

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

Re: Using Teradata PT To copy from other databases

Yes I think issue with ODBC connection. Can you please give the format of Connection string which we can use in variable file? I will try with this option instead of DSN Name. Pls see the bingary log below :

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 infbiftp 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:

infbiftp-132,17,0,OperatorEnter,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,,1,0
infbiftp-132,116,5,UtilityName,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TPT Load Operator,1,0
infbiftp-132,0,5,LoadVersionId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,14.00.00.08,1,0
infbiftp-132,115,1,UtilityId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,1,1,0
infbiftp-132,132,5,LoadTdpId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TDPID_NAME,1,0
infbiftp-132,1,5,LoadUserName,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TERADATA_USER_NAME,1,0
infbiftp-132,128,5,LoadDbase,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,TERADATA_DB_NAME,1,0
infbiftp-132,21,1,LoadTableId,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,1,1,1
infbiftp-132,22,5,LoadTableName,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,database.tablename,1,1
infbiftp-132,19,0,LoadPhase1Begin,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,,1,1
infbiftp-132,135,0,LoadSessEnd,MAIN_STEP,LOAD_OPERATOR,1,2014-05-08,,1,0
infbiftp-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

Hope this helps to figure out the issue.
Enthusiast

Re: Using Teradata PT To copy from other databases

Can you please help me to fix the issue?

mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

I assumed you're talking about the attribute "ConnectString".  You can refer to the Teradata PT manual for description.   Basically,  it is optional attribute that specifies an alternative method for connecting to the data source.  When it is used, the operator will ignore the values set in the following 3 attributes:  "DSNName", "UserName" and "UserPassword".

This string can contain any valid connect and/or driver information that is acceptable to the ODBC driver that you use.   The string specified in the attribute "ConnectString" will override the default setting in the ODBC initialization file.

We certify the product with Data Direct ODBC driver.  What is the ODBC driver that you are using? 

What is the OS of the machine you use? 

How do you define the DSN in the file "odbc.ini"?

Can I see you how you set up value for the attributes "DSNName", "UserName", "UserPassword" and "ConnectString" in the script or job variable file?

Can you give us the snap shot of all the environment variables prior to the run?

Enthusiast

Re: Using Teradata PT To copy from other databases

Yes correct. I mentioned about "ConnectString". I am using DataDirect odbc driver and Solaris.

TPT script
-----------

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
);

Variable file:
--------------

/**********************************/
/* Values for ODBC operator */
/*********************************/
ODBCInstances = 1,
ODBCPrivateLogName = 'abc.txt',
DSNname = 'dsnname',
ConnectString ='ConnectString'
ODBCUserName = 'username',
ODBCPassword = 'password',
SelectStmt = 'select * from abc;'

Is there anyway to give all the log on information (for Oracle) in the variable file itself instead of referring to ODBC file? If yes please provide sample variable file.

Thanks !!!
mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

Based on what you gave, it looks like the ODBC operator will use 'dsnname', 'username' and 'userpassword' to connect an Oracle session.

Have you defined the 'dsnname' in the file odbc.ini, located under the directory where the Data Direct driver is installed?  You must define the 'dsnname' before you can use it in a TPT script.

Before you run TPT script, you must set and export the following environment variables: ODBCINI and LD_LIBRARY_PATH.

For example, if you install the Data Direct ODBC driver under the directory /home/DataDirect, then the environment variables will be set as follows:

        ODBCINI=/home/DataDirect/odbc.ini

        LD_LIBRARY_PATH=/home/DataDirect/lib:$LD_LIBRARY_PATH

Enthusiast

Re: Using Teradata PT To copy from other databases

I have defined the DSN name in the odbc.ini file and also assigned the environment variables ODBCINI and LD_LIBRARY_PATH.  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". Now, 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?

Enthusiast

Re: Using Teradata PT To copy from other databases

Nice article.. can I use Tpt to transfer data from teradata to Oracle?
mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

Unfortunately, TPT cannot transfer data fromTeradata to Oracle.  

Enthusiast

Re: Using Teradata PT To copy from other databases

Hello, could you help me with the error I am receiving?

Teradata Parallel Transporter Load Operator Version 15.00.00.00

W_1_o_other_odbc: private log not specified

Teradata Parallel Transporter ODBC Operator Version 15.00.00.00

W_0_o_other_odbc: private log not specified

W_0_o_other_odbc: connecting sessions

W_0_o_other_odbc: TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5701,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'.'

W_0_o_other_odbc: TPT17176: Info: Message received from ODBC driver:

STATE=01000, CODE=5703,

MSG='[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.'

W_0_o_other_odbc: TPT17175: Warning: Message received from ODBC driver:

STATE=S1092, CODE=0,

MSG='[Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier'W_1_o_other_odbc: connecting sessions

W_1_o_other_odbc: preparing target table

W_1_o_other_odbc: entering Acquisition Phase

W_0_o_other_odbc: sending SELECT request

W_0_o_other_odbc: data retrieval complete

W_0_o_other_odbc: Total Rows Exported:  0

 

mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

Based on what you showed me, the messages from driver are not errrors.  They are informative message and warning.   The first two are informative and the last one is a warning.  

A session is successfully logged on and the SELECT query seems to  perform successfully.

I'm not sure what the problem is that your are seeking help here.