Problems using Teradata Parallel Transporter

Tools

Problems using Teradata Parallel Transporter

Hello everyone,

I am having some issues with Teradata Parallel Transporter. When I schedule a job with TPT using an ODBC source for my data being loaded, everything runs smooth. However if I use flat files as the source of my data, I run into some problems. When I use flat files created with BTEQ, only one row will be loaded from that flat file into the target table, when in fact there should be several hundred loaded into the table. But when I use flat files created via fastexport, The job will load all possible rows. However, all NULLs are turned into zeros and every row in any Decimal columns is automatically converted to a NULL value.

Here's a sample script I've been working on:

/* 1 */
/* 2 */
/* 3 */ DEFINE JOB another_test
/* 4 */ DESCRIPTION 'Another test, using fast export this time instead of BTEQ export.'
/* 5 */ (
/* 6 */ DEFINE OPERATOR W_1_o_another_test
/* 7 */ TYPE LOAD
/* 8 */ SCHEMA *
/* 9 */ ATTRIBUTES
/* 10 */ (
/* 11 */ VARCHAR UserName,
/* 12 */ VARCHAR UserPassword,
/* 13 */ VARCHAR LogTable,
/* 14 */ VARCHAR TargetTable,
/* 15 */ INTEGER BufferSize,
/* 16 */ INTEGER ErrorLimit,
/* 17 */ INTEGER MaxSessions,
/* 18 */ INTEGER MinSessions,
/* 19 */ INTEGER TenacityHours,
/* 20 */ INTEGER TenacitySleep,
/* 21 */ VARCHAR AccountID,
/* 22 */ VARCHAR DateForm,
/* 23 */ VARCHAR ErrorTable1,
/* 24 */ VARCHAR ErrorTable2,
/* 25 */ VARCHAR NotifyExit,
/* 26 */ VARCHAR NotifyExitIsDLL,
/* 27 */ VARCHAR NotifyLevel,
/* 28 */ VARCHAR NotifyMethod,
/* 29 */ VARCHAR NotifyString,
/* 30 */ VARCHAR PauseAcq,
/* 31 */ VARCHAR PrivateLogName,
/* 32 */ VARCHAR TdpId,
/* 33 */ VARCHAR TraceLevel,
/* 34 */ VARCHAR WorkingDatabase
/* 35 */ );
/* 36 */
/* 37 */ DEFINE SCHEMA W_0_s_another_test
/* 38 */ (
/* 39 */ employee_no INTEGER,
/* 40 */ dept_no SMALLINT,
/* 41 */ last_name CHARACTER(20),
/* 42 */ first_name VARCHAR(12),
/* 43 */ salary DECIMAL
/* 44 */ );
/* 45 */
/* 46 */ DEFINE OPERATOR W_0_o_another_test
/* 47 */ TYPE DATACONNECTOR PRODUCER
/* 48 */ SCHEMA W_0_s_another_test
/* 49 */ ATTRIBUTES
/* 50 */ (
/* 51 */ VARCHAR FileName,
/* 52 */ VARCHAR Format,
/* 53 */ VARCHAR OpenMode,
/* 54 */ INTEGER BlockSize,
/* 55 */ INTEGER BufferSize,
/* 56 */ INTEGER RetentionPeriod,
/* 57 */ INTEGER RowsPerInstance,
/* 58 */ INTEGER SecondarySpace,
/* 59 */ INTEGER UnitCount,
/* 60 */ INTEGER VigilElapsedTime,
/* 61 */ INTEGER VigilWaitTime,
/* 62 */ INTEGER VolumeCount,
/* 63 */ VARCHAR AccessModuleName,
/* 64 */ VARCHAR AccessModuleInitStr,
/* 65 */ VARCHAR DirectoryPath,
/* 66 */ VARCHAR ExpirationDate,
/* 67 */ VARCHAR IndicatorMode,
/* 68 */ VARCHAR PrimarySpace,
/* 69 */ VARCHAR PrivateLogName,
/* 70 */ VARCHAR RecordFormat,
/* 71 */ VARCHAR RecordLength,
/* 72 */ VARCHAR SpaceUnit,
/* 73 */ VARCHAR TextDelimiter,
/* 74 */ VARCHAR VigilNoticeFileName,
/* 75 */ VARCHAR VigilStartTime,
/* 76 */ VARCHAR VigilStopTime,
/* 77 */ VARCHAR VolSerNumber,
/* 78 */ VARCHAR UnitType
/* 79 */ );
/* 80 */
/* 81 */ APPLY
/* 82 */ (
/* 83 */ 'INSERT INTO SQL_CLASS_TEST.Employee_Table (Employee_No,Dept_No,Last_name,First_name,Salary) VALUES (:employee_no,:dept_no,:last_name,:first_name,:salary);'
/* 84 */ )
/* 85 */ TO OPERATOR
/* 86 */ (
/* 87 */ W_1_o_another_test[1]
/* 88 */
/* 89 */ ATTRIBUTES
/* 90 */ (
/* 91 */ UserName = '******',
/* 92 */ UserPassword = '*******',
/* 93 */ LogTable = 'SQL_CLASS_TEST.Employee_Table_log',
/* 94 */ TargetTable = 'SQL_CLASS_TEST.Employee_Table',
/* 95 */ TdpId = '***.***.**.**'
/* 96 */ )
/* 97 */ )
/* 98 */ SELECT employee_no, dept_no, last_name, first_name, salary FROM OPERATOR
/* 99 */ (
/* 100 */ W_0_o_another_test[1]
/* 101 */
/* 102 */ ATTRIBUTES
/* 103 */ (
/* 104 */ FileName = 'employee_table_test.txt',
/* 105 */ Format = 'BINARY',
/* 106 */ OpenMode = 'Read',
/* 107 */ DirectoryPath = 'c:\temp',
/* 108 */ IndicatorMode = 'N',
/* 109 */ TextDelimiter = '|'
/* 110 */ )
/* 111 */ );
/* 112 */ );

Does anyone know why TPT is giving me guff when I use flat files? Any help regarding TPT is much appreciated! Also, if there's anything else I can post to help my situation, let me know. Thanks!

5 REPLIES

Re: Problems using Teradata Parallel Transporter

I notice that you have "BINARY" specified as the file format even though your file extention is ".txt". Is your data file really in a non-textual (binary) format? Also, "BINARY" requires a leading two-bytes on each record that indicates the record length.

I've had success with both the "Delimited" and "Text" options of TPT (actually, it's TWB on the version that I'm on) with flat files, but I have not worked with the "BINARY" format.

Hopefully, this helps get you closer.

Re: Problems using Teradata Parallel Transporter

Hi,

I am trying to LOAD a table with the following TPT scripts.

1.       ErrorLIMIT1 = 5

2.       ErrorLIMIT2 = 5

3.       Maxsessions = 1

4.       Minsessions = 1

5.       Loader_Instance = 1

I have a file with 10 UV Values for a single Key value e.g., for primary key = 2 there are 10 UV records. I expected TPT to fail but it did not. Can anyone explain this behavior? What is the scenario in which Errorlimit is used by TPT and it fails.

Thanks

Teradata Employee

Re: Problems using Teradata Parallel Transporter

The ErrorLimit attribute applies to the number of error records during the acquisition phase.

During the acquisition phase, error records include these errors:

* Data conversion errors

* Constraint violations

* AMP configuration changes

The ErrorLimit attribute does not apply to UV records. UV records occur in the application phase.

Re: Problems using Teradata Parallel Transporter

Thanks .

Does this Errorlimit apply to the case when the column data type is integer and the values are coming as varchar 'a' ,'b' etc?

Teradata Employee

Re: Problems using Teradata Parallel Transporter

Yes, the ErrorLimit applies to the case when the column data type is integer and the values are coming as varchar 'a', 'b', etc.