Solved

Using sub-Sproc to supply dataset to parent sProc?

Posted on 2008-10-09
6
426 Views
Last Modified: 2012-05-05
Before I go off into "left-field" chasing something impossible ... let me ask a question  ...

In an sProc, I declare and open a cursor ... like this ...

     CREATE PROCEDURE MyLIB.AA_PROC ()
          P1 : BEGIN
               DECLARE C1 CURSOR FOR SELECT * FROM MyLIB.MyTBL;
               OPEN C1;
          END P1;

If, inside BB_PROC, I wanted to return the results returned from AA_PROC, is that possible?
Meaning, maybe, something like this?

     CREATE PROCEDURE MyLIB.BB_PROC ()
          P1 : BEGIN
               DECLARE C9 CURSOR FOR SELECT * FROM MyLIB.AA_PROC;
               OPEN C9;
          END P1;

If this is possible, could someone show me the proper syntax?
0
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22680060
it is possible, but not in the way you wrote it
you will have to see how to read a result set back from a procedure
look here
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyresultsete2.htm
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22680074
0
 
LVL 5

Author Comment

by:volking
ID: 22680329
@momi
You're right, the link you gave (repeated below) is EXACTLY what I'm trying to do ...
     http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyresultsete2.htm

But, there is no SQL source code in the examples?
There is RPG, ODBC, JDBC ... but no SQL?

sigh ... I can keep guessing ... but a Syntax Example in SQL would be great!
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22681775
have you checked out the second link?
0
 
LVL 5

Accepted Solution

by:
volking earned 0 total points
ID: 22682338
@momi
yes. The second link is to a hebrew website. But the key command "RETURN TO CLIENT" does not appear anywhere and besides, it talks about COBOL, not SQL ...
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22684456
here you go with the example from the hebrew link :

CREATE PROCEDURE dbload(inout load_statement VARCHAR(1000)
                      , out my_result INTEGER
                      , out sql_state CHAR(5)
                      , out sql_code INTEGER
  )
    SPECIFIC dbload1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
dbload1: BEGIN
    DECLARE SQLSTATE char(5) default '00000';
    DECLARE SQLCODE  integer default 0;
    DECLARE step INTEGER;
    body: BEGIN
        DECLARE loc RESULT_SET_LOCATOR VARYING;
        DECLARE ROWS_READ BIGINT;
        DECLARE ROWS_SKIPPED BIGINT;
        DECLARE ROWS_LOADED BIGINT;
        DECLARE ROWS_REJECTED BIGINT;
        DECLARE ROWS_DELETED BIGINT;
        DECLARE ROWS_COMMITTED BIGINT;
        DECLARE ROWS_PARTITIONED BIGINT;
        DECLARE NUM_AGENTINFO_ENTRIES BIGINT;
        DECLARE MSG_RETRIEVAL VARCHAR(512);
        DECLARE MSG_REMOVAL VARCHAR(512);


        DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
        BEGIN
            SET sql_state = sqlstate;
            SET sql_code = sqlcode;
        END;


        SET step = 1;
        IF load_statement = '' THEN
            SET load_statement = 'LOAD FROM (DATABASE core SELECT * FROM
safebox.test) OF CURSOR MESSAGES ON SERVER REPLACE INTO safebox.test';
        END IF;
        CALL SYSPROC.ADMIN_CMD(load_statement);
        SET step = 2;
        ASSOCIATE RESULT SET LOCATOR (loc)
        WITH PROCEDURE SYSPROC.ADMIN_CMD;
        SET step = 3;
        ALLOCATE my_curs CURSOR
        FOR RESULT SET loc;
        SET step = 4;
        OPEN my_curs;
        SET step = 5;
        FETCH FROM my_curs INTO
            ROWS_READ
          , ROWS_SKIPPED
          , ROWS_LOADED
          , ROWS_REJECTED
          , ROWS_DELETED
          , ROWS_COMMITTED
          , ROWS_PARTITIONED
          , NUM_AGENTINFO_ENTRIES
          , MSG_RETRIEVAL
          , MSG_REMOVAL
          ;
        SET step = 6;
        SELECT RESULT_KEY
        INTO my_result
        FROM FINAL TABLE (
            INSERT INTO LOAD_RESULTS (
                    ROWS_READ
                  , ROWS_SKIPPED
                  , ROWS_LOADED
                  , ROWS_REJECTED
                  , ROWS_DELETED
                  , ROWS_COMMITTED
                  , ROWS_PARTITIONED
                  , NUM_AGENTINFO_ENTRIES
                  , MSG_RETRIEVAL
                  , MSG_REMOVAL
               )
               VALUES (
                    ROWS_READ
                  , ROWS_SKIPPED
                  , ROWS_LOADED
                  , ROWS_REJECTED
                  , ROWS_DELETED
                  , ROWS_COMMITTED
                  , ROWS_PARTITIONED
                  , NUM_AGENTINFO_ENTRIES
                  , MSG_RETRIEVAL
                  , MSG_REMOVAL
               )
          );
        SET step = 7;
        CLOSE my_curs WITH RELEASE;
        SET step = 99;
    END body;
    RETURN step;
END dbload1@


0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question