teradata stored procedure

Anonymous — Sat, 16 Nov 2024 10:27:38 GMT

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.

Re: teradata stored procedure

Anonymous — Wed, 17 Aug 2016 07:36:07 GMT

Hi,
Could you please post your job setting screenshots into forum which will be helpful for us to address your issue?
Best regards
Sabrina

Re: teradata stored procedure

Anonymous — Thu, 18 Aug 2016 16:40:36 GMT

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.

topic teradata stored procedure in Talend Studio

teradata stored procedure

Re: teradata stored procedure

Re: teradata stored procedure