Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

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.
Highlighted
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Vijai,

Try to use the tbuild -h option to increase the shared memory size as follow:

 tbuild -h 100M -f <TPT script> ...

-h 100M means to use 100 mb of shared memory.

Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Thanks a lot Tony.. just a quick question. does the Shared memory is allocated from client system or is it the database space?

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Vija, it's allocated from client system.

Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Thanks Tony..

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi Tony/Feinholz,

I have a requirement in my project to extract my data from DB2 tables to Teradata without landing the data in disk. Is it possible to write a BTEQ to extract data from DB2 tables and load in Teradata staging tables with TPT.

We have tried executing a TPT script in Mainframe JCL but we dont have the appropriate Lib file so unable to do so. Could you please help/guide on whether a BTEQ script can be written to extract and load the data into Teradata tables with TPT.

Thank you.

Regards,

Deepak

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Just to add, can a TPT job script be executed using tbuild -f from UNIX to extract the data from DB2 Tables to teradata tables. I am currently trying to execute a sample TPT job script using Tbuild and facing the below error message 

Teradata Parallel Transporter Load Operator Version 13.10.00.03

Teradata Parallel Transporter Export Operator Version 13.10.00.03

LOAD_OPERATOR: private log not specified

DATACONN: private log not specified

LOAD_OPERATOR: TPT10309: Invalid 'TenacitySleep' attribute value

LOAD_OPERATOR: Total processor time used = '0.004092 Second '

LOAD_OPERATOR: Start : Tue Nov 19 16:16:35 2013

LOAD_OPERATOR: End   : Tue Nov 19 16:16:35 2013

DATACONN: TPT10551: CLI '224' occurred while connecting to the RDBMS

DATACONN: TPT10507: CLI Error 224: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.

DATACONN: Total processor time used = '0.021271 Second '

DATACONN: Start : Tue Nov 19 16:16:35 2013

DATACONN: End   : Tue Nov 19 16:16:35 2013

Job step MAIN_STEP terminated (status 8)

Job sample terminated (status 8)

 

Any inputs would be of great help!

 


Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

BTEQ cannot extract data from DB2 tables because BTEQ can only interface with a Teradata database.

You can use TPT to extract data from a DB2 table and load the data into Teradata without landing the data to disk. See the "Moving External Data into Teradata Database" chapter in the TPT User Guide for more information.

Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi,

I need load date from oracle database to teradata in linux machine. Do you know, how i can do this with mload script??

Someboy can tell me a example od how i can to connect to oracle database directly from the mload script??

Regards

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

When you say "I need load date from oracle to teradata in linux machine", I assume you meant "I need to load data from oracle to teradata in linux machine".

If my assumption is correct, then you cannot do this with a MultiLoad script because MultiLoad cannot interface with Oracle. MultiLoad can only interface with a Teradata Database.

You can use TPT to extract data from Oracle database and load the data into a Teradata Database. See the "Moving External Data into Teradata Database" chapter in the TPT User Guide for more information.

Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

I am trying to use TPT to move data from a table  Oracle 11 R2 to Teradata 14 using the ODBC operator with Oracle ODBC driver and Teradata Driver

I have linux machine , from this i need connect to Oracle database (unix) and Teradata database (Linux) with a script

I installed Oracle CLient 11.2 in the machine and TTus 14.0

I have configured the odbc.ini and odbcinst.ini and the tnsnames with the connection to Oracle Database ans Teradata

****odbc.ini ***

[ODBC]

InstallDir=/opt/teradata/client/14.00/odbc_32

Trace=0

TraceDll=/opt/teradata/client/14.00/odbc_32/lib/odbctrac.so

TraceFile=/usr/joe/odbcusr/trace.log

TraceAutoStop=0

[ODBC Data Sources]

default=tdata.so

testdsn=tdata.so

[TRAINING]

Driver=Teradata

Description=Teradata running Teradata V1R5.2

DBCName= dir IP

LastUser=dbc

Username=*****

Password=*****

Database=training

DefaultDatabase=training

[AUX_BBDD]

Driver=/opt/teradata/oracle11/11.2.0/lib/libsqora.so.11.1

DSN=Oracle

ServerName=AUX_BBDD

UserID=system

Password=orBD02.11

***********************************

*********odbcinst.ini

[ODBC DRIVERS]

Teradata=Installed

[Teradata]

Driver=/opt/teradata/client/14.00/odbc_32/lib/tdata.so

APILevel=CORE

ConnectFunctions=YYY

DriverODBCVer=3.51

SQLLevel=1

[Oracle]

Description= Oracle ODBC Driver for Oracle

Driver=/opt/teradata/oracle11/lib/libsqora.so.11.1

FileUsage= 1

Driver Logging=7

**********************************************

I did script tpt for loadind data from Oracle and executed with tbuild -f , but this have error

DEFINE JOB CARGA_TABCAERR

DESCRIPTION 'carga de datos'

(

DEFINE SCHEMA TABCAERR

DESCRIPTION 'Schema of Oracle Data Source'

(

        CODMEN VARCHAR(35),

    TITMEN VARCHAR(30),

    TEXMEN VARCHAR(200),

    ICOMEN VARCHAR(11),

    BOTMEN VARCHAR(16),

    DEFMEN DECIMAL(1,0)

);

DEFINE OPERATOR ODBC_Operator

DESCRIPTION 'TPT ODBC Operator'

TYPE ODBC

SCHEMA TABCAERR

ATTRIBUTES

(

VARCHAR PrivateLogName = 'ODBC_OPERATOR',

VARCHAR DSNName = 'AUX_BBDD',

VARCHAR UserName = '*****',

VARCHAR UserPassword = '*****',

VARCHAR SelectStmt = 'select CODMEN,TITMEN,TEXMEN,ICOMEN,BOTMEN,DEFMEN  from cat.tabcaerr;'

);

DEFINE OPERATOR LOAD_OPERATOR

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'loadoper_privatelog',

Varchar WorkingDatabase = 'TRAINING',

VARCHAR TargetTable = 'TABCAERR',

VARCHAR TdpId = 'BAR02201',

VARCHAR UserName = 'DBC',

VARCHAR UserPassword = 'trBD02.11',

VARCHAR ErrorTable1 = 'TRAINING.ERRTABLE1',

VARCHAR ErrorTable2 = 'TRAINING.ERRTABLE2',

VARCHAR LogTable = 'TRAINING.TABCAERR_LOG'

);

APPLY

( 'INSERT INTO training.TABCAERR (:CODMEN,:TITMEN,:TEXMEN,:ICOMEN,:BOTMEN,:DEFMEN);'

)

TO OPERATOR ( LOAD_OPERATOR)

SELECT * FROM OPERATOR (ODBC_Operator);

);

);

***************************

THE EXIT os this execution is :

Teradata Parallel Transporter Version 14.00.00.09

Job log: /opt/teradata/client/14.00/tbuild/logs/root-17.out

Job id is root-17, running on bar02202

Found CheckPoint file: /opt/teradata/client/14.00/tbuild/checkpoint/rootLVCP

This is a restart job; it restarts at step MAIN_STEP.

Teradata Parallel Transporter Load Operator Version 14.00.00.09

LOAD_OPERATOR: private log specified: loadoper_privatelog

Teradata Parallel Transporter ODBC Operator Version 14.00.00.09

ODBC_Operator: private log specified: ODBC_OPERATOR

ODBC_Operator: connecting sessions

ODBC_Operator: TPT17122: Error: unable to connect to data source

ODBC_Operator: TPT17101: Fatal error received from ODBC driver:

              STATE=IM003, CODE=0,

              MSG='[DataDirect][ODBC lib] Specified driver could not be loaded'

ODBC_Operator: disconnecting sessions

ODBC_Operator: TPT17124: Error: unable to disconnect from data source

ODBC_Operator: TPT17101: Fatal error received from ODBC driver:

              STATE=08003, CODE=0,

              MSG='[DataDirect][ODBC lib] Connection not open'

ODBC_Operator: Total processor time used = '0 Second(s)'

ODBC_Operator: Start : Fri Nov 29 10:11:54 2013

ODBC_Operator: End   : Fri Nov 29 10:11:54 2013

LOAD_OPERATOR: connecting sessions

LOAD_OPERATOR: preparing target table

LOAD_OPERATOR: entering Acquisition Phase

LOAD_OPERATOR: disconnecting sessions

LOAD_OPERATOR: Total processor time used = '0.25 Second(s)'

LOAD_OPERATOR: Start : Fri Nov 29 10:11:54 2013

LOAD_OPERATOR: End   : Fri Nov 29 10:11:57 2013

Job step MAIN_STEP terminated (status 12)

Job root terminated (status 12)

Can you tell me , what I can load the Driver from script JOb /tbuild) from read in Oracle database ??