All Forums Database
01 May 2013
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

suzi.limberg 2 posts Joined 05/15
26 Jan 2016

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? 
 

Adeel Chaudhry 773 posts Joined 04/08
26 Jan 2016

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

-- If you are stuck at something .... consider it an opportunity to think anew.

suzi.limberg 2 posts Joined 05/15
28 Jan 2016

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

Someshnr 53 posts Joined 06/07
09 Mar 2016

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))

Fred 1096 posts Joined 08/04
10 Mar 2016

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.

Someshnr 53 posts Joined 06/07
10 Mar 2016

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

 

10 Mar 2016

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.

Fred 1096 posts Joined 08/04
11 Mar 2016

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.
 
 

Someshnr 53 posts Joined 06/07
15 Mar 2016

Thanks Fred !! Appreciate your response and time.

amittera 35 posts Joined 12/09
10 Jun 2016

Guys, 
 
Do we have any clue to this issue if it got resolved. We tried checking Informatica/ network  etc.
Many blogs suggst its issue at the clinet side i.e configuration etc, but still no luck. The issue is now speadingly widely across multiple application for different enviroement. We had some similar issue an year back, same CLI error , not  extactly 207, but things went fine after restarting the Integration services at Informatica end.
I am still stuck at the issue. :(
 
Severity               Timestamp         Node     Thread  Message Code  Message
INFO      30/05/2016 10:00:58        node01_ITSUSMPL00311              WRITER_1_*_1 TPTWR_32605   [INFO] DML statements on table [CTM_S.PLW_PLW_CODE_PRIM_INDIC_DIM] for system operator [TD_LOAD] built successfully.
 
INFO      30/05/2016 10:01:02        node01_ITSUSMPL00311              WRITER_1_*_1 TPTWR_24516   [INFO] [CTM_S.PLW_PLW_CODE_PRIM_INDIC_DIM] Table has been Truncated using [ODBC Connection].
 
INFO      30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_35120   [INFO] A error has occured for instance [1] for target [1].
 
INFO      30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_34210   [DEBUG] Formatting TPT API error message...
 
ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_207        [ERROR] Type:(Teradata CLI Error), Error: (CLI error 207 (no message text available))
ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311                              TPTWR_21211   [ERROR] Plug-in failed to Initiate Teradata database connection.
 
ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311              WRITER_1_*_1 SDKS_38502       Plug-in #315000's target [PLW_PLW_CODE_PRIM_INDIC_DIM: Partition 1] failed in method [init].
ERROR  30/05/2016 10:02:18        node01_ITSUSMPL00311              WRITER_1_*_1 WRT_8068           Writer initialization failed. Writer terminating.
INFO      30/05/2016 10:02:19        node01_ITSUSMPL00311                              TPTWR_32203   [INFO] Teradata database connection terminated successfully.
 
ERROR  30/05/2016 10:02:19        node01_ITSUSMPL00311              WRITER_1_*_1 SDKS_38502       Plug-in #315000's target [PLW_PLW_CODE_PRIM_INDIC_DIM: Partition 1] failed in method [deinit].
 
 
 
One other log for the TPT trace.
 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_33304 : [TRACE] Retrieving Target Driver for given Target index...

 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_33403 : [TRACE] Retrieving Group Driver for given Group index...

 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_32502 : [INFO] De-Initialized Teradata PT Writer Group Driver.

 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_35118 : [INFO] TPTSyncManager for Target [1] is deintialized.

 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_32402 : [INFO] De-Initialized Teradata PT Writer Target Driver.

 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38510 : SDK target and group deinitialized with status [-1].

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TPTWR_32302 : [INFO] De-Initialized Teradata PT Writer Plug-In.

 

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38023 : Plug-in 315000's [deinit] method invocation successful.

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38025 : Plug-in 315000 deinitialized and unloaded with status [-1].

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : SDKS_38019 : Writer SDK plug-ins deinitialized with status [-1].

2016-06-09 13:22:06 : INFO : (53768 | MAPPING) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6018 : The session completed with [0] row transformation errors. 

2016-06-09 13:22:07 : INFO : (53768 | MANAGER) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : PETL_24002 : Parallel Pipeline Engine finished.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : PETL_24013 : Session run completed with failure.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6846 : Ready to execute post-session variable assignment.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6847 : Post-session variable assignment executed.

2016-06-09 13:22:07 : INFO : (53768 | DIRECTOR) : (IS | PCIS_Tera_LnxDev_NA) : node01_ITSUSMPL00311 : TM_6022 : 

 
 

Amit Saxena
Teradata Consultant

11 Jun 2016

Hi amit, as said earlier collect stats on the source tables helped a bit. But as the volume grew over the period from 40 million to 250,50million, the issue repeated. DBA suggested to use TPT with PDO to overcome the issue... Few we converted, few we changed to a stored proc and did not encounter those issues. At that moment we had to believe that TPT loader has limitations. But depends on version we use as well. 3 yrs now... It may be upgraded...

And other question to you, after how many rows this error is coming? And still how many rows are pending to be loaded? And does the error repeat at the same point whenever it was re run or appears randomly?

amittera 35 posts Joined 12/09
11 Jun 2016

The iss for us is relating now at the Login time out issue which we are now recommeding to increasing to 740 sec, as a temp workaround, also there is a patch involved to be done at Informatica server. All of sudden , jobs which were running fine went in to CLI issues..
 

Amit Saxena
Teradata Consultant

11 Jun 2016

I don't think increasing to 740 might be a good idea. That affect entire database and may risk the whole application. Please see if collect stats prior to load on all invloved table works. Also please let me know about the volume and the period it takes to fail each time....

Otherwise in infa you have to load partial data instead of loading entire volume... But again that is a code change...

amittera 35 posts Joined 12/09
11 Jun 2016

I will take that point but , dont think , it has to do something with the collection of stats, App team is pretty mature and they do take care on this very carefully. normally thier code is just perfect kind. Issue what we saw was, it takes maximum time .
checked the log and found that there is delay in connecting the TPT connection. While we can see that the ODBC connection is stabilizing quickly and it is working fine.
We have observed since last few days that sometimes it is stabilizing the TPT connection fast but some time it is taking the long time to connect. Attached is the Informatica log for the same.
Can you detail a more little how it can be DB risk..
I will also check on the config change on Infa side as you mentioned.

Amit Saxena
Teradata Consultant

namitapradhan 1 post Joined 07/16
21 Jul 2016

Hi Amit,
Did you get a resolution for this?
We have started getting this issue for 2 of our sessions. On checking the trace files, we see the errors below
**** 03:20:11 TPT10508: RDBMS error 2633: Too many load/unload tasks running: try again later
UPDATE_OPERATOR: Load/unload task limit exceeded, try running again later
**** 03:26:11 Attempting to resubmit
 
UPDATE_OPERATOR: TPT10508: RDBMS error 2633: Too many load/unload tasks running: try again later
**** 03:26:12 TPT10508: RDBMS error 2633: Too many load/unload tasks running: try again later
UPDATE_OPERATOR: Load/unload task limit exceeded, try running again later
**** 03:32:12 Attempting to resubmit
 
UPDATE_OPERATOR: TPT10508: RDBMS error 2633: Too many load/unload tasks running: try again later
**** 03:32:12 TPT10508: RDBMS error 2633: Too many load/unload tasks running: try again later
UPDATE_OPERATOR: Load/unload task limit exceeded, try running again later
**** 03:38:12 Attempting to resubmit
Thanks
Namita.

amittera 35 posts Joined 12/09
05 Aug 2016

If its nothing at the TPT end, then you can refer below that we did :
1)  increasing the timeout value to 740 seconds , but that s not the perm fix and can be checked with GSC.
2) We changed the server side configuration settings , something related to Load balancing.
 
Check TPT session limit exceeded logs on the TERADATA console .

Amit Saxena
Teradata Consultant

You must sign in to leave a comment.