Archives of the TeradataForum

Message Posted: Wed, 29 Apr 2009 @ 20:20:04 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I am attempting to trap the SQLSTATE for SQLCODE error 5321.

I looked it up in the mapping, but could not find it.

Therefore, I am running a proc and deliberately allowing the error. However, my error handler is not trapping it.

I continue to see the error message at the bottom of SQL Assistant instead.

Is this due to the fact that the sql is executed using DBC.SysExecSql?

Is there anyway to make this work?

Thank you

     REPLACE PROCEDURE STORED_PROCSD.RECREATE_UNIQUE_INDEXES
         (IN in_database CHAR(30), IN in_table CHAR(30))
     /***************************************************************************
     * This is designed to be called after using CAPTURE_UNIQUE_INDEXES   *
     ****************************************************************************
     /
     BEGIN
        L1: BEGIN
        /********************************   error handling   ************************************
        * It is possible that this procedure is rerun  after the calling process initially    *
        * fails  This may leave some of the indexes created and some not so          *
        * the process simply moves to the next CREATE statement                          *
        ****************************************************************************************/
        DECLARE CONTINUE HANDLER
        FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
        INSERT INTO DBCMNGR.Proc_Error_Table
        (:SQLSTATE, CURRENT_TIMESTAMP, 'Indexes already exist','Failed to index. Job may have restarted.');

        FOR fCreateIndex AS c_all_columns CURSOR FOR
           -- The sql for the cursor
              SELECT
              'CREATE UNIQUE INDEX ' || TRIM(INDEXNAME)
              || ' (' || TRIM(COLUMNLIST)
              || ') ON ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) as the_statement
              FROM
                 DBCMNGR.INDEXSTORAGE
              WHERE
                 TRIM(DATABASENAME) = in_database
                 AND TRIM(TABLENAME) = in_table
                 --  Process the data
                 DO
                    --  Potential trouble spot
                    CALL DBC.SYSEXECSQL (fCreateIndex.the_statement);
                 END FOR; -- End of process
        END L1;

        -- Empty the storage table of references to the indexes
        L2: BEGIN
        DECLARE v_createstatement VARCHAR(300);
        DELETE
        FROM
           DBCMNGR.INDEXSTORAGE
        WHERE
           TRIM(DATABASENAME) = in_database
           AND TRIM(TABLENAME) = in_table
        ;
        END L2;
        END;

Vincent Bernard
Sr.Teradata DBA
C&S Wholesale Grocers

	<Prev	Next>		<<First	<Prev	Next>	Last>>