Hi ,
I Have following requirement:
DB2 Sql query to pass from a parameter file
for example, I would create a parameter file with (SELECT column 1, column 2 FROM Table name) then job would read it and create a file with the contents named table.txt
How to write/modify below ksh script to call the parameter file and write "query out put "to a text file ??
Environment: DB2 tables, AIX, Datastage7.5.2
Shell script: unload.sh (Table name as a parameter )
#!/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;
*************************
echo " " >> $JOBLOG
echo " ** Start export of table $TABLENAME ** " >> $JOBLOG
ls -l >> $JOBLOG
echo " " >> $JOBLOG
####### Unload Table $TABLENAME #######
db2 "EXPORT TO $INST_HOME/$TABLENAME.ext of del SELECT * FROM schema.$TABLENAME FOR FETCH ONLY WITH UR" >> $JOBLOG
Explanation: Here table name is passed as a variable and gives the out put to .ext file but I want to pass the parameter file (in the form of sql query)
Please share your ideas here
Thank you
---------- Post updated 12-09-11 at 09:22 AM ---------- Previous update was 12-08-11 at 11:06 PM ----------
Hi ,
I am looking at the ksh script to execute a parameter file (which has DB2 sql query stored in it)
Please post your ideas here
Thank you
What does your parameter file look like? Is it SQL or just a table of data? Do you need to do any substitution inside it?
Hi,
Parameter file is of format (.ini)consist of Properties and directories:
name=value
Query=select colA,colB FROM schema.tablename (i.e actual query to pass for the table)
I think above approach is works
I welcome more ideas to implement based on my requirement
Thank you
Running db2 9,000 times to process 9,000 lines doesn't sound very efficient. You can feed them in direct and run db2 only once.
You didn't specify what to do with the 'name=' parameter so my script ignores it, chops off the 'Query=' from the front, and prints direct:
while read LINE
do
# Skip lines that aren't queries
[ "${LINE:0:6}" = "Query=" ] || continue
echo "${LINE:6}" # Echo everything after Query=
done < inputfile | db2
1 Like
Here we are specifying "sql query" in the script itself ..if it is a small we can accommodate here
Requirements:
We have multiple tables of same format to unload:-
so we want to modify the query often without changing the code (script)i.e without disturbing the code only just modify the sql query in a file.
Sql query=stored in a file and call that file in the script to execute the query
Thanks and welcome for your ideas
Read my code again, it does what you want, unless the file isn't actually as you described it.
If you just stored all the queries in a file, one by one, without the 'value=' and 'query=' stuff, you could feed that file right into db2 raw with no shell script at all.
How to execute your code then ??
using the command line
db2=>
or any other mechanism to unload the DB2 tables ??
According to db2's instructions, it can read multiple statements from standard input. Nearly any database interface can do this because running a command 9,000 times to process one batch of data doesn't make much sense.
command_that_prints_lots_and_lots_of_queries | db2
It can also execute an individual statement from the commandline like you're using it. If you're only doing one statement, this can be convenient.
@Corona688
Thanks.. can you please give me some sample code for the data extraction
I think I did so a couple posts ago...
Unless that's not what you wanted, in which case please describe what you do want.
Hi ,
For the DB2 table export :I am looking to execute sql query from a (.sql )or a parameter file (which has "query=" SELECT statement" ) file using (ksh) unix script that is used by Batch (Datastagejob).
Job:Execute command (Command Array,Parameter file)
Thank you