All Forums Tools
stevehager 7 posts Joined 11/11
22 Nov 2011
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:

               TargetDatabase = 'DBA_TABLES'

          TYPE           DDL
               VARCHAR       PRIVATELOGNAME = 'export_log'
              ,VARCHAR       TDPID          = @Target
              ,VARCHAR       USERNAME       = 'xxx'
              ,VARCHAR       USERPASSWORD   = 'xxx'
              ,VARCHAR ARRAY ERRORLIST      = ['3803']

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

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.



feinholz 1234 posts Joined 05/08
22 Nov 2011

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';


Samya 21 posts Joined 11/11
28 Dec 2011

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

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

feinholz 1234 posts Joined 05/08
04 Jan 2012

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


Kiruthika 1 post Joined 03/12
22 Mar 2012


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 ????


feinholz 1234 posts Joined 05/08
08 May 2012

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.



sujitvarpe 2 posts Joined 08/13
28 Aug 2013

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?

You must sign in to leave a comment.