Bteq-import- Error Handling Not working

Database
Enthusiast

Bteq-import- Error Handling Not working

Hi, i have a bteq which uses repeat function to load a file into a table. we have a parameter for the input file. if the parameter is not available, the script is failing with a Return code 0, inspite of using the label to exit if errorcode <>0. as the import error is not giving any error code, teradata is not picking up the error..i asume, it is picking the error code from the previous succeful sql statement ( create table) and exiting with Return code 0.  

Please let me know how to exit the bteq if there is such error during import file.

Code:

======

bteq << EOF

.SET SESSION TRANSACTION btet;

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.SET SESSIONS 4

.SET REPEATSTOP ON

.LOGON ${LOGON};

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.SET ERROROUT STDOUT;

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.SET WIDTH 254;

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.SET ERRORCODE 3807 SEVERITY 0;

DROP TABLE db1.abc;

.SET ERRORCODE 3807 SEVERITY 8;

CREATE MULTISET TABLE  db1.abc ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      col1 CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

      col2 CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,

      col3 CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      )

PRIMARY INDEX ( col1 ,col2 );

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.IMPORT FILE = '/share/remote/file_$PARAMETER.dat';

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.REPEAT * PACK 100

USING

(

col1 CHAR(5),

col2 CHAR(7),

col3 CHAR(12)

)

INSERT INTO db1.abc

(

col1,

col2,

col3

);

.IF ERRORCODE <> 0 THEN .GOTO byebye;

.quit 0

.LABEL byebye 

.quit 50

 EOF

****************************************************** 

LOG

****************************************************** 

BTEQ 14.10.00.10 Mon Jun 20 13:59:53 2016 PID: 14957

+---------+---------+---------+---------+---------+---------+---------+----

.SET SESSION TRANSACTION btet;

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+----

.SET SESSIONS 5

+---------+---------+---------+---------+---------+---------+---------+----

.SET REPEATSTOP ON

+---------+---------+---------+---------+---------+---------+---------+----

.SET QUIET

 *** Type QUIET OFF; to resume output.

+---------+---------+---------+---------+---------+---------+---------+----

.LOGON xxxxx/xxxxx,

 *** Logon successfully completed.

 *** 4 Sessions logged on.

 *** Teradata Database Release is 14.10.07.01                   

 *** Teradata Database Version is 14.10.07.01                     

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 9 seconds.

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+----

.SET ERROROUT STDOUT;

 *** Error messages now directed to STDOUT.

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+----

.SET WIDTH 254;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.SET ERRORCODE 3807 SEVERITY 0;

 *** Error: Unrecognized command ERRORCODE

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

DROP TABLE USER_WORK.psg3_SMB_BAMS_MSV_LD_HS;

 *** Total elapsed time was 2 seconds.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.SET ERRORCODE 3807 SEVERITY 8;

 *** Error: Unrecognized command ERRORCODE

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

CREATE MULTISET TABLE  db1.abc ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      col1 CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

      col2 CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,

      col3 CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      )

PRIMARY INDEX ( col1 ,col2 );

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

 .IMPORT FILE = '/share/remote/file_.dat';

 *** Warning: No IMPORT mode was given, assuming field mode.

 *** Error: The following occurred during an Access Module open:

 Requested file not found.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.REPEAT * PACK 100

 *** Error: An import file must be open before specifying a

            pack factor for a repeat.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

USING

(

col1 CHAR(5),

col2 CHAR(7),

col3 CHAR(12)

)

INSERT INTO db1.abc

(

col1,

col2,

col3

);

 *** Error: Use IMPORT to open a file first before

           trying to read from it.

 *** Warning: Out of data. 

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.quit 0

 *** You are now logged off from the DBC.

 *** Exiting BTEQ...

 *** RC (return code) = 0 

Thank You





2 REPLIES
Enthusiast

Re: Bteq-import- Error Handling Not working

I used a query to find the activity count of the table and if activity count is 0 then I set it to exit out. Is there any better solution t catch the error after the import failed

Teradata Employee

Re: Bteq-import- Error Handling Not working

The .IF command can only test the results of the most recent SQL statement (ERRORCODE, ERRORLEVEL, or ACTIVITYCOUNT), not other BTEQ commands.

You apparently meant to .SET ERRORLEVEL 3807 SEVERITY 0 and then set it back to 8.

There's no way to explicitly set a BTEQ return code based on a missing or empty import file.