procedure erroe

hi there,
getting error below while running procedure via command editor, Anu idea whats wrong?

------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE dmart_mkt1.spGenDates1 ( IN in_StartDT DATE,IN in_EndDt DATE)
SPECIFIC spGenDates1
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
 DECLARE DateVar DATE;
 DECLARE v_counter INTEGER DEFAULT 0;
 if in_StartDT is null then
   SET in_StartDT = Date('1972-01-01');
 end if;

 if in_EndDt is null then
    SET in_StartDT = current_date;
 end if;

 SET DateVar = in_StartDT;

 insert_loop:
 LOOP
   IF days(DateVar) > days(in_EndDt) THEN
      LEAVE insert_loop;
   end if;
     INSERT INTO DMART_MKT1.STG_DATE_TEST (FISCAL_YR
      ,QUARTER_ID
      ,FISCAL_QTR
      ,QUARTER_DISPLAY
      ,MONTH_ID
        ,FISCAL_MTH
      ,DAYS_IN_MONTH
      ,MONTH_NAME
      ,DATE_DISPLAY
      ,DAY_OF_WEEK
      ,DAY_OF_MONTH
      ,DAY_OF_YEAR
        ,WEEK_OF_MONTH
      ,WEEK_OF_QUARTER
      ,WEEK_OF_YEAR
      ,WEEKDAY
      ,FISCAL_DATE
      ,ETL_LAST_UPD_DT
      ,ETL_CREATE_DT
      ,ETL_ARCHIVED_REC)
     VALUES (YEAR(DateVar),
      INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(quarter(DateVar))))),
      quarter(DateVar),
      concat('Q',rtrim(char(quarter(DateVar)))),
      INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(month(DateVar))))),
      month(DateVar),
      day((DateVar +1 month -day(DateVar) day)) ,
      monthname(DateVar),
      CHAR(DateVar,iso),
      DAYOFWEEK(DateVar),
      DAY(DateVar),
      DAYOFYEAR(DateVar),
      WEEK(DateVar) - WEEK((DateVar - day(DateVar) day +1 day)) + 1,
      WEEK(DateVar) - Week(DateVar  - DAYOFYEAR(DateVar) DAYS + ((QUARTER(DateVar ) - 1) * 3) MONTHS + 1 DAYS) ,
      WEEK(DateVar),DAYNAME(DateVar),DateVar,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'N');
   SET dateVar = dateVar + 1 days;
 END LOOP insert_loop;
END;
------------------------------------------------------------------------------
CREATE PROCEDURE dmart_mkt1.spGenDates1 ( IN in_StartDT DATE,IN in_EndDt DATE)
SPECIFIC spGenDates1
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
DECLARE DateVar DATE
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "DECLARE
DateVar DATE".  Expected tokens may include:  "<psm_semicolon>".  LINE
NUMBER=6.  SQLSTATE=42601

DECLARE v_counter INTEGER DEFAULT 0
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "0" was found following "NTER INTEGER DEFAULT".  
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

if in_StartDT is null then SET in_StartDT = Date('1972-01-01')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "if in_StartDT is null then" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
SQLSTATE=42601

end if
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "end if".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

if in_EndDt is null then SET in_StartDT = current_date
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "if in_EndDt is null then" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_proc>".  
SQLSTATE=42601

end if
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "end if".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SET DateVar = in_StartDT
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "DateVar" was found following "SET ".  Expected
tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

insert_loop: LOOP IF days(DateVar) > days(in_EndDt) THEN LEAVE insert_loop
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "insert_loop: LOOP IF days(DateVar) >" was found
following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
"<create_table>".  SQLSTATE=42601

end if
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "end if".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

INSERT INTO DMART_MKT1.STG_DATE_TEST (FISCAL_YR ,QUARTER_ID ,FISCAL_QTR ,QUARTER_DISPLAY ,MONTH_ID ,FISCAL_MTH ,DAYS_IN_MONTH ,MONTH_NAME ,DATE_DISPLAY ,DAY_OF_WEEK ,DAY_OF_MONTH ,DAY_OF_YEAR ,WEEK_OF_MONTH ,WEEK_OF_QUARTER ,WEEK_OF_YEAR ,WEEKDAY ,FISCAL_DATE ,ETL_LAST_UPD_DT ,ETL_CREATE_DT ,ETL_ARCHIVED_REC) VALUES (YEAR(DateVar), INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(quarter(DateVar))))), quarter(DateVar), concat('Q',rtrim(char(quarter(DateVar)))), INTEGER(concat(rtrim(char(year(DateVar))),rtrim(char(month(DateVar))))), month(DateVar), day((DateVar +1 month -day(DateVar) day)) , monthname(DateVar), CHAR(DateVar,iso), DAYOFWEEK(DateVar), DAY(DateVar), DAYOFYEAR(DateVar), WEEK(DateVar) - WEEK((DateVar - day(DateVar) day +1 day)) + 1, WEEK(DateVar) - Week(DateVar  - DAYOFYEAR(DateVar) DAYS + ((QUARTER(DateVar ) - 1) * 3) MONTHS + 1 DAYS) , WEEK(DateVar),DAYNAME(DateVar),DateVar,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'N')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0206N  "DATEVAR" is not valid in the context where it is used.  
SQLSTATE=42703

SET dateVar = dateVar + 1 days
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "SET dateVar =" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  
SQLSTATE=42601

END LOOP insert_loop
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "LOOP" was found following "END ".  Expected
tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  Expected tokens may include:  "JOIN <joined_table>                              ".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>".  The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.  

 As an aid to the programmer, 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
nav29Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
sure, you need to change your command terminator to be something else than ;
for example #
you can do it using the switch parameters of the db2 command, i think it's the -t switch
0
momi_sabagCommented:
by command editor you mean the gui tool?
if so, search under options for the chatacter that terminates a commnd (command separator) and change it from ; to something else
0
momi_sabagCommented:
from the command line
db2 -td$ will make $ to be the terminator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.