Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: UGT HELP : calling SQL Stored procedure giving error sqlcode : -104

    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.:-(

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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);

    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

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    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
    Last edited by anamika123; 01-29-09 at 07:43.

Posting Permissions

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