Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    10

    Unanswered: Cannot create proc from CLP

    Hi, I'm new to db2... I'm creating a SQL/PL procedure from the clp, using the command

    db2 -tvf procs.db2

    Where the procs.db2 file contains:


    CREATE PROCEDURE DB2ADMIN.p_getreports ( )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
    DB2ADMIN.REPORT.REPORT_ID AS REPORT_ID,
    DB2ADMIN.REPORT.REPORT_NAME AS REPORT_NAME,
    DB2ADMIN.REPORT.DESCRIPTION AS DESCRIPTION,
    DB2ADMIN.REPORT.CREATE_TIMESTAMP AS CREATE_TIMESTAMP,
    DB2ADMIN.REPORT.UPDATE_TIMESTAMP AS UPDATE_TIMESTAMP
    FROM
    DB2ADMIN.REPORT;

    -- Cursor left open for client application
    OPEN cursor1;

    END P1


    I'm getting the error:

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "DB2ADMIN.REPORT". Expected tokens may include: "JOIN <joined_table>". LINE
    NUMBER=17. SQLSTATE=42601



    It seems it doesn't like the termination character.... can anyone help?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Cannot create proc from CLP

    the option -t indicates the statement termination character is ;. For SQL Procedure(,Triggers & Functions) statements, semi-colon cannot be used as they will be used in the body to delimit SQL Statements.

    Use an alternative like @ and use db2 -td@ -vf procs.db2

    CREATE PROCEDURE DB2ADMIN.p_getreports ( )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
    DB2ADMIN.REPORT.REPORT_ID AS REPORT_ID,
    DB2ADMIN.REPORT.REPORT_NAME AS REPORT_NAME,
    DB2ADMIN.REPORT.DESCRIPTION AS DESCRIPTION,
    DB2ADMIN.REPORT.CREATE_TIMESTAMP AS CREATE_TIMESTAMP,
    DB2ADMIN.REPORT.UPDATE_TIMESTAMP AS UPDATE_TIMESTAMP
    FROM
    DB2ADMIN.REPORT;

    -- Cursor left open for client application
    OPEN cursor1;

    END P1@

    HTH

    Cheers

    Sathyaram

    Originally posted by Ae589
    Hi, I'm new to db2... I'm creating a SQL/PL procedure from the clp, using the command

    db2 -tvf procs.db2

    Where the procs.db2 file contains:


    CREATE PROCEDURE DB2ADMIN.p_getreports ( )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
    DB2ADMIN.REPORT.REPORT_ID AS REPORT_ID,
    DB2ADMIN.REPORT.REPORT_NAME AS REPORT_NAME,
    DB2ADMIN.REPORT.DESCRIPTION AS DESCRIPTION,
    DB2ADMIN.REPORT.CREATE_TIMESTAMP AS CREATE_TIMESTAMP,
    DB2ADMIN.REPORT.UPDATE_TIMESTAMP AS UPDATE_TIMESTAMP
    FROM
    DB2ADMIN.REPORT;

    -- Cursor left open for client application
    OPEN cursor1;

    END P1


    I'm getting the error:

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "DB2ADMIN.REPORT". Expected tokens may include: "JOIN <joined_table>". LINE
    NUMBER=17. SQLSTATE=42601



    It seems it doesn't like the termination character.... can anyone help?

Posting Permissions

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