Solved

Using sub-Sproc to supply dataset to parent sProc?

Posted on 2008-10-09
6
424 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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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