Hi,

I have a DB2 export script which selects the data from the table and export into a output file...when run the following script on Command Line, its working fine and I am getting the right output..

export to /utility/xfer/USER.txt of del modified by chardel~ \
select '"'|| row_id ||'",' || rtrim(char(created)) || ',"' || rtrim (par_row_id) || '","' || rtrim (login) || '","' || rtrim (user_flg)|| '"' FROM SIEBEL.S_USER

Output:~"1-XYF",2001-01-08-19.09.42.000000,"1-XYF","YNANGMP","Y"~

But when I run the above script as shell script I am not getting the correct output..here is the shell script and output:

db2 "export to /utility/xfer/USER.txt of del modified by chardel~ \
select '"'|| row_id ||'",' || rtrim(char(created)) || ',"' || rtrim (par_row_id) || '","' || rtrim (login) || '","' || rtrim (user_flg)|| '"' FROM SIEBEL.S_USER "

Output:|| row_id ||,2001-01-08-19.09.42.000000, || rtrim (par_row_id) || , || rtrim (login) || , || rtrim (user_flg)||

could you please guide me is there anything needs to be modified in the shell script and why its not working the way I want when I run the above script...your help is greatly appreciated

Use the \" so the script does not translate the "

Koz,

Thanks for the suggestion...can you be more specific..like where exactly I need use that?

Sorry, I was wrong.... Here is a example of what we did in creating a sql from an sql in a script. We do not use ". This may help.....

db2 "select 'db2 reorg table $OWNER.$TABLE Index $OWNER.' || name , ' use tempspace1 ' from sysibm.sysindexes where indextype = 'CLUS' and tbcreator = '$OWNER' and tbname = '$TABLE' union select 'db2 reorg table $OWNER.$TABLE Index ' || varchar( rtrim(creator),8) || '.' || name , ' use tempspace1 ' from sysibm.sysindexes where tbname = '$TABLE' and tbname not in (select tbname from sysibm.sysindexes where indextype = 'CLUS' and tbcreator = '$OWNER' and tbname = '$TABLE') and tbcreator = '$OWNER' and uniquerule = 'P'" >> $CMDLOG

Which creates a statement
db2 reorg table O.T Index O.I use tempspace1

So the single quote surround the characters I want in the statement else it will pull the value from the selected column.....

Also check where you end a line and continue it ......

I used the \" in other scripts so it would not translate it as a delimeter, which would not really help in your case.

I guess you'll be better off putting your statement in a file, say "exp.sql", and running it like:

db2 -f exp.sql

This way you don't have to worry about escaping and stuff.

Cells,


create file like mkreorg.sql ( it is only for one table , if you want whole schema , remove tbname in where cluase)

vi mkreorg.sql


put fooling statement in mkreorg.sql



connect to dbname ;

select ' reorg table '||trim(tbcreator)||'.'||ltrim(tbname) || ' index '|| rtrim(creator)||'.'||ltrim(name) ||' use tempspace1 ;'
from sysibm.sysindexes
where tbcreator = $OWNER and tbname = $TABLE
and ( indextype = 'CLUS' or uniquerule = 'P' );




run mkreorg.sql to genereate reorg scripts ( reorg.sql)


as below

db2 -tvf mkreorg.sql -z reorg.sql
db2 -tvf reorg.sql -z reorg.log



above will do what you want to do


Let me know , if any questions


Thank You

Lekharaju Ennam
Certified Oracle8i & DB2 UDB DBA
Florida A&M University