Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Question Unanswered: how to excute these sql code

    Code:
    begin atomic
      declare sqlcode integer default 0; 
      declare procedure_name varchar(50);
      declare mycursor cursor for select Procedure_name from SYSIBM.SQLPROCEDURES where Procedure_schem = 'db2admin';
      open mycursor; 
      cursorLoop:
      loop 
      fecth mycursor into procedure_name ; 
      if sqlcode<>0 then leave cursorLoop; 
      	EXECUTE IMMEDIATE 'drop procedure '||procedure_name;
      end if; 
      end loop; 
      close mycursor;
    end;
    when i excute the code above,i got a error message
    An unexpected token "for" was found following "lare mycursor cursor". Expected tokens may include: "<SQL_variable_condition_declaration>". LINE NUMBER=4
    .

    i want to drop all my procedures,and the alternative code works on oracle.
    what's wrong with my code and how to modify?

    sorry form my poor english and my poor knowledge.

  2. #2
    Join Date
    Jul 2012
    Posts
    6
    i am new to DB2
    please help me
    thanks

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    There are several ways to drop all the procedures in a specific schema.

    You can use the command-line (db2cwadmin.bat). For some people this is the most easy method.

    You can use anonymous block in ANSI SQL PL. You tried to do that, but you got the syntax wrong, and made other mistakes.

    You can use anonymous block like Oracle PL/SQL if your instance and database is created for Oracle compatibility and you are using DB2 v9.7 or higher. DB2 v9.7 supports many SQL*PLUS features, and has a shell called CLPPLUS which behaves like SQL*PLUS in many respects. You can choose to use CLPPLUS or ignore it, but in both cases you can still run SQL*PLUS type anonymous block (with some limitations).


    For example , on the db2cwadmin.bat command line:
    Code:
    db2 connect to _your_database_name_
    
    db2 -x "select 'drop procedure '||rtrim(procschema)||'.'||rtrim(procname)||';' from syscat.procedures where procschema='DB2ADMIN' " > drop_db2admin_procedures.sql
    
    
    db2 -tvf drop_db2admin_procedures.sql

    If you prefer to use anonymous blocks, then use "compound SQL (compiled)" style of blocks like this in V9.7 and higher:

    Code:
    --#SET TERMINATOR @
    
    set SERVEROUTPUT ON
    @
    
    BEGIN
      declare v_procedure_name varchar(128);
      declare v_at_end INT default 0;
      declare c_mycursor cursor for
       select procname 
       from syscat.procedures 
       where procschema='DB2ADMIN';
      declare continue handler for not found 
        set v_at_end = 1;
    
      open  c_mycursor; 
      myloop:
      LOOP 
         fetch c_mycursor into v_procedure_name ; 
         if v_at_end = 1 
         then leave myloop;
         end if;
         call dbms_output.put_line('Drop procedure DB2ADMIN.'||v_procedure_name); 
         EXECUTE IMMEDIATE 'drop procedure DB2ADMIN.'||v_procedure_name ;
      END LOOP; 
      close c_mycursor;
    END
    @

    But if you come from an Oracle background, and you are familiar with SQL*Plus style of anonymous blocks, then you can use the same syntax in DB2 V9.7 and higher, if you configure your DB2-instance to support Oracle-compatibility , stop and restart the instance, then create a *new* database in which you do your work. This new database will let DB2 accept the following syntax:

    Code:
    
    SET SQLCOMPAT PLSQL
    /
    SET SERVEROUTPUT ON
    /
    
    DECLARE
      v_procname VARCHAR2(128);
      v_ErrorText VARCHAR(1024);
      CURSOR c_mycursor IS
        SELECT procname
          FROM syscat.procedures
          WHERE procschema='DB2ADMIN';
    BEGIN
      OPEN c_mycursor;
      LOOP
        FETCH c_mycursor INTO v_procname;
        EXIT WHEN c_mycursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Drop procedure DB2ADMIN.'||v_procname);
        EXECUTE IMMEDIATE 'drop procedure DB2ADMIN.'||v_procname ;
      END LOOP;
      CLOSE c_mycursor;
    EXCEPTION
      WHEN OTHERS THEN
         v_ErrorText :=SUBSTR(SQLERRM,1, 1024);
         DBMS_OUTPUT.PUT_LINE('FAILED WITH MESSAGE: '||v_ErrorText);
    
    END;
    /


    So, choose the scripting method that best suits your environment and your skills.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that using FOR statement might be easier.

    Please try...
    Code:
    BEGIN
    
       FOR v1 AS
          SELECT procschema || '.' || procname AS qualified_procname
           FROM  syscat.procedures 
           WHERE procschema = 'DB2ADMIN'
       DO
          EXECUTE IMMEDIATE 'DROP PROCEDURE ' || qualified_procname;
       END FOR;
    
    END@

  5. #5
    Join Date
    Jul 2012
    Posts
    6
    thanks for your replies.
    "compound SQL (compiled)" is exactly what i need and it works.
    but it fails using FOR statement.thanks all the same.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... it fails using FOR statement. ...
    What error did you got? At compile time? or execution time?
    Or, did you got unexpected result?

  7. #7
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    What error did you got? At compile time? or execution time?
    Or, did you got unexpected result?
    i am using DB2 Express-C,and use SQL Manager for DB2 to connect the database.

    the code i put in is
    Code:
    --#SET TERMINATOR @
    
    
    BEGIN
       FOR v1 AS
          SELECT rtrim(procschema) || '.' || procname AS qualified_procname
           FROM  syscat.procedures 
           WHERE procschema = 'DB2ADMIN'
       DO
          EXECUTE IMMEDIATE 'DROP PROCEDURE ' || v1.qualified_procname;
       END FOR;
    END;
    @
    And the error message is
    An unexpected token "END-OF-STATEMENT" was found following "rocname; END FOR ". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=8. SQLSTATE=42601
    which troubles all the time.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You have two statement terminators after the final END. You should have only one.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wonder why so many peoples beeing under their wrong(from my piont of view) impressions, without consious of that fact.

    If sueking copied my code without any modifications and adding only "--#SET TERMINATOR @",
    he/she might reach the another solution without making extra/redundant dialogs with n_i and me.

    If my query(without any modifications) was wrong, and/or returned wrong result or error message,
    it would be useful for us to know more concretely/detailed the bahavior of DB2(and/or general SQL Language/Relaional Daabase concept).
    Last edited by tonkuma; 07-27-12 at 17:05.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My quriosity was why sueking didn't tried my suggestion "END@" after got error by "END; @"?

    It make me thinking of almost every people captured somehow by theire old experirences(procedual, one by one, ...), regardless of me.

  11. #11
    Join Date
    Jul 2012
    Posts
    6
    well,actually i have tried your original code ,"END@" style and some other style before.but it always come out the same error message.

    now i know the the problem is probably the TERMINATOR,though it still puzzles me.i dont kown if it is due to the server-db2 express-c,or the client tool-sql manager for db2.

    appreciate for yor help.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by sueking View Post
    thanks for your replies.
    "compound SQL (compiled)" is exactly what i need and it works.
    but it fails using FOR statement.thanks all the same.
    What is the working "compound SQL (compiled)"?
    Please publish whole code.

  13. #13
    Join Date
    Jul 2012
    Posts
    6
    Code:
    begin
      declare SQLCODE int;
      declare procname varchar(50);
      declare mycursor cursor for
       select procname
       from syscat.procedures
       where procschema = current schema;
    
      open mycursor; 
      FETCH mycursor INTO procname;
      WHILE (SQLCODE = 0) DO
        EXECUTE IMMEDIATE 'drop procedure '||procname;
        FETCH mycursor INTO procname;
      END WHILE;
      close mycursor;
    end
    i didnt add "--#SET TERMINATOR @" nor did i use @

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So, try by removing "@" from my code, like...
    Code:
    BEGIN
    
       FOR v1 AS
          SELECT procschema || '.' || procname AS qualified_procname
           FROM  syscat.procedures 
           WHERE procschema = 'DB2ADMIN'
       DO
          EXECUTE IMMEDIATE 'DROP PROCEDURE ' || qualified_procname;
       END FOR;
    
    END

Posting Permissions

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