DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
chandra.shekhar@tcs.com



Group memberships:
Premium Members

Joined: 17 Jan 2011
Posts: 353
Location: Mumbai, India
Points: 2973

Post Posted: Thu Dec 07, 2017 12:07 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,

I am trying to load data in my Teradata table using a Teradata Connector stage.
I have used the User-Defined SQL functionality available there, I am using a File containing all the Insert SQLs.

Now I have used parameters in that SQL and defined those parameters in the job properties of the job. But the job aborts with the below message when I run the job

Code:
RDBMS code 3802: Database '#jpINPUT_SOURCE_DB#' does not exist.  SQL statement: LOCKING TABLE #jpINPUT_SOURCE_DB#.EMD


This parameter jpINPUT_SOURCE_DB is defined in the job properties, I am inputting the correct value at the run time as well. Not sure what I am missing. Kindly suggest.

_________________
Thanx and Regards,
ETL User
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Thu Dec 07, 2017 3:16 am Reply with quote    Back to top    

I don't believe that it's possible to use parameter references successfully in an SQL file.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42280
Location: Denver, CO
Points: 217097

Post Posted: Thu Dec 07, 2017 8:08 am Reply with quote    Back to top    

It's not, as you're finding there's no "second step" after the file is brought into the job to resolve any parameters. When I had a need to do exactly what you are doing, I had a pre-processing step where we took our "template" SQL file (with our own markers for where the runtime values needed to be) and edited in the values for the current run and had the job use that "runtime" version of the file.

Are you sure you really need to use a SQL File for this, that it is an appropriate solution?

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 274

Points: 2760

Post Posted: Thu Dec 07, 2017 8:57 am Reply with quote    Back to top    

our team routinely generates sql this way:
a user variable stage contains the parameterized sql
a flat file stage writes a 'this run' version of populated sql to a file on the server
the connector stage picks up the 'this run' file and uses that.

you can use a format of "statement" : '\n' to make the output file human readable.

there is probably a way to have the parameterized sql in a file and populated as well, but we chose to store the sql in the stage to save clutter and to it will migrate with the job without having to remember to drag a text file along with it every time.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42280
Location: Denver, CO
Points: 217097

Post Posted: Thu Dec 07, 2017 10:35 am Reply with quote    Back to top    

Right. However, this parameter doesn't look like something that would ever change once the jobs were in their environment, hence my last question. Seems like it may come down to what "all the insert SQLs" means.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
chandra.shekhar@tcs.com



Group memberships:
Premium Members

Joined: 17 Jan 2011
Posts: 353
Location: Mumbai, India
Points: 2973

Post Posted: Tue Dec 12, 2017 4:08 am Reply with quote    Back to top    

chulett wrote:
Are you sure you really need to use a SQL File for this, that it is an appropriate solution?

This was our first approach for loading this particular table, since the INSERT SQLs were not finalized so we thought to have a file containing all the SQLs. In case, those needed to be changed, we'll simply update the file instead of changing the DS job.

UCDI wrote:
a user variable stage contains the parameterized sql

Are you talking about the user variable stage available in the Sequence Job, we can't use sequences.

_________________
Thanx and Regards,
ETL User
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Tue Dec 12, 2017 10:46 pm Reply with quote    Back to top    

chandra.shekhar@tcs.com wrote:
, we can't use sequences.

Is this for a technical or a political reason?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
chandra.shekhar@tcs.com



Group memberships:
Premium Members

Joined: 17 Jan 2011
Posts: 353
Location: Mumbai, India
Points: 2973

Post Posted: Wed Dec 13, 2017 5:18 am Reply with quote    Back to top    

haha..
Actually, our client has made this rule of not using the Sequences. Rolling Eyes

_________________
Thanx and Regards,
ETL User
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42280
Location: Denver, CO
Points: 217097

Post Posted: Wed Dec 13, 2017 8:10 am Reply with quote    Back to top    

Which would translate to "political". Wink

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 274

Points: 2760

Post Posted: Thu Dec 14, 2017 1:09 pm Reply with quote    Back to top    

no sequences is like saying don't use datastage. You lose at least 1/2 of the functionality of the tool.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours