TPT load in informatica giving CLI Error

Database
Enthusiast

Re: TPT load in informatica giving CLI Error

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 : 

Enthusiast

Re: TPT load in informatica giving CLI Error

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?
Enthusiast

Re: TPT load in informatica giving CLI Error

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

Enthusiast

Re: TPT load in informatica giving CLI Error

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...
Enthusiast

Re: TPT load in informatica giving CLI Error

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.

Not applicable

Re: TPT load in informatica giving CLI Error

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.

Enthusiast

Re: TPT load in informatica giving CLI Error

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 .