How to ignore * (asterisk) in a variable

Shell Programming and Scripting
1

I am using a shell script to read SQL statements stored in a DB2 table and write them out to a file. The problem I have is that some SQL statements have an "" in them which gets resolved as the list of files in the current directory when I run the script. How can I prevent the "" from being resolved??

Actual SQL: SELECT * FROM RTDS.TSPSTOPS

Result SQL: SELECT gen.out gen.sql gen2.sql getsql.sh getsql2.sh sqlrow.out sqltxt.out test.sh FROM RTDS.TSPSTOPS

Code Snippet:

sqlrow=$(db2 +c -x fetch from c1 )
fetchrc=$?
echo fetchrc = $rc

echo ${sqlrow}

2

No variable in here => no expansion will take place. Maybe your example is too short? Do you in reality have something like "x=$(db2 $var fetch from c1)"? In this case surround $var with quotes.

3 
> line1="ls *"
> echo $line1
ls 50964hdr.txt bin cbin dead.letter greetings junk.tmp masters orictl sample tmp

this seems to be what you were referring to

> echo "$line1"
ls *

this 2nd example is what I think you are trying to get

4

My apologies for my example not being very clear. The "Actual SQL" shown in my OP is the value that the fetch will return into the shell variable "sqlrow". Since the Actual SQL has an "*" in it, when I echo $sqlrow, the * gets resolved and substitutes in the list of files from the current directory.

So, duh, I was able to resolve that by putting double quotes around the variable on the echo like : echo "$sqlrow"

Now, the plot thickens a bit. The sql is stored in a varchar(32000) on the source table. When I put the double quotes on the echo, I now get a record echo'd out that is 32000 bytes long. (The actual SQL statements will range from 50 - 10000 bytes). So, I don't want to write out all that extra space on the end of each record.

I can get the length of the sql, and I'm now trying to substr it in awk as follows where $rowlen contains the length of the sql:

sqltxt=`echo "$sqlrow"|awk '{print substr($0,1,$rl)}' rl=$rowlen`

However, I'm now getting the message "awk: record ` SELECT * F...' too long"

What is the best way for me to either suppress the extra spaces on the end of each record or to substr out the actual SQL??

5

tr -s " "
will suppress extra " " space charcters

> line2="This  2spaces   3spaces    4and5after     *"
> echo "$line2"
This  2spaces   3spaces    4and5after     *
> echo "$line2" | tr -s " "
This 2spaces 3spaces 4and5after *
6

If the column is defined as varchar(32000), I don't understand why you are retrieving what appears to be full width columns. I'm using Oracle instead of DB2, but the principle should be the same.

create table my_table
	(sql_statement varchar(1000));

insert into my_table values ('select * from table2');

select sql_statement || '#' from my_table;

Here is my output:

SQL_STATEMENT||'#'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from table2#

1 row selected.

Have you tried trimming the output as you select it from the DB2 database?

select trim(sql_statement) || '#' from my_table;

The output is the same (i.e., no trailing spaces for the data), unless the long line of dashes is giving you a problem:

TRIM(SQL_STATEMENT)||'#'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from table2#

1 row selected.
7

It's been a while since I worked with DB2, but, the db2 command line verb may have a switch that would suppress the output of padded spaces, if it (i.e., the db2 command line verb) is the culprit. Do you get padded spaces when you run the query interactively? Say, from within Toad?

8

joeyg,
thanks for the tip on the "tr" command. It is doing what I need it to do.

just as an fyi, I was also able to mess around and get the "cut" command to work since I had the statement length available, but I think I'll stick with "tr"

thanks,
bp