All Forums Tools
brindamaiti 11 posts Joined 10/11
11 Apr 2012
Passing date parameter (current month) to tbuild from command line

Hello,

We are using TPT to extract data from Teradata to Oracle.

The script that we are using needs to extract data only for the current month from the Teradata table .

How can we pass the current month to the script.

We run tbuild from  command line.

The command being used is

tbuild -f <script_to_which_curr_month_is passed>.

 

feinholz 1234 posts Joined 05/08
11 Apr 2012

TPT itself cannot send data to Oracle, so it is not clear to me from your post how you are getting the data from Teradata to Oracle.

However, as far as passing information into a script, you can use the -u command line option.

In the script you create a "job variable".

On the command line you pass a value pair for the -u option and assign a value to the job variable.

In the script, if you want this current month to be part of the SELECT statement, you could so this:

Let's assume we call the job variable "curr_month".

The SELECT statement would be:

. . . .

VARCHAR SelectStmt = 'SELECT * FROM TABLE <table name> WHERE CURRENT_MONTH = ' | @curr_month | ';'

. . . .

(Something like that. I do not know the syntax of your SELECT statement. You will have to code accordingly.)

You basically have have to break up the string assigned to the SelectStmt attribute into a series of strings with the concatenation and the job variable.

--SteveF

brindamaiti 11 posts Joined 10/11
12 Apr 2012

Hello,

Thanks for the quick response.

So a little bit more clarification here is

1. The statement  (just pasting a small part ) is

ATTRIBUTES
(
VARCHAR  currmonth  = @jobvar_currmonth,

VARCHAR SelectStmt = 'SELECT
 

     CAST(COUNTER_POST_IND as VARCHAR(2))
    ,CD04008
 FROM   where  CD04008 ='| @curr_month|'  '

)

The script is test.txt

 

2) I execute it by

tbuild -f test.txt -u 201204

 

Does this look correct ?

 

Thanks.

Regards,

brinda

 

 

 

 

 

feinholz 1234 posts Joined 05/08
12 Apr 2012

If your job variable is "curr_month" then the command line would be something like this:

tbuild -f text.txt -u "curr_month=201204"

Please read the documentation on the proper use of the -u command line option.

--SteveF

brindamaiti 11 posts Joined 10/11
13 Apr 2012

Thanks for this information.

Best regards,

brinda

brindamaiti 11 posts Joined 10/11
13 Apr 2012

Hello,

I am still not able to get it work .

My statement is

VARCHAR Cd = @Cd ,

ATTRIBUTES

(

VARCHAR PrivateLogName = 'selector_log',

VARCHAR TdpId = 'ABC.DEF4.NET',

VARCHAR UserName = 'User',

VARCHAR UserPassword = 'passwd',

VARCHAR Cd = @Cd ,

VARCHAR SelectStmt = '

SELECT

CAST(CD13005 as VARCHAR(15))
FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit

where CD13005 =' | @Cd | ' ,

VARCHAR ReportModeOn

);

I run it this command

tbuild -f LPI_AUDIT.txt.bak  -u "Cd = '1' "

And I get this error

TPT_INFRA: TPT04017: Error: Exception "TPT_INFRA: TPT02026: Error: End of job script encountered inside of a quoted string
" caught during job script file parsing/compilation.
Job script compilation failed.
Job terminated with status 12.
 

Kindly help.

Thanks

 

 

brindamaiti 11 posts Joined 10/11
13 Apr 2012

I have also tried

FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit

where CD13005 ' || @Cd || ' ,

feinholz 1234 posts Joined 05/08
13 Apr 2012

Ok, a couple of things.

First, I mistyped. The concatenation *is* || and not |.

Second, you cannot put VARCHAR Cd = @Cd in the attribute list.

That will not do anything. The operator does not know about "Cd" as an attribute to the operator. You are using job variables, not attributes.

Third, I believe the issue is with the quotes. Whatever you specified on the command line for the value of Cd will be placed into the script. And the value '1' (with single quotes) will be placed as the value in the concatenation. But the single quotes will be interpreted as part of the concatentaiton and not as quotes you need around the value because you need to give the SELECT statement a string.

Therefore, you need to double the single-quote:

tbuild -f LPI_AUDIT.txt.bak  -u "Cd = ''1'' "

--SteveF

brindamaiti 11 posts Joined 10/11
16 Apr 2012

Thanks you for the quick response. But I still face issues and lookl  forward to your quick suggestions.

I have gone throug the documentation as well. And this is what it says

From the Document

--------------------------

For example, if the job script contains the following attribute list:
ATTRIBUTES
(
VARCHAR UserName = @UsrID,
VARCHAR UserPassword = @Pwd
);
you can assign values to the variables “UsrId” and “Pwd” as follows:
tbuild -f daily_job.tpt -u "UsrID = 'John Doe', Pwd = 'ABC123' "
 

--------------------------

 So  I have done this

1) Still continued with placing Cd in the attribute list (and I know that is something wrong as you have mentioned )

2) My select statement now is

VARCHAR SelectStmt = '

SELECT

CAST(CD13005 as VARCHAR(15))
FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit

where CD13005 =' || @Cd ||' ,

3) Run this as

tbuild -f LPI_AUDIT.txt.bak  -u "Cd = ''1'' "

Still I get errors

"

TPT_INFRA: TPT04017: Error: Exception "TPT_INFRA: TPT02026: Error: End of job script encountered inside of a quoted string
" caught during job script file parsing/compilation.
Job script compilation failed.
Job terminated with status 12.
"

Thanks.

best regards,

brinda

 

 

feinholz 1234 posts Joined 05/08
16 Apr 2012

There must be some other syntax problem with the script. Doubling the single-quote was just to get you to supply the correct value to the WHERE clause (the string '1' instead of the numeric value 1).

Check the entire script to make sure you do not have single quotes embedded inside other single quoted strings.

 

--SteveF

anu c 3 posts Joined 06/15
12 Jun 2015

Hi,
I would like to add to the same topic where I am using the month and other details as parameters to TPT .I have 2 questions as below
1.  I was able to use the job variable to pass the user id details and variables to pass month, but the file which is getting generated is of huge size than the normal way of exraction. ie tbuild -f tpt.txt.The record count is same, and data is also same as before, but only size is huge. Why is it so.
2. I wanted to give the WHEREcondition as a run time parameter. How can I give whole WHERE condition dynamically in the select.

feinholz 1234 posts Joined 05/08
12 Jun 2015

1. please provide more information; not sure what you mean by "file which is being generated is of huge size". which file?
2. you need to split up the SELECT statement and use concatenation operator
 
'SELECT * FROM TABLE abc ' || @my_where_clause || ' ; '
 

--SteveF

anu c 3 posts Joined 06/15
15 Jun 2015

1. As part of the TPT extraction, an input extraction data file is generated in another location. Normally this file is around 3GB without using the job variables or run time parameters. But when I gave the job vars option and runtime parameters like month , the extracted file size became 11GB.The data count is same as before but not sure why the size alone got increased . Please give me an idea where to check to reduce the size.
2. Thanks.. I will test in this way and come back if any issue
Thanks
 

anu c 3 posts Joined 06/15
15 Jun 2015

1. As part of the TPT extraction, an input extraction data file is generated in another location. Normally this file is around 3GB without using the job variables or run time parameters. But when I gave the job vars option and runtime parameters like month , the extracted file size became 11GB.The data count is same as before but not sure why the size alone got increased . Please give me an idea where to check to reduce the size.
2. Thanks.. I will test in this way and come back if any issue
Thanks
 

feinholz 1234 posts Joined 05/08
15 Jun 2015

1. Again, it is unclear to me what "input extraction data file" you are referring to. You will have to provide the script and job variable file and any other information you can.
 

--SteveF

You must sign in to leave a comment.