Archives of the TeradataForum

Message Posted: Tue, 16 Dec 2008 @ 10:08:48 GMT

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

Hi Rit,

unfortunately I can not provide you the script since it is intellectual property of the company I worked for.

What I can give you is an idea what we did.

Basically the Shell script is more or less a wrapper script (I thnk this is it how you call it in in Ab Initio). The shell script is nothing else then parsing a Textfile which contains the basic BTEQ script (contains SQL and some Teradata specific script commands) and exchange things like user, password or database name (in case you work on a development system and want to migrate the script later on to the productive system) which you are going to provide when you start the shell script.

BTEQ itself can not handle parameters so you need to parse them first thru the shell script to provide the information.

As I said I can not provide you any details, but since your are an Ab Initio expert I am pretty sure that you are able to wirte such a script, with all events and eventhandler which may occure (including logs etc.)

When you parsed the script you just execute the produced file witin the script by calling BTEQ < {Dir}/textfilename {optional you can write an output to a file besides the log file by adding [> {Dir}/outputfilename]}

The BTEQ does not need to be too complex just the most important commands are needed:

     /************************************************************
     ***
     ***  TERADATA BETQ
     ***
     ***  Table Name : {your Table]
     ***  File Name  : {your Scriptname}.btq
     ***  Parameter        : $LOGON_USER                        Logon User
     ***                $DATA_TO_DT
     ***                $DATA_FROM_DT
     ***                $BATCH_PARM_16        Version Number of the TABLE or
     Environment
     ************************************************************/

     /* ---------------------------------------------------------------------
       *  Logon DBS
       *  --------------------------------------------------------------------
     */
     logon $LOGON_USER;  <-- Provided from the shell script

     .SET WIDTH 200;


     /*
       *  --------------------------------------------------------------------
       *   setting DATEFORM if necessary
       *  --------------------------------------------------------------------
     */
     SET SESSION DATEFORM=ANSIDATE;

     /*
       *  --------------------------------------------------------------------
       *   Basic Verification of temporary input table from AB Initio
       *  --------------------------------------------------------------------
     */
     SEL count(*) AS No_of_Rows FROM  databasename.Table_name_$BATCH_PARM_16;
     <-- Provided from the shell script
     .IF ACTIVITYCOUNT = 0 THEN .GOTO ERROR66

     /*
       *  --------------------------------------------------------------------
       *   Collect Statistics
       *  --------------------------------------------------------------------
     */

     COLLECT STATISTICS databasename.Table_name_$BATCH_PARM_16 COLUMN
     (column1, column2, column3);
     COLLECT STATISTICS databasename.Table_name_$BATCH_PARM_16 COLUMN (column5);
     COLLECT STATISTICS databasename.Table_name_$BATCH_PARM_16 COLUMN (column4);
     COLLECT STATISTICS databasename.Table_name_$BATCH_PARM_16 COLUMN
     (column1, column3);
     ...


     /**************************************************************************************


       * Verify the Table
       *

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


       */

     /*
       * Exit
       */
     .if errorcode!=0 then .exit 1;

     .quit 0;

     .LABEL ERROR66
     .REMARK 'Error 66: databasename.Table_name_$BATCH_PARM_16 is empty'
     .QUIT 66

Hope this helps

Joerg

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