Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    Unanswered: Display results from db2 store procedures

    Hello,

    I'm totally new to db2 and trying to create a store procedure that pulls data into a session table, does some data manipulations and then finally outputs the result. Is there a way to do this without using cursor as cursor only displays 4 rows at a time, hence being too slow for 100 rows output?

    Thank you so much for your help!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how do you output the result ?
    you could publish the code for the sp and/or db2level + 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
    Aug 2003
    Posts
    9
    It would just be a simple select from the session table. Below is an example but my sproc is more complex with lots of manipulations before display the final output. So is there a way NOT having to use the cursor at the end of the sproc to display the data?

    Thanks for your help!


    CREATE OR REPLACE PROCEDURE PSGSCRATCH.SP_Example
    (
    In Num Integer default 10
    )
    P1: Begin

    DECLARE GLOBAL TEMPORARY TABLE SESSION.Prod
    (
    superregionname varchar(200),NumIn Integer,Result Integer
    )
    in ts_usertemp00
    with replace
    on commit preserve rows
    not logged;

    Insert Into SESSION.Prod
    select 'AMER',Num,Num*5 from sysibm.sysdummy1;

    Begin
    DECLARE cursor1 CURSOR WITH RETURN for
    Select * from SESSION.Prod;
    OPEN cursor1;

    End;

    END P1;

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if there is only 1 row, you could return the values in out variables
    select x,y into var1,var2.... and return var1,var2
    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
    Aug 2003
    Posts
    9
    Hi przytula_guy... This is just an example, but of course the result set is not just one row. It is a couple thousands of rows. Currently as you can see I use cursor to output the result and it is too slow...

    Any idea of how to output the result without using cursor in the example sproc?

    Thanks!

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you are using cursor with return
    if the result is really needed, why not handling this in the application instead of in the sp ?
    is the sp really needed for this .. or just for easiness...
    this is a design error
    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

  7. #7
    Join Date
    Aug 2003
    Posts
    9
    Hi przytula_guy,

    Well... so cursor has to be used in order to output the data from a db2 sproc, correct? There is absolute no other way, right?

    And again, please ignore the structure of the sproc as this is just an example that I would like to return the data at the end of the sproc. So the question is still is there other way to return/output the data by NOT having to use cursor.

    Thanks!

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    NO not with sproc
    why is the cursor a problem ? PDPSI is still good for you (problem determination problem source investigation)
    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

Posting Permissions

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