teradata stored procedure

One Star

teradata stored procedure

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 =  'drop table "iSTG_T"."' || TRIM(StgTable) ||'";';
    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.
Moderator

Re: teradata stored procedure

Hi,
Could you please post your job setting screenshots into forum which will be helpful for us to address your issue?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: teradata stored procedure

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.