Collect stats with DML statements in Informatica

Database
Enthusiast

Collect stats with DML statements in Informatica

Hi All,

May I have your suggestions in below-

In informatica , in sql transformation below code is written -->

BT

collect stats on table_1;

ET;

Delete from table_2;

UPdate table_2 

from table_1....

............;

Insert into table_2

......

Collect stats on table_2;

But some times this code is not giving any error anbut some times one ODBC error is coming like Too many ET statements..

I believe collect stats is a DDL and whenever it has to be written with a DML it has to be written in the end.

But not sure how some times the above code is working fine and some times not.

We also thought of moving the first collect stats statement in Pre SQL block

Please suggest the correct way to write above..

Cheers!

Nishant 

2 REPLIES
Enthusiast

Re: Collect stats with DML statements in Informatica

Hi Experts,

Your thoughts on the below pls.

Junior Contributor

Re: Collect stats with DML statements in Informatica

Hi Nishant,

when BT/ET is used the session is running in Teradata mode. So each request is a standalone transaction unless you use BT/ET.

If it's actually BT;COLLECT STATS;ET; you might simple remove the BT/ET

Dieter