Hi Everybody,

I need to write a script to monitor tablespace health.

I am done with tablespace health. But I need to run this script for 7 different databases.

I need something like:

FOR dbname IN ('ABC' 'BCD','CDE','DEF','EFG',.. .. ..)
DO
connect to &dbname user dbfinder using mypass;

select current timestamp, tabspc_type,............;

disconnect &dbname;

END FOR

TERMINATE;


So I have statement to collect data ; but I want to modify only first line to add the database name in group (array).

Help please

DBFinder

This would not be a SQL script. Use an OS script instead. Just pretext each DB2 command with "db2".

Andy

Well , you mean,

C:\Program Files\IBM\SQLLIB\BIN>getstatus ABC BCD CDE DEF EFG FGH . . . .

upto 9 or more parameters

then in batch file

getstatus.bat:

@echo off

db2 -tf tblspc.sql %1 %2 .... ..... ..... ...

echo on

------------
this does not work.
tblsp.sql has series of sql statements to select some tablspace parameters.

So I have to use .BAT file for all sql commands and reitrate the block using %1 %2 %3 %4 ............ parameters.

That may work ... I will write something and get back to you.

But there may be some simple method that we can save database names in memory like arrays or tables. Moreover we might want username and passwords be passed for each database.

Help will be appreciated.
DBFinder

You are going to have to use .bat to do this. In order to execute SQL you need a connection, and you want to connect in you script. So SQL cannot be used.

Andy

@ echo off

set DATABASES=ABC BCD CDE DEF

for %%b in (%DATABASES%) do CALL :dISPLAY %%b

goto :finish


:dISPLAY


db2 connect to %1 user DBFinder using Mypass

db2 "select current timestamp,substr(tablespace_name,1,20) as TBSPC_NAME,. . . . . . ."

goto :eof

:finish

set DATABASES=
db2 connect reset