TPT job fails in 14.10 but passes in 13.10

Tools

TPT job fails in 14.10 but passes in 13.10

Hi,

Recently we upgraded to 14.10 and all of our jobs are fine. But there are few tables that fail in v14.10 but loads fine in v13.10

This is my TPT script

DEFINE JOB TGT_TBL_NM_TPT (
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
( VARCHAR tdpid='tdpid'
, VARCHAR username='UNAME'
, VARCHAR userpassword='UNAME'
);
DEFINE SCHEMA SCHEMA_TGT_TBL_NM (
"SYS_ZP_CD" VARCHAR(5)
, "SYS_ZP_ST_CD" VARCHAR(2)
, "SYS_ZP_CNTY_CD" VARCHAR(3)
);
DEFINE OPERATOR EXPORT_OPER_TGT_TBL_NM
TYPE DATACONNECTOR PRODUCER
SCHEMA SCHEMA_TGT_TBL_NM
ATTRIBUTES
( VARCHAR FileName
, VARCHAR Format
, VARCHAR OpenMode
, INTEGER BlockSize
, INTEGER BufferSize
, INTEGER RetentionPeriod
, INTEGER RowsPerInstance
, INTEGER SecondarySpace
, INTEGER UnitCount
, INTEGER VigilElapsedTime
, INTEGER VigilWaitTime
, INTEGER VolumeCount
, VARCHAR AccessModuleName
, VARCHAR AccessModuleInitStr
, VARCHAR DirectoryPath
, VARCHAR ExpirationDate
, VARCHAR IndicatorMode
, VARCHAR PrimarySpace
, VARCHAR PrivateLogName
, VARCHAR RecordFormat
, VARCHAR RecordLength
, VARCHAR SpaceUnit
, VARCHAR TextDelimiter
, VARCHAR VigilNoticeFileName
, VARCHAR VigilStartTime
, VARCHAR VigilStopTime
, VARCHAR VolSerNumber
, VARCHAR UnitType
);

DEFINE OPERATOR LOAD_OPER_TGT_TBL_NM
TYPE LOAD
SCHEMA *
ATTRIBUTES
( VARCHAR UserName
, VARCHAR UserPassword
, VARCHAR LogTable
, VARCHAR TargetTable
, INTEGER BufferSize
, INTEGER ErrorLimit
, INTEGER MaxSessions
, INTEGER MinSessions
, INTEGER TenacityHours
, INTEGER TenacitySleep
, VARCHAR AccountID
, VARCHAR DateForm
, VARCHAR WorkTable
, VARCHAR ErrorTable1
, VARCHAR ErrorTable2
, VARCHAR NotifyExit
, VARCHAR NotifyExitIsDLL
, VARCHAR NotifyLevel
, VARCHAR NotifyMethod
, VARCHAR NotifyString
, VARCHAR PauseAcq
, VARCHAR PrivateLogName
, VARCHAR TdpId
, VARCHAR TraceLevel
, VARCHAR WorkingDatabase
);

STEP DELETE_TARGET
(
APPLY
( 'DELETE FROM STG_DB.TGT_TBL_NM ALL;' )
TO OPERATOR (DDL_OPERATOR);
);

STEP INSERT_TGT_TBL_NM_LOAD (
APPLY (
'INSERT INTO STG_DB.TGT_TBL_NM (
SYS_ZP_CD
, SYS_ZP_ST_CD
, SYS_ZP_CNTY_CD
) VALUES (
:SYS_ZP_CD
, :SYS_ZP_ST_CD
, :SYS_ZP_CNTY_CD
);'
)
TO OPERATOR
( LOAD_OPER_TGT_TBL_NM
ATTRIBUTES
( UserName = 'UNAME'
, UserPassword = 'UNAME'
, WorkingDatabase = 'WRK_DB'
, WorkTable = 'WRK_DB.TGT_TBL_NM_w'
, LogTable = 'WRK_DB.TGT_TBL_NM_l'
, ErrorTable1 = 'WRK_DB.TGT_TBL_NM_e'
, ErrorTable2 = 'WRK_DB.TGT_TBL_NM_u'
, TargetTable = 'STG_DB.TGT_TBL_NM '
, TdpId = 'TDPID'
)
)
SELECT
"SYS_ZP_CD"
, "SYS_ZP_ST_CD"
, "SYS_ZP_CNTY_CD"
FROM OPERATOR
( EXPORT_OPER_TGT_TBL_NM
ATTRIBUTES
( FileName = 'TGT_TBL_NM.dat'
, Format = 'DELIMITED'
, OpenMode = 'Read'
, DirectoryPath = '/tmp/landingFiles'
, IndicatorMode = 'N'
, TextDelimiter = '^'
)
);
);
);

This is my failed job log (in V14,10)

Teradata Parallel Transporter Version 14.10.00.00

Job log: /tmp/landingFiles/TGT_TBL_NM./tmp/landingFilesjobnm-19.out

Job id is TGT_TBL_NM./tmp/landingFilesjobnm-19, running on DEVSVR

Teradata Parallel Transporter SQL DDL Operator Version 14.10.00.00

DDL_OPERATOR: private log not specified

DDL_OPERATOR: connecting sessions

DDL_OPERATOR: The RDBMS retryable error code list was not found

DDL_OPERATOR: The job will use its internal retryable error codes

DDL_OPERATOR: sending SQL requests

DDL_OPERATOR: Rows Deleted:  0

DDL_OPERATOR: disconnecting sessions

DDL_OPERATOR: Total processor time used = '0.028875 Second(s)'

DDL_OPERATOR: Start : Thu Oct 24 10:55:00 2013

DDL_OPERATOR: End   : Thu Oct 24 10:55:02 2013

Job step DELETE_TARGET completed successfully

Teradata Parallel Transporter Load Operator Version 14.10.00.00

LOAD_OPER_TGT_TBL_NM: private log not specified

Teradata Parallel Transporter EXPORT_OPER_TGT_TBL_NM: TPT19006 Version 14.10.00.00

EXPORT_OPER_TGT_TBL_NM: TPT19003 NotifyMethod: 'None (default)'

EXPORT_OPER_TGT_TBL_NM: TPT19008 DataConnector Producer operator Instances: 1

EXPORT_OPER_TGT_TBL_NM: TPT19003 ECI operator ID: EXPORT_OPER_TGT_TBL_NM-5768160

EXPORT_OPER_TGT_TBL_NM: TPT19222 Operator instance 1 processing file '/tmp/landingFiles/TGT_TBL_NM.dat'.

LOAD_OPER_TGT_TBL_NM: connecting sessions

LOAD_OPER_TGT_TBL_NM: The RDBMS retryable error code list was not found

LOAD_OPER_TGT_TBL_NM: The job will use its internal retryable error codes

LOAD_OPER_TGT_TBL_NM: preparing target table

LOAD_OPER_TGT_TBL_NM: entering Acquisition Phase

EXPORT_OPER_TGT_TBL_NM: TPT19350 I/O error on file '/tmp/landingFiles/TGT_TBL_NM.dat'.

EXPORT_OPER_TGT_TBL_NM: TPT19134 !ERROR! Fatal data error processing file '/tmp/landingFiles/TGT_TBL_NM.dat'. Delimited Data Parsing error: Column length overflow(s) in row 1450.

LOAD_OPER_TGT_TBL_NM: disconnecting sessions

EXPORT_OPER_TGT_TBL_NM: TPT19221 Total files processed: 0.

LOAD_OPER_TGT_TBL_NM: Total processor time used = '0.198781 Second(s)'

LOAD_OPER_TGT_TBL_NM: Start : Thu Oct 24 10:55:05 2013

LOAD_OPER_TGT_TBL_NM: End   : Thu Oct 24 10:56:54 2013

Job step INSERT_TGT_TBL_NM_LOAD terminated (status 8)

Job TGT_TBL_NM./tmp/landingFilesjobnm terminated (status 8)

Job start: Thu Oct 24 10:54:59 2013

Job end:   Thu Oct 24 10:56:54 2013

Same job ran fine in 13.10

I looked at the data and I  found that the issues is with NULL characters with in:

If we do a "more" in unix, you don't see it, but this is what I found

user@devsvr:head -1451 /tmp/landingFiles/TGT_TBL_NM.dat | tail -3

1^AB^GEORGETOWN

2^^ALLENTOWN

3^^MORRISVILLE

user@devsvr:cat -v /tmp/landingFiles/TGT_TBL_NM.dat | head -1451 | tail -3

1^GEO^GEORGETOWN

2^^@^@^@^ALLENTOWN

3^MOR^MORRISVILLE

They are actually NULL characters (\0 - NON-Printable NULL character)

Also, I have one more quick question. why does it say "The RDBMS retryable error code list was not found" are we missing anything in the configs?

I appreciate any kind of reply. 

TIA.

Rama.

3 REPLIES
Teradata Employee

Re: TPT job fails in 14.10 but passes in 13.10

This issue is a known issue and has been fixed.

However, the fix has not yet been backported to 14.10.

-- SteveF

Re: TPT job fails in 14.10 but passes in 13.10

Thank you Feinholz. Do you have any idea or a rumor as to when will this fix will be ported?

Teradata Employee

Re: TPT job fails in 14.10 but passes in 13.10

Looks like this fix appears in efix #10 for 14.0 (14.00.00.10), and efix #3 for 14.10 (14.10.00.03).

Please check the patch server to see if those efixes are present.

-- SteveF