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'
VARCHAR PRIVATELOGNAME = 'export_log'
,VARCHAR TDPID = @Target
,VARCHAR USERNAME = 'xxx'
,VARCHAR USERPASSWORD = 'xxx'
,VARCHAR ARRAY ERRORLIST = ['3803']
( '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.
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';
Can We use Job Variables in the Schema defination of a TPT Script?
That is , the name of the columns as job variables.
Yes, you should be able to use the job variables anywhere.
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 ????
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.
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?