Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2011
    Posts
    30

    Unanswered: Stoared Procedure

    I have one main procedure in that there are two(A and B) subprocedure,

    i have to get output from A and store in the output table .
    Then do the union with B
    show the output ,.

    I realy try hard but not get code.

    can you please provide the proper code ..


    thanks in advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so why don't you publish the already existing code
    this will be easier to understand
    also as always : db2 version/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
    Dec 2011
    Posts
    30
    thnakd you very much


    db2 9.5 AIX 6.4


    ---------Procedure C-----------------------

    DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1( name int,no int,address int)



    FOR loop as

    select * from emp;

    do

    insert into TEMPTAB1 values(call B(x,y,z);

    end for;

    union

    call C


    steps
    1) call procedure C
    2) in the for loop, for each row from select query, i have call procedure B and insert the data into temp table
    3) Do the union for rows in TEMP table and the output from the procedure C
    4)show the output

    above is the rouph plan.
    please help as i do not understand how to put that in SP

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if you have never user this type of program, have a look in sample directory of db2 - many are available
    also the doc can help you
    DECLARE CURSOR
    try to understand what todo, instead of copy/past proposed code....
    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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Does your procedure B return just a single row (x,y,z) of values, or multiple rows?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Dec 2011
    Posts
    30
    Thanks for your quick reply....

    The procedure B get more than one row when it gets call.

    when select Query gets the 10 rows then pro B should be called 10 times.

  7. #7
    Join Date
    Jun 2012
    Posts
    1
    Just put the insert stat in proc A, and call the proc A in proc C and the union the temp table with output of proc C.
    Its a best solution. Nothing will work better than this.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by nanaavinash View Post
    The procedure B get more than one row when it gets call.
    when select Query gets the 10 rows then pro B should be called 10 times
    From this I understand that at each call, procedure B returns one row (x,y,z) through its three "OUT" parameters. It will most likely also need some "IN" parameter(s), most likely the key column(s) of table "emp"?
    (I've assumed that column is called "key", is an INT, and that it's passed into procedure B as the first parameter.)

    As others have already mentioned, this is probably not the most performant setup.
    But nevertheless, if you want to do it that way, you need something in the following style:

    Code:
    DECLARE SQLCODE INT;
    DECLARE x INT;
    DECLARE y INT;
    DECLARE z INT;
    DECLARE k INT;
    DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1( name int,no int,address int);
    DECLARE c CURSOR FOR SELECT key FROM emp;
    OPEN c;
    FETCH c INTO k;
    WHILE SQLCODE = 0 DO
      CALL B(k,x,y,z);
      INSERT INTO TEMPTAB1 values(x,y,z);
      FETCH c INTO k;
    END WHILE;
    
    <Here, you still need to do something with the rows of TEMPTAB1 ...>
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Dec 2011
    Posts
    30
    Thank you very much

    when i am trying to pass IN parameter(ID) to procedure B..
    it gives me error

    SQL0206N "ID" is not valid in the context where it is used. LINE NUMBER=19. SQLSTATE=42703

    which is also the IN parameter parent procedure

    please tell me the syntax

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by nanaavinash View Post
    please tell me the syntax
    The syntax can be found in the SQL Reference Guide for your version of DB2;
    it is in Chapter "Statements", under "CREATE PROCEDURE".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •