Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Illegal symbol "<end-of-statement>". Some symbols that might be legal are: ) union

    Hi,

    I am new to stored procedures on db2 Z/OS.
    I have connected to the Mainframe machine via IBM data studio to access Db2 & to create native plsql stored procedures.Sorry to post my code, but I have given everything in a proper way. Able to compile but while running I am getting the below error:

    CREATE PROCEDURE ()TRY
    LANGUAGE SQL
    FENCED
    COLLID TEST
    WLM ENVIRONMENT DB8GENV1
    RUN OPTIONS 'NOTEST(NONE,*,*,*)'
    P1: BEGIN

    DECLARE DBNAME varchar(50);
    DECLARE exitcode INTEGER DEFAULT 0;
    DECLARE STMT1 varchar(1000);
    DECLARE TRY_SERVER varchar(3000);
    DECLARE TRY_SQLDB_METADATA varchar(4000);
    DECLARE tableExists int;
    DECLARE LOG_PROCESS VARCHAR(4000);
    DECLARE JOB_ID INTEGER;
    DECLARE LINE_NUM INTEGER;
    DECLARE M_JOB_ID INTEGER;
    DECLARE USR_MAP VARCHAR(4000);
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE TEXT VARCHAR(32672);

    DECLARE mycur CURSOR WITH HOLD FOR
    select SCHEMA_NAME from DBLIST;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET exitcode = 1;

    OPEN mycur;
    FETCH FROM mycur INTO DBNAME;

    wloop1:
    WHILE (SQLCODE = 0)
    DO

    SET TRY_SQLDB_METADATA = 'INSERT INTO TRY1.TRY_SQLDB_METADATA
    (DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,MAX_ LENGTH,IS_NULLABLE)
    (select '''||DBNAME||''' as DB_NAME,
    TAB.NAME as TABLE_NAME,
    COL.NAME as COLUMN_NAME,
    COL.COLTYPE as S_COLUMN_TYPE,
    COL.LENGTH as MAX_LENGTH,
    COL.NULLS as IS_NULLABLE
    from SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL
    where TAB.CREATOR = '''||DBNAME||''' and TAB.NAME not like ''SYS%''
    and TAB.CREATOR not like ''SYS%'' and
    TAB.type = ''T'' and COL.TBCREATOR=TAB.CREATOR';

    execute immediate TRY_SQLDB_METADATA;

    SET exitcode = 0 ;
    FETCH FROM mycur INTO DBNAME;
    IF (exitcode = 1)
    THEN
    LEAVE wloop1;
    END IF;
    END WHILE;

    CLOSE mycur;
    END P1

    Error:

    Run: TRY1.TRY()

    {? = call TRY()}
    ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ) UNION EXCEPT. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30
    Run of routine failed.
    - Roll back completed successfully.

    what does this error. I never faced this while I m working on DB2LUW .


    How to resolve this.

    OS: Windows,
    Db2 : 8 version

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The dynamic SQL statement posted above lacks a matching ) for a ( .
    That's what the hint "symbols that might be legal are ) " might suggest.
    Why do you want a ( ) around the select in this specific case anyway?

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the help, I corrected the error.

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
  •