using SQL in routine

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
India2000
Participant
Posts: 274
Joined: Sun Aug 22, 2010 11:07 am

using SQL in routine

Post by India2000 »

Hi,

I want to use SQL statement :

select colum1 from schema.table where colum2='abc'


Cmd = "db2 " : "'connect to pdw_dev user pdw using Db2dba1'"

Call DSExecute('UNIX',Cmd,Output,Status)

Cmd = "db2 -x " : "'select colum1 from schema.table where colum2='abc' '"

Call DSExecute('UNIX',Cmd,Output1,Status)




This routine doesn't return anything from the SQL. Can anyone help me with the syntax? Thanks
Last edited by India2000 on Sun Dec 24, 2017 11:01 am, edited 1 time in total.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

How about starting by checking the status of your calls and logging that? I'd also include logging the output so you have some clue what is going on while you get this working.
-craig

"You can never have too many knives" -- Logan Nine Fingers
India2000
Participant
Posts: 274
Joined: Sun Aug 22, 2010 11:07 am

Post by India2000 »

db2 -x 'select colum1 from schema.table where colum2='abc' '

DSLogInfo called from :0

Result= SQL0206N "abc" is not valid in the context where its used
SQLSTATE=42703
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

And... solved?

If not, did you look up the error or have any thoughts on what the issue might be? What happens when you run that same statement at the command line as the user that the job is running under? Get it running there before throwing it into the routine...
-craig

"You can never have too many knives" -- Logan Nine Fingers
India2000
Participant
Posts: 274
Joined: Sun Aug 22, 2010 11:07 am

Post by India2000 »

no, not.
if I use the DB2 commandline processor to run the query it returns the rows.

This command works for the numeric constant values like but not for character value.
db2 -x 'select colum1 from schema.table where colum2='1123'
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You can't (successfully) enclose single quote characters in single quotes.
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 »

... or (successfully) use mismatched quotes.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

Try running with Double quotes

db2 -x "select colum1 from schema.table where colum2='abc'"
Venkata Srini
Post Reply