All Forums Tools
vennelakanti00 3 posts Joined 11/11
11 Mar 2015
Can we pass schema and sql statement as variables to tpt script

Hi,
Is it possible to pass schema definition and sql statement in job variables file or command line parameters? %INCLUDE needs exact filename to be mentioned for schema definition which makes the tpt script for one particular extract. We are working on a generic script for 100+ tables so that we can pass the schema and sql in jv file along with instances.
 
DEFINE SCHEMA SOCPARC_SUBSET_PP
DESCRIPTION 'DW_ADDRESS'
(
@SCHEMADEF
);
VARCHAR SelectStmt        = @SQL
 
Job Variable script:
SCHEMADEF=`cat xyz.txt`
SQL=`cat abc.txt`
 

feinholz 1234 posts Joined 05/08
11 Mar 2015

Symbol substitution in TPT is exactly that. A "substitution".
It is not "substitution and execute".
Thus, having "cat" in the string will not actually execute the "cat" command.
You can do:
 
   %INCLUDE @schema_file_name
 
and then in the job variable file, define schema_file_name as xyz.txt.
 
For the SelectStmt, no the job variable file must have the actual SELECT statement assigned to the job variable named SQL.
Or, on the command line, you can use the -u command line option and assign SQL=SELECT . . . .;
 

--SteveF

vennelakanti00 3 posts Joined 11/11
23 Mar 2015

Hi Steve,
Thanks for the inputs on this. We were able to pass the values having a shell wrapper around the actual tbuild command and also schema file as you mentioned. We can use similar approach for INSERT as well.
In Shell:
sql=`cat sql.txt`
ins=`cat insert.txt`
schema="schema.txt"
tbuild -f tpt_script -u "sql='$sql' schema='$schema'"
tpt script:
INCLUDE @schema
sql=@sql
 

You must sign in to leave a comment.