Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Angry Unanswered: DB2 dynamic sql query inside of the sql script

    I work on db2 v 8.01 for AIX.
    Is it possible to write dynamic sql query inside the script like in store procedure?
    I would like to be able to define variables and execute dynamic queries from within the script - something like:
    DECLARE stmt VARCHAR(1000);
    DECLARE table_name VARCHAR(50);
    SET table_name = 'DEPT_'||deptNumber||'_T';
    SET stmt = 'DROP TABLE '||table_name;
    PREPARE s1 FROM stmt;
    EXECUTE s1;


    I can do that from within the store procedure but not in the script.

    Please help

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Why not just write a shell script?

  3. #3
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    A suggestion....

    Create a script with this:
    SELECT 'DROP TABLE ' || name FROM sysibm.systables
    WHERE name LIKE 'DEPT_%_T'
    @

    Execute
    db2 -td@ -f script > drop.$$

    Then
    db2 -t -f drop.$$

Posting Permissions

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