Pass SQL Query as Parameter Thru Unix Script

[bikan]

Hi All,

I have a job with design as follows

DB2/UDB Enterprise Select Stmt --> Transformer --> Flat File

The input source query to the DB2 stage needs to be passed as a parameter to the job.

When I pass this query while running the job in designer/director, the job runs perfectly fine.

My job unltimately has to run thru Unix script (not thru sequencer). When I try to pass the SQL query to the job thru Unix script, the job fails to invoke.

The command used to run the jobs is

/dstage/dsadm/Ascential/DataStage/DSEngine/bin/dsjob -server servername -user usr_id -password passwd -run -wait -param DB_QUERY=select count(*) as COUNT from table_name proj_name jobname

The error I am getting is
Invalid arguments: dsjob -run [-mode <NORMAL RESET VALIDATE>] [-param <name>=<value>] [-warn <n>] [-rows <n>] [-wait] [-opmetadata <TRUE FALSE>] [-disableprjhandler] [-disablejobhandler] [-jobstatus] [-userstatus] [-local] [-useid] <project> <jobjobid> Status code = -9999 DSJE_DSJOB_ERROR

The important thing to note is, if I remove the DB_QUERY parameter from the script and hard code it in the job then the job is working properly thru script.

DS Gurus, please help me in this regard.

Thank you in advance for your responses.

[ray.wurlod]

The input source query to the DB2 stage needs to be passed as a parameter to the job.

Why?

How do you propose to handle other queries that might return other than a single integer column?

Have you really thought this through properly?

Why are there no quotes surrounding a string that contains spaces in your command? The operating system command parser will return -param DB_QUERY=select which is probably not what you are after. Other command line tokens, such as count(*) and from, will be passed to dsjob but it won't know what to do with them.

Take some time to learn how command parsers work. It will be time well spent.

[DS_FocusGroup]

i think you should write a shell script for this and use the DB connect functionality to do this.

[bikan]

Hi,

Ray:
My query given here is just a sample. Actually I am selecting four fields with same format from different tables. My intention is to run same job with different source query as parameter creating output in same format.

I tried giving query in Quotes and also with '\' . I can see that 'dsjob' command is passing full query before giving error while job invocation.

DS_FocusGroup:
Yes I am doing this in a shell script. I can run the same job thru the shell script when I hard code the query in job and not pass it as parameter.

[csri]

try assigning the query to a variable and passing the variable to dsjob command. Like:

queryStr='select count(*) as COUNT from table_name'

/dstage/dsadm/Ascential/DataStage/DSEngine/bin/dsjob -server servername -user usr_id -password passwd -run -wait -param DB_QUERY="$queryStr" proj_name jobname

or

/dstage/dsadm/Ascential/DataStage/DSEngine/bin/dsjob -server servername -user usr_id -password passwd -run -wait -param DB_QUERY='select count(*) as COUNT from table_name' proj_name jobname

or

/dstage/dsadm/Ascential/DataStage/DSEngine/bin/dsjob -server servername -user usr_id -password passwd -run -wait -param DB_QUERY='select count(\*) as COUNT from table_name' proj_name jobname

Hope this helps.

[Developer9]

Hi All,

I Have the same requirement to pass the SQL query as parameter to the job for reading column names for the given table..

I have multiple table names with similar column names..so I want to parameterize such away that it should read the column names from the Query and generate output file..Sequential file

Environment:DB2 UDB enterprise stage,Datastage 7.5.2 ,AIX
Please post your ideas here

Thank you

[FranklinE]

It looks like the requirement is "telling" you how to design the job. There are other and to my mind better ways to approach this.

Just as a first thought: A table with two columns, an identifier and a char column with the text of the query in it. Your parameter would be the identifier, and you'd use a lookup to find the query text and import it to your job as an internal parameter.

My second thought is that you may have just a few separate queries now, but what will you do when you have ten, or fifty? Organizing by identifiers let's you minimize the coding you have to do, as well as giving you an easy way to update your jobs with additional queries.

Parameters make my job easier. When they start making it more difficult, I know I've made a bad design decision.

[Developer9]

It looks like the requirement is "telling" you how to design the job. There are other and to my mind better ways to approach this.

Just as a first thought: A table with two columns, an identifier and a char column with the text of the query in it. Your parameter would be the identifier, and you'd use a lookup to find the query text and import it to your job as an internal parameter.

My second thought is that you may have just a few separate queries now, but what will you do when you have ten, or fifty? Organizing by identifiers let's you minimize the coding you have to do, as well as giving you an easy way to update your jobs with additional queries.

Parameters make my job easier. When they start making it more difficult, I know I've made a bad design decision.

Hi Franklin,

I am sorry I am confused here :D

I am looking the way such as

Sql query=Select statement with table name as parameter

to be passed to the script

Can you be please more clarify on this

Thank you

[FranklinE]

I must have misread the question. If the table name is the only thing different in the query, then you should make the table_name value the parameter:

Code: Select all

Sql Query = select * from #pTable_name#

You pass your parameter down from script to sequence to job using the standard method in DataStage, and your command line would have "-param pTable_name=TABLE_NAME" where you set the contents of TABLE_NAME however you find necessary or convenient.

I use Control-M for scheduling. It runs a script passing a parameter for the current date. I build the dsjob command line in the script, passing the date to my DataStage parameter. I reference that date in the sequencers and in every job they run.

[Developer9]

@Franklin,

I am not using any scheduling tool and thought of implementing using dsjob commands:


dsjob -run -param "Query=select statement ..."


Any idea to implement this kind of command

Thank you

[FranklinE]

@Franklin,

I am not using any scheduling tool and thought of implementing using dsjob commands:


dsjob -run -param "Query=select statement ..."


Any idea to implement this kind of command

Thank you

It depends on how you are invoking the script. Do you have an automated way to run the script with input parameters?

What I wrote about passing that date parameter is the rest of your answer. Set a variable in the script to the query text, then name the variable as your parameter on the command line.

Code: Select all

THIS_SQL="select * ..." or the script input parameter
dsjob ... -param pSQL_query=THIS_SQL ...

In your Designer code:

Code: Select all

Sequence properties/parameters tab -- Parameter name pSQL_query, default value is blank.

Then just repeat the parameter in the sequence stage editor where it calls for parameters on down to the job, with your DB2 stage looking something like:

Code: Select all

Sql query = #pSQL_query#

[Developer9]

It depends on how you are invoking the script. Do you have an automated way to run the script with input parameters?

Hi,

Currently I am using .sh script to unload from DB2 Table:

#!bin/ksh

if [ $# -ne 1 ];
then
echo " " >> $JOBLOG_DFLT
echo "** Argument missing ** i.e., Table name not passed: " >> $JOBLOG_DFLT
exit 10;
fi

TABLENAME=$1;

####### Unload Table $TABLENAME #######

db2 "EXPORT TO $INST_HOME/$TABLENAME.ext of del SELECT * FROM SCHEMA.$TABLENAME FOR FETCH ONLY WITH UR" >> $JOBLOG


Here it is checking only Table name as an argument ..i.e.If table name is presented it is selecting all the fileds from it and exporting to .ext file..


Server job Batch job:To execute this script

1.Parameter files:.ini file to specify all the project variables.
2.I am using Command array:

datastage/unload.sh #tablename#

Here I am getting output file .ext format with data corresponds to " select * from table".


Now How I can modify above script to accommodate the parameter file to specify the SQL query ..as you mention we discussed in these posts ..


Thank you

[chulett]

Developer9,

Please stop hijacking multiple posts with your issue. I'm locking this three year old post and you can continue the conversation in the appropropriate place: the post you created:

viewtopic.php?t=143583