load_operator: TPT10517: At least 1 instance could not connect special sessions

Tools & Utilities
Enthusiast

load_operator: TPT10517: At least 1 instance could not connect special sessions

Hi, fellows. 

We need your help. We are getting this error 

load_operator: TPT10517: At least 1 instance could not connect special sessions

when we want to load from INFORMIX SERVER to Teradata server. through TPT tool. The error describes that not be able to connect.

I'm attaching the log in order you can check any details. It looks like doesn't any additional error. 

Please, your kindly help. 

 

I'm a newbie DBA in Teradata and this is an error reported by the developer.

qv03-server>tlogview -l job_tpt_tabla_prod-16.out
Using memory mapped file for IPC

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Coordinator Version 15.00.00.00
Teradata Parallel Transporter SQL DDL Operator Version 15.00.00.00
ddl_operator: private log specified: ddl_log
ddl_operator: connecting sessions
ddl_operator: sending SQL requests
ddl_operator: TPT10508: RDBMS error 3807: Object 'DWH_LOG.ET_trcompras_2' does not exist.
ddl_operator: TPT18046: Warning: error is ignored as requested in ErrorList
ddl_operator: TPT10508: RDBMS error 3807: Object 'DWH_LOG.UV_trcompras_2' 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.106594 Second(s)'
ddl_operator: Start : Tue Mar 21 08:47:24 2017
ddl_operator: End   : Tue Mar 21 08:47:27 2017
Job step setup_tables completed successfully
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Coordinator Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter file_reader[1]: TPT19006 Version 15.00.00.00
file_reader[1]: TPT19206 Attribute 'TraceLevel' value reset to 'Statistics Only'.
file_reader[1]: TPT19010 Instance 1 directing private log report to 'dataconnector_log-1'.
file_reader[1]: TPT19003 NotifyMethod: 'None (default)'
file_reader[1]: TPT19008 DataConnector Producer operator Instances: 1
file_reader[1]: TPT19003 ECI operator ID: 'file_reader-40698064'
Teradata Parallel Transporter Load Operator Version 15.00.00.00
load_operator: private log specified: load_log
file_reader[1]: TPT19222 Operator instance 1 processing file '/work1/teradata/dat/trcompras_pipe_prod'.
load_operator: connecting sessions
load_operator: TPT10517: At least 1 instance could not connect special sessions
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
load_operator: disconnecting sessions
load_operator: Total processor time used = '0.386705 Second(s)'
load_operator: Start : Tue Mar 21 08:47:36 2017
load_operator: End   : Tue Mar 21 08:47:42 2017
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
file_reader[1]: TPT19221 Total files processed: 0.
file_reader[1]: TPT19229 0 error rows sent to error file /work1/teradata/dat/trcompras.err
Job step load_trans_table terminated (status 12)
Job job_tpt_tabla_prod terminated (status 12)
Job start: Tue Mar 21 08:47:20 2017
Job end:   Tue Mar 21 08:47:44 2017
Total available memory:          20000632
Largest allocable area:          20000632
Memory use high water mark:        163752
Free map size:                       1024
Free map use high water mark:          49
Free list use high water mark:          0

 

 

15 REPLIES
Teradata Employee

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Does the script specify multiple instances of the load operator?

You can't use more instances than the number of parallel sessions (granted by TASM, or requested by the script whichever is less).

With a single instance of the Data Connector operator, there's no benefit in using multiple Load operator instances - just use multiple sessions per instance.

Teradata Employee

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Look at the output from the Load operator private log (if one is specified in the script; if not, then all of the Load operator messages will be in the public log which can be output with the tlogview tool).

 

One thing that confuses people is that they specified some number of sessions to use, but TASM changed that number, and the resultant number of sessions to be used in the job us less than the total number of instances specified in the job.

 

The Load operator output will show the session count requested by the user and what was actually used for the job. If there was a TASM override, the output will show that as well.

 

Incidentally, you do not need very many Load operator instances. In most cases, 1 is enough. In other cases, I have seen 2 being used. Given enough data sessions, it is rare that any job would need more than 2 instances of the Load operator.

 

-- SteveF
Enthusiast

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Hi thanks for ur response. It looks like on the load script they are not using any parameters about connections numbers. Where can i located "... Load operator messages will be in the public log which can be output with the tlogview tool)" as per your last reply ??

I'm attaching the load script:

/******************************************************************************/
/*                                                                            */
/* Teradata Parallel Transporter                                              */
/* script_tpt_"tabla".sh                                                      */
/*                                                                            */
/******************************************************************************/
/*                                                                            */
/* Descripcion:                                                               */
/*                                                                            */
/* Este script utiliza dos pasos (job steps)                                  */
/*                                                                            */
/* El primer paso llamado "setup_tables", utiliza un Operador DDL para crear  */
/* tablas.                                                                    */
/*                                                                            */
/* El segundo paso llamado "load_trans_table", utiliza instancias multiples   */
/* del operador DATACONNECTOR para leer filas desde un archivo delimitado     */
/* y utiliza instancias multiples del operador LOAD para registrar las filas  */
/* dentro de una tabla vacia en Teradata.                                     */
/*                                                                            */
/* El script toma los parametros del archivo job_tpt_carga.properties         */
/*                                                                            */
/******************************************************************************/

USING CHARACTER SET ASCII
DEFINE JOB job_tpt_t4886reg
DESCRIPTION 'CARGA A UNA TABLA DE TERADATA DESDE ARCHIVO'
(

    DEFINE SCHEMA schm_job_tpt_t4886regcom
    (
       ann_preslib            VARCHAR(6)
      ,num_preslib            VARCHAR(11)
      ,per_ope                VARCHAR(6)
      ,num_cuo                VARCHAR(100)
      ,num_cuo_correlasiento  VARCHAR(20)
      ,fec_emicdp             VARCHAR(10)
      ,fec_vtocdp             VARCHAR(10)
      ,cod_tipcdp             VARCHAR(2)
      ,cod_seriecdp           VARCHAR(20)
      ,ann_dua                VARCHAR(6)
      ,num_cdp                VARCHAR(20)
      ,num_cdpfin             VARCHAR(20)
      ,cod_tipdocid           VARCHAR(1)
      ,num_docid              VARCHAR(15)
      ,des_nombre             VARCHAR(100)
      ,mto_bi1                VARCHAR(16)
      ,mto_igv_1              VARCHAR(16)
      ,mto_bi2                VARCHAR(16)
      ,mto_igv_2              VARCHAR(16)
      ,mto_bi3                VARCHAR(16)
      ,mto_igv_3              VARCHAR(16)
      ,mto_adqng              VARCHAR(16)
      ,mto_isc                VARCHAR(16)
      ,mto_otros              VARCHAR(16)
      ,mto_adqt               VARCHAR(16)
      ,mto_tc                 VARCHAR(6)
      ,fec_emicdpmod          VARCHAR(10)
      ,cod_tipcdpmod          VARCHAR(2)
      ,num_seriecdpmod        VARCHAR(20)
      ,cod_dua_dsi            VARCHAR(4)
      ,num_cdpmod             VARCHAR(20)
      ,num_cdpnodom           VARCHAR(20)
      ,fec_emidetracc         VARCHAR(10)
      ,num_detracc            VARCHAR(20)
      ,ind_ret                VARCHAR(1)
	  ,cod_moneda			  VARCHAR(3)
	  ,cod_clasifbienes		  VARCHAR(1)
	  ,cod_contrato			  VARCHAR(12)
	  ,cod_error1			  VARCHAR(1)
	  ,cod_error2			  VARCHAR(1)
	  ,cod_error3			  VARCHAR(1)
	  ,cod_error4			  VARCHAR(1)
	  ,ind_cdpcancel		  VARCHAR(1)
      ,ind_estado             VARCHAR(1)
    );
   
    DEFINE OPERATOR ddl_operator
    TYPE DDL
    ATTRIBUTES
    (
       VARCHAR PrivateLogName   = 'ddl_log'
      ,VARCHAR TdpId            = @jobvar_tdpid
      ,VARCHAR UserName         = @jobvar_username
      ,VARCHAR UserPassword     = @jobvar_password
      ,VARCHAR ErrorList        = '3807'
    );

    DEFINE OPERATOR file_reader
    TYPE DATACONNECTOR PRODUCER
    SCHEMA schm_job_tpt_t4886regcom
    ATTRIBUTES
    (
       VARCHAR PrivateLogName       = 'dataconnector_log'
      ,VARCHAR DirectoryPath        = @jobvar_datafiles_path
      ,VARCHAR FileName             = 'tablaprodrcompras_pipe_prod'
      ,VARCHAR Format               = 'Delimited'
      ,VARCHAR OpenMode             = 'Read'
      ,VARCHAR TextDelimiter        = '|'
      ,VARCHAR EscapeQuoteDelimiter  = '\'
      ,VARCHAR RowErrFileName       = @jobvar_datafiles_path || '/tablaprodrcompras.err'
    );
    
    DEFINE OPERATOR load_operator
    TYPE LOAD
    SCHEMA *
    ATTRIBUTES
    (
       VARCHAR PrivateLogName   = 'load_log'
      ,VARCHAR TdpId            = @jobvar_tdpid
      ,VARCHAR UserName         = @jobvar_username
      ,VARCHAR UserPassword     = @jobvar_password
      ,VARCHAR TargetTable      = @jobvar_tgt_dbname || '.tablaprodrcompras_2'
      ,VARCHAR LogTable         = @jobvar_log_dbname || '.LG_tablaprodrcompras_2'
      ,VARCHAR ErrorTable1      = @jobvar_log_dbname || '.ET_tablaprodrcompras_2'
      ,VARCHAR ErrorTable2      = @jobvar_log_dbname || '.UV_tablaprodrcompras_2'
      ,INTEGER ErrorLimit       = @jobvar_errorLimit
      ,INTEGER TenacityHours    = @jobvar_tenacityHours
    );
    
    DEFINE OPERATOR os_command_operator
    TYPE OS COMMAND
    ATTRIBUTES
    (
       VARCHAR PrivateLogName = 'oscommand_log'
      ,VARCHAR OsCmd = 'sh' || ' ' || @jobvar_shellfiles_path || '/'
          || 'BTEQ_job_carga_tablaprodrcompras.sh' || ' ' || @jobvar_tdpid || '/'
          || @jobvar_username || ',' || @jobvar_password || ' '
          || @jobvar_logfiles_path || ' ' || @jobvar_tgt_dbname
      ,VARCHAR IgnoreError = 'YES'
    );
    
    STEP setup_tables
    (
      APPLY
         ('DROP TABLE ' || @jobvar_log_dbname || '.ET_tablaprodrcompras_2;')
        ,('DROP TABLE ' || @jobvar_log_dbname || '.UV_tablaprodrcompras_2;')
        ,('DROP TABLE ' || @jobvar_tgt_dbname || '.tablaprodrcompras_2;')
        ,(
         'CREATE MULTISET TABLE ' || @jobvar_tgt_dbname || '.tablaprodrcompras_2'
      || '('
      || '     ann_preslib            SMALLINT              NULL'
      || '    ,num_preslib            INTEGER               NULL'
      || '    ,per_ope                CHAR(6)               NULL'
      || '    ,num_cuo                VARCHAR(100)          NULL'
      || '    ,num_cuo_correlasiento  VARCHAR(20)           NULL'
      || '    ,fec_emicdp             DATE                  NULL'
      || '    ,fec_vtocdp             DATE                  NULL'
      || '    ,cod_tipcdp             CHAR(2)               NULL'
      || '    ,cod_seriecdp           VARCHAR(20)           NULL'
      || '    ,ann_dua                SMALLINT              NULL'
      || '    ,num_cdp                VARCHAR(20)           NULL'
      || '    ,num_cdpfin             VARCHAR(20)           NULL'
      || '    ,cod_tipdocid           CHAR(1)               NULL'
      || '    ,num_docid              VARCHAR(15)           NULL'
      || '    ,des_nombre             VARCHAR(100)          NULL'
      || '    ,mto_bi1                DECIMAL(15,2)         NULL'
      || '    ,mto_igv_1              DECIMAL(15,2)         NULL'
      || '    ,mto_bi2                DECIMAL(15,2)         NULL'
      || '    ,mto_igv_2              DECIMAL(15,2)         NULL'
      || '    ,mto_bi3                DECIMAL(15,2)         NULL'
      || '    ,mto_igv_3              DECIMAL(15,2)         NULL'
      || '    ,mto_adqng              DECIMAL(15,2)         NULL'
      || '    ,mto_isc                DECIMAL(15,2)         NULL'
      || '    ,mto_otros              DECIMAL(15,2)         NULL'
      || '    ,mto_adqt               DECIMAL(15,2)         NULL'
      || '    ,mto_tc                 DECIMAL(5,3)          NULL'
      || '    ,fec_emicdpmod          DATE                  NULL'
      || '    ,cod_tipcdpmod          CHAR(2)               NULL'
      || '    ,num_seriecdpmod        VARCHAR(20)           NULL'
      || '    ,num_cdpmod             VARCHAR(20)           NULL'
      || '    ,cod_dua_dsi            CHAR(4)               NULL'
      || '    ,num_cdpnodom           VARCHAR(20)           NULL'
      || '    ,fec_emidetracc         DATE                  NULL'
      || '    ,num_detracc            VARCHAR(20)           NULL'
      || '    ,ind_ret                CHAR(1)               NULL'
	  || '    ,cod_moneda			  CHAR(3)				NULL'
	  || '    ,cod_clasifbienes		  CHAR(1)				NULL'
	  || '    ,cod_contrato			  VARCHAR(12)			NULL'
	  || '    ,cod_error1			  CHAR(1)				NULL'
	  || '    ,cod_error2			  CHAR(1)				NULL'
	  || '    ,cod_error3			  CHAR(1)				NULL'
	  || '    ,cod_error4			  CHAR(1)				NULL'
	  || '    ,ind_cdpcancel		  CHAR(1)				NULL'
      || '    ,ind_estado             CHAR(1)               NULL'
      || ')'
      || 'NO PRIMARY INDEX'
      || ';'
         )
      TO OPERATOR (ddl_operator);
    );
    
    STEP load_trans_table
    (
      APPLY
         (
         'INSERT INTO ' || @jobvar_tgt_dbname || '.tablaprodrcompras_2'
      || '('
      || '     ann_preslib'
      || '    ,num_preslib'
      || '    ,per_ope'
      || '    ,num_cuo'
      || '    ,num_cuo_correlasiento'
      || '    ,fec_emicdp'
      || '    ,fec_vtocdp'
      || '    ,cod_tipcdp'
      || '    ,cod_seriecdp'
      || '    ,ann_dua'
      || '    ,num_cdp'
      || '    ,num_cdpfin'
      || '    ,cod_tipdocid'
      || '    ,num_docid'
      || '    ,des_nombre'
      || '    ,mto_bi1'
      || '    ,mto_igv_1'
      || '    ,mto_bi2'
      || '    ,mto_igv_2'
      || '    ,mto_bi3'
      || '    ,mto_igv_3'
      || '    ,mto_adqng'
      || '    ,mto_isc'
      || '    ,mto_otros'
      || '    ,mto_adqt'
      || '    ,mto_tc'
      || '    ,fec_emicdpmod'
      || '    ,cod_tipcdpmod'
      || '    ,num_seriecdpmod'
      || '    ,num_cdpmod'
      || '    ,cod_dua_dsi'
      || '    ,num_cdpnodom'
      || '    ,fec_emidetracc'
      || '    ,num_detracc'
      || '    ,ind_ret'
	  || '	  ,cod_moneda'
	  || '    ,cod_clasifbienes'
	  || '    ,cod_contrato'
	  || '    ,cod_error1'
	  || '    ,cod_error2'
	  || '    ,cod_error3'
	  || '    ,cod_error4'
	  || '    ,ind_cdpcancel'
      || '    ,ind_estado'
      || ')'
      || 'VALUES'
      || '('
      || '     :ann_preslib'
      || '    ,:num_preslib'
      || '    ,:per_ope'
      || '    ,:num_cuo'
      || '    ,:num_cuo_correlasiento'
      || '    ,:fec_emicdp (DATE, FORMAT ''MM/DD/YYYY'')'
      || '    ,:fec_vtocdp (DATE, FORMAT ''MM/DD/YYYY'')'
      || '    ,:cod_tipcdp'
      || '    ,:cod_seriecdp'
      || '    ,:ann_dua'
      || '    ,:num_cdp'
      || '    ,:num_cdpfin'
      || '    ,:cod_tipdocid'
      || '    ,:num_docid'
      || '    ,:des_nombre'
      || '    ,:mto_bi1'
      || '    ,:mto_igv_1'
      || '    ,:mto_bi2'
      || '    ,:mto_igv_2'
      || '    ,:mto_bi3'
      || '    ,:mto_igv_3'
      || '    ,:mto_adqng'
      || '    ,:mto_isc'
      || '    ,:mto_otros'
      || '    ,:mto_adqt'
      || '    ,:mto_tc'
      || '    ,:fec_emicdpmod (DATE, FORMAT ''MM/DD/YYYY'')'
      || '    ,:cod_tipcdpmod'
      || '    ,:num_seriecdpmod'
      || '    ,:cod_dua_dsi'
      || '    ,:num_cdpmod'
      || '    ,:num_cdpnodom'
      || '    ,:fec_emidetracc (DATE, FORMAT ''MM/DD/YYYY'')'
      || '    ,:num_detracc'
      || '    ,:ind_ret'
	  || '	  ,:cod_moneda'
	  || '    ,:cod_clasifbienes'
	  || '    ,:cod_contrato'
	  || '    ,:cod_error1'
	  || '    ,:cod_error2'
	  || '    ,:cod_error3'
	  || '    ,:cod_error4'
	  || '    ,:ind_cdpcancel'
      || '    ,:ind_estado'
      || ');'
         )
      TO OPERATOR (load_operator[11])
      SELECT * FROM OPERATOR(file_reader[1]);
    );
    
    STEP exec_bteq
    (
      APPLY TO OPERATOR (os_command_operator);
    );
    
);

 

Teradata Employee

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Your script specified a private log name of load_log for the load operator, so you need to add -f load_log or just -f "*" to your tlogview command.

The script also specifies 11 instances of the Load operator; as @feinholz says, you probably should specify (or default to) only 1 instance.

Teradata Employee

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

A few more things.

1. if you do not specify a MaxSessions attribute (and value) then the TPT Load operator will attempt to connect 1 session per available AMP. If this is not what is intended, then the job script should specify the number of sessions. Of course, if you know that TASM is enabled and the number of sessions will be controlled by TASM, then you do not need to specify the MaxSessions attribute.

2. You do not need to provide the concatenation syntax with your SQL statements.

 

-- SteveF
Enthusiast

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Hi, 

thanks for your answers. 

Well, I know that TASM is enabled in order to control sessions number of users. So, what should I do in the script in order to avoid that error ???

thanks

Teradata Employee

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Use only one instance of the Load operator, i.e. change

TO OPERATOR (load_operator[11])

to

TO OPERATOR (load_operator[1])

or just

TO OPERATOR (load_operator)

Enthusiast

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

We have drop some temporary tables (log table).

When TPT is running it creates some log tables, we drop those tables and waiting for the user in order to execute job again.

will let you know results. 

 

 

Enthusiast

Re: load_operator: TPT10517: At least 1 instance could not connect special sessions

Hi, We have run this job. And have good news. We put only : TO OPERATOR (load_operator[1]) after that, the job run without any problem. But this took the double of time than normally takes. I mean, this job takes 4 hours and with load_operator[1] took 8 hours. We will change to load_operator[4] in order to test. any comment my fellows?