Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using sub-Sproc to supply dataset to parent sProc?

Posted on 2008-10-09
6
Medium Priority
?
442 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 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

927 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