Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Unanswered: Stored procedure errors.

    Hi, i'm new to DB2 and after lot of effort i wrote this stored procedure, but on execution i'm getting following errors

    Expected tokens may include: "END-OF-STATEMENT".. SQLCODE=-104, SQLSTATE=42601, "BEGIN-OF-STATEMENT"

    I have no clue on this...below is the code


    create or replace procedure ODS_T.datapartition(IN schemaname CHAR(10), IN tablename CHAR(20), IN jobid INT)
    SPECIFIC ODS_T.datapartition
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
    BEGIN
    DECLARE V_SCHEMANAME VARCHAR(1024);
    DECLARE V_TABLENAME VARCHAR(1024);
    DECLARE V_JOBID INTEGER;
    DECLARE V_PART_PER VARCHAR(1024);
    DECLARE V_BUSINESS_DATE VARCHAR(10);
    DECLARE V_SUFFIX VARCHAR(100);
    DECLARE V_SQLSTATE VARCHAR(100);
    DECLARE V_SQLCODE INTEGER;
    DECLARE a varchar(1024);
    DECLARE V_DYNSQL varchar(1024);

    --select sqlstate into V_SQLSTATE from sysibm.sysdummy1;
    --select sqlcode into V_SQLCODE from sysibm.sysdummy1;
    set V_SCHEMANAME =schemaname;
    set V_TABLENAME =tablename;
    set V_JOBID =jobid;
    select PART_PER into V_PART_PER from CTRL_T.CTRL_TARGET where upper(TARGET_TABLE) = upper(V_TABLENAME) and upper(TARGET_SCHEMA)=upper(V_SCHEMANAME);
    select to_char(BUSINESS_DATE, 'YYYY-MM-DD') into V_BUSINESS_DATE from ctrl_t.ctrl_load where JOB_STATUS=0 and JOB_ID=jobid and upper(TARGET_TABLE) = upper(V_TABLENAME) and upper(TARGET_SCHEMA)=upper(V_SCHEMANAME);
    set V_SUFFIX=VARCHAR_FORMAT(BUSINESS_DATE,'YYYY/MM/DD');
    set a='PART_'||V_PART_PER||'_'||V_SUFFIX;

    set V_DYNSQL='ALTER TABLE' || V_SCHEMANAME.V_TABLENAME || 'ADD PARTITION' || a || 'STARTING from' || V_BUSINESS_DATE || 'ENDING at' || V_BUSINESS_DATE || 'inclusive';
    EXECUTE IMMEDIATE V_DYNSQL;
    END

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if you look at previous entries in the forum talking about this (many)
    the statement terminator !! because ; is already in use.....
    eg. db2 -td@ -vf xxxxx
    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
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Additional remark: you'll have to add a blank after 'ALTER TABLE' (within the quotes) at the one but last line of your procedure, and similarly at beginning and end of the subsequent text fields; and don't forget explicit quotes around non-numeric data:
    Code:
    set V_DYNSQL='ALTER TABLE ' || V_SCHEMANAME || '.' || V_TABLENAME || ' ADD PARTITION ' || a || ' STARTING from ''' || V_BUSINESS_DATE || ''' ENDING at ''' || V_BUSINESS_DATE || ''' inclusive';
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just two additional remarks:
    - If you want to access SQLCODE and/or SQLSTATE, you'll have to declare them explicitly.
    - To the content of one variable into an other one, you never need SELECT .. INTO .. FROM sysdummy1: just use the SET statement! Actually, the right hand side of SET can even be (or contain) a subquery, e.g.:
    Code:
    SET V_PART_PER = (select PART_PER
                      from CTRL_T.CTRL_TARGET
                      where upper(TARGET_TABLE) = upper(V_TABLENAME)
                        and upper(TARGET_SCHEMA)=upper(V_SCHEMANAME));
    Your SELECT .. INTO .. version for this statement is of course fine (and even better, but that is a personal opinion), but for the two commented-out statements I would never use SELECT..INTO..
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

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
  •