Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Question Unanswered: How to use array in SQL scripts ??

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This would not be a SQL script. Use an OS script instead. Just pretext each DB2 command with "db2".

    Andy

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    @ 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

Posting Permissions

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