I needed to automated the table partitioning activity thru a stored proc. After 5 days, the 1st added partition should be detached and new partition should be added.I told my developer i want a proc to schedule the activity.
The commans I want to execute are :

CONNECT TO <dbname> user <username> using <password> ;
Set Schema='A;
ALTER TABLE mailbox ADD PARTITION DAY30
STARTING FROM ('2008-12-30-00.00.00') INCLUSIVE ENDING AT ('2008-12-30-23.59.59') INCLUSIVE IN TBSPC01;
ALTER TABLE mailbox DETACH PARTITION Part01 INTO TABLE T1;
DROP TABLE T1;
Connect reset;

the developer gave me the following stored proc:

DROP SPECIFIC PROCEDURE A.USPALTERPARTITIONS
@
SET SCHEMA A
@
SET CURRENT PATH=SYSFUN
@
CREATE PROCEDURE A.USPALTERPARTITIONS
(
)
SPECIFIC A.USPALTERPARTITIONS
DYNAMIC RESULT SETS 0
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

--************************************************** *********************************************
-- Procedure name : USPALTERPARTITIONS
-- Description : This sp is used to detach and add partitions
-- Input parameters : None
-- Output parameters : None
-- Result sets : None
-- Output : None
-- Dependencies
-- A) Tables : mailbox
-- B) Nested Procedures : -None-
-- Procedure logic : Detach the old partition (5 days older) and alter the table
-- to add a new partition
-- Revision History : None
--************************************************** *********************************************

P1 : BEGIN

DECLARE v_db2_msg VARCHAR(80) DEFAULT '';
DECLARE v_error SMALLINT DEFAULT 0;

DECLARE v_starting_from VARCHAR(20) DEFAULT '';
DECLARE v_ending_at VARCHAR(20) DEFAULT '';

DECLARE V_CUR_DAY CHAR(5);
DECLARE V_5DAYS_BEFORE CHAR(5);
DECLARE V_ADD_PARTITION VARCHAR(200);
DECLARE V_DETACH_PARTITION VARCHAR(200);

DECLARE V_ADD VARCHAR(200);
DECLARE V_DETACH VARCHAR(200);

-- Declare Handler for 'Not Found' condition
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN NOT ATOMIC
GET DIAGNOSTICS EXCEPTION 1 v_db2_msg = MESSAGE_TEXT;
SET v_error = 1;
END;

-- Declare Handler for SQL Warning
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN NOT ATOMIC
GET DIAGNOSTICS EXCEPTION 1 v_db2_msg = MESSAGE_TEXT;
SET v_error = 2;
END;

-- GET THE CURRENT DAY

SELECT 'DAY' || (CASE WHEN DAY(CURRENT_DATE) < 10 THEN '0'|| CHAR(DAY(CURRENT_DATE))
ELSE CHAR(DAY(CURRENT_DATE))
END)
INTO V_CUR_DAY
FROM SYSIBM.SYSDUMMY1;

-- GET THE FIVE DAYS BEFORE DAY

SELECT 'DAY' || (CASE WHEN DAY(CURRENT_DATE - 5 DAYS) < 10 THEN '0'|| CHAR(DAY(CURRENT_DATE - 5 DAYS))
ELSE CHAR(DAY(CURRENT_DATE - 5 DAYS))
END)
INTO V_5DAYS_BEFORE
FROM SYSIBM.SYSDUMMY1;

-- FRAME THE STARTING FROM AND ENDING AT VALUES

SET V_STARTING_FROM = RTRIM(CHAR(YEAR(CURRENT DATE))) ||'-'|| RTRIM(CHAR(MONTH(CURRENT DATE)))||'-'||RTRIM(CHAR(DAY(CURRENT DATE))) || '-00.00.00';
SET V_ENDING_AT = RTRIM(CHAR(YEAR(CURRENT DATE))) ||'-'|| RTRIM(CHAR(MONTH(CURRENT DATE)))||'-'||RTRIM(CHAR(DAY(CURRENT DATE))) || '-23.59.59';

-- FRAME THE DETACH PARTITION SQL

SET V_DETACH_PARTITION = ' ALTER TABLE A.mailbox DETACH PARTITION '|| V_5DAYS_BEFORE || ' INTO OFFM.' || V_5DAYS_BEFORE;

-- FRAME THE ADD NEW PARTITION SQL

SET V_ADD_PARTITION = ' ALTER TABLE A.mailbox ADD PARTITION '|| V_CUR_DAY || ' STARTING FROM (' || V_STARTING_FROM || ') INCLUSIVE ENDING AT (' || V_ENDING_AT || ') INCLUSIVE IN TBSPC'||SUBSTR(V_CUR_DAY,4,2);

-- RUN THE DETACH SQL USING ADMIN_CMD
-- CALL SYSPROC.ADMIN_CMD(V_DETACH_PARTITION);

-- PREPARE THE SQLS
PREPARE v_DETACH FROM V_DETACH_PARTITION;
PREPARE v_ADD FROM V_ADD_PARTITION;

-- EXECUTE THE SQLS

EXECUTE V_DETACH;
EXECUTE V_ADD;

-- RUN THE ADD NEW PARTITION SQL USING ADMIN_CMD
-- CALL SYSPROC.ADMIN_CMD(V_ADD_PARTITION);

END P1
@

=============
I was sucessfully able to add the stored proc on the DB. But when i call it it gave the following error:

CALL A.USPALTERPARTITIONS()
SQL0104N An unexpected token "-" was found following "STARTING FROM (2009".
Expected tokens may include: ",". SQLSTATE=42601

SQL0104N An unexpected token "-" was found following "STARTING FROM (2009". Expected tokens may include: ", ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601


============

Can someone HELP pls, it is very UGT.:-(

the result will be something like:
... STARTING FROM ( 2009-01-01 ) INCLUSIVE ENDING ...

but the date must be in quotes
... STARTING FROM ('2009-01-01') INCLUSIVE ENDING ...

so you have to add the quotes in your SET-statement

STARTING FROM (''' || V_STARTING_FROM || ''') INCLUSIVE ENDING AT (''' || V_ENDING_AT || ''') INCLUSIVE

Gr8! thx. But one more query related to this.
Here in the proc i want the Tablespace name to be of the partion i have detached. how can i do that,
Means if i detach Partition 01 having Table space as TBSPC03. i want my ADD statement Table space as TBSPC03.
Currently the tablesapce name is created based on the date.

Also i want to schedule this stored proc through a windows batch file. any idea how i can do it