All Forums Tools
gpolanch 46 posts Joined 12/11
05 May 2012
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);
);

);

 

 

gpolanch 46 posts Joined 12/11
08 May 2012

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

feinholz 1234 posts Joined 05/08
08 May 2012

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

gpolanch 46 posts Joined 12/11
10 May 2012

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

feinholz 1234 posts Joined 05/08
11 May 2012

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

gpolanch 46 posts Joined 12/11
12 May 2012

Hi Steven,

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

-Greg

 

ericsun2 44 posts Joined 06/10
08 Mar 2013

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.
 

feinholz 1234 posts Joined 05/08
08 Mar 2013

@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

You must sign in to leave a comment.