How do people handle no file found error in BTEQ IMPORT on Network Attached systems?

Tools

How do people handle no file found error in BTEQ IMPORT on Network Attached systems?

When the IMPORT file is missing BTEQ issues an error and continues with the next SQL Statement in the Network attached system. In the mainframe systems this ab ends the script.   This behaviour in the network system causes concerns and makes me wonder how other folks deal with this issue.  I don't like the idea of writing a script to make sure that the IMPORT files exist before the BTEQ.  Is there a way to handle this within the BTEQ script?

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

Requested file not found.

*** Importing deactivated

Even the next Using-SQL-Statement  issues an Error but is not caught by the ERRORCODE

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

            trying to read from it.

Thanks..

4 REPLIES
Teradata Employee

Re: How do people handle no file found error in BTEQ IMPORT on Network Attached systems?

Very recently, by way of BTEQ efixes, we've added support for using a new SYSTEMRETURNCODE keyword as part of an .IF command condition to enable the return code value resulting from using a .OS command to be checked.

.IF SYSTEMRETURNCODE != 0 THEN .GOTO foobar

So you can now use the .OS command to issue a system call to test for a file's existence and then use the .IF command to test the result to take some action as needed.

Example .OS command using a Windows PowerShell cmdlet...

.OS powershell "if ($false -eq (test-path -path myfile.txt)) {exit 1}"

Example .OS command using a UNIX utility...

.OS test -e myfile.txt

The BTEQ efix versions that contain this capability :

14.00.00.21

14.10.00.15

15.00.00.06

15.10.01.02

Information about this new keyword has not yet been added to BTEQ's Reference manual. That is likely going to happen for 16.00. Adding recognition of the SYSTEMRETURNCODE keyword was actually done as part of a larger enhancement implemented to support use of multiple conditions on each .IF command, along with recognition of a new WARNINGCODE keyword, to facilitate precise checking of .COMPILE command results. But for overall feature completeness we also made sure .IF use was viable for .OS command result checking.

The SYSTEMRETURNCODE keyword can be used in .IF command conditions for all flavors of BTEQ. However, due to platform-specific restrictions with regard to negative exit codes, the keyword will only be valid for use in a .EXIT command when using Windows BTEQ.

The ERRORCODE keyword can only be used to check on the results of SQL requests. For many use cases this is sufficient. But there are a few BTEQ-command-specific cases such as "file not found" and "empty file" import scenarios which historically have presented problems. For some users consider them errors and others do not. We believe being able to use the .IF SYSTEMRETURNCODE approach will help resolve the more egregious cases.

Re: How do people handle no file found error in BTEQ IMPORT on Network Attached systems?

Hooray! Thanks for the enhancement! Thank you! Thank you!

So I suppose any minor version equal to or higher than the ones listed here in each of the major versions will have this enhancement included, Correct?

I am curently using BTEQ 15.10.01.00.

Thanks a lot again!

Teradata Employee

Re: How do people handle no file found error in BTEQ IMPORT on Network Attached systems?

Efix versions equal to or higher than what I listed will have it. So to get it now, you will need to pick up the latest efix version for 15.10.01 which is... 15.10.01.02

It will also be in the base version of 16.00 when that release goes GCA.

Teradata Employee

Re: How do people handle no file found error in BTEQ IMPORT on Network Attached systems?

And actually for 15.10.01 there is now a more recent .03 efix but it is .02 that you minimally need.