Generic approach for running DML likely to cause an error, via BTEQ

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Generic approach for running DML likely to cause an error, via BTEQ

Doing an oracle conversion.  I'm looking for the best practice to handle simple ETL steps like 'check if a table exists, if so drop it'

 

How do people generally do that in BTEQ?

In Oracle i would have a simple procedure to accept the schema_name, table_name and drop if it exists

How would you do that generically in BTEQ, ie how would you pass the current database name to a procedure owned by another database?  Without getting ugly and writing out to a file etc

 

Alternatively I tried doing it directly in BTEQ, temporarily setting maxerror to ignore the error:

.SET MAXERROR 8

drop table nonexistent_table;

.SET MAXERROR 1

but that will set the return code to 8 and once the script completes it will terminate.  Is there a way to 'reset' the errorlevel?

 

Thanks for any help

  • bteq
  • ETL
4 REPLIES
Teradata Employee

Re: Generic approach for running DML likely to cause an error, via BTEQ

How about this:

 

.SET ERRORLEVEL 3807 SEVERITY 0

DROP TABLE this.doesNotExist;

.SET ERRORLEVEL 3807 SEVERITY 8

N/A

Re: Generic approach for running DML likely to cause an error, via BTEQ

This SP will drop a table and ignore the table doesn't exist error. If you don't specify the database name it defaults to the session's default database.

 

/*
   Drop a table ignoring 3807 error (Table doesn't exist)
*/
REPLACE PROCEDURE Drop_Table
(
  IN db_name VARCHAR(128) CHARACTER SET Unicode,
  IN tbl_name VARCHAR(128) CHARACTER SET Unicode,
  OUT msg VARCHAR(400) CHARACTER SET Unicode
) SQL SECURITY INVOKER
BEGIN
   DECLARE full_name VARCHAR(361)  CHARACTER SET Unicode;

   DECLARE sql_stmt VARCHAR(500)  CHARACTER SET Unicode;

   DECLARE exit HANDLER FOR SqlState 'T3807'
   BEGIN
      SET msg = full_name || ' doesn''t exist.';
   END;

   SET full_name = '"' || Coalesce(db_name,DATABASE) || '"."' || tbl_name || '"';

   SET sql_stmt = 'DROP TABLE ' || full_name || ';';

   EXECUTE IMMEDIATE sql_stmt;

   SET msg = full_name || ' dropped.';

END;

Re: Generic approach for running DML likely to cause an error, via BTEQ

Thanks dieter

 

How would i pass the db name to that procedure from a BTEQ script, assuming i'm running with a default database different to the procedure owner?  As i understand it BTEQ doesn't support variables.  Would i have to populate a volatile table or something?  eg

 

select database;   <-- returns 'DEV_1'

 

call etl_load.drop_table(<database_name>, 'my_table', oMsg);

Re: Generic approach for running DML likely to cause an error, via BTEQ

Thanks Fred, that works!