Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    19

    Unanswered: Drop schema via stored proc ??

    Guys ,
    can we DROP A SCHEMA (Containing objects/widout any objects) via a stored proc ?

    I tried to drop a table within my schema via stored proc ..Foll code worked fine :
    CREATE PROCEDURE procschema5(IN name VARCHAR(10))
    LANGUAGE SQL
    BEGIN
    DROP table sagar3.employee;
    END@

    and when i call it my table got deleted.


    but following is not working :

    CREATE PROCEDURE procschema6(IN name VARCHAR(10))
    LANGUAGE SQL
    BEGIN
    DROP SCHEMA sagar3 RESTRICT;
    END@

    error is : DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "DROP" was found following " LANGUAGE SQL BEGIN
    ". Expected tokens may include: "SET". LINE NUMBER=4. SQLSTATE=42601

    Thanks for reading !

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    could you indicate version and platform so this can be verified in the doc
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You cannot drop schema using static SQL in stored procedures. Only Tables, indexes and views can be done.

    Try using dynamic SQL.

    SQL statements that can be executed in routines - IBM DB2 9.7 for Linux, UNIX, and Windows

    Or, if your db2 version/platform supports use ADMIN_DROP_SCHEMA procedure
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2013
    Posts
    19
    DB2 9.5

    "IBM Data Server Client Version 9.5 Fix Pack 5 "

    and i am using CONTROL CENTER.......

  5. #5
    Join Date
    Mar 2013
    Posts
    19
    Thanks satyaram...
    was offline for 2 days so cudnt reply on time....
    Will work on the link that u provided.........

    and post reply later

  6. #6
    Join Date
    Mar 2013
    Posts
    19
    Quote Originally Posted by sathyaram_s View Post
    You cannot drop schema using static SQL in stored procedures. Only Tables, indexes and views can be done.

    Try using dynamic SQL.

    SQL statements that can be executed in routines - IBM DB2 9.7 for Linux, UNIX, and Windows

    Or, if your db2 version/platform supports use ADMIN_DROP_SCHEMA procedure


    THis works fine for me :

    CREATE PROCEDURE SP_SAMPLE()
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE c CURSOR WITH RETURN FOR
    SELECT * FROM sagar123.emp;
    OPEN c;
    END

    CALL SP_SAMPLE()


    But when i write DROP SCHEMA or CALL_ADMIN_DROP its not working

    CREATE PROCEDURE SP_SAMPLE89()
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE c CURSOR WITH RETURN FOR
    DROP SCHEMA sagar1234 RESTRICT; OR "call admin_drop_schema ('SAGAR1234', NULL, 'ERRORSCHEMA', 'SCHEMADROPERR')"
    OPEN c;
    END

    neither i can insert values in my table.....
    can u tell em where i am WRONG ?

Tags for this Thread

Posting Permissions

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