Variable declaration in a SQL Script

[dmsanchezr]

Hi there, I wonder if the following is possible in my command line interface: to write a SQL script that declares a variable, assigns any value to that variable and use this value for any purpose. For example:



declare myvar varchar

select myvar = volume_name from volumes where stgpool_name = 'BACKUPPOOL' and status = 'FULL'

update vol myvar access=readonly



I'm not sure this sintax will work on a TSM script, so if anyone can help me I will be grateful.



Greetings,



Diego.

 

[spmcant]

Hello

You can write a shell script ie:

for i in $(dsmck q vol stat=full|grep stgpool|grep Full|awk '{print $1}')

do

echo $i

done

at the echo $i : just upd vol $i acc=reado



One must be careful about sql against the TSM DB they can load the server down or if your selection creates a very large temp table will you have enought TSM space in the DB to handle it. We had a sql select that created a temp table and we did not have enough room and had to kill the TSM Server it as hung.

 

[heada]

Whats wrong with this from the admin command-line?



update vol "*" access=readonly wherestgpool=backuppool wherestatus=full



Does the same thing and you don't have to write anything new. Just my 0.02$US(Your excahnge rate my vary)



-Aaron

 

[dmsanchezr]

What I am looking for is a script that returns only one row, that's why the TSM command is not really useful for me.

 

[bmcferon]

If you are handy with KSH, Ive got functions I use that give me single line output'



Here they are:



function tsmchomp {

#Format the TSM Query information into a useable data stream

sed 1,12d | sed -e :a -e '$d;N;2,4ba' -e 'P;D'

}

function dsmconnect {

#Open a batch TSM admin session, and run the command

dsmadmc -id=$id -password=$pwd -se=$1 "$2 $3 $4 $5"

}



Then you process by



dsmconnect servername command switch1 switch2 switch3 switch4 switch5 | tsmchomp



I usually create SQL scripts within TSM so that I keep switches down to a minimum.





<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><font class="pn-sub">Quote:</font><HR></TD></TR><TR><TD><FONT class="pn-sub"><BLOCKQUOTE>What I am looking for is a script that returns only one row, that's why the TSM command is not really useful for me.</BLOCKQUOTE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE>