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

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.

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

Let me guess: that would be... you?

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