Archives of the TeradataForum

Message Posted: Wed, 01 Dec 2010 @ 10:13:00 GMT

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

Hi Ralph,

The answer would be to eliminate any reliance on a ".RUN FILE" statement.

You could use a bteq execution framework (common module) and pass the parameter as a part of a multi-line" SQL Statement.

Mine is based on a Korn Shell script (see below). I think the two .export portions can be left out for your purposes. Alternatively, the output file could be scrubbed/searched for output/messages if required. If you make use of an export file, you need to ensure a random/unique filename if there is going to be execution concurrency.

Keep in mind, you would need to put the "SET QUERY BAND" statement into a variable, prior to executing the statement contained in the variable.

In this case, your SQL Query/statements could be put into a multiline variable, as such:

     MY_SQL_STATEMENT=`echo " SET QUERY_BAND = 'UNISON_SCHED=${UNISON_SCHED};'
     FOR session;\n"${MY_SQL_QUERY};"`

Then:

     execbteq.ksh "${DBCONNECT}" "${USERNAME}" "${MYPASSWD}"
     "${MY_SQL_STATEMENT}" "${MY_OPTIONAL_EXPORT_FILE}"

Here is the Korn Shell Script:

     #***************************************************************************
     #***************************************************************************
     #***************************************************************************
     #***************************************************************************
     #***************************************************************************
     #!/usr/bin/ksh
     ############################################################################
     ###
     ### ScriptName: execbteq.ksh
     ###
     ### Created:  Etienne Stieger          2006-10-31
     ### Updated:  Etienne Stieger          2006-11-01
     ###
     ############################################################################
     ### Description: Acts as a low-level wrapper for executing SQL via bteq.
     ### The export file is purely for capturing data output from "select" statements.
     #. ${HOME}/.profile
     ### $1 = Connect String (for example dbc/, ml1/, etc - or empty string ""'" or '')"'
     ### $2 = UID
     ### $3 = PWD
     ### $4 = SQL Statement
     ### $5 = Export file name

     bteq << EOF
     .quiet on
     .logon ${1}${2},${3};
     .titledashes off
     .echoreq off
     .export file = '${5}',close
     ${4}
     .export reset
     .exit
     EOF

     RC=${?}

     exit ${RC}
     #***************************************************************************
     #***************************************************************************
     #***************************************************************************
     #***************************************************************************
     #***************************************************************************

Kind regards

Etienne Stieger
DW DBA, Technical Specialist
Analytics and Information Management (AIM/EIW) Standard Bank

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