TPT return code 12 during INSERT

Tools
Enthusiast

TPT return code 12 during INSERT

Hello,

I had a TPT script that was working great, pulling data from an Oracle DB via ODBC, and inserting into Teradata.  Then I changed the target Teradata DB to a different DB, and now I'm getting a return code 12 during INSERT operator.  The new target DB is not the default DB of the TPT user.  The private log is shown below, as well as the TPT script.  The table GP_ORA_STAGE was indeed created in Teradata, but I get the message "is being loaded" when I try to SELECT from the table.  Can anyone tell me what could be causing this?  Thanks!

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

     =                                                                 =

     =                  Teradata Database Information                  =

     =                                                                 =

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

**** 17:21:59 Teradata Database Version:      '13.10.03.08                     '

**** 17:21:59 Teradata Database Release:      '13.10.03.07                   '

**** 17:21:59 Maximum request size supported: 1MB

**** 17:21:59 Session character set:          'ASCII'

**** 17:21:59 Total AMPs available:           72

**** 17:21:59 Data Encryption:                supported

**** 17:21:59 Restart log table 'odbc_test_log' has been created

**** 17:21:59 Current working DATABASE set:   'xxxxxxx'

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

     =                                                                 =

     =                   Special Session Connection                    =

     =                                                                 =

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

**** 17:22:01 Number of sessions adjusted due to TASM:      23

              Instance Assigned Connected Result

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

                  1        12       12    Successful

                  2        11       11    Successful

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

                Total      23       23    Successful

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

     =                                                                 =

     =                 Target/Error Table Information                  =

     =                                                                 =

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

**** 17:22:01 This job will use the following tables:

              Target Table:  'GP_ORA_STAGE '

              Error Table 1: 'odbc_test_error1'

              Error Table 2: 'odbc_test_error2'

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

     =                                                                 =

     =                        Acquisition Phase                        =

     =                                                                 =

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

**** 17:22:01 DML statement for DML Group: 1

     INSERT INTO GP_ORA_STAGE (MANUFACTURER_C,

                               NAME_RW

                               ) VALUES (:MANUFACTURER_C,

                                         :NAME_RW

                                         );

**** 17:22:01 Number of records per buffer for this job: 222

**** 17:22:03 Starting to send data to the RDBMS

**** 17:22:05 Checkpoint complete. Rows sent: 2922

**** 17:22:08 Finished sending rows to the RDBMS

                        Instance    Rows Sent

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

                            1            2922

                            2               0

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

                          Total          2922

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

     =                                                                 =

     =                        Application Phase                        =

     =                                                                 =

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

**** 17:22:08 Application Phase Begin

**** 17:22:08 TPT10508: RDBMS error 3807: Object 'odbc_test_log' does not exist.

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

     =                                                                 =

     =                        Logoff/Disconnect                        =

     =                                                                 =

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

**** 17:22:08 Logging off all sessions

              Instance      Cpu Time

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

                   1        0.30 Seconds

                   2        0.23 Seconds

**** 17:22:09 Total processor time used = '0.53125 Second(s)'

.        Start : Sat May 05 17:21:54 2012

.        End   : Sat May 05 17:22:09 2012

.        Highest return code encountered = '12'.

**** 17:22:09 This job terminated

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

/**************************************************************************/

/* */

/* Adapted from Teradata Parallel Transporter User Guide - Job Example 06 */ 

/* */

/**************************************************************************/

/* */

/* Description: */

/* */

/* This example script uses two job steps. */

/* */

/* The first job step, called "Setup_Tables", uses the DDL */

/* Operator to setup the target table. */

/* */

/* The second job step, called "Insert_Into_Table", uses the */

/* ODBC Operator to read data from the Oracle stage table  */

/* and uses the Load Operator to write the data to the */

/* the empty Teradata stage table. */

/* */

/**************************************************************/

DEFINE JOB MOVE_ORA_TO_TD

DESCRIPTION 'MOVE ORACLE STAGE TABLE TO TERADATA'

(

DEFINE SCHEMA Stage_Schema

(

MANUFACTURER_C     VARCHAR(30),                                                

NAME_RW     VARCHAR(254)                                                       

);

DEFINE OPERATOR DDL_Operator

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName = 'gp_ddl_log',

VARCHAR TdpId = 'xxxxxxx',

VARCHAR UserName = @UsrID,

VARCHAR UserPassword = @Pwd,

VARCHAR WorkingDatabase = 'xxxxxxx',

VARCHAR ARRAY ErrorList = ['3807','3803']

);

DEFINE OPERATOR ODBC_Operator

DESCRIPTION 'Teradata Parallel Transporter ODBC Operator'

TYPE ODBC

SCHEMA Stage_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName = 'gp_odbc_log',

VARCHAR DSNName = 'ORCLSE_DDWP',

VARCHAR UserName = @OUsrID,

VARCHAR UserPassword = @OPwd,

VARCHAR SelectStmt = 'SELECT * FROM GP_ORA_STAGE;'

);

DEFINE OPERATOR Load_Operator

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'load_log',

VARCHAR TdpId = 'xxxxxxx',

VARCHAR UserName = @UsrID,

VARCHAR UserPassword = @Pwd,

VARCHAR WorkingDatabase = 'xxxxxxx',

VARCHAR TargetTable = 'GP_ORA_STAGE ',

VARCHAR LogTable = 'odbc_test_log',

VARCHAR ErrorTable1 = 'odbc_test_error1',

VARCHAR ErrorTable2 = 'odbc_test_error2'

);

Step Setup_Into_Tables

(

APPLY

/***('drop table odbc_test_result_e1;' ),

('drop table odbc_test_result_e2;' ),

('drop table odbc_test_result;' ), ***/

('drop table GP_ORA_STAGE;' ),

('create table GP_ORA_STAGE (

MANUFACTURER_C     VARCHAR(30),                                                

NAME_RW     VARCHAR(254)                                                       

 );')

TO OPERATOR (DDL_Operator);

);

Step Insert_Into_Tables

(

APPLY

('INSERT INTO GP_ORA_STAGE (

MANUFACTURER_C,                                                                

NAME_RW                                                                        

) VALUES

(

:MANUFACTURER_C,                                                               

:NAME_RW                                                                       

);')

TO OPERATOR (Load_Operator[2])

SELECT

MANUFACTURER_C,                                                                

NAME_RW                                                                        

FROM OPERATOR (ODBC_Operator);

);

);

Tags (5)
7 REPLIES
Enthusiast

Re: TPT return code 12 during INSERT

Can anyone lend a hand on this?   Where can I find more information about this error?   The public log says "terminated status 12".   I could not find any info on status 12 in the TPT User Guide.  The User Guide gives info on accessing the error tables, but when I try to SELECT from the tables using BTEQ, I am told that the table is being loaded.

select ErrorCode, ErrorFieldName from medmining_feas.odbc_test_error1;

 *** Failure 2652 Operation not allowed: Medmining_FEAS.odbc_test_error1 is

 being Loaded.

                Statement# 1, Info =0

 *** Total elapsed time was 1 second.

select * from medmining_feas.odbc_test_error2;

 *** Failure 2652 Operation not allowed: Medmining_FEAS.odbc_test_error2 is

 being Loaded.

                Statement# 1, Info =0

 *** Total elapsed time was 1 second.

Below is a copy of the public log.

C:\Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\logs>tlogvi

ew -j XG_MEDMINING_OR_MANUFACTURER-615

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize

Teradata Parallel Transporter Coordinator Version 13.10.00.04

Teradata Parallel Transporter Executor Version 13.10.00.04

Teradata Parallel Transporter SQL DDL Operator Version 13.10.00.04

DDL_Operator: private log specified: gp_ddl_log

DDL_Operator: connecting sessions

DDL_Operator: sending SQL requests

DDL_Operator: TPT10508: RDBMS error 3807: Object 'GP_ORA_STAGE' does not exist.

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

DDL_Operator: disconnecting sessions

DDL_Operator: Total processor time used = '0.125 Second(s)'

DDL_Operator: Start : Tue May 08 21:37:21 2012

DDL_Operator: End   : Tue May 08 21:37:26 2012

Job step Setup_Into_Tables completed successfully

Teradata Parallel Transporter Coordinator Version 13.10.00.04

Teradata Parallel Transporter Executor Version 13.10.00.04

Teradata Parallel Transporter Executor Version 13.10.00.04

Teradata Parallel Transporter Executor Version 13.10.00.04

Teradata Parallel Transporter Load Operator Version 13.10.00.03

Load_Operator: private log specified: load_log

Teradata Parallel Transporter ODBC Operator Version 13.10.00.04

ODBC_Operator: private log specified: gp_odbc_log

ODBC_Operator: connecting sessions

Load_Operator: connecting sessions

Load_Operator: preparing target table

Job is running in Buffer Mode

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

Task(SELECT_2[0001]): checkpoint completed, status = Success

Task(APPLY_1[0002]): checkpoint completed, status = Success

ODBC_Operator: sending SELECT request

Data Block size: 1092632  Buffers/Block: 17  Data Buffer size: 64272

ODBC_Operator: data retrieval complete

ODBC_Operator: Total Rows Exported:  2922

Task(SELECT_2[0001]) ready to checkpoint

Task(SELECT_2[0001]): checkpoint completed, status = Success

Load_Operator: entering Application Phase

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

 Total Rows Sent = 0

Load_Operator: TPT10508: RDBMS error 3807: Object 'odbc_test_log' does not exist

.

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

 Total Rows Sent = 0

Load_Operator: disconnecting sessions

ODBC_Operator: disconnecting sessions

ODBC_Operator: Total processor time used = '0.03125 Second(s)'

ODBC_Operator: Start : Tue May 08 21:37:29 2012

ODBC_Operator: End   : Tue May 08 21:37:40 2012

Load_Operator: Total processor time used = '0.1875 Second(s)'

Load_Operator: Start : Tue May 08 21:37:29 2012

Load_Operator: End   : Tue May 08 21:37:41 2012

TPT_INFRA: TPT02258: Error: Operator checkpointing error, status = System Error

Task(APPLY_1[0002]): checkpoint completed, status = System Error

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

 Total Rows Sent = 0

Job step Insert_Into_Tables terminated (status 12)

Job XG_MEDMINING_OR_MANUFACTURER terminated (status 12)

Total available memory:          10000000

Largest allocable area:          10000000

Memory use high water mark:       1101888

Free map size:                       1024

Free map use high water mark:          21

Free list use high water mark:          0

Teradata Employee

Re: TPT return code 12 during INSERT

This might be a case where you switched databases but did not clean up between the first job run and the subsequent one.

Thus, TPT thinks the job is a restart, but the error you are getting is this:

Load_Operator: TPT10508: RDBMS error 3807: Object 'odbc_test_log' does not exist

The output shows that the job went straight to the Application Phase but now the DBS cannot find the table.

Most likely, you will need to clean up the job and start over.

Something is out of synch between the 2 jobs.

-- SteveF
Enthusiast

Re: TPT return code 12 during INSERT

Hi Steven,

Thanks for your response.  To start fresh, I dropped all the error log tables  in both DB's and deleted all files in the checkpoint directory.   Then, when transferring data to the "new" database (different than the user's default), the ErrorTable1 and ErrorTable2 tables are correctly created in the new database, but the LogTable is still being created in the user's default database, so I believe the TPT Load operator is not finding the LogTable where it expects to see it.   Is there any way to resolve this?   When transferring data to the user's default database, all error tables are created in the default database, and there is no error.

The way that I am specifying the new database is using the WorkingDatabase attribute.  Is there perhaps another way to set the working database?  Perhaps the actual SQL DATABASE statement?

DEFINE OPERATOR Load_Operator

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'load_log',

VARCHAR TdpId = 'ghstdanx',

VARCHAR UserName = @UsrID,

VARCHAR UserPassword = @Pwd,

VARCHAR WorkingDatabase = FEAS',

VARCHAR TargetTable = 'GP_ORA_STAGE ',

VARCHAR LogTable = 'odbc_test_log',

VARCHAR ErrorTable1 = 'odbc_test_error1',

VARCHAR ErrorTable2 = 'odbc_test_error2'

);

Thanks!

-Greg

Teradata Employee

Re: TPT return code 12 during INSERT

Try naming the log table with the database name qualifier. When you provide a value for the WorkingDatabase, we issue the SQL DATABASE request under the covers.

But that then changes the context of the database from which the job is run.

However, for historical reasons, we create the log table prior to issuing the DATABASE request.

I believe we document this. If you want the log table in a different database context from where the load is taking place, you must fully qualify the log table name.

-- SteveF
Enthusiast

Re: TPT return code 12 during INSERT

Hi Steven,

Hey, that worked !   Thanks alot.  Guess I should have tried qualifying that, but I thought the WorkingDatabase covered it.

-Greg

Enthusiast

Re: TPT return code 12 during INSERT

I have question to @feinholz regarding the "Data Block Size" 1092632 mentioned in the above log:

ODBC_Operator: sending SELECT request
Data Block size: 1092632 Buffers/Block: 17 Data Buffer size: 64272
ODBC_Operator: data retrieval complete
ODBC_Operator: Total Rows Exported: 2922

And mine load script is dealing with 16K bytes wide row, but it only uses 449912. I want to learn which parameters in TPT script will affect the "Data Block Size" and how to tune it in the positive way.

Task(SELECT_2[0008]): checkpoint completed, status = Success

Task(SELECT_2[0012]): checkpoint completed, status = Success

Task(SELECT_2[0010]): checkpoint completed, status = Success

Data Block size: 449912  Buffers/Block: 7  Data Buffer size: 64272

Thanks a lot.

Teradata Employee

Re: TPT return code 12 during INSERT

@gpolanch: it is not intuitive, but the WorkingDatabase dictates to which database context we switch after we connect. However, due to a backwards compatibility issue (we have to act the way MultiLoad acted), the restart log table handling occurs prior to switching database contexts.

@ericsun2: please provide the exact version of TPT you are using.

-- SteveF