All Forums Tools
ashk660 10 posts Joined 10/12
28 Jan 2014
TPT12106: Cannot restart !!! Please resubmit the job.

Hi,
I am trying to populate my load table form Oracle (source) using TPT. My TPT job is failing due to "TPT12106: Cannot restart !!! Please resubmit the job" and getting completed after dropping and recreating table.
TPT Log :
spin_OPERATOR: connecting sessions

spin_OPERATOR: preparing target table

spin_OPERATOR: entering Acquisition Phase

Job is running in Buffer Mode

Task(APPLY_1[0001]): restart completed, status = Success

spin_READ_OPERATOR: TPT12106: Cannot restart !!! Please resubmit the job.

**** 23:03:04 TPT12106: Cannot restart !!! Please resubmit the job.

TPT_INFRA: TPT02263: Error: Operator restart error, status = Multi Phase Error

Task(SELECT_2[0001]): restart completed, status = Multi Phase Error

TPT_INFRA: TPT03714: Error: Restart command failed with 37

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

spin_OPERATOR: disconnecting sessions

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

 

Are there any other ways to resolve this ?
 

Ashish
ratchetandclank 49 posts Joined 01/08
29 Jan 2014

The information provided is not enough to suggest. Please provide the following information:
 
* TPT Script
* Oracle ODBC driver used and its version. 
* Sample data (if possible).

TDJeet1982 9 posts Joined 06/12
29 Jan 2014

Please NOTE: This script does not fail at every run.

To add on this ,the script runs fine but throws error in one of runs.
I mean in 4-5 consecutive runs it will fail once or twice .
Workaround : I recreate the table and it runs fine then again for few of the run and fails again.
This is not only with this particular script but i have more scripts behave in same way.
 
 
ODBC Driver Information : Datadirect drivers

     ===================================================================

     =                                                                 =

     =                     ODBC Driver Information                     =

     =                                                                 =

     ===================================================================

 

     ODBC version        = '03.52.0000'

     DBMS name           = 'Oracle'

     DBMS version        = '11.02.0000 Oracle 11.2.0.3.0'

     Driver name         = 'W#ora27.dll'

     Driver version      = '07.12.0060 (B0067, U0042)'

     Driver ODBC version = '03.52'

 

==========================================================================

TPT Script:

USING CHARACTER SET  ASCII

 

DEFINE JOB bracket_pl_rel_spin_LOAD

 

DESCRIPTION 'LOAD bracket_pl_rel_spin_ld TABLE'

 

(

 

 

 

  DEFINE SCHEMA bracket_pl_rel_spin_ODBC_SCHEMA

 

  DESCRIPTION 'TABLE bracket_pl_rel_spin_ld ODBC SCHEMA'

 

  (

 

    SourceCol_1          VARCHAR(20),

 

    SourceCol_2          VARCHAR(40),

 

    SourceCol_3          VARCHAR(125),

 

    SourceCol_4          VARCHAR(255),

 

    SourceCol_5          NUMBER,

 

    SourceCol_6          VARCHAR(60),

 

    SourceCol_7          TIMESTAMP

 

  );

 

 

 

  DEFINE OPERATOR bracket_pl_rel_spin_DDL_OPERATOR()

 

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'

 

  TYPE DDL

 

  ATTRIBUTES

 

  (

 

    VARCHAR PrivateLogName = 'bracket_pl_rel_spin_DDL',

 

    VARCHAR TdpId = 'edwp',

 

    VARCHAR UserName = 'abc',

 

    VARCHAR UserPassword = @TDPassword,

 

    VARCHAR AccountID,

 

    VARCHAR ErrorList = '3807'

 

  );

 

 

 

  DEFINE OPERATOR bracket_pl_rel_spin_OPERATOR

 

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

 

  TYPE LOAD

 

  SCHEMA *

 

  ATTRIBUTES

 

  (

 

    INTEGER Errorlimit=1,

 

    INTEGER MaxSessions = 20,

 

    INTEGER MinSessions = 1,

 

    VARCHAR UserName = 'abc',

 

    VARCHAR UserPassword = @TDPassword,

 

    VARCHAR AccountID,

 

    VARCHAR PrivateLogName = 'bracket_pl_rel_spin_LOAD',

 

    VARCHAR TdpId = 'edwp',

 

    VARCHAR LogTable = 'DEV_EDW_TMP.bracket_pl_rel_spin_LT',

 

    VARCHAR WorkTable = 'DEV_EDW_TMP.bracket_pl_rel_spin_WT',

 

    VARCHAR ErrorTable1 = 'DEV_EDW_TMP.bracket_pl_rel_spin_E1',

 

    VARCHAR ErrorTable2 = 'DEV_EDW_TMP.bracket_pl_rel_spin_E2',

 

    VARCHAR TargetTable = 'DEV_EDW_LZ.bracket_pl_rel_spin_ld'

 

  );

 

 

 

  DEFINE OPERATOR bracket_pl_rel_spin_READ_OPERATOR

 

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER ODBC READER'

 

  TYPE ODBC

 

  SCHEMA bracket_pl_rel_spin_ODBC_SCHEMA

 

  ATTRIBUTES

 

  (

 

    VARCHAR DSNName = 'Oracle_PSWTOOLS_dd',

 

    VARCHAR UserName = 'xyz',

 

    VARCHAR UserPassword = @SRCPassword,

 

    VARCHAR PrivateLogName,

 

    VARCHAR ConnectString,

 

    VARCHAR TruncateData,

 

    VARCHAR SelectStmt = 'SELECT bracket_pl_rel_spin.CODE_ID

 

                               , bracket_pl_rel_spin.CODE_NAME

 

                               , bracket_pl_rel_spin.SHORT_NAME

 

                               , bracket_pl_rel_spin.DESCRIPTION

 

                               , bracket_pl_rel_spin.SORT_KEY

 

                               , bracket_pl_rel_spin.LAST_MODIFIED_BY

 

                               , cast( TO_CHAR(SYS_EXTRACT_UTC(last_modified ), ''yyyy-mm-dd hh24:mi:ss.sssss'')  as char(26))

 

                          FROM rli.bracket_pl_rel_spin'

 

  );

 

 

 

  STEP setup_tables

 

  (

 

    APPLY

 

    ('DROP TABLE DEV_EDW_TMP.bracket_pl_rel_spin_E1;'),

 

    ('DROP TABLE DEV_EDW_TMP.bracket_pl_rel_spin_E2;'),

 

    ('DROP TABLE DEV_EDW_TMP.bracket_pl_rel_spin_WT;'),

 

    ('DROP TABLE DEV_EDW_TMP.bracket_pl_rel_spin_LT;')

 

    TO OPERATOR ( bracket_pl_rel_spin_DDL_OPERATOR[1] );

 

  );

 

 

 

  STEP load_tables

 

  (

 

    APPLY

 

    (

 

      'INSERT INTO DEV_EDW_LZ.bracket_pl_rel_spin_ld

 

      ( code_id

 

      , code_name

 

      , short_name

 

      , description

 

      , sort_key

 

      , last_modified_by

 

      , last_modified

 

      )

 

      VALUES

 

      ( :SourceCol_1

 

      , :SourceCol_2

 

      , :SourceCol_3

 

      , :SourceCol_4

 

      , :SourceCol_5

 

      , :SourceCol_6

 

      , :SourceCol_7

 

      );'

 

    )

 

    TO OPERATOR ( bracket_pl_rel_spin_OPERATOR[1])

 

    SELECT * FROM OPERATOR ( bracket_pl_rel_spin_READ_OPERATOR[1] );

 

  );

 

 

 

);

 

 

 

===========================================================================

TDJeet1982 9 posts Joined 06/12
29 Jan 2014

LOG FILE :

Teradata Parallel Transporter Version 14.00.00.08

Job log: C:\Program Files (x86)\Teradata\client\14.00\Teradata Parallel Transporter/logs/_edw-17467.out

Job id is _edw-17467, running on beta

Teradata Parallel Transporter SQL DDL Operator Version 14.00.00.08

bracket_pl_rel_spin_DDL_OPERATOR: private log specified: bracket_pl_rel_spin_DDL

bracket_pl_rel_spin_DDL_OPERATOR: connecting sessions

bracket_pl_rel_spin_DDL_OPERATOR: sending SQL requests

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_E1' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_E2' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_WT' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_LT' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: disconnecting sessions

bracket_pl_rel_spin_DDL_OPERATOR: Total processor time used = '0.40625 Second(s)'

bracket_pl_rel_spin_DDL_OPERATOR: Start : Mon Jan 27 23:02:49 2014

bracket_pl_rel_spin_DDL_OPERATOR: End   : Mon Jan 27 23:02:52 2014

Job step setup_tables completed successfully

Teradata Parallel Transporter Load Operator Version 14.00.00.08

bracket_pl_rel_spin_OPERATOR: private log specified: bracket_pl_rel_spin_LOAD

Teradata Parallel Transporter ODBC Operator Version 14.00.00.08

bracket_pl_rel_spin_READ_OPERATOR: private log not specified

bracket_pl_rel_spin_READ_OPERATOR: connecting sessions

bracket_pl_rel_spin_OPERATOR: connecting sessions

bracket_pl_rel_spin_OPERATOR: preparing target table

bracket_pl_rel_spin_OPERATOR: entering Acquisition Phase

bracket_pl_rel_spin_READ_OPERATOR: TPT12106: Cannot restart !!! Please resubmit the job.

TPT_INFRA: TPT02263: Error: Operator restart error, status = Multi Phase Error

Task(SELECT_2[0001]): restart completed, status = Multi Phase Error

bracket_pl_rel_spin_OPERATOR: disconnecting sessions

bracket_pl_rel_spin_READ_OPERATOR: disconnecting sessions

bracket_pl_rel_spin_READ_OPERATOR: Total processor time used = '0.015625 Second(s)'

bracket_pl_rel_spin_READ_OPERATOR: Start : Mon Jan 27 23:02:55 2014

bracket_pl_rel_spin_READ_OPERATOR: End   : Mon Jan 27 23:03:04 2014

bracket_pl_rel_spin_OPERATOR: Total processor time used = '1.25 Second(s)'

bracket_pl_rel_spin_OPERATOR: Start : Mon Jan 27 23:02:55 2014

bracket_pl_rel_spin_OPERATOR: End   : Mon Jan 27 23:03:07 2014

Job step load_tables terminated (status 12)

Job _edw_dashboard terminated (status 12)

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize

Teradata Parallel Transporter Coordinator Version 14.00.00.08

Teradata Parallel Transporter Executor Version 14.00.00.08

Teradata Parallel Transporter SQL DDL Operator Version 14.00.00.08

bracket_pl_rel_spin_DDL_OPERATOR: private log specified: bracket_pl_rel_spin_DDL

bracket_pl_rel_spin_DDL_OPERATOR: connecting sessions

bracket_pl_rel_spin_DDL_OPERATOR: sending SQL requests

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_E1' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_E2' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_WT' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

bracket_pl_rel_spin_DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'DEV_EDW_TMP.bracket_pl_rel_spin_LT' does not exist.

bracket_pl_rel_spin_DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

bracket_pl_rel_spin_DDL_OPERATOR: disconnecting sessions

bracket_pl_rel_spin_DDL_OPERATOR: Total processor time used = '0.40625 Second(s)'

bracket_pl_rel_spin_DDL_OPERATOR: Start : Mon Jan 27 23:02:49 2014

bracket_pl_rel_spin_DDL_OPERATOR: End   : Mon Jan 27 23:02:52 2014

Job step setup_tables completed successfully

Teradata Parallel Transporter Executor Version 14.00.00.08

Teradata Parallel Transporter Coordinator Version 14.00.00.08

Teradata Parallel Transporter Executor Version 14.00.00.08

Teradata Parallel Transporter Load Operator Version 14.00.00.08

bracket_pl_rel_spin_OPERATOR: private log specified: bracket_pl_rel_spin_LOAD

Teradata Parallel Transporter ODBC Operator Version 14.00.00.08

bracket_pl_rel_spin_READ_OPERATOR: private log not specified

 

     ===================================================================

     =                                                                 =

     =                  TERADATA PARALLEL TRANSPORTER                  =

     =                                                                 =

     =              ODBC OPERATOR     VERSION 14.00.00.08              =

     =                                                                 =

     =          OPERATOR SUPPORT LIBRARY VERSION 14.00.00.08           =

     =                                                                 =

     = COPYRIGHT 2001-2012, TERADATA CORPORATION. ALL RIGHTS RESERVED. =

     =                                                                 =

     ===================================================================

 

**** 23:02:55 Processing starting at: Mon Jan 27 23:02:55 2014

 

     ===================================================================

     =                                                                 =

     =                      Module Identification                      =

     =                                                                 =

     ===================================================================

 

     ODBC Operator for Windows on beta

     ODBCMain   : 14.00.00.04

     ODBCSQL    : 14.00.00.06

     ODBCUtil   : 14.00.00.03

     PcomCLI    : 14.00.00.24

     PcomMBCS   : 14.00.00.01

     PcomMsgs   : 14.00.00.04

     PcomNtfy   : 14.00.00.01

     PcomPx     : 14.00.00.11

     PcomUtil   : 14.00.00.09

     PXICU      : 14.00.00.08

     TDICU      : 14.00.00.01

 

     ===================================================================

     =                                                                 =

     =                      Attribute Definitions                      =

     =                                                                 =

     ===================================================================

 

**** 23:02:55 Options in effect for this job:

              OperatorType:  Producer

              Instances:     1

              Character set: 'ASCII'

              Checkpoint:    No checkpoint in effect

              Restart:       Yes

              DataBlockSize: 64K bytes

 

     ===================================================================

     =                                                                 =

     =                     SQL Session Connection                      =

     =                                                                 =

     ===================================================================

 

**** 23:02:55 Connecting to RDBMS:    'Oracle'

**** 23:02:55 Connecting with UserId: 'xyz'

bracket_pl_rel_spin_READ_OPERATOR: connecting sessions

 

     ===================================================================

     =                                                                 =

     =                     ODBC Driver Information                     =

     =                                                                 =

     ===================================================================

 

     ODBC version        = '03.52.0000'

     DBMS name           = 'Oracle'

     DBMS version        = '11.02.0000 Oracle 11.2.0.3.0'

     Driver name         = 'W#ora27.dll'

     Driver version      = '07.12.0060 (B0067, U0042)'

     Driver ODBC version = '03.52'

 

     ===================================================================

     =                                                                 =

     =               Source Table Column/Field Defintion               =

     =                                                                 =

     ===================================================================

 

     Column Name                    Offset Length Type      

     ============================== ====== ====== ========================

     CODE_ID                             0     20 VARCHAR2

     CODE_NAME                          20     40 VARCHAR2

     SHORT_NAME                         60    125 VARCHAR2

     DESCRIPTION                       185    255 VARCHAR2

     SORT_KEY                          440      8 BINARY_DOUBLE

     LAST_MODIFIED_BY                  448     60 VARCHAR2

     CAST(TO_CHAR(SYS_EXTRACT_UTC(LAST_MODIFIED),'YYYY-MM-DDHH24:MI:SS.SSSSS')ASCHAR(26))    508     26 CHAR

     ============================== ====== ====== ========================

     EXPECTED RECORD LENGTH: 534

 

     ===================================================================

     =                                                                 =

     =                     Column/Field Definition                     =

     =                                                                 =

     ===================================================================

 

     Column Name                    Offset Length Type      

     ============================== ====== ====== ========================

     SourceCol_1                         0     20 VARCHAR

     SourceCol_2                        22     40 VARCHAR

     SourceCol_3                        64    125 VARCHAR

     SourceCol_4                       191    255 VARCHAR

     SourceCol_5                       448     18 NUMBER(*)

     SourceCol_6                       468     60 VARCHAR

     SourceCol_7                       530     26 CHAR

     ============================== ====== ====== ========================

     INDICATOR BYTES NEEDED: 1

     EXPECTED RECORD LENGTH: 557

bracket_pl_rel_spin_OPERATOR: connecting sessions

bracket_pl_rel_spin_OPERATOR: preparing target table

bracket_pl_rel_spin_OPERATOR: entering Acquisition Phase

Job is running in Buffer Mode

Task(APPLY_1[0001]): restart completed, status = Success

bracket_pl_rel_spin_READ_OPERATOR: TPT12106: Cannot restart !!! Please resubmit the job.

**** 23:03:04 TPT12106: Cannot restart !!! Please resubmit the job.

TPT_INFRA: TPT02263: Error: Operator restart error, status = Multi Phase Error

Task(SELECT_2[0001]): restart completed, status = Multi Phase Error

TPT_INFRA: TPT03714: Error: Restart command failed with 37

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

bracket_pl_rel_spin_OPERATOR: disconnecting sessions

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

 

     ===================================================================

     =                                                                 =

     =                        Logoff/Disconnect                        =

     =                                                                 =

     ===================================================================

 

bracket_pl_rel_spin_READ_OPERATOR: disconnecting sessions

**** 23:03:04 Logging off all sessions

 

              Instance      Cpu Time     

              ========  ================ 

                   1        0.02 Seconds

 

**** 23:03:04 Total processor time used = '0.015625 Second(s)'

.        Start : Mon Jan 27 23:02:55 2014

.        End   : Mon Jan 27 23:03:04 2014

.        Highest return code encountered = '12'.

bracket_pl_rel_spin_READ_OPERATOR: Total processor time used = '0.015625 Second(s)'

bracket_pl_rel_spin_READ_OPERATOR: Start : Mon Jan 27 23:02:55 2014

bracket_pl_rel_spin_READ_OPERATOR: End   : Mon Jan 27 23:03:04 2014

**** 23:03:04 This job terminated

bracket_pl_rel_spin_OPERATOR: Total processor time used = '1.25 Second(s)'

bracket_pl_rel_spin_OPERATOR: Start : Mon Jan 27 23:02:55 2014

bracket_pl_rel_spin_OPERATOR: End   : Mon Jan 27 23:03:07 2014

Job step load_tables terminated (status 12)

Job _edw_dashboard terminated (status 12)

Total available memory:          10000000

Largest allocable area:          10000000

Memory use high water mark:         81428

Free map size:                       1024

Free map use high water mark:          22

Free list use high water mark:          0

 

TDJeet1982 9 posts Joined 06/12
30 Jan 2014

Adding more to above question : How many TPT loads we can run in parallel?
Is there any attribute that is used to limit this ? or does it depend on utility slots ?

feinholz 1234 posts Joined 05/08
30 Jan 2014

It depends on utility slots.

--SteveF

TDJeet1982 9 posts Joined 06/12
03 Feb 2014

This did not solve my problem.
 

feinholz 1234 posts Joined 05/08
04 Feb 2014

It looks like something is out of sync.
For some reason the ODBC operator thinks the job is a restart job.
Start from the beginning, but before you run tbuild, try running:
 
     $ twbrmcp is _edw
 
(I used "is_edw" because that looks like the user id used for the job that had the error.)

--SteveF

TDJeet1982 9 posts Joined 06/12
09 Feb 2014

Reason : Checkpoint files are getting created of same name for all jobs.
Need to use jobname -j option. 

You must sign in to leave a comment.