Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Shell script doesn't work..CL works?

    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

  2. #2
    Join Date
    May 2002
    Posts
    43
    Use the \" so the script does not translate the "

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    Koz,

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

  4. #4
    Join Date
    May 2002
    Posts
    43
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by cells
    Koz,

    Thanks for the suggestion...can you be more specific..like where exactly I need use that?
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96

    Re: Shell script doesn't work..CL works?

    Originally posted by cells
    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



    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •