TPT variables in script itself (Providing a default value that could then be overridden)

Tools
Enthusiast

TPT variables in script itself (Providing a default value that could then be overridden)

I want to specify a default value for a variable (@TargetDatabase in my upcoming example) in the TPT script itself and then be able to override it via the command line or the job variables file (if necessary).  I understand how to set the value of a variable using the command line (tbuild -u) or the job variables file (tbuild -v) but have been unable to find or guess the syntax for setting the variable in the script itself.

The TPT User Guide (version 14.0, page 45) says:


Using job variables for job script parameters requires completion of two setup activities:


Reference the variables in the job script.


Assign values to the variables in the one of the following places, shown in processing order,


from highest to lowest priority.


on the command line (highest priority)


in a local job variables file (next highest)


in the global job variables file (UNIX and Windows platforms) (next)

in the job script itself (lowest)  <--- This is the feature I am interested in.

I attempted to use an "ATTRIBUTES" phrase in the JOB, OPERATOR, and APPLY sections, to no avail:

DEFINE JOB

     ACCESS_RIGHT_TXTTABLE_CREATION

          ATTRIBUTES

              (

               TargetDatabase = 'DBA_TABLES'

              )

(

     DEFINE OPERATOR

          TABLE_CREATION

          TYPE           DDL

          ATTRIBUTES

              (

               VARCHAR       PRIVATELOGNAME = 'export_log'

              ,VARCHAR       TDPID          = @Target

              ,VARCHAR       USERNAME       = 'xxx'

              ,VARCHAR       USERPASSWORD   = 'xxx'

              ,VARCHAR ARRAY ERRORLIST      = ['3803']

              )

     ;

     APPLY

         ( 'EXEC ' || @TargetDatabase ||'.ACCESS_RIGHT_TXT_T;' )

     TO OPERATOR (TABLE_CREATION)

     ;

)

;

Results in failure:

TPT_INFRA: TPT04042: Error: Line 21 of Job Script File 'Create.DBA_TABLES.ACCESS_RIGHT_TXT.tpt.txt': Referenced job variable 'TargetDatabase' has no value.

6 REPLIES
Teradata Employee

Re: TPT variables in script itself (Providing a default value that could then be overridden)

Check out the SET command.

(Although I am not sure why you want to set the job variables in the script itself. It defeats the purpose of having job variables.)

Whether the job variable hold numeric or string, make sure to use single quotes around the value:

SET TargetDatabase = 'some_databasename';

-- SteveF
Enthusiast

Re: TPT variables in script itself (Providing a default value that could then be overridden)

Can We use Job Variables in the Schema defination of a TPT Script?

That is , the name of the columns as job variables.

Teradata Employee

Re: TPT variables in script itself (Providing a default value that could then be overridden)

Yes, you should be able to use the job variables anywhere.

-- SteveF

Re: TPT variables in script itself (Providing a default value that could then be overridden)

Hi,

I have an output file which has 3 coulmns , i've to load this to a teradata table which has 4 coulmns.

The value for the 4th column should be passed through runtime tbuild command.

Is this possible ????

Teradata Employee

Re: TPT variables in script itself (Providing a default value that could then be overridden)

Yes, you can do this.

You must create a job variable. You will assign a value to the job variable on the command line (or in the job variable file). You then need to restructure your DML statement with the concatenation syntax to incorporate the job variable.

-- SteveF

Re: TPT variables in script itself (Providing a default value that could then be overridden)

Hi,

I have requirement of extracting MySQL Tables and load in to the teradata Tables.There are 'N' number of different tables in MySQL and Teradata.I have configuration table which contain source table names,target table names and column to be included from source to target.I have to load 'N' number of source in to target using one script.Can it be possible?

Can i dynamically get source and target table schema from configuration table?