TPT status code 12 during Insert

Tools & Utilities

TPT status code 12 during Insert

Hello,

 

We have job to complete load a table from file. We wanted to capture the error code from TPT for our application.

We try to load more than 30K records. It get loaded successfully if all records are valid. 

When we give one of column data incorrect data type, it puts entries in error table and log table. Job status is 12. WHich is fine. But does not give any TPT error TPT10510. TPT10510 give error only if source file is a few records (<50 records).

 

Please find job and variable file:

 

Variable:

----------------------------

TargetTdpId='$TargetTdpId'
,TargetUserName='$TargetUserName'
,TargetErrorList = ['3807', '3803']
,TargetTable = '$TargetDbName.$TargetTable'
,LogTable = @TargetTable || '_RL'
,ErrorTable = @TargetTable || '_ET'
,StreamPrivateLogName = 'stream_log'
,ErrorLimit = 1
,SourceDirectoryPath= 'data/pending/$Request_Id'
,SourceFileName = '$SourceFileName'
,SourceFormat = 'Delimited'
,SourceTextDelimiter = ','
,EscapeTextDelimiter = '\'
,Openmode = 'Read'
,AcceptExcessColumns ='N'
,AcceptMissingColumns='N'
,StreamInstances = 1
,ReaderInstances = 1

 

JOB

-------------------

DEFINE JOB LOAD_TABLE_FROM_FILE_COMPLETE
DESCRIPTION 'Complete load Teradata table from file'
(
STEP DELETE_DATA(
APPLY
('DROP TABLE '||@TargetTable||'_BKP;'),
('CREATE MULTISET TABLE '|| @TargetTable||'_BKP AS (SELECT * FROM '|| @TargetTable||')WITH DATA;'),
('DELETE FROM '|| @TargetTable||';')
TO OPERATOR ($DDL());
);
STEP INSERT_DATA(
APPLY $INSERT TO OPERATOR ($STREAM()[@StreamInstances])
SELECT * FROM OPERATOR ($DATACONNECTOR_PRODUCER()[@ReaderInstances]);
);
);

 

 

LOG

---------------------------------------

Teradata Parallel Transporter Version 14.10.00.10
Job log: /opt/teradata/client/14.10/tbuild/logs/LOAD_TABLE_FROM_FILE_COMPLETE-190364.out
Job id is LOAD_TABLE_FROM_FILE_COMPLETE-190364, running on xxxxx
Teradata Parallel Transporter SQL DDL Operator Version 14.10.00.10
$DDL: private log not specified
$DDL: connecting sessions
$DDL: sending SQL requests
$DDL: Rows Deleted: 30281
$DDL: disconnecting sessions
$DDL: Total processor time used = '0.034761 Second(s)'
$DDL: Start : Tue Apr 25 01:03:07 2017
$DDL: End : Tue Apr 25 01:03:09 2017
Job step DELETE_DATA completed successfully
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.10
Teradata Parallel Transporter Stream Operator Version 14.10.00.10
$STREAM: private log specified: stream_log
$DATACONNECTOR_PRODUCER: Instance 1 directing private log report to 'dtacop-xxxxxx-49021022-1'.
$DATACONNECTOR_PRODUCER: DataConnector Producer operator Instances: 1
$DATACONNECTOR_PRODUCER: ECI operator ID: '$DATACONNECTOR_PRODUCER-49021022'
$DATACONNECTOR_PRODUCER: Operator instance 1 processing file 'data/pending/766/dummy_data_v2.csv'.
$STREAM: Start-up Rate: UNLIMITED statements per Minute
$STREAM: Operator Command ID for External Command Interface: $STREAM41615386
$STREAM: connecting sessions
$STREAM: entering Load Phase
$STREAM: disconnecting sessions
$DATACONNECTOR_PRODUCER: Total files processed: 0.
$STREAM: Total processor time used = '0.059474 Second(s)'
$STREAM: Start : Tue Apr 25 01:03:10 2017
$STREAM: End : Tue Apr 25 01:03:13 2017
Job step INSERT_DATA terminated (status 12)
Job LOAD_TABLE_FROM_FILE_COMPLETE terminated (status 12)
Job start: Tue Apr 25 01:03:04 2017
Job end: Tue Apr 25 01:03:13 2017

7 REPLIES
Teradata Employee

Re: TPT status code 12 during Insert

Perhaps there was some other "fatal error". Look at the private logs, too:

tlogview -j jobid -f "*"

Re: TPT status code 12 during Insert

Hello Fred,

 

Thanks for the replay.

 

Here is the log. 

 

 

tlogview -j LOAD_TABLE_FROM_FILE_COMPLETE-190364 -f "*"

 

Using memory mapped file for IPC

TPT_INFRA: TPT04101: Warning: Teradata PT cannot connect to Unity EcoSysetm Manager.
The job will continue without event messages being sent to Unity EcoSystem Manager.
TPT_INFRA: TPT04190: Warning: OMD API failed to initialize
Teradata Parallel Transporter Executor Version 14.10.00.10
Teradata Parallel Transporter Coordinator Version 14.10.00.10
CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.
DELETE_DATA APPLY_1[0001] $DDL DBS EDWUAT
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 INITIATE-Started 01:03:07 0.0000 0.0000 65000 0 0 0 0 0 N Y
LOAD_TABLE_FROM_FILE_COMPLETE-190364,17,5,OperatorEnter,DELETE_DATA,$DDL,1,2017-04-25,2017-04-25 01:03:07,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,176,5,OperatorVersion,DELETE_DATA,$DDL,1,2017-04-25,14.10.00.10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,177,1,OperatorType,DELETE_DATA,$DDL,1,2017-04-25,5,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,178,1,OperatorInstances,DELETE_DATA,$DDL,1,2017-04-25,1,1,0
Teradata Parallel Transporter SQL DDL Operator Version 14.10.00.10
$DDL: private log not specified

===================================================================
= =
= TERADATA PARALLEL TRANSPORTER 32-BIT =
= =
= SQL DDL OPERATOR VERSION 14.10.00.10 =
= =
= OPERATOR SUPPORT LIBRARY VERSION 14.10.00.10 =
= =
= COPYRIGHT 2001-2014, TERADATA CORPORATION. =
= ALL RIGHTS RESERVED. =
= =
===================================================================

**** 01:03:07 Processing starting at: Tue Apr 25 01:03:07 2017

===================================================================
= =
= Module Identification =
= =
===================================================================

32-bit SQL DDL Operator for AIX release 1 on xxxxxx
DdlMain : 14.10.00.04
DdlCLI : 14.10.00.09
DdlUtil : 14.10.00.01
PcomCLI : 14.10.00.56
PcomMBCS : 14.10.00.02
PcomMsgs : 14.10.00.02
PcomNtfy : 14.10.00.05
PcomPx : 14.10.00.13
PcomUtil : 14.10.00.13
PXICU : 14.10.00.10
TDICU : 14.10.00.00
CLIv2 : 14.10.00.10

===================================================================
= =
= Control Session Connection =
= =
===================================================================

$DDL: connecting sessions
**** 01:03:08 Connecting to RDBMS: 'EDWUAT'
**** 01:03:08 Connecting with UserId: 'SALES_DATA_ENG_USER'
LOAD_TABLE_FROM_FILE_COMPLETE-190364,181,1,TotalSessConnected,DELETE_DATA,$DDL,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,187,1,SessConnectedByInstance,DELETE_DATA,$DDL,1,2017-04-25,1,1,0

===================================================================
= =
= Teradata Database Information =
= =
===================================================================

**** 01:03:08 Teradata Database Version: '15.10.04.07'
**** 01:03:08 Teradata Database Release: '15.10.04.02'
**** 01:03:08 Maximum request size supported: 1MB
**** 01:03:08 Session character set: 'ASCII'
**** 01:03:08 Data Encryption: supported
**** 01:03:08 Enhanced Statement Status Level: 1
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 INITIATE-Ended 01:03:08 1.0000 0.0334 65000 0 0 0 0 0 N Y
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 EXECUTE-Started 01:03:08 0.0000 0.0000 65000 0 0 0 0 0 N Y

===================================================================
= =
= SQL Requests =
= =
===================================================================

**** 01:03:08 Starting to send DDL statements to the RDBMS.
$DDL: sending SQL requests
**** 01:03:08 Submitting the following request for DDL Group 1:
DROP TABLE xxxxxxxx.Bap_Load_Test_BKP;

**** 01:03:09 *** Command Successful.
**** 01:03:09 Submitting the following request for DDL Group 2:
CREATE MULTISET TABLE xxxxxxxx.Bap_Load_Test_BKP AS (SELECT * FROM xxxxxxxx.Bap_Load_Test)WITH DATA;

**** 01:03:09 *** Command Successful.
**** 01:03:09 Submitting the following request for DDL Group 3:
DELETE FROM xxxxxxxx.Bap_Load_Test;

$DDL: Rows Deleted: 30281
**** 01:03:09 Finished sending DDL statements.
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 EXECUTE-Ended 01:03:09 1.0000 0.0013 65000 0 0 0 0 0 Y Y
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 TERMINATE-Started 01:03:09 0.0000 0.0000 65000 0 0 0 0 0 Y Y
LOAD_TABLE_FROM_FILE_COMPLETE-190364,186,3,CPUTimeByInstance,DELETE_DATA,$DDL,1,2017-04-25,0.034761,1,0

Total Requests Sent: 3
Total Requests Succeeded: 3
Total Requests Failed: 0
Total Requests Skipped: 0

===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

$DDL: disconnecting sessions
**** 01:03:09 Logging off all sessions

Instance Cpu Time
======== ================
1 0.03 Seconds

**** 01:03:09 Total processor time used = '0.034761 Second(s)'
. Start : Tue Apr 25 01:03:07 2017
. End : Tue Apr 25 01:03:09 2017
. Highest return code encountered = '0'.
$DDL: Total processor time used = '0.034761 Second(s)'
$DDL: Start : Tue Apr 25 01:03:07 2017
$DDL: End : Tue Apr 25 01:03:09 2017
**** 01:03:09 This job terminated
LOAD_TABLE_FROM_FILE_COMPLETE-190364,179,5,OperatorEndTS,DELETE_DATA,$DDL,1,2017-04-25,2017-04-25 01:03:09,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,18,1,OperatorExit,DELETE_DATA,$DDL,1,2017-04-25,0,1,0
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 TERMINATE-Ended 01:03:09 0.0000 0.0010 65000 0 0 0 0 0 Y Y
Job step DELETE_DATA completed successfully
Teradata Parallel Transporter Coordinator Version 14.10.00.10
Teradata Parallel Transporter Executor Version 14.10.00.10
Teradata Parallel Transporter Executor Version 14.10.00.10
INSERT_DATA APPLY_1[0001] $STREAM DBS EDWUAT
INSERT_DATA SELECT_2[0001] $DATACONNECTOR_PRODU FILE data/pending/766 c1247700_dummy_data_v2.csv
CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 INITIATE-Started 01:03:10 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 INITIATE-Started 01:03:10 0.0000 0.0000 65000 0 0 0 0 0 N Y
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.10
LOAD_TABLE_FROM_FILE_COMPLETE-190364,17,5,OperatorEnter,INSERT_DATA,$STREAM,1,2017-04-25,2017-04-25 01:03:10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,176,5,OperatorVersion,INSERT_DATA,$STREAM,1,2017-04-25,14.10.00.10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,177,1,OperatorType,INSERT_DATA,$STREAM,1,2017-04-25,2,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,178,1,OperatorInstances,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,184,5,PrivateLogName,INSERT_DATA,$STREAM,1,2017-04-25,stream_log,1,0
Teradata Parallel Transporter Stream Operator Version 14.10.00.10
$STREAM: private log specified: stream_log

===================================================================
= =
= TERADATA PARALLEL TRANSPORTER 32-BIT =
= =
= STREAM OPERATOR VERSION 14.10.00.10 =
= =
= OPERATOR SUPPORT LIBRARY VERSION 14.10.00.10 =
= =
= COPYRIGHT 2001-2014, TERADATA CORPORATION. =
= ALL RIGHTS RESERVED. =
= =
===================================================================

**** 01:03:10 Processing starting at: Tue Apr 25 01:03:10 2017

===================================================================
= =
= Module Identification =
= =
===================================================================

32-bit Stream Operator for AIX release 1 on xxxxxx
StreamMain : 14.10.00.10
StreamCLI : 14.10.00.14
StreamSess : 14.10.00.10
StreamSort : 13.00.00.01
StreamUtil : 14.10.00.12
PcomCLI : 14.10.00.56
PcomMBCS : 14.10.00.02
PcomMsgs : 14.10.00.02
PcomNtfy : 14.10.00.05
PcomPx : 14.10.00.13
PcomUtil : 14.10.00.13
PXICU : 14.10.00.10
$DATACONNECTOR_PRODUCER: TPT19206 Attribute 'TraceLevel' value reset to 'Statistics Only'.
$DATACONNECTOR_PRODUCER: Instance 1 directing private log report to 'dtacop-xxxxxxxx-49021022-1'.
= TraceFunction: 'NO (defaulted)' (=0) =
==========================================================================
= =
= TERADATA PARALLEL TRANSPORTER 32-BIT =
= =
= DATACONNECTOR OPERATOR VERSION 14.10.00.10 =
= =
= DataConnector UTILITY LIBRARY VERSION 14.10.00.78 =
= =
= COPYRIGHT 2001-2014, Teradata Corporation. ALL RIGHTS RESERVED. =
= =
==========================================================================

Operator name: '$DATACONNECTOR_PRODUCER' instance 1 of 1 [Producer]

**** 01:03:10 Processing starting at: Tue Apr 25 01:03:10 2017

==========================================================================
= =
= Operator module static specifics =
= =
= Operator module name:'dtacop', version:'14.10.00.10' =
= =
= pmdcomt_HeaderVersion: 'Common 14.10.00.09' - packing 'options align=p =
= acked' =
= pmddamt_HeaderVersion: 'Common 14.10.00.01' - packing 'options align=p =
= acked' =
= =
==========================================================================

==========================================================================
= =
= > General attribute Definitions < =
= =
= TraceLevel: '' =
= EndianFlip: 'NO (defaulted)' (=0) =
= IndicatorMode: 'NO (defaulted)' (=0) =
= NullColumns: 'YES (defaulted)' (=1) =
= SYSTEM_CharSetId: 'ASCII' =
= =
==========================================================================

BIG ENDIAN platform
WARNING! NotifyMethod: 'None (default)'
Operator 'dtacop' main source version:'14.10.00.41'

==========================================================================
= =
= > Operator attribute Definitions < =
= =
==========================================================================

$DATACONNECTOR_PRODUCER: DataConnector Producer operator Instances: 1
FileList: 'NO (defaulted)' (=0)
MultipleReaders: 'NO (defaulted)' (=0)
EnableScan: 'Yes (defaulted)'
Initializing with CharSet = 'ASCII'.
TDICU : 14.10.00.00
Alphabetic CSName=ASCII
Established character set ASCII
Single-byte character set in use

==========================================================================
= =
= > Module Identification < =
= =
==========================================================================

32-bit DataConnector operator for AIX release 1 on xxxxxx
TDICU................................... 14.10.00.00
PXICU................................... 14.10.00.10
PMPROCS................................. 14.10.00.11
PMRWFMT................................. 14.10.00.03
PMTRCE.................................. 13.00.00.02
PMMM.................................... 14.10.00.02
DCUDDI.................................. 14.10.00.59
PMHEXDMP................................ 14.10.00.01
PMUNXDSK................................ 14.10.00.13

WARNING! OpenMode attribute not specified, default 'Read' being used.
$DATACONNECTOR_PRODUCER: TPT19203 Required attribute 'OpenMode' not found. Defaulting to 'Read'.
QuotedData: 'NO (defaulted)' (=0)
AcceptEOFasEOR: 'YES (defaulted)' (=1)
AppendDelimiter: 'NO (defaulted)' (=0)
TextDelimiter = ',' [1 byte(s) x'2C']
WARNING! EscapeTextDelimiter attribute not specified, there is no default
WARNING! OpenQuoteMark attribute not specified, there is no default
WARNING! CloseQuoteMark attribute not specified, there is no default
WARNING! EscapeQuoteDelimiter attribute not specified, there is no default
WARNING! TrimChar attribute not specified, there is no default
UseGeneralUDDIcase: 'NO (defaulted)' (=0)
UDDI_ParsingCase=1100000
WriteBOM: 'NO (defaulted)' (=0)
AcceptExcessColumns: 'NO (defaulted)' (=0)
AcceptMissingColumns: 'NO (defaulted)' (=0)
TruncateColumnData: 'NO (defaulted)' (=0)
TruncateColumns: 'NO (defaulted)' (=0)
TruncateLongCols: 'NO (defaulted)' (=0)
WARNING! RecordErrorFilePrefix attribute not specified, there is no default
RecordErrorVerbosity: OFF (default) (=0)
FileName: 'c1247700_dummy_data_v2.csv'
OpenMode: 'READ' (1)
Format: 'DELIMITED' (1)
IOBufferSize: 131072

Full File Path: data/pending/766/c1247700_dummy_data_v2.csv
Data Type Ind Length Offset M
VARCHAR ( 7) 1 20 0 N
VARCHAR ( 7) 1 20 20 N
VARCHAR ( 7) 1 20 40 N
VARCHAR ( 7) 1 20 60 N
VARCHAR ( 7) 1 11 80 N
VARCHAR ( 7) 1 20 91 N
VARCHAR ( 7) 1 20 111 N
VARCHAR ( 7) 1 20 131 N
Schema is not all character data
Schema is compatible with delimited data
Validating parsing case: 1100000.
SBCS (QUOTED DATA: No), Delimiter[1]: ','
Delimiter: x'2C'
Escape Delimiter: x''
Open Quote: x''
Close Quote: x''
Escape Quote: x''
Log will include stats only

$DATACONNECTOR_PRODUCER: ECI operator ID: '$DATACONNECTOR_PRODUCER-49021022'
**** 01:03:10 From file 'c1247700_dummy_data_v2.csv' (Size 0), starting to send rows.
$DATACONNECTOR_PRODUCER: Operator instance 1 processing file 'data/pending/766/c1247700_dummy_data_v2.csv'.
CLIv2 : 14.10.00.10
LOAD_TABLE_FROM_FILE_COMPLETE-190364,116,5,UtilityName,INSERT_DATA,$STREAM,1,2017-04-25,TPT Stream Operator,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,4,5,StreamVersionId,INSERT_DATA,$STREAM,1,2017-04-25,14.10.00.10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,115,1,UtilityId,INSERT_DATA,$STREAM,1,2017-04-25,5,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,134,5,StreamTdpId,INSERT_DATA,$STREAM,1,2017-04-25,EDWUAT,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,5,5,StreamUserName,INSERT_DATA,$STREAM,1,2017-04-25,SALES_DATA_ENG_USER,1,0

===================================================================
= =
= Attribute Definitions =
= =
===================================================================

**** 01:03:10 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
Error limit: 1 rejected record(s)
Tenacity: 4 hour limit to successfully connect
Sleep: 6 minute(s) between connect retries
Buffers: 3
Pack: 20
Robust: In effect
$STREAM: Start-up Rate: UNLIMITED statements per Minute

===================================================================
= =
= Column/Field Definition =
= =
===================================================================

Column Name Offset Length Type
============================== ====== ====== ========================
"Store_ID" 0 20 VARCHAR
"Postcode" 22 20 VARCHAR
"Country" 44 20 VARCHAR
"Region" 66 20 VARCHAR
"Priority" 88 11 VARCHAR
"Quarter" 101 20 VARCHAR
"WeekDay" 123 20 VARCHAR
"Product_Name" 145 20 VARCHAR
============================== ====== ====== ========================
INDICATOR BYTES NEEDED: 1
EXPECTED RECORD LENGTH: 168

===================================================================
= =
= Column/Field MacroCharSet Definition =
= =
===================================================================

Column Name MacroCharSet
============================== ====================================
"Store_ID" Not specified; will use default
"Postcode" Not specified; will use default
"Country" Not specified; will use default
"Region" Not specified; will use default
"Priority" Not specified; will use default
"Quarter" Not specified; will use default
"WeekDay" Not specified; will use default
"Product_Name" Not specified; will use default
============================== ====================================
**** 01:03:10 Operator Command ID for External Command Interface: $STREAM41615386
$STREAM: Operator Command ID for External Command Interface: $STREAM41615386

===================================================================
= =
= Control Session Connection =
= =
===================================================================

$STREAM: connecting sessions
**** 01:03:10 Connecting to RDBMS: 'EDWUAT'
**** 01:03:10 Connecting with UserId: 'SALES_DATA_ENG_USER'
LOAD_TABLE_FROM_FILE_COMPLETE-190364,130,5,StreamDbase,INSERT_DATA,$STREAM,1,2017-04-25,SALES_DATA_ENG_USER,1,0

===================================================================
= =
= Teradata Database Information =
= =
===================================================================

**** 01:03:11 Teradata Database Version: '15.10.04.07'
**** 01:03:11 Teradata Database Release: '15.10.04.02'
**** 01:03:11 Maximum request size supported: 1MB
**** 01:03:11 Session character set: 'ASCII'
**** 01:03:11 RDBMS supports upsert SQL
**** 01:03:11 Data Encryption: supported
**** 01:03:11 RDBMS supports Array Support
**** 01:03:11 Enhanced Statement Status Level: 1
**** 01:03:11 RDBMS supports Statement Independence
**** 01:03:11 Restart log table 'xxxxxxxx.Bap_Load_Test_RL' has been created
**** 01:03:11 Error table 'xxxxxxxx.Bap_Load_Test_ET' has been created
**** 01:03:11 Default MacroCharSet: 'LATIN'

===================================================================
= =
= Special Session Connection =
= =
===================================================================

**** 01:03:11 Maximum number of special sessions requested: All
**** 01:03:11 Minimum number of special sessions required: 1

Instance Assigned Connected Result
======== ======== ========= ======================
1 1 1 Successful
LOAD_TABLE_FROM_FILE_COMPLETE-190364,180,1,TotalSessAssigned,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,181,1,TotalSessConnected,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,182,1,TASMSessionLimit,INSERT_DATA,$STREAM,1,2017-04-25,0,1,0
======== ======== ========= ======================
Total 1 1 Successful

===================================================================
= =
= DML Information =
= =
===================================================================

**** 01:03:11 DML statement for DML Group: 1

INSERT INTO xxxxxxxx.Bap_Load_Test VALUES ( :Store_ID, :Postcode, :Country, :Region, :Priority, :Quarter, :WeekDay, :Product_Name);
LOAD_TABLE_FROM_FILE_COMPLETE-190364,59,1,StreamImportBegin,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 INITIATE-Ended 01:03:11 1.0000 0.0576 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 INITIATE-Ended 01:03:11 1.0000 0.0093 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 EXECUTE-Started 01:03:11 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 EXECUTE-Started 01:03:11 0.0000 0.0000 65000 0 0 0 0 0 N Y
CheckPoint No. 1 started.
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 CHECKPOINT-Started 01:03:11 0.0000 0.0001 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 CHECKPOINT-Started 01:03:11 0.0000 0.0001 65000 0 0 0 0 0 N Y
Task(SELECT_2[0001]): checkpoint completed, status = Success
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 CHECKPOINT-Ended 01:03:12 1.0000 0.0005 65000 0 0 0 0 0 N Y
Task(APPLY_1[0001]): checkpoint completed, status = Success
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 CHECKPOINT-Ended 01:03:12 1.0000 0.0004 65000 0 0 0 0 0 N Y
CheckPoint No. 1 finished successfully.

===================================================================
= =
= Load Phase =
= =
===================================================================

$STREAM: entering Load Phase
**** 01:03:12 Starting to send data to the RDBMS
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 60, Total Rows Sent = 0
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 TERMINATE-Started 01:03:13 0.0000 0.0000 65000 0 0 0 0 0 N Y
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 4, Total Rows Received = 0, Total Rows Sent = 3693
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 TERMINATE-Started 01:03:13 0.0000 0.0000 65000 0 0 0 0 0 N Y

LOAD_TABLE_FROM_FILE_COMPLETE-190364,186,3,CPUTimeByInstance,INSERT_DATA,$STREAM,1,2017-04-25,0.059474,1,0
Closing file: 'Data File'

Detaching default Access Module
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

$STREAM: disconnecting sessions
**** 01:03:13 Logging off all sessions
Files read by this instance: 0
**** 01:03:13 Total processor time used = '0.00 Seconds(s)'
**** 01:03:13 Total files processed: 0
$DATACONNECTOR_PRODUCER: Total files processed: 0.
Shutting down API
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 TERMINATE-Ended 01:03:13 0.0000 0.0011 65000 0 0 0 0 0 N Y
LOAD_TABLE_FROM_FILE_COMPLETE-190364,137,0,StreamSessEnd,INSERT_DATA,$STREAM,1,2017-04-25,,1,0

Instance Cpu Time
======== ================
1 0.06 Seconds

**** 01:03:13 Total processor time used = '0.059474 Second(s)'
. Start : Tue Apr 25 01:03:10 2017
. End : Tue Apr 25 01:03:13 2017
. Highest return code encountered = '12'.
$STREAM: Total processor time used = '0.059474 Second(s)'
$STREAM: Start : Tue Apr 25 01:03:10 2017
$STREAM: End : Tue Apr 25 01:03:13 2017
**** 01:03:13 This job terminated
LOAD_TABLE_FROM_FILE_COMPLETE-190364,179,5,OperatorEndTS,INSERT_DATA,$STREAM,1,2017-04-25,2017-04-25 01:03:13,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,18,1,OperatorExit,INSERT_DATA,$STREAM,1,2017-04-25,12,1,0
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 TERMINATE-Ended 01:03:13 0.0000 0.0017 65000 0 0 0 0 0 N Y
Job step INSERT_DATA terminated (status 12)
Job LOAD_TABLE_FROM_FILE_COMPLETE terminated (status 12)
Job start: Tue Apr 25 01:03:04 2017
Job end: Tue Apr 25 01:03:13 2017
Total available memory: 20000676
Largest allocable area: 20000676
Memory use high water mark: 279912
Free map size: 1024
Free map use high water mark: 20
Free list use high water mark: 0

Teradata Employee

Re: TPT status code 12 during Insert

It would actually view better if you did this:

 

tlogview -j <job-name> -f "*" -g

 

The -g will sort the messages so that all messages by the same operator are grouped together.

The above output intersperses the Stream and DC operator messages, making it harder to read.

 

Please run the job that results in the TPT10510 error and post that output from tlogview (with -g).

 

Thanks!

-- SteveF

Re: TPT status code 12 during Insert

Hello SteveF,

 

Thanks for the reply.

 

Here is the log.

 

Public log:


Using memory mapped file for IPC

TPT_INFRA: TPT04101: Warning: Teradata PT cannot connect to Unity EcoSysetm Manager.
The job will continue without event messages being sent to Unity EcoSystem Manager.
TPT_INFRA: TPT04190: Warning: OMD API failed to initialize
Teradata Parallel Transporter Executor Version 14.10.00.10
Teradata Parallel Transporter Coordinator Version 14.10.00.10
Teradata Parallel Transporter SQL DDL Operator Version 14.10.00.10
$DDL: private log not specified

===================================================================
= =
= TERADATA PARALLEL TRANSPORTER 32-BIT =
= =
= SQL DDL OPERATOR VERSION 14.10.00.10 =
= =
= OPERATOR SUPPORT LIBRARY VERSION 14.10.00.10 =
= =
= COPYRIGHT 2001-2014, TERADATA CORPORATION. =
= ALL RIGHTS RESERVED. =
= =
===================================================================

**** 01:03:07 Processing starting at: Tue Apr 25 01:03:07 2017

===================================================================
= =
= Module Identification =
= =
===================================================================

32-bit SQL DDL Operator for AIX release 1 on petrie
DdlMain : 14.10.00.04
DdlCLI : 14.10.00.09
DdlUtil : 14.10.00.01
PcomCLI : 14.10.00.56
PcomMBCS : 14.10.00.02
PcomMsgs : 14.10.00.02
PcomNtfy : 14.10.00.05
PcomPx : 14.10.00.13
PcomUtil : 14.10.00.13
PXICU : 14.10.00.10
TDICU : 14.10.00.00
CLIv2 : 14.10.00.10

===================================================================
= =
= Control Session Connection =
= =
===================================================================

$DDL: connecting sessions
**** 01:03:08 Connecting to RDBMS: 'XXXXXX'
**** 01:03:08 Connecting with UserId: 'XXXXX_XXXX_XXX_USER'

===================================================================
= =
= Teradata Database Information =
= =
===================================================================

**** 01:03:08 Teradata Database Version: '15.10.04.07'
**** 01:03:08 Teradata Database Release: '15.10.04.02'
**** 01:03:08 Maximum request size supported: 1MB
**** 01:03:08 Session character set: 'ASCII'
**** 01:03:08 Data Encryption: supported
**** 01:03:08 Enhanced Statement Status Level: 1

===================================================================
= =
= SQL Requests =
= =
===================================================================

**** 01:03:08 Starting to send DDL statements to the RDBMS.
$DDL: sending SQL requests
**** 01:03:08 Submitting the following request for DDL Group 1:
DROP TABLE XXXXX_XXXX_XXX_APP.Bap_Load_Test_BKP;

**** 01:03:09 *** Command Successful.
**** 01:03:09 Submitting the following request for DDL Group 2:
CREATE MULTISET TABLE XXXXX_XXXX_XXX_APP.Bap_Load_Test_BKP AS (SELECT * FROM XXXXX_XXXX_XXX_APP.Bap_Load_Test)WITH DATA;

**** 01:03:09 *** Command Successful.
**** 01:03:09 Submitting the following request for DDL Group 3:
DELETE FROM XXXXX_XXXX_XXX_APP.Bap_Load_Test;

$DDL: Rows Deleted: 30281
**** 01:03:09 Finished sending DDL statements.
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

Total Requests Sent: 3
Total Requests Succeeded: 3
Total Requests Failed: 0
Total Requests Skipped: 0

===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

$DDL: disconnecting sessions
**** 01:03:09 Logging off all sessions

Instance Cpu Time
======== ================
1 0.03 Seconds

**** 01:03:09 Total processor time used = '0.034761 Second(s)'
. Start : Tue Apr 25 01:03:07 2017
. End : Tue Apr 25 01:03:09 2017
. Highest return code encountered = '0'.
$DDL: Total processor time used = '0.034761 Second(s)'
$DDL: Start : Tue Apr 25 01:03:07 2017
$DDL: End : Tue Apr 25 01:03:09 2017
**** 01:03:09 This job terminated
Job step DELETE_DATA completed successfully
Teradata Parallel Transporter Coordinator Version 14.10.00.10
Teradata Parallel Transporter Executor Version 14.10.00.10
Teradata Parallel Transporter Executor Version 14.10.00.10
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.10
Teradata Parallel Transporter Stream Operator Version 14.10.00.10
$STREAM: private log specified: stream_log
$DATACONNECTOR_PRODUCER: TPT19206 Attribute 'TraceLevel' value reset to 'Statistics Only'.
$DATACONNECTOR_PRODUCER: Instance 1 directing private log report to 'dtacop-xxxxxxxx-49021022-1'.
$DATACONNECTOR_PRODUCER: DataConnector Producer operator Instances: 1
$DATACONNECTOR_PRODUCER: TPT19203 Required attribute 'OpenMode' not found. Defaulting to 'Read'.
$DATACONNECTOR_PRODUCER: ECI operator ID: '$DATACONNECTOR_PRODUCER-49021022'
$DATACONNECTOR_PRODUCER: Operator instance 1 processing file 'data/pending/766/dummy_data_v2.csv'.
$STREAM: Start-up Rate: UNLIMITED statements per Minute
$STREAM: Operator Command ID for External Command Interface: $STREAM41615386
$STREAM: connecting sessions
Task(SELECT_2[0001]): checkpoint completed, status = Success
Task(APPLY_1[0001]): checkpoint completed, status = Success
$STREAM: entering Load Phase
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 60, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 4, Total Rows Received = 0, Total Rows Sent = 3693
$STREAM: disconnecting sessions
$DATACONNECTOR_PRODUCER: Total files processed: 0.
$STREAM: Total processor time used = '0.059474 Second(s)'
$STREAM: Start : Tue Apr 25 01:03:10 2017
$STREAM: End : Tue Apr 25 01:03:13 2017
Job step INSERT_DATA terminated (status 12)
Job LOAD_TABLE_FROM_FILE_COMPLETE terminated (status 12)
Job start: Tue Apr 25 01:03:04 2017
Job end: Tue Apr 25 01:03:13 2017
Total available memory: 20000676
Largest allocable area: 20000676
Memory use high water mark: 279912
Free map size: 1024
Free map use high water mark: 20
Free list use high water mark: 0


Private log PXCRM:


CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.
CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.
CheckPoint No. 1 started.
CheckPoint No. 1 finished successfully.

Private log TWB_SRCTGT:


DELETE_DATA APPLY_1[0001] $DDL DBS XXXXXX
INSERT_DATA APPLY_1[0001] $STREAM DBS XXXXXX
INSERT_DATA SELECT_2[0001] $DATACONNECTOR_PRODU FILE data/pending/766 dummy_data_v2.csv

Private log TWB_STATUS:


DELETE_DATA APPLY_1[0001] Success $DDL 1 1 INITIATE-Started 01:03:07 0.0000 0.0000 65000 0 0 0 0 0 N Y
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 INITIATE-Ended 01:03:08 1.0000 0.0334 65000 0 0 0 0 0 N Y
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 EXECUTE-Started 01:03:08 0.0000 0.0000 65000 0 0 0 0 0 N Y
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 EXECUTE-Ended 01:03:09 1.0000 0.0013 65000 0 0 0 0 0 Y Y
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 TERMINATE-Started 01:03:09 0.0000 0.0000 65000 0 0 0 0 0 Y Y
DELETE_DATA APPLY_1[0001] Success $DDL 1 1 TERMINATE-Ended 01:03:09 0.0000 0.0010 65000 0 0 0 0 0 Y Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 INITIATE-Started 01:03:10 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 INITIATE-Started 01:03:10 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 INITIATE-Ended 01:03:11 1.0000 0.0576 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 INITIATE-Ended 01:03:11 1.0000 0.0093 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 EXECUTE-Started 01:03:11 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 EXECUTE-Started 01:03:11 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 CHECKPOINT-Started 01:03:11 0.0000 0.0001 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 CHECKPOINT-Started 01:03:11 0.0000 0.0001 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 CHECKPOINT-Ended 01:03:12 1.0000 0.0005 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 CHECKPOINT-Ended 01:03:12 1.0000 0.0004 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 TERMINATE-Started 01:03:13 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 TERMINATE-Started 01:03:13 0.0000 0.0000 65000 0 0 0 0 0 N Y
INSERT_DATA SELECT_2[0001] Success $DATACONNECTOR_PRODU 1 1 TERMINATE-Ended 01:03:13 0.0000 0.0011 65000 0 0 0 0 0 N Y
INSERT_DATA APPLY_1[0001] Success $STREAM 1 1 TERMINATE-Ended 01:03:13 0.0000 0.0017 65000 0 0 0 0 0 N Y

Private log TWB_EVENTS:


LOAD_TABLE_FROM_FILE_COMPLETE-190364,17,5,OperatorEnter,DELETE_DATA,$DDL,1,2017-04-25,2017-04-25 01:03:07,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,176,5,OperatorVersion,DELETE_DATA,$DDL,1,2017-04-25,14.10.00.10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,177,1,OperatorType,DELETE_DATA,$DDL,1,2017-04-25,5,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,178,1,OperatorInstances,DELETE_DATA,$DDL,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,181,1,TotalSessConnected,DELETE_DATA,$DDL,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,187,1,SessConnectedByInstance,DELETE_DATA,$DDL,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,186,3,CPUTimeByInstance,DELETE_DATA,$DDL,1,2017-04-25,0.034761,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,179,5,OperatorEndTS,DELETE_DATA,$DDL,1,2017-04-25,2017-04-25 01:03:09,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,18,1,OperatorExit,DELETE_DATA,$DDL,1,2017-04-25,0,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,17,5,OperatorEnter,INSERT_DATA,$STREAM,1,2017-04-25,2017-04-25 01:03:10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,176,5,OperatorVersion,INSERT_DATA,$STREAM,1,2017-04-25,14.10.00.10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,177,1,OperatorType,INSERT_DATA,$STREAM,1,2017-04-25,2,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,178,1,OperatorInstances,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,184,5,PrivateLogName,INSERT_DATA,$STREAM,1,2017-04-25,stream_log,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,116,5,UtilityName,INSERT_DATA,$STREAM,1,2017-04-25,TPT Stream Operator,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,4,5,StreamVersionId,INSERT_DATA,$STREAM,1,2017-04-25,14.10.00.10,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,115,1,UtilityId,INSERT_DATA,$STREAM,1,2017-04-25,5,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,134,5,StreamTdpId,INSERT_DATA,$STREAM,1,2017-04-25,XXXXXX,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,5,5,StreamUserName,INSERT_DATA,$STREAM,1,2017-04-25,XXXXX_XXXX_XXX_USER,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,130,5,StreamDbase,INSERT_DATA,$STREAM,1,2017-04-25,XXXXX_XXXX_XXX_USER,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,180,1,TotalSessAssigned,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,181,1,TotalSessConnected,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,182,1,TASMSessionLimit,INSERT_DATA,$STREAM,1,2017-04-25,0,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,59,1,StreamImportBegin,INSERT_DATA,$STREAM,1,2017-04-25,1,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,186,3,CPUTimeByInstance,INSERT_DATA,$STREAM,1,2017-04-25,0.059474,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,137,0,StreamSessEnd,INSERT_DATA,$STREAM,1,2017-04-25,,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,179,5,OperatorEndTS,INSERT_DATA,$STREAM,1,2017-04-25,2017-04-25 01:03:13,1,0
LOAD_TABLE_FROM_FILE_COMPLETE-190364,18,1,OperatorExit,INSERT_DATA,$STREAM,1,2017-04-25,12,1,0

Private log stream_log:



===================================================================
= =
= TERADATA PARALLEL TRANSPORTER 32-BIT =
= =
= STREAM OPERATOR VERSION 14.10.00.10 =
= =
= OPERATOR SUPPORT LIBRARY VERSION 14.10.00.10 =
= =
= COPYRIGHT 2001-2014, TERADATA CORPORATION. =
= ALL RIGHTS RESERVED. =
= =
===================================================================

**** 01:03:10 Processing starting at: Tue Apr 25 01:03:10 2017

===================================================================
= =
= Module Identification =
= =
===================================================================

32-bit Stream Operator for AIX release 1 on petrie
StreamMain : 14.10.00.10
StreamCLI : 14.10.00.14
StreamSess : 14.10.00.10
StreamSort : 13.00.00.01
StreamUtil : 14.10.00.12
PcomCLI : 14.10.00.56
PcomMBCS : 14.10.00.02
PcomMsgs : 14.10.00.02
PcomNtfy : 14.10.00.05
PcomPx : 14.10.00.13
PcomUtil : 14.10.00.13
PXICU : 14.10.00.10
TDICU : 14.10.00.00
CLIv2 : 14.10.00.10

===================================================================
= =
= Attribute Definitions =
= =
===================================================================

**** 01:03:10 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
Error limit: 1 rejected record(s)
Tenacity: 4 hour limit to successfully connect
Sleep: 6 minute(s) between connect retries
Buffers: 3
Pack: 20
Robust: In effect

===================================================================
= =
= Column/Field Definition =
= =
===================================================================

Column Name Offset Length Type
============================== ====== ====== ========================
"Store_ID" 0 20 VARCHAR
"Postcode" 22 20 VARCHAR
"Country" 44 20 VARCHAR
"Region" 66 20 VARCHAR
"Priority" 88 11 VARCHAR
"Quarter" 101 20 VARCHAR
"WeekDay" 123 20 VARCHAR
"Product_Name" 145 20 VARCHAR
============================== ====== ====== ========================
INDICATOR BYTES NEEDED: 1
EXPECTED RECORD LENGTH: 168

===================================================================
= =
= Column/Field MacroCharSet Definition =
= =
===================================================================

Column Name MacroCharSet
============================== ====================================
"Store_ID" Not specified; will use default
"Postcode" Not specified; will use default
"Country" Not specified; will use default
"Region" Not specified; will use default
"Priority" Not specified; will use default
"Quarter" Not specified; will use default
"WeekDay" Not specified; will use default
"Product_Name" Not specified; will use default
============================== ====================================
**** 01:03:10 Operator Command ID for External Command Interface: $STREAM41615386

===================================================================
= =
= Control Session Connection =
= =
===================================================================

**** 01:03:10 Connecting to RDBMS: 'XXXXXX'
**** 01:03:10 Connecting with UserId: 'XXXXX_XXXX_XXX_USER'

===================================================================
= =
= Teradata Database Information =
= =
===================================================================

**** 01:03:11 Teradata Database Version: '15.10.04.07'
**** 01:03:11 Teradata Database Release: '15.10.04.02'
**** 01:03:11 Maximum request size supported: 1MB
**** 01:03:11 Session character set: 'ASCII'
**** 01:03:11 RDBMS supports upsert SQL
**** 01:03:11 Data Encryption: supported
**** 01:03:11 RDBMS supports Array Support
**** 01:03:11 Enhanced Statement Status Level: 1
**** 01:03:11 RDBMS supports Statement Independence
**** 01:03:11 Restart log table 'XXXXX_XXXX_XXX_APP.Bap_Load_Test_RL' has been created
**** 01:03:11 Error table 'XXXXX_XXXX_XXX_APP.Bap_Load_Test_ET' has been created
**** 01:03:11 Default MacroCharSet: 'LATIN'

===================================================================
= =
= Special Session Connection =
= =
===================================================================

**** 01:03:11 Maximum number of special sessions requested: All
**** 01:03:11 Minimum number of special sessions required: 1

Instance Assigned Connected Result
======== ======== ========= ======================
1 1 1 Successful
======== ======== ========= ======================
Total 1 1 Successful

===================================================================
= =
= DML Information =
= =
===================================================================

**** 01:03:11 DML statement for DML Group: 1

INSERT INTO XXXXX_XXXX_XXX_APP.Bap_Load_Test VALUES ( :Store_ID, :Postcode, :Country, :Region, :Priority, :Quarter, :WeekDay, :Product_Name);

===================================================================
= =
= Load Phase =
= =
===================================================================

**** 01:03:12 Starting to send data to the RDBMS


===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

**** 01:03:13 Logging off all sessions

Instance Cpu Time
======== ================
1 0.06 Seconds

**** 01:03:13 Total processor time used = '0.059474 Second(s)'
. Start : Tue Apr 25 01:03:10 2017
. End : Tue Apr 25 01:03:13 2017
. Highest return code encountered = '12'.
**** 01:03:13 This job terminated

Private log dtacop-xxxxxxxx-49021022-1:


= TraceFunction: 'NO (defaulted)' (=0) =
==========================================================================
= =
= TERADATA PARALLEL TRANSPORTER 32-BIT =
= =
= DATACONNECTOR OPERATOR VERSION 14.10.00.10 =
= =
= DataConnector UTILITY LIBRARY VERSION 14.10.00.78 =
= =
= COPYRIGHT 2001-2014, Teradata Corporation. ALL RIGHTS RESERVED. =
= =
==========================================================================

Operator name: '$DATACONNECTOR_PRODUCER' instance 1 of 1 [Producer]

**** 01:03:10 Processing starting at: Tue Apr 25 01:03:10 2017

==========================================================================
= =
= Operator module static specifics =
= =
= Operator module name:'dtacop', version:'14.10.00.10' =
= =
= pmdcomt_HeaderVersion: 'Common 14.10.00.09' - packing 'options align=p =
= acked' =
= pmddamt_HeaderVersion: 'Common 14.10.00.01' - packing 'options align=p =
= acked' =
= =
==========================================================================

==========================================================================
= =
= > General attribute Definitions < =
= =
= TraceLevel: '' =
= EndianFlip: 'NO (defaulted)' (=0) =
= IndicatorMode: 'NO (defaulted)' (=0) =
= NullColumns: 'YES (defaulted)' (=1) =
= SYSTEM_CharSetId: 'ASCII' =
= =
==========================================================================

BIG ENDIAN platform
WARNING! NotifyMethod: 'None (default)'
Operator 'dtacop' main source version:'14.10.00.41'

==========================================================================
= =
= > Operator attribute Definitions < =
= =
==========================================================================

FileList: 'NO (defaulted)' (=0)
MultipleReaders: 'NO (defaulted)' (=0)
EnableScan: 'Yes (defaulted)'
Initializing with CharSet = 'ASCII'.
Alphabetic CSName=ASCII
Established character set ASCII
Single-byte character set in use

==========================================================================
= =
= > Module Identification < =
= =
==========================================================================

32-bit DataConnector operator for AIX release 1 on petrie
TDICU................................... 14.10.00.00
PXICU................................... 14.10.00.10
PMPROCS................................. 14.10.00.11
PMRWFMT................................. 14.10.00.03
PMTRCE.................................. 13.00.00.02
PMMM.................................... 14.10.00.02
DCUDDI.................................. 14.10.00.59
PMHEXDMP................................ 14.10.00.01
PMUNXDSK................................ 14.10.00.13

WARNING! OpenMode attribute not specified, default 'Read' being used.
QuotedData: 'NO (defaulted)' (=0)
AcceptEOFasEOR: 'YES (defaulted)' (=1)
AppendDelimiter: 'NO (defaulted)' (=0)
TextDelimiter = ',' [1 byte(s) x'2C']
WARNING! EscapeTextDelimiter attribute not specified, there is no default
WARNING! OpenQuoteMark attribute not specified, there is no default
WARNING! CloseQuoteMark attribute not specified, there is no default
WARNING! EscapeQuoteDelimiter attribute not specified, there is no default
WARNING! TrimChar attribute not specified, there is no default
UseGeneralUDDIcase: 'NO (defaulted)' (=0)
UDDI_ParsingCase=1100000
WriteBOM: 'NO (defaulted)' (=0)
AcceptExcessColumns: 'NO (defaulted)' (=0)
AcceptMissingColumns: 'NO (defaulted)' (=0)
TruncateColumnData: 'NO (defaulted)' (=0)
TruncateColumns: 'NO (defaulted)' (=0)
TruncateLongCols: 'NO (defaulted)' (=0)
WARNING! RecordErrorFilePrefix attribute not specified, there is no default
RecordErrorVerbosity: OFF (default) (=0)
FileName: 'dummy_data_v2.csv'
OpenMode: 'READ' (1)
Format: 'DELIMITED' (1)
IOBufferSize: 131072

Full File Path: data/pending/766/dummy_data_v2.csv
Data Type Ind Length Offset M
VARCHAR ( 7) 1 20 0 N
VARCHAR ( 7) 1 20 20 N
VARCHAR ( 7) 1 20 40 N
VARCHAR ( 7) 1 20 60 N
VARCHAR ( 7) 1 11 80 N
VARCHAR ( 7) 1 20 91 N
VARCHAR ( 7) 1 20 111 N
VARCHAR ( 7) 1 20 131 N
Schema is not all character data
Schema is compatible with delimited data
Validating parsing case: 1100000.
SBCS (QUOTED DATA: No), Delimiter[1]: ','
Delimiter: x'2C'
Escape Delimiter: x''
Open Quote: x''
Close Quote: x''
Escape Quote: x''
Log will include stats only

**** 01:03:10 From file 'dummy_data_v2.csv' (Size 0), starting to send rows.
Closing file: 'Data File'
Detaching default Access Module
Files read by this instance: 0
**** 01:03:13 Total processor time used = '0.00 Seconds(s)'
**** 01:03:13 Total files processed: 0
Shutting down API

Teradata Employee

Re: TPT status code 12 during Insert

Much easier to read. Thanks!

Unfortunately, the output does not show any error.

And so I was wondering how you came up with the TPT10510 error in your first post.

The only way I can determine what may have gone wrong (unless you can send me the data file and script) is to ask you to turn trace on, and then send me the log file (the binary .out file) by email.

To turn on trace for the Stream operator, add an attribute to the script in the Stream operator definition:

        VARCHAR TraceLevel = 'all'

 

Thanks!

-- SteveF

Re: TPT status code 12 during Insert

Hi Steve,

 

Wanted to clarify the issue. We wanted to capture TPT error code and to do some action based on type of error code.

 

When we load a small file with one bad record and errorlimit=1, we get TPT10510 fatal error and one record in _ET table.

But, in this case, errorlimit is 1 and this file has one bad record that get inserted to _ET table. No TPT10510 error get raised. 

 

I will capture log with TraceLevel="all"

 

Thanks,

-Manas

 

Highlighted
Teradata Employee

Re: TPT status code 12 during Insert

We are looking into why there is a scenario where the TPT10510 error is not being output.

 

-- SteveF