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

    Unanswered: Db2 stored procedure returns empty

    Hi,
    I am very new to Db2 procedures. I have written one procedure & trying to read records from the file.It is working fine & i can see nothing when it runs. Kindly look at the below code & correct me if I am wrong. I tried to get the figure it out through Google ,but I can't.
    My code is

    CREATE PROCEDURE READ_META_DISTINCT ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE TB_NAME VARCHAR(128);
    DECLARE EOF INT DEFAULT 0;
    DECLARE STMT VARCHAR(500);
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM META_DISTINCT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF=1;
    -- Cursor left open for client application
    OPEN cursor1;
    WHILE EOF=0
    DO
    FETCH cursor1 INTO TB_NAME;

    END WHILE;
    CLOSE cursor1;
    END P1

    I don't know what to place after FETCH to display the values from the variable.Please let me know.

    Thanks In advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    try to read about sp if being new
    an sp runs at the server location and does not show on remote console
    datastudio can help you while debugging an sp and see what is happening
    the indicated sp just fetches all rows from table and leaves when eof condition
    if sample code is needed : look in sample directory where db2 installed and you find many samples....
    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
    Jul 2014
    Posts
    294

    Db2 stored procedure returns empty

    @przytula_guy

    Thank you..

  4. #4
    Join Date
    Jul 2014
    Posts
    294

    Db2 stored procedure returns empty

    This is my procedure 1:

    CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
    DYNAMIC RESULT SETS 2

    P1: BEGIN
    DECLARE RESULT1 RESULT_SET_LOCATOR VARYING;
    DECLARE cursor1 CURSOR WITH RETURN for
    SELECT * FROM SYSIBM.SYSCOLUMNS;

    --CREATE TABLE VENKATASD.META_DATA LIKE SYSIBM.SYSCOLUMNS;----('MANUALLY CREATED')
    INSERT INTO VENKATASD.META_DATA(SELECT * FROM SYSIBM.SYSCOLUMNS);
    -- Cursor left open for client application

    CALL PROCEDURE2();
    ASSOCIATE RESULT SET LOCATOR(RESULT1) WITH PROCEDURE PROCEDURE2;
    ALLOCATE CSR1 CURSOR FOR RESULT SET RESULT1;
    --FETCH CSR1 INTO RESULT1;--
    OPEN cursor1;

    END P1

    This is My procedure 2:

    CREATE PROCEDURE PROCEDURE2 ()
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN

    DECLARE cursor1 CURSOR WITH RETURN TO CALLER FOR

    SELECT * FROM VENKATASD.META_DATA WHERE TBCREATOR='VENKATASD';
    ----CREATE TABLE VENKATASD.META_DISTINCT(TBNAME VARCHAR(128));-----(manually created)
    INSERT INTO VENKATASD.META_DISTINCT(SELECT DISTINCT TBNAME FROM VENKATASD.META_DATA);
    -- Cursor left open for client application
    OPEN cursor1;
    ----CALL DISPLAY_META_DISTINCT();----
    ----OPEN cursor2;---
    END P1

    I am not getting the desired output: . two procedures are compiling & on running the first procedure ,I can see the output from procedure1(first select statement) that's it.

    Kindly correct me if I am wrong .

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
  •