Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    47

    Unanswered: DB2 CLP commands in a Stored Procedure ?

    Hi,

    Can we issue db2 commands in a stored procedure.

    For ex : ALTER TABLE EMPLOYEE NOT LOGGED INITIALLY WITH EMPTY TABLE;

    (Passing the name of the table as a parameter to the stored procedure)

    Thanks
    Venky

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ALTER TABLE is not a CLP command, it's a SQL statement that "can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared..."

    Hint: use PREPARE statement.

  3. #3
    Join Date
    Jul 2005
    Posts
    47

    Question Checking out if this SP works.

    Hi,

    Can anyone please let me know if this stored procedure serve my purpose.

    CREATE PROCEDURE <SCHEMA>.EMPTYTABLE ( IN TBLNAME INTEGER, OUT RESULT INTEGER )
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- TBLNAME
    -- RESULT
    ------------------------------------------------------------------------
    P1: BEGIN

    -- Declare variable
    DECLARE AVGSAL_TMP FLOAT DEFAULT 0.0;
    DECLARE TABLE = TBLNAME;

    -- Declare cursors for result set and output parameter data

    DECLARE cursor1 CURSOR WITH HOLD WITH RETURN FOR
    SELECT COUNT(*) FROM <SCHEMA>.TABLE ;
    SET stmt = 'ALTER TABLE' || <SCHEMA>.TABLE || 'NOT LOGGED INITIALLY WITH EMPTY TABLE';
    PREPARE s2 FROM stmt;
    EXECUTE s2;

    DECLARE cursor2 CURSOR WITH RETURN FOR
    SELECT COUNT(*) FROM <SCHEMA>.TABLE;

    -- Cursor1 is left open for client application
    OPEN cursor1;
    OPEN cursor2;
    FETCH cursor2 into RESULT_TEMP;

    CLOSE cursor2;
    SET RESULT = RESULT_TEMP;

    END P1


    Thanks,
    Venky

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by venky5436

    Can anyone please let me know if this stored procedure serve my purpose.
    Let me guess: that would be... you?

  5. #5
    Join Date
    Jul 2005
    Posts
    47
    Thanks a lot for your suggestion it worked succesfully.But I am trying to modify it to delete a particular row depending on the where condition.

    I just want to insert this statement
    ALTER TABLE schema.tablename NOT LOGGED INITIALLY;
    delete from schema.tablename where month = 200512 ;

    (SET stmt1 = 'ALTER TABLE '|| p_TableName ||' ACTIVATE NOT LOGGED INITIALLY ';
    PREPARE s2 FROM stmt;
    EXECUTE s2;


    SET stmt2 = 'DELETE FROM '|| p_TableName ||' WHERE MONTH = ' || 'P_MONTH';
    PREPARE s3 FROM stmt2;
    EXECUTE s3


    It compiles without any error but it ends with TABLENAME undefined.
    Does having 2 different statement on the same table ina procedure lock the table that it canot be accesed twice i mean one for alter and the other for delete.

    CREATE PROCEDURE SCHEMA.SP_Empty_Table (IN p_TableName VARCHAR(50), OUT p_Rows INTEGER, OUT RtnCode INTEGER )

    ------------------------------------------------------------------------

    -- SQL Stored Procedure

    -----------------------------------------------------------------------
    LANGUAGE SQL
    SPECIFIC EMPTY_TABLE1
    P1: BEGIN

    declare stmt varchar(150);
    -- Declare cursors for result set and output parameter data

    SET stmt = 'ALTER TABLE '|| p_TableName ||' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
    PREPARE s1 FROM stmt;
    EXECUTE s1;
    COMMIT;
    END P1

Posting Permissions

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