Shell Programming and Scripting

There are several problems with your code:

1) You use a here-document which isn't closed in the second script:

Code:

bteq > ps_generate_sql.log <<- EOF

This says: execute the "bteq"-command, redirect its output (stdout) to "ps_generate_sql.log" and use everything up to "- EOF" as input to it. As there is no second "- EOF" I'm not sure if this will work at all.

2) Another thing is the naming of the here-document: "- EOF" is problematic as whitespace has special meaning in the shell.

3) There is absolutely no reason to use global variables and make one script dependent on the environment another script sets. This is about as error-prone as it can be.

4) The variable you pass cannot be used, because there are different program enviroments at work: a variable expansion can only take place in the shell, but you try to get the SQL-interpreter to expand it - that won't work. You will have to create a temporary copy of the SQL-file with the variable you want to expand, do the replacement by "by hand" (that is: using some UNIX utility) and then throw away this modified copy. I have modified your script accordingly.

5) There are some minor things you should habitually do in your scripts: let them exit with some exit-code and explicitly so, scripts should not just terminate because the end of the input file is reached; always define your variables and do not take them for granted ("typeset"); always use quoting as a means of protection of whitespace and other special characters within your arguments.

Rewrite the scripts the following way:

Code:

#!/bin/ksh

typeset PLAN="$1"

print - "PLAN is: \"$PLAN\""
inner_shell.ksh "$PLAN"

exit $?

inner_shell.ksh:

Code:

#!/bin/ksh

typeset PLAN="$1"
typeset CmdFile="/home/i276764/ps_refresh/PS_generate_SQL.sql"   # i suppose?
typeset CmdTmp="/tmp/PS_generate_SQL.sql"
typeset -i ErrLvl=0

print - "$0 called with \"$PLAN\""
sed 's/\${PLAN}/'"${PLAN}"'/' $CmdFile > $CmdTmp

bteq > ps_generate_sql.log <<EOF
.run FILE=/home/i27/tdata_stg_logon.txt
.set errorout STDOUT
.set width 2900

.run file="${CmdTmp}" 

.IF errorlevel > 8 then .goto badreturn
.Quit 0
EOF

ErrLvl=$?
rm -rf $CmdTmp

exit $ErrLvl

/home/i276764/ps_refresh/PS_generate_SQL.sql (i believe? if not, change the name and the mechanism of copying/modifying a "blueprint-file" shown above accordingly)

Code:

select 
	lst.pln
	,lst.databasename
	,lst.table_name
	,lst.view_typ
from TABLE1 lst
left join TABLE2 cnt
        on lst.pln=cnt.pln
	and lst.table_name=cnt.table_name
	and lst.view_typ=cnt.view_typ
where cnt.table_name is null
	and view_typ = 'views_all'
	and pln in (${PLAN}))

I hope this helps.

bakunin