TPT load in informatica giving CLI Error

Database
Enthusiast

TPT load in informatica giving CLI Error

Hello,

Greetings....

I am loading Teradata (V13.10.03.08) database using informaticav9.1.0 (with Hotfix1). In Infa, i am using TPT connection.

I have the follwing property on the TPT Connection string used in informatica..

Tenacity 4, Max/Min, 10/10. Sleep 6. Block Size 64000. System Operator: Export.

With this connection i have read around 1billion records and jobs ran to success. But with the same connection one of our job is giving CLI error. I have traced the load and i found the below error message in the Trace File.

The below trace file is from the source table.

FETCHBUFFER: got end-of-request (done with block)

PC_ENDREQUEST: entering

PC_ENDREQUEST: leaving

FETCHBUFFER: session 72, initiating request for stmt: 1 block: 50869

PC_INITIATEREQ: entering with request: ''

PC_INITIATEREQ: request length: 8

PC_INITIATEREQ: Using length: 0

PC_INITIATEREQ: leaving with return code: 0

FETCHBUFFER: session 72, sent irq stmt: 1 block: 50869

PC_FETCHRESP: entering, expecting flavor: 0

PC_ERRORSET: entering with CLI error code: 220

PC_ERRORSET: leaving with CLI error code: 220

PC_FETCHRESP: leaving with return code: 220

PC_SETCODE: entering

PC_SETCODE: current condition code: 0

PC_SETCODE: setting condition code: 12

PC_SETCODE: leaving

.

.

.

.

**** 13:57:15 Total processor time used = '2413.29 Second(s)'

     .        Start : Wed May  1 13:09:09 2013

     .        End   : Wed May  1 13:57:15 2013

     .        Highest return code encountered = '12'.

EXPORT_OPERATOR: Total processor time used = '2413.29 Second(s)'

.

.

.

**** 13:57:15 This job terminated

In informatica, i get the below error,

Message Code: TPTRD_21215

Message: [ERROR] Plug-in failed to fetch data on row [29914789] with status [2115].

Message: Partition-level [SQ_source]: Plug-in #315000 failed in run().

Message Code: TPTRD_21509

Message: [ERROR] Plug-in failed to read data from table [source_table_name] of Teradata database.

Any help on how to resolve this would be appreciated. Thanks for your time.

Regards,

prabhakar. T

16 REPLIES

Re: TPT load in informatica giving CLI Error

We are encountering CLI errors with TPT via Informatica. Did you get resolution to your post in May 2013 for the CLI errors? If so, what did you need to do? 

Teradata Employee

Re: TPT load in informatica giving CLI Error

What are the errors you are getting? And are/were you able to run the similar jobs using same TPT connection?

Re: TPT load in informatica giving CLI Error

We are getting the following CLI errors (just a snapshot out of a session log). When error occurs, they restart and then it finishes successfully. The errors are sporadic and we can't recreate them. We have incidents open with both Teradata and Informatica to address. The sessions include the 'truncate and then fast-load' to a Teradata Staging Database. I am just looking for information on others who had the same error to see if I can gleen anything while we wait for responses. 

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

2016-01-15 04:22:34 : INFO : (39400 | WRITER_1_*_1) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : TPTWR_24514 : [INFO] [PROD_DPS_SERA.POLICY_COUNT_F_DP] Table has been Truncated.

2016-01-15 04:25:29 : INFO : (39400 | ) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : TPTWR_35120 : [INFO] A error has occured for instance [1] for target [1].

2016-01-15 04:25:29 : INFO : (39400 | ) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : TPTWR_34210 : [DEBUG] Formatting TPT API error message...

2016-01-15 04:25:29 : ERROR : (39400 | ) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : TPTWR_207 : [ERROR] Type:(Teradata CLI Error), Error: (Completed Successfully.

)

2016-01-15 04:25:29 : ERROR : (39400 | ) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : TPTWR_21211 : [ERROR] Plug-in failed to Initiate Teradata database connection.

2016-01-15 04:25:29 : ERROR : (39400 | WRITER_1_*_1) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : SDKS_38502 : Plug-in #315000's target [POLICY_COUNT_F_DP: Partition 1] failed in method [init].

2016-01-15 04:25:29 : ERROR : (39400 | WRITER_1_*_1) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : WRT_8068 : Writer initialization failed. Writer terminating.

2016-01-15 04:26:53 : INFO : (39400 | ) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : TPTWR_32203 : [INFO] Teradata database connection terminated successfully.

2016-01-15 04:26:53 : ERROR : (39400 | WRITER_1_*_1) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : SDKS_38502 : Plug-in #315000's target [POLICY_COUNT_F_DP: Partition 1] failed in method [deinit].

2016-01-15 04:26:53 : INFO : (39400 | WRITER_1_*_1) : (IS | LASER_PR_ETL01_IS) : Node01_LASER_PR_ETL01 : WRT_8035 : Load complete time: Fri Jan 15 04:26:53 2016

Enthusiast

Re: TPT load in informatica giving CLI Error

Hi Suzi.Limberg,

Have you resolved the issue? If  you got a resolution can you please share? We are getting the following Error from Informatica 9.6 to Teradata TD15.0 connection. The jobs runs fine most of the time. If it fails we restart again and job successfully completes. Lately this issue is causing lots of failures and looking for a solution as soon as possible.

This is what we get from the log file:

WRITER_1_*_1> TPTWR_32604 [INFO] Table information (schema) retrieved successfully.

WRITER_1_*_1> TPTWR_32605 [INFO] DML statements on table [<$DATABASE>.<$TABLE_NAME>] for system operator [TD_UPDATE] built successfully.

TPTWR_35120 [INFO] A error has occured for instance [1] for target [1].

TPTWR_34210 [DEBUG] Formatting TPT API error message...

CMN_1761 Timestamp Event: [Sat Feb 27 05:18:36 2016]

TPTWR_207 [ERROR] Type:(Teradata CLI Error), Error: (CLI error 207 (no message text available))

Teradata Employee

Re: TPT load in informatica giving CLI Error

CLI 207 means that the client OS told CLIv2 that the TCP/IP connection request failed.

If this is a Unix / Linux server, be sure you have the environment variable THREADONOFF=1

It also appears COPERR is not set properly, though I suspect the message text won't provide much help.

Or perhaps you have exceeded some OS limit. Contact the system administrator for the Informatica server for help.

Enthusiast

Re: TPT load in informatica giving CLI Error

Thanks Fred !! Our Linux server's  THREADONOFF  is set to 1 (THREADONOFF=1) .  Followings are our COPERR settings. Is there anything to do with Maxsessions settings for each job and concurrency of load jobs? Our Maxsession limit is set to 16 for each job and 20 load utility slots are available. Total number of nodes 12 and 2 PEs per node and 288 AMPs. Do you you see any issues with these settings? 

COPERR settings for informatica server:

core file size (blocks, -c) unlimited

data seg size (kbytes, -d) unlimited

scheduling priority (-e) 0

file size (blocks, -f) unlimited

pending signals (-i) 1030588

max locked memory (kbytes, -l) 32

max memory size (kbytes, -m) unlimited

open files (-n) 32000

pipe size (512 bytes, -p) 8

POSIX message queues (bytes, -q) 819200

real-time priority (-r) 0

stack size (kbytes, -s) 10240

cpu time (seconds, -t) unlimited

max user processes (-u) 578374

virtual memory (kbytes, -v) unlimited

file locks (-x) unlimited

 

Enthusiast

Re: TPT load in informatica giving CLI Error

Ah... Almost 3 years... Had hard time to recollect.... Yes we did resolve this issue. We added to collect statistics before the load on source table which is the stage table and I think the job ran to success.
Teradata Employee

Re: TPT load in informatica giving CLI Error

Those look like ulimit settings (and I see no issues with those).

COPERR is an environment variable (typically set to /opt/teradata/client/vv.vv/lib64)

If the problem was exceeding a database limit, e.g. too many sessions or too many concurrent utilities, then you should have received a different message. CLI 207 is typically a problem on the client; perhaps it's a system-wide limit rather than a user-specific limit, e.g. number of open file descriptors (which includes TCP socket connections). Enlist the help of your Linux sysadmin to troubleshoot.

Enthusiast

Re: TPT load in informatica giving CLI Error

Thanks Fred !! Appreciate your response and time.