Using Parameters in the File in User defined SQL

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chandra.shekhar@tcs.com
Premium Member
Premium Member
Posts: 353
Joined: Mon Jan 17, 2011 5:03 am
Location: Mumbai, India

Using Parameters in the File in User defined SQL

Post by chandra.shekhar@tcs.com »

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: Select all

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
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I don't believe that it's possible to use parameter references successfully in an SQL file.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
chandra.shekhar@tcs.com
Premium Member
Premium Member
Posts: 353
Joined: Mon Jan 17, 2011 5:03 am
Location: Mumbai, India

Post by chandra.shekhar@tcs.com »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

chandra.shekhar@tcs.com wrote:, we can't use sequences.
Is this for a technical or a political reason?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chandra.shekhar@tcs.com
Premium Member
Premium Member
Posts: 353
Joined: Mon Jan 17, 2011 5:03 am
Location: Mumbai, India

Post by chandra.shekhar@tcs.com »

haha..
Actually, our client has made this rule of not using the Sequences. :roll:
Thanx and Regards,
ETL User
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Which would translate to "political". :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

no sequences is like saying don't use datastage. You lose at least 1/2 of the functionality of the tool.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Sounds like the client needs a wee bit of education!
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I would wager it is one of those "Parallel Only!" sites where, for whatever reason, that is the mandate coming down from above. And it typically translates to no Sequence or Server jobs of any kind, no matter how appropriate one might be.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply