Recent comments
Introduction to the Teradata Parallel Transporter Operators
The Teradata Parallel Transporter (Teradata PT) operators play a vital role in high-speed data extraction and loading geared towards the Teradata Database. Besides interfacing with the Teradata Database, some of the Teradata PT operators provide access to external sources such as files, ODBC-compliant DBMS, and message-oriented middleware.
There are four different types of operators: 1) producer operators, 2) consumer operators, 3) standalone operators, and 4) user-written custom operators.
Producer Operators
Producer operators read data from a source and send the data to the Teradata PT data streams. Data streams are in-memory buffers that allow the data to be passed from a producer operator to a consumer operator without landing the data to disk. Teradata PT provides the following producer operators:
• Export operator: Utilizes multiple sessions of the Teradata FastExport protocol to extract large volumes of data at fast speeds from one or more tables on the Teradata Database.
• SQL Selector operator: Uses a single Teradata SQL session to extract low volumes of data from a table on the Teradata Database. The SQL Selector operator has two features not available in the Export operator. The SQL Selector operator can extract data in “field mode” (i.e. columns in VARCHAR format) and pass the data to the Data Connector operator in order to have the data written in delimited format to an external file. The SQL Selector operator can extract LOB data.
• Data Connector operator: As a producer operator, the Data Connector operator reads data in parallel from multiple sources such as files, named pipes, access modules, and message queue systems.
• FastLoad INMOD Adapter operator: Reads data from any Teradata FastLoad INMOD routines. The INMOD routines are user-written. The INMOD routines preprocess the data before the data are passed to the FastLoad INMOD Adapter operator.
• MultiLoad INMOD Adapter operator: Reads data from a Teradata MultiLoad or TPump INMOD routine. The INMOD routine is written by the user. The INMOD routines preprocess the data before the data are passed to the MultiLoad INMOD Adapter operator.
• ODBC operator: Reads data from many ODBC-compliant DBMS, such as Oracle, SQL Server, and DB2.
Consumer Operators
Consumer operators “consume” data from the Teradata PT data streams and send data to the Teradata Database or files. Teradata PT provides the following consumer operators:
• Load operator: Utilizes multiple sessions of the Teradata FastLoad protocol to insert large volumes of data at fast speeds into an empty table on the Teradata Database.
• Update operator: Uses multiple sessions of the Teradata MultiLoad protocol to perform Inserts, Updates, Deletes, and Upserts into empty or populated tables (up to 5 tables concurrently) on the Teradata Database.
• Stream operator: Uses multiple Teradata SQL sessions to perform Inserts, Updates, Deletes, and Upserts into empty or populated tables (up to 127 tables concurrently) on the Teradata Database without locking the tables. Locking occurs at the row level.
• SQL Inserter operator: Uses a single Teradata SQL session to insert low volumes of data into an empty or populated table on the Teradata Database. The SQL Inserter operator has a feature not available in the Load, Update, or Stream operator. The SQL Inserter operator can load LOB data.
• Data Connector operator: As a consumer operator, the Data Connector operator writes data to files or access modules.
• FastExport OUTMOD Adapter operator: Writes data to a Teradata FastExport OUTMOD routine. The OUTMOD routine is written by the user. The OUTMOD routines post process the data.
Standalone Operators
Standalone operators perform unique functions that do not involve sending data to, or receiving data from, the Teradata PT data streams. Teradata PT provides the following standalone operators:
• DDL operator: Sends SQL requests to the Teradata Database to perform setup operations prior to running load or export tasks. For example, before loading the data using the Load operator use the DDL operator to send the DROP TABLE and CREATE TABLE requests to ensure the target table is empty.
• OS Command operator: Sends commands to the client OS. Commands can be operating system commands, executables, or shell scripts.
• Load operator: As a standalone operator, the Load operator sends a request to the Teradata Database to apply the data on the target table. For example, when there is no more data in a staged loading, use the Load operator as a standalone operator to apply the data already in the target table.
• Update operator: As a standalone operator, the Update operator performs the Delete task on a single table on the Teradata Database. The Delete task performs faster than using a single DELETE SQL statement when a large percentage of rows in a table need to be deleted, such as deleting all rows with a transaction date prior to a certain date.
Custom Operators
Custom operators are user-developed operators that must follow the requirements of the Teradata PT operator interface. Custom operators must be written in C or C++ programming language. Teradata PT provides a sample custom operator when you install Teradata PT.
I have a big file(50GB) sitting in UNIX which is generated from TPT fast export. The file contains sales details of the individuals. From this file we are generating separate files for each distinct individual using UNIX commands. For Example
Individual ID SalesAmt
1 50
2 60
1 70
3 80
We are generating 3 files here for 3 distinct individuals.
can we do this while we generate the file itself using TPT fast export. I heard about OUTMOD routine option but don't know how to use this.
Please help.
Thanks in advance.
You can use the TPT FastExport OUTMOD Adapter operator with your user-written OUTMOD library.
The TPT FastExport OUTMOD Adapter operator can pass the data to your user-written OUTMOD library.
Your user-written OUTMOD library can generate separate files for each distinct individual.
See the TPT Reference manual for more information about the TPT FastExport OUTMOD Adapter operator.
See the TPT sample user guide #12 for a sample TPT script that uses the TPT FastExport OUTMOD
Adapter operator. The TPT sample user guide is available after you have installed the TPT
software.
See the Teradata FastExport manual for information on how to create and compile your
user-written OUTMOD library.
Your user-written OUTMOD library should be compatible with TPT.
Thanks a lot Tony. Could you have all these documents available online, that would be a great help.
The documents are available online at this URL:
http://www.info.teradata.com/templates/eSrchResults.cfm?prodline=&txtpid=&txtrelno=&txtttlkywrd=TTU14.10&rdsort=Title&srtord=Asc&wNew=True&nm=Teradata+Tools+and+Utilities+14.10
Thanks a lot. Its a great help.
Hi Tony,
I am trying to develop an outmod c script, and I am getting below error.
FILE_WRITER: TPT19501 Can not load OUTMOD module. 'dlopen' failed, reason: ''
EXPORT_OPERATOR: connecting sessions
EXPORT_OPERATOR: disconnecting sessions
EXPORT_OPERATOR: Total processor time used = '3.03 Second(s)'
Could you please suggest why I am getting this error?
Below is the c program.
#include <stdio.h>
#include <string.h>
#include <errno.h>
#include <stdlib.h>
#include<sys/stat.h>
struct in_struct
{
char FIELD1 [2048];
};
struct out_rec
{
char FIELD1 [2048];
};
FILE *file1_fp;
#define EQUAL 0
#define NUM_OF_RCDS_WRITTEN 1
#define BAD_OPEN 50
#define BAD_WRITE 52
#define FILE_CTR 1
int _dynamn( entrytype,
stmtno,
resplen,
resprec,
outlen,
outrec
)
int *entrytype;
int *stmtno;
int *resplen;
struct in_struct *resprec;
int *outlen;
char *outrec;
{
struct out_rec out_struct ;
struct out_rec *out_struct_ptr = &out_struct;
char file_name [150];
static int Record_Size = 0;
static int File_Size = 0;
static int Num_Files = 0;
static unsigned long int tot_record_ctr = 0;
switch(*entrytype)
{
case 1:
case 6:
strcpy(file_name,getenv( "outmod_file_name" ));
if ((file1_fp=fopen(file_name,"w")) == NULL)
{
fprintf(stderr,"%s %d:Bad Open on file <%s>,errno=%d\n",
__FILE__,__LINE__,file_name,errno);
fflush(stderr);
exit(BAD_OPEN);
}
break;
case 2:
fclose(file1_fp);
printf("\nThe extract is done \n");
printf("\nTotal outmod_records Written: %d \n",tot_record_ctr);
break;
case 3:
Record_Size=*resplen;
File_Size=Record_Size+1;
*resplen = 0;
memcpy(out_struct_ptr->FIELD1,resprec->FIELD1, Record_Size);
out_struct_ptr->FIELD1[Record_Size] = '\n';
if ( fwrite(out_struct_ptr,File_Size,1,file1_fp) != NUM_OF_RCDS_WRITTEN )
{
fprintf(stderr,"%s %d:Bad Write on file <%s>,errno=%d\n",
__FILE__,__LINE__,file_name,errno);
perror(file_name);
fflush(stderr);
exit(BAD_WRITE);
}
tot_record_ctr++;
break;
case 4:
break;
case 5:
if ((file1_fp=freopen(file_name,"w",file1_fp)) == NULL)
{
fprintf(stderr,"%s %d:Bad ReOpen on file <%s>,errno=%d\n",
__FILE__,__LINE__,file_name,errno);
fflush(stderr);
exit(BAD_OPEN);
}
break;
}
return(0);
}
Can you send me the TPT job script?
Below is the TPT script.
DEFINE JOB J_TABLE_NAME
(
DEFINE SCHEMA S_TABLE_NAME
DESCRIPTION 'LOADING for TABLE_NAME'
(
COL1 VARCHAR(30),
COL2 VARCHAR(10)
);
DEFINE OPERATOR EXPORT_OPERATOR
TYPE EXPORT
SCHEMA S_TABLE_NAME
ATTRIBUTES
(
VARCHAR TraceLevel ='NONE',
VARCHAR TdpId =@TdpId,
VARCHAR UserName =@UserName,
VARCHAR UserPassword =@PassWord,
VARCHAR PrivateLogName ='TABLE_NAME_export_operator',
VARCHAR SelectStmt ='SELECT
Col1,
Col2
FROM DATABASENAME.TABLE_NAME
'
'
);
DEFINE OPERATOR FILE_WRITER
DESCRIPTION 'TPT DATA CONNECTOR OPERATOR'
TYPE FASTEXPORT OUTMOD
SCHEMA S_TABLE_NAME
ATTRIBUTES
(
VARCHAR TraceLevel ='NONE',
VARCHAR PrivateLogName ='TABLE_NAME_file_writer',
VARCHAR DirectoryPath ='$HOME/TPT/',
VARCHAR FileName ='TABLE_NAME.lst',
VARCHAR Format ='Delimited',
VARCHAR OpenMode ='Write',
VARCHAR TextDelimiter ='~_~',
VARCHAR OutmodName='$HOME/TPT/testC.c'
);
APPLY TO OPERATOR (FILE_WRITER)
select
COL1,
COL2
from OPERATOR (EXPORT_OPERATOR);
);
The OutmodName attribute refers to the name of your FastExport OUTMOD library name, not the OUTMOD source file.
Hi Tony,
Thanks for your quick response. I am bit confused here. The OUTMOD library name means the out file created from the c program(after compiling) , do you have any sample scripts which I can refer.
Yes, the OUTMOD library name is the file created from the c program (after compiling).
You can refer to the sample "uguide12.txt" script.
The script is in the TPT install directory under the "sample/userguide" sub-directory.
Hi Tony,
I have used below command to compile the c program.
gcc -I/usr/include -shared -fPIC testC.c -o testOUTMOD
and same testOUTMOD name is used in OutmodName attribute.
But I am getting the same error. my os is linux OS.
Try to use the fully qualified path name to the testOUTMOD file for the OutmodName attribute.
Example:
VARCHAR OutmodName = '/dir1/dir2/testOUTMOD',
I did the same, but no luck :(
Can you set the TPT FastExport OUTMOD Adapter operator's TraceLevel attribute to "ALL" in the TPT job script?
Example:
VARCHAR TraceLevel = 'ALL',
Re-run the TPT job script.
After the job finishes, use the TPT "tlogview" command to extract the TPT operator private logs.
Example:
tlogview -j <TPT job id> -f "*" -g -o tpt_logs.txt
Send me the "tpt_logs.txt" file.
Also, send the output of the directory listing that contains the "testOUTMOD" library file.
Hi Tony, I am seeing the below error from the log file tpt_logs.txt
===================================================================
= =
= Special Session Connection =
= =
===================================================================
**** 22:42:22 Maximum number of special sessions requested: All
**** 22:42:22 Minimum number of special sessions required: 1
SG_VARtypesFlag=1, NumIndBytes=1, TotalFixedLength=41
loading OUTMOD='$HOME/TPT/testOUTMOD'
Could not load OUTMOD! dlopen failed!
$HOME/testOUTMOD: wrong ELF class: ELFCLASS64
Setting exit code = 12.
pmClose entry <<<<
Closing file=TABLE_NAME.lst
closed via default paramod
Record count=0
pmrwfmtWtext entry <<<<
pmunxWBuf entry <<<<
FileInfo->Buffer contents
hex: Hexadecimal formatted display from address F5F9D018 for 0 bytes.
!WARNING! Write of empty buffer bypassed
pmUnxDskClose entry <<<<
pmDetach entry <<<<
Detaching default Access Module
entry
pmShutDown entry <<<<
Shutting down API
Closing logs
and listing of the directory.
-rw-r----- 1 devuser1 devuser1 4744 May 2 04:03 testC.o
-rwxr-x--- 1 devuser1 devuser1 6299 May 2 19:05 a.out
-rw-r----- 1 devuser1 devuser1 3213 May 2 19:51 testC.c
-rwxrwxrwx 1 devuser1 devuser1 9320 May 2 20:08 testC
-rwxr-x--- 1 devuser1 devuser1 9320 May 2 20:09 testOUTMOD
-rw-r----- 1 devuser1 devuser1 1706 May 2 20:09 testOutMode.ksh
Try to re-compile the testOUTMOD as a 32-bit library.
TPT supports 32-bit library.
I tried below command and getting "cc1: error: unrecognized command line option "-m31"
gcc -I/usr/include -m31 -shared -fPIC testC.c -o testOUTMOD
is there anything I need to do with gcc?
Try the "-m32" option.
Now I am getting beloe error.
In file included from /usr/include/features.h:352,
from /usr/include/stdio.h:28,
from testC.c:1:
/usr/include/gnu/stubs.h:7:27: error: gnu/stubs-32.h: No such file or directory
It looks like the "gnu/stubs-32.h" file does not exist in your include search path but I am not sure.
Yes, I checked, it is not there, but I could see gnu/stubs-64.h
Try to install the "glibc-devel-32bit" package.
Hi Tony,
I have installed the glibc-devel-32bit and got the below error message.
TPT_INFRA: TPT01036: Error: Task (TaskID: 4, Task Name: INSERT_1[0001]) terminated due to the receipt of signal number 11
But When I check the log its not showing any errormessage other than above.
Allocating buffer size=262144
Schema column count=2
ColumnHandleList Type Ind Length Offset VAR NC D M
151706584 7 1 30 0 1 1 1
151730056 7 1 10 30 1 1 1
SG_VARtypesFlag=1, NumIndBytes=1, TotalFixedLength=41
loading OUTMOD='/ngs/app/ivndrptd/TPT/testOUTMOD'
Resolving entrypoint '_dynamn'
Done with loading the outmod.
omodaddr: f768369c
I think there is a problem in my C program, but I gont get any error msg while compiling. please suggest.
Hi Tony, I have modified C prgram. its running now. but its not writting anything in the file which is being generated in C program. it is directly writting into the file which I mentioned in the TPT script as per select statement.
when I checked log I am seeing below content. but SQL is returning 1 row from TPT.
FileInfo->Buffer contents
hex: Hexadecimal formatted display from address F6000018 for 0 bytes.
!WARNING! Write of empty buffer bypassed
pmUnxDskGetPos entry <<<<
And I am confused after seeing below message in log. why do I have 3 check points. Is there anything I need to change? please suggest.
CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.
CheckPoint No. 1 started.
CheckPoint No. 1 finished successfully.
CheckPoint No. 2 started.
CheckPoint No. 2 finished successfully.
CheckPoint No. 3 started.
CheckPoint No. 3 finished successfully.
Try this:
* Don't specify a value for the TPT FastExport OUTMOD Adapter operator's FileName attribute in the TPT job script.
The FileName attribute is optional.
The 3 checkpoints are internal and normal in TPT.
No Luck :( gettimg new error.
FILE_WRITER: TPT19426 pmWrite failed. DataConnector API not initialized (26)
FILE_WRITER: TPT19306 Fatal error writing data.
Resolving entrypoint '_dynamn'
Done with loading the outmod.
omodaddr: f76df40c
OUTMOD returned, req=1, OutLen=0, InLen=0
entry
OUTMOD returned, req=4, OutLen=0, InLen=0
entry
SYSTEM_BUFFERMODE: 'no'
Data from PX_GetRow: SH=166886228, ColCount=2
Schema: col=0, Ind=0, Len=10, off=3
Schema: col=1, Ind=1, Len=0, off=15
Hexadecimal formatted display from address F68C2C42 for 15 bytes.
0000 40 0A 00 32.30 31 32 2D.30 37 2D 30.36 00 00 *@..2012-07-06.. *
!ERROR! API not initialized
!ERROR! on record 1
pmWrite rc=26
'Access Module API not initialized (call pmInit)'
Setting exit code = 12.
entry
OUTMOD returned, req=2, OutLen=0, InLen=0
Closing logs
Even I have tried not to write anything in the file, still getting error. I feel its not going to case 3:("where we Process response record)
Can you set the TPT FastExport OUTMOD Adapter's TraceLevel attribute to 'ALL'?
Example:
VARCHAR TraceLevel = 'ALL',
Then, re-run the TPT job and send me the entire TPT FastExport OUTMOD Adapter operator's private log.
Private log TABLE_NAME_file_writer-1:
==========================================================================
= =
= TERADATA PARALLEL TRANSPORTER =
= =
= FASTEXPORT OUTMOD ADAPTER OPERATOR VERSION 13.10.00.05 =
= =
= FastExport OUTMOD Adapter UTILITY LIBRARY VERSION 13.10.00.17 =
= =
= COPYRIGHT 2001-2010, Teradata Corporation. ALL RIGHTS RESERVED. =
= =
==========================================================================
Operator name: 'FILE_WRITER' instance 1 of 1 [Consumer]
**** 19:45:17 Processing starting at: Wed May 8 19:45:17 2013
==========================================================================
= =
= Operator module static specifics =
= =
= Compiled for platform: '32-bit LINUX' =
= Operator module name:'feoutop', version:'13.10.00.05C' =
= =
= pmdcomt_HeaderVersion: 'Common 13.10.00.10' - packing 'pack (push, 1)' =
= pmddamt_HeaderVersion: 'Common 13.10.00.01' - packing 'pack (push, 1)' =
= =
==========================================================================
==========================================================================
= =
= > General attribute Definitions < =
= =
= TraceLevel: 'ALL' =
= >> RESTART MODE << =
= EndianFlip: 'NO (defaulted)' =
= IndicatorMode: 'NO (defaulted)' =
= SYSTEM_CharSetId: 'UTF8' =
= =
==========================================================================
LITTLE ENDIAN platform
Operator module name:'feoutop', source version:'13.10.00.02'
Schema column count=2
ColumnHandleList Type Ind Length Offset VAR NC D M
167758808 7 1 30 0 1 1 1
167780888 7 1 3 30 1 1 1
SG_VARtypesFlag=1, NumIndBytes=1, TotalFixedLength=34
loading OUTMOD='$HOME/TPT/testOUTMOD'
Resolving entrypoint '_dynamn'
Done with loading the outmod.
omodaddr: f762c40c
OUTMOD returned, req=1, OutLen=0, InLen=0
entry
OUTMOD returned, req=5, OutLen=0, InLen=0
entry
OUTMOD returned, req=4, OutLen=0, InLen=0
entry
SYSTEM_BUFFERMODE: 'no'
Data from PX_GetRow: SH=167750484, ColCount=2
Schema: col=0, Ind=0, Len=10, off=3
Schema: col=1, Ind=0, Len=1, off=15
Hexadecimal formatted display from address F680FCEE for 16 bytes.
0000 00 0A 00 32.30 31 32 2D.30 37 2D 30.36 01 00 59 *...2012-07-06..Y*
!ERROR! API not initialized
!ERROR! on record 1
pmWrite rc=26
'Access Module API not initialized (call pmInit)'
Setting exit code = 12.
entry
OUTMOD returned, req=2, OutLen=0, InLen=0
Closing logs
===================================================================
= =
= TERADATA PARALLEL TRANSPORTER =
= =
= EXPORT OPERATOR VERSION 13.10.00.05 =
= =
= OPERATOR SUPPORT LIBRARY VERSION 13.10.00.06 =
= =
= COPYRIGHT 2001-2011, TERADATA CORPORATION. ALL RIGHTS RESERVED. =
= =
===================================================================
**** 19:45:17 Processing starting at: Wed May 8 19:45:17 2013
===================================================================
= =
= Module Identification =
= =
===================================================================
Export Operator for Linux release 2.6.32-300.39.2.el5uek on host.com
ExportMain : 13.10.00.11
ExportCLI : 13.10.00.09
ExportUtil : 13.10.00.02
PcomCLI : 13.10.00.19
PcomMBCS : 13.00.00.02
PcomMsgs : 13.01.00.07
PcomNtfy : 13.00.00.03
PcomPx : 13.10.00.18
PcomUtil : 13.10.00.09
PXICU : 13.10.00.06
TDICU : 13.10.00.02
CLIv2 : 13.10.00.02
===================================================================
= =
= Attribute Definitions =
= =
===================================================================
**** 19:45:18 Options in effect for this job:
OperatorType: Producer
Instances: 1
Character set: 'UTF8'
238,1 67%
Instances: 1
Character set: 'UTF8'
Checkpoint: No checkpoint in effect
Notify: Not enabled
Restart: Yes
Tenacity: 4 hour limit to successfully connect
Sleep: 6 minute(s) between connect retries
Date format: INTEGERDATE
Blocksize: Maximum allowable
OutLimit: No limit in effect
===================================================================
= =
= Column/Field Definition =
= =
===================================================================
Column Name Offset Length Type
============================== ====== ====== ========================
COL1 0 30 VARCHAR
COL2 32 3 VARCHAR
============================== ====== ====== ========================
INDICATOR BYTES NEEDED: 1
EXPECTED RECORD LENGTH: 38
Please let me know if you need further details. Thanks.
The TPT log shows the job is in the restart mode.
Can you re-run the TPT job from the beginning?
Step 1: Delete the TPT job's checkpoint file(s) in the TPT "checkpoint" directory.
Note: The TPT "checkpoint" directory is located in the $TWB_ROOT directory.
The default value for $TWB_ROOT is /opt/teradata/client/13.10/tbuild.
Step 2: Run the TPT job.
Step 3: Send me the entire TPT FastExport OUTMOD Adapter operator's private log.
I removed thr check point file and rerun the TPT again.
pls find below the log.
Private log TWB_STATUS:
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 INITIATE-Started 02:15:15 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 INITIATE-Started 02:15:15 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 INITIATE-Ended 02:15:38 23.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 INITIATE-Ended 02:15:38 23.0000 2.1900 65000 0 0 0 0 0 N Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 EXECUTE-Started 02:15:38 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 EXECUTE-Started 02:15:38 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 CHECKPOINT-Started 02:15:38 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 CHECKPOINT-Started 02:15:38 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 CHECKPOINT-Ended 02:15:39 1.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 CHECKPOINT-Ended 02:15:39 1.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 CHECKPOINT-Started 02:15:39 1.0000 0.0200 65000 1 0 2 0 0 Y Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 TERMINATE-Started 02:15:39 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP INSERT_1[0001] Success FILE_WRITER 1 1 TERMINATE-Ended 02:15:39 0.0000 0.0000 65000 0 0 0 0 0 N Y
MAIN_STEP SELECT_2[0001] System Error EXPORT_OPERATOR 1 1 CHECKPOINT-Ended 02:15:40 2.0000 0.0200 65000 1 0 2 0 0 Y Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 TERMINATE-Started 02:15:40 0.0000 0.0000 65000 0 0 0 0 0 Y Y
MAIN_STEP SELECT_2[0001] Success EXPORT_OPERATOR 1 1 TERMINATE-Ended 02:16:00 20.0000 0.0300 65000 0 0 0 0 0 Y Y
Private log TWB_EVENTS:
JOBID-77,17,0,OperatorEnter,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
JOBID-77,116,5,UtilityName,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,TPT Export Operator,2,0
JOBID-77,8,5,ExportVersionId,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,13.10.00.05,2,0
JOBID-77,115,1,UtilityId,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,3,2,0
JOBID-77,131,5,ExportTdpId,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,XXXXX.com,2,0
JOBID-77,9,5,ExportUserName,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,c1133700,2,0
JOBID-77,127,5,ExportDbase,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,C1133700,2,0
JOBID-77,86,0,ExportBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
JOBID-77,88,5,ExportReqSubmitBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09, select col1,cast(col2 as varchar(1)) from database.TEST_OUTMOD ,2,0
JOBID-77,89,0,ExportReqSubmitEnd,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
JOBID-77,99,1,ExportStmtCount,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,1,2,0
JOBID-77,100,1,ExportBlockCount,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,2,2,0
JOBID-77,90,0,ExportReqFetchBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
JOBID-77,92,0,ExportStmtFetchBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
JOBID-77,94,1,ExportStmtNumber,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,1,2,0
JOBID-77,95,1,ExportStmtBlockCount,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,2,2,0
JOBID-77,93,0,ExportStmtFetchEnd,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
JOBID-77,96,1,ExportStmtRowsFetched,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,2,2,0
JOBID-77,91,0,ExportReqFetchEnd,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0
Private log TABLE_NAME_file_writer-1:
==========================================================================
= =
= TERADATA PARALLEL TRANSPORTER =
= =
= FASTEXPORT OUTMOD ADAPTER OPERATOR VERSION 13.10.00.05 =
= =
= FastExport OUTMOD Adapter UTILITY LIBRARY VERSION 13.10.00.17 =
= =
= COPYRIGHT 2001-2010, Teradata Corporation. ALL RIGHTS RESERVED. =
= =
==========================================================================
Operator name: 'FILE_WRITER' instance 1 of 1 [Consumer]
**** 02:15:15 Processing starting at: Thu May 9 02:15:15 2013
==========================================================================
= =
= Operator module static specifics =
= =
= Compiled for platform: '32-bit LINUX' =
= Operator module name:'feoutop', version:'13.10.00.05C' =
= =
= pmdcomt_HeaderVersion: 'Common 13.10.00.10' - packing 'pack (push, 1)' =
= pmddamt_HeaderVersion: 'Common 13.10.00.01' - packing 'pack (push, 1)' =
= =
==========================================================================
==========================================================================
= =
==========================================================================
= =
= > General attribute Definitions < =
= =
= TraceLevel: 'ALL' =
= EndianFlip: 'NO (defaulted)' =
= IndicatorMode: 'NO (defaulted)' =
= SYSTEM_CharSetId: 'UTF8' =
= =
==========================================================================
LITTLE ENDIAN platform
Operator module name:'feoutop', source version:'13.10.00.02'
Schema column count=2
ColumnHandleList Type Ind Length Offset VAR NC D M
160517080 7 1 30 0 1 1 1
160539536 7 1 3 30 1 1 1
SG_VARtypesFlag=1, NumIndBytes=1, TotalFixedLength=34
loading OUTMOD='$HOME/TPT/testOUTMOD'
Resolving entrypoint '_dynamn'
Done with loading the outmod.
omodaddr: f769554c
OUTMOD returned, req=1, OutLen=0, InLen=0
entry
OUTMOD returned, req=4, OutLen=0, InLen=0
entry
SYSTEM_BUFFERMODE: 'no'
Data from PX_GetRow: SH=160508756, ColCount=2
Schema: col=0, Ind=0, Len=1, off=3
Schema: col=1, Ind=0, Len=1, off=6
Hexadecimal formatted display from address F6878DF6 for 7 bytes.
0000 00 01 00 42.01 00 32 *...B..2 *
!ERROR! API not initialized
!ERROR! on record 1
pmWrite rc=26
'Access Module API not initialized (call pmInit)'
Setting exit code = 12.
entry
OUTMOD returned, req=2, OutLen=0, InLen=0
Closing logs
===================================================================
= =
= Module Identification =
= =
===================================================================
Export Operator for Linux release 2.6.32-300.39.2.el5uek on XXXX.com
ExportMain : 13.10.00.11
ExportCLI : 13.10.00.09
ExportUtil : 13.10.00.02
PcomCLI : 13.10.00.19
PcomMBCS : 13.00.00.02
PcomMsgs : 13.01.00.07
PcomNtfy : 13.00.00.03
PcomPx : 13.10.00.18
PcomUtil : 13.10.00.09
PXICU : 13.10.00.06
TDICU : 13.10.00.02
CLIv2 : 13.10.00.02
===================================================================
= =
= Attribute Definitions =
= =
===================================================================
**** 02:15:15 Options in effect for this job:
OperatorType: Producer
Instances: 1
Character set: 'UTF8'
Checkpoint: No checkpoint in effect
Notify: Not enabled
Tenacity: 4 hour limit to successfully connect
Sleep: 6 minute(s) between connect retries
Date format: INTEGERDATE
Blocksize: Maximum allowable
OutLimit: No limit in effect
===================================================================
= =
= Column/Field Definition =
= =
===================================================================
Column Name Offset Length Type
============================== ====== ====== ========================
COL1 0 30 VARCHAR
COL2 32 3 VARCHAR
============================== ====== ====== ========================
INDICATOR BYTES NEEDED: 1
EXPECTED RECORD LENGTH: 38
You are using the TPT 13.10.00.05 version.
Can you install the newer TPT 13.10.00.14 version?
Try the TPT job using the newer TPT 13.10.00.14 version.
Make sure to remove the TPT checkpoint file(s) before re-running the TPT job.
Send me the TPT FastExport OUTMOD Adapter operator's private log using the newer TPT version.
Hello Tony,
I am trying to use a where clause inside TPT export selector. I am getting syntax error for the following definition due to the quotes present in IN LIST of where clause. I cannot avoid the quotes for the list as its a varchar filed. Is there any way out to solve this, may be using some escape character?
Thanks In Advance.
/*** Export Operator Definition ***/
DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA SCHEMA_NAME
ATTRIBUTES
(
VARCHAR PrivateLogName = 'exportoper_privatelog',
INTEGER MaxSessions = 8,
INTEGER MinSessions,
VARCHAR TdpId = 'XXXXX',
VARCHAR UserName = 'XXXXXX',
VARCHAR UserPassword = 'XXXXXX',
VARCHAR SelectStmt = 'SELECT
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5
from TABLE where COLUMN3 IN ('000362464WH', '20550K01A');'
Best,
Shyam
Shyam,
You can escape the single quote with two consecutive single quotes, like this:
IN (''000362464WH'', ''20550K01A'');
Hi Tony,
Can you please share sample TPT script which loads data from Oracle to Teradata with ODBC operator? I am trying the same using the below script but its failing due to ODBC issues.
DEFINE JOB COPY_ORACLE_TO_TERADATA
DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'
(
DEFINE SCHEMA TABLE_STRUCTURE
DESCRIPTION 'THE STRUCTURE OF THE TABLE'
(
COL_1 DECIMAL(15,0),
COL_2 VARCHAR(30)
);
DEFINE SCHEMA TABLE_STRUCTURE_TGT
DESCRIPTION 'THE STRUCTURE OF THE TARGET TABLE'
(
COL_1 DECIMAL(15,0),
COL_2 VARCHAR(30)
);
/*** Export Operator Definition ***/
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
);
/*** Load Operator Definition ***/
DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA TABLE_STRUCTURE_TGT
ATTRIBUTES
(
VARCHAR PrivateLogName = 'loadoper_privatelog',
INTEGER MaxSessions = 16,
INTEGER MinSessions,
VARCHAR TargetTable = @TargetTable,
VARCHAR TdpId = @TargetTdpId,
VARCHAR UserName = @TargetUserName,
VARCHAR UserPassword = @TargetUserPassword,
VARCHAR ErrorTable1 = @TargetDataBase || '.LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = @TargetDataBase || '.LOADOPER_ERRTABLE2',
VARCHAR LogTable = @TargetDataBase || '.LOADOPER_LOGTABLE'
);
/*** Apply Statement ***/
APPLY
(
'INSERT INTO ' || @TargetTable || '
(
:COL_1,
:COL_2
);'
)
TO OPERATOR (LOAD_OPERATOR [1]) SELECT * FROM OPERATOR (EXPORT_OPERATOR);
);
Also can you share the format of Connection string and DSN name which we should follow in variable file?
Thanks
VetriSlevan,
What is the failure message?
Your script should work if your ODBC configuration was setup properly. You need to make sure the ODBC configurations is setup properly before you run the TPT job script. You may need to contact your ODBC administrator.
Have you looked at the ODBC Operator chapter in the "Teradata Parallel Transporter Reference"? It has information about configuring the ODBC initialization file.
The format of the Connection string is a free form text. The TPT ODBC Operator passes the Connection string to the ODBC driver for connection identification.
The DSN name is the name of the data source.
Hi Tony.
I am getting the below ODBC error. So we need more clarification on using ConnString in the variable file. I checked the TPT manual and the format is like
ConnectString = 'connectString' . But it will be helpful if I get the variable file with sample values.
***********************************
Teradata Parallel Transporter Version 14.00.00.08
Job log: /apps/teradata/client/14.00/tbuild/logs/<user_name>-144.out
Job id is <user_name>-144, running on <server.com>
Found CheckPoint file: /apps/teradata/client/14.00/tbuild/checkpoint/<user_name>LVCP
This is a restart job; it restarts at step MAIN_STEP.
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: /data/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=IM002, CODE=0,
MSG='[DataDirect][ODBC lib] Data source name not found and no default driver specified'
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.1 Second(s)'
EXPORT_OPERATOR: Start : Thu May 15 06:48:05 2014
EXPORT_OPERATOR: End : Thu May 15 06:48:05 2014
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '19.85 Second(s)'
LOAD_OPERATOR: Start : Thu May 15 06:48:05 2014
LOAD_OPERATOR: End : Thu May 15 06:48:32 2014
Job step MAIN_STEP terminated (status 12)
Job <user_name> terminated (status 12)
org.apache.commons.exec.ExecuteException: Process exited with an error: 12(Exit value: 12)
at org.apache.commons.exec.DefaultExecutor.executeInternal(DefaultExecutor.java:346)
at org.apache.commons.exec.DefaultExecutor.execute(DefaultExecutor.java:149)
at org.apache.commons.exec.DefaultExecutor.execute(DefaultExecutor.java:136)
at com.ge.health.fa.jfm.enhancement.Session$1.run(Session.java:93)
at java.lang.Thread.run(Thread.java:619)
Thanks!!!
VetriSlevan,
Here is a sample of the ConnectString attribute:
'DSN=MYDSN;UID=username;PWD=Secret'
You need to replace the MYDSN, username, and Secret values with your own values.
According to the TPT17122/TPT17101 errors, you specifed an incorrect or undefined DSN name. Make sure you are using the correct DSN name.
Hi Tony,
Awesome... I just need one more clarification. As per my understanding we need to use either Connection String or DSN name but the sample ConnectString('DSN=MYDSN;UID=username;PWD=Secret') you have provided has DSN name. I am trying to connect only by using Connectstring (but without DSN). Is that possible? If so can you please provide sample Connection String attribute? I could not get this proper format.
Thank you !!!
VetriSlevan,
Yes, it's possible to connect only by using the ConnectString attribute (without the DSNName attribute).
I have already provided you with a sample ConnectString attribute:
'DSN=MYDSN;UID=username;PWD=Secret'
I have resolved the data source name issue and have assigned all the environment variables. 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?
VetriSlevan,
You might be not setting the LD_LIBRARY_PATH or might be pointing it to a wrong location.
The LD_LIBRARY_PATH should point to the lib folder of the DataDirect Drivers.
Since you are using the DataDirect Drivers, have you tried to use the sample application provided by the DataDirect in its install path to see if you are able to make a connection using the DSN?
Also, can you send me the output of the ldd command on the libodbcop.so file and also the Driver file that you are using?
Hi Tony,
I dont find any file named libodbcop.so in the lib path. However, I can see the file with the name libodbc.so. Please find the output for the command ldd, libodbc.so file
-bash-3.00$ ldd <odbc_lib_path>/libodbc.so
libDWicu26.so => <odbc_lib_path>/libDWicu26.so
libodbcinst.so => <odbc_lib_path>/libodbcinst.so
libsocket.so.1 => /usr/lib/sparcv9/libsocket.so.1
libdl.so.1 => /usr/lib/sparcv9/libdl.so.1
libCrun.so.1 => /usr/lib/sparcv9/libCrun.so.1
libnsl.so.1 => /usr/lib/sparcv9/libnsl.so.1
librt.so.1 => /usr/lib/sparcv9/librt.so.1
libm.so.1 => /usr/lib/sparcv9/libm.so.1
libc.so.1 => /usr/lib/sparcv9/libc.so.1
libmp.so.2 => /usr/lib/sparcv9/libmp.so.2
libmd.so.1 => /usr/lib/sparcv9/libmd.so.1
libscf.so.1 => /usr/lib/sparcv9/libscf.so.1
libaio.so.1 => /usr/lib/sparcv9/libaio.so.1
libdoor.so.1 => /usr/lib/sparcv9/libdoor.so.1
libuutil.so.1 => /usr/lib/sparcv9/libuutil.so.1
libgen.so.1 => /usr/lib/sparcv9/libgen.so.1
libm.so.2 => /usr/lib/sparcv9/libm.so.2
/platform/SUNW,SPARC-Enterprise-T5220/lib/sparcv9/libc_psr.so.1
/platform/SUNW,SPARC-Enterprise-T5220/lib/sparcv9/libmd_psr.so.1
-bash-3.00$
I am using the driver file "DWora26.so" and its located in <odbc_lib_path> and I have set the same in LD_LIBRARY_PATH variable before executing the TPT script.
Thanks,
Vetri Selvan
Vetri,
The libodbcop.so file should be in the /opt/teradata/client/14.00/tbuild/lib directory. Check and send me the ldd output for the libodbcop.so file.
Thanks.
Hi Tony,
Please see ldd output below:
-bash-3.00$ ldd libodbcop.so
libodbc.so => /apps/teradata/client/14.00/odbc_32/lib/libodbc.so
libodbcinst.so => /apps/teradata/client/14.00/odbc_32/lib/libodbcinst.so
libopcommon.so => /apps/teradata/client/14.00/tbuild/lib/libopcommon.so
libpxicu.so => /apps/teradata/client/14.00/tbuild/lib/libpxicu.so
libnsl.so.1 => /lib/libnsl.so.1
libivicu25.so => /apps/teradata/client/14.00/odbc_32/lib/libivicu25.so
libsocket.so.1 => /usr/lib/libsocket.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libCrun.so.1 => /usr/lib/libCrun.so.1
libcliv2.so => /apps/teradata/client/14.00/lib/libcliv2.so
libmp.so.2 => /lib/libmp.so.2
libmd.so.1 => /lib/libmd.so.1
libscf.so.1 => /lib/libscf.so.1
libc.so.1 => /lib/libc.so.1
librt.so.1 => /usr/lib/librt.so.1
libm.so.1 => /usr/lib/libm.so.1
libCstd.so.1 => /usr/lib/libCstd.so.1
libdoor.so.1 => /lib/libdoor.so.1
libuutil.so.1 => /lib/libuutil.so.1
libgen.so.1 => /lib/libgen.so.1
libaio.so.1 => /lib/libaio.so.1
/platform/SUNW,SPARC-Enterprise-T5220/lib/libmd_psr.so.1
libm.so.2 => /lib/libm.so.2
/platform/SUNW,SPARC-Enterprise-T5220/lib/libc_psr.so.1
/usr/lib/cpu/sparcv8plus/libCstd_isa.so.1
-bash-3.00$
Regards
Vetri,
Your LD_LIBRARY_PATH should point to the DataDirect lib folder and the ODBCINI should point to the odbc.ini file. and even after doing this, if you are unable to run the tests, then i suggest you try to connect to the DSN you are using through the sample application in the "/opt/Progress/DataDirect/Connect_for_ODBC_71/samples/example" folder. If this does not work, then please check your entries in the odbc.ini file to make sure the entries are correct.
-- Vishnu Vardhan --
Vetriselvan,
Please use the same odbc.ini file, and execute the sample program (of data direct) and see whether you are able to connect to the desired DSN.
Hi Vishnu,
We don’t have Progress DataDirect driver installed in the server separately. So I am using the datadirect odbc driver that came along with informatica installation and hence my lib path is "/apps/informatica/Informatica951/ODBC7.0/lib" and the driver "DWora26.so" for connecting to oracle from TPT script. Will that be causing the connection issue?
Regards