Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using sub-Sproc to supply dataset to parent sProc?

Posted on 2008-10-09
6
Medium Priority
?
438 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 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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