I am a teradata DBA and our talend developers are having an issue when executing a SPROC. As any teradata person knows when a tptload job fails the target table becomes locked. i have written a SPROC which does the following
SET Stmt = 'CREATE TABLE "iUTL' || '"."' || TRIM(StgTable) || '_temp" AS (SEL * FROM "iSTG_T"."' || TRIM(stgtable) || '") WITH DATA;';
EXECUTE IMMEDIATE Stmt;
i have a continue handler for this statement if a a SQL return code is return which states istg_t.stgtable is loading.
if it is loading i do the following.
SET Stmt = 'create table "iSTG_T"."' || TRIM(StgTable) || '_new" as "iSTG_T"."' || TRIM(StgTable) || '" WITH NO DATA AND STATISTICS;' ;
EXECUTE IMMEDIATE Stmt;
SET Stmt = 'RENAME TABLE' || '"iSTG_T"."' ||TRIM(STGTABLE) ||'_new" TO "iSTG_T"."' || TRIM(StgTable) ||'";' ;
EXECUTE IMMEDIATE Stmt;
everything works as expected. a new table is created and the old one dropped and the tptloads successfully. after the tpt there are several other SPROCS that do updates/inserts etc...
the problem is that these other procs after the tpt are not committing. looking in the teradata log, all steps are executed successfully however when the job is complete the data is not updated.
if this step is removed from the talend job or the table is not in a load status (the procs drops the _temp table created and exits). everything works fine.
any ideas or thoughts would be much appreciated.
Thanks Sabrina. I am not a Talend guy. i have forwarded your request to our talend team. do you have and sample screen shots you are looking for? that might make it easier to get you the right thing the quickest.
