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

    Unanswered: Procedure not displaying output

    Hi,

    I have written my first procedure in db2.To get row count from all the tables & to insert them into an external table. But my procedure is running for more time. may be some infinite loop is happening. Kindly help me to resolve this.

    CREATE PROCEDURE ROW_COUNT (IN USERID VARCHAR(10),IN PASSWORD VARCHAR(10))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE t_DBNAME VARCHAR(50);
    DECLARE t_TBNAME VARCHAR(30);
    DECLARE TB_NAME VARCHAR(100);
    DECLARE SQLCODE INTEGER;
    DECLARE RESULT VARCHAR(100);
    DECLARE DBNAME varchar(50);
    DECLARE exitcode INTEGER DEFAULT 0;
    DECLARE STMT1 varchar(1000);
    DECLARE SERVER1 varchar(3000);
    --DECLARE MENTIS_META_DATA varchar(4000);
    ----DECLARE tableExists int;

    DECLARE mycur CURSOR FOR
    select DATABASE_NAME as v_DBNAME
    from DBLIST;

    DECLARE cursor1 CURSOR FOR
    SELECT SERVER as DBNAME,TABLE_NAME as TBNAME FROM MENTIS_META_DATA;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET exitcode = 1;

    EXECUTE IMMEDIATE 'DROP WRAPPER "WRAPPER1"';
    SET STMT1 = 'CREATE WRAPPER "WRAPPER1" LIBRARY ''db2drda.dll'' OPTIONS (ADD DB2_FENCED ''N'')';
    EXECUTE IMMEDIATE STMT1;

    OPEN mycur;
    loop_over_dblist:
    LOOP
    FETCH FROM mycur INTO DBNAME;
    IF exitcode = 1 THEN
    LEAVE loop_over_dblist;
    END IF;

    SET SERVER1 = 'CREATE SERVER "'||DBNAME||'" TYPE DB2/UDB VERSION ''10.5'' '
    ||'WRAPPER "MENTIS_WRAPPER" AUTHORIZATION "'||USERID||'" PASSWORD "'||"PASSWORD"||'" '
    ||'OPTIONS (ADD DBNAME '''||DBNAME||''')';

    execute immediate SERVER1;

    END LOOP loop_over_dblist;

    -- Cursor left open for client application
    OPEN cursor1;
    FETCH cursor1 INTO t_DBNAME,t_TBNAME;
    WHILE SQLCODE = 0
    DO

    SET RESULT = 'INSERT INTO M101(SELECT NAME as TABLE_NAME,CARD as TABLE_COUNT FROM '||t_DBNAME||'.SYSIBM.SYSTABLES WHERE NAME= '||t_TBNAME||')';
    CALL DBMS_OUTPUT.PUT_LINE(RESULT);



    ----CALL DBMS_OUTPUT.PUT_LINE(TB_CARD);
    FETCH cursor1 INTO t_DBNAME,t_TBNAME;
    END WHILE;
    ---END LOOP loop_over_dblist;
    END P1


    Thanks in advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    according to your last post : I will never come back to this forum again..
    and yet.. : still presenting problems with just a publish of code.. no problem description - no show of what is happening - not what has been done - how you discovered this problem - not db2level - platform....
    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
    fetched all the columns from all the databases into a table say m1 . now by using that M1 i am getting the table name & generating their count of rows & inserting into other table these count values.

    SET RESULT = 'INSERT INTO M101 (TABLE_NAME,TABLE_COUNT) VALUES (SELECT NAME as TABLE_NAME,CARD as TABLE_COUNT FROM '||t_DBNAME||'.SYSIBM.SYSTABLES WHERE NAME= '||t_TBNAME||')';

    really don't know y this is taking a long time to execute. Problem may be with the loop.I need ur suggestion regarding the loop , whether I am missing any thing here..

    So i posted the code,

    U can find the problem description on the beginning of this post. I made it clear
    Last edited by HABBIE; 08-19-14 at 08:48. Reason: clear

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
  •