Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Anyone use DB2 on OS/390

    Abd if you do, how do you execute a sproc in QMF?

    Or Spufi, or anywhere for that matter?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    select <owner>.<procname>(parameters) from sysibm.sysdummy1

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks...but I get

    Code:
    --DROP PROCEDURE BXBB72DA.QUERY_TAB RESTRICT;              
                                                               
      CREATE PROCEDURE BXBB72DA.QUERY_TAB                      
      (IN TABNAME  CHAR(18))                                   
      LANGUAGE SQL                                             
        BEGIN                                                  
          DECLARE V_NAME CHAR(18);                             
          SELECT * FROM SYSIBM.SYSTABLES WHERE NAME = TABNAME; 
        END                                                    
                                                               
    SELECT BXBB72DA.QUERY_TAB('P%') FROM SYSIBM.SYSDUMMY1;     
    
    QUERY MESSAGES:                           
    FUNCTION BXBB72DA.QUERY_TAB was not found.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not sure about os/390 and spufi, but have you tried "CALL BXBB72DA.QUERY_TAB('P%')" ?

    I don't think you'll be able to return the results though...

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    Are you using Stored Procedure Builder to build the SQL stored procedure? You can also test the stored procedure from Stored Procedure Builder. It seems that the definition of a SQL stored procedure you have is not complete. Stored procedure builder will compile it and create an associated package in DB2. Also, the stored procedure does not return a value. You can use the CALL command to execute the stored procedure from a trigger. I am not sure how the CALL will work in SPUFI or QMF.

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    I just ran a test query. The stored procedure will not execute using SQL. (Sorry, I was getting it confused with calling a UDF) Speaking of UDF's. You could write a simple program that uses the CALL command to call the stored procedure and create a UDF to execute the program. THEN, you can use the UDF in an sql statement to call the stored procedure. Gee, isn't that simple?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Guys...DB2 is my roots...then it was on to SQL Server 6.5..then to Oracle 8i...back to SQL 2000...various other desktop databases...

    And this is just f'n painful....

    Now there are SQL stored procedures and there are external stored procedures...

    Does everyone just build external store procs?

    And what good is an external stored proc anyway?

    AND WHY doesn't DB2 have a extened SQL language like T-sql or pl-sql?

    Hell they could use rexx...

    and btw, yes I did try the call...

    Code:
    CALL QUERY_TAB('PERSON'); 
    
    
    QUERY MESSAGES:                                        
    The statement is not valid in this database manager.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Brett,
    Not sure what version you are on

    On DB2 V8, the manuals clearly describe how to create an SQL Stored procedure on the host (without using Dev Centre).

    IIRC, the following are the steps :

    1) Convert the SQL PL code to C
    2) Precompile, compile, bind and linkedit the converted code
    3) Define the procedure to db2 from spufi

    For steps 1 and 2, there is a sample JCL in the SDSNSAMP dataset.

    I had managed to create a few procedures this way, until I had Dev Centre installed on my machine.

    Sorry, if I have not been of much help
    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm stuck in 7.2...

    And the sproc I posted compiles fine in QMF and I can see the sproc in the catalog...

    I just can't execute it.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Unfortunately, create procedure command does not do the conversion to c, precomile etc for you Those steps have to be done manually CREATE PROCEDURE in SPUFI/QMF registers the procedure for you

    AFAIK, CALL is not yet supported from the host itself You may have to use a DB2 Connect client to use the call statement

    REXX can be used to call the SP on the host
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks...I just don't get it...

    What is CALL Then?

    Why does DB2 treats sprocs differently than every other platform...

    I mean, Functions can be created in QMF right?

    And then just called like in posted example. Yes?

    Gotta test it.

    So why not sprocs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, did a call from JAVA...said it couldn't load the sproc to the appropriate load lib..

    Sounds like a configuration issue on the DB2 side...

    Alos got a demo of some external stored procs...

    The data is returned by an OPEN CURSOR with no Fetches? What the hell is going on?

    At least in Oracle you return a cursor pointer...

    In SQL Server you actually specify a sql result set...whisch would cause DB2 all kinds of grief...

    Come on IBM...lets get with it....

    Makes Oracle look like a walk in the park...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2004
    Posts
    1

    QMF and DB2 Stored Procedures

    Hi I am new to this forum.
    I believe QMF v7.2 does not support calling DB2 Stored Procedures period.
    However QMF or Windows v7.2 does.
    The SQL statement CREATE PROCEDURE XYZ.... should work from any client since it is SQL that is executed at the Database server (provided there is a suitable compiler if any compiling is called for by the language of the sproc)

    The SQL statement CALL XYX(param1,param2...) on the other hand requires the client app potentially to handle multiple result sets, and so CALL will not be supported by every client app that can talk to databases

    I believe that QMF v8 introduced support for calling DB2 Stored Procedures with the CALL syntax. It also introduced in the QMF HPO component a DB2 Cobol Stored Procedure builder.

    I say "I believe" because I have not taken the time to find the annoncement letter but http://publib.boulder.ibm.com/infoce...sqk2mst315.htm
    has a section describing QMF v8 and DB2 Stored Procedures

    I hope this helps

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope...saw a demo of a 7.2 EXTERNAL stored proc written in COBOL

    So I said what's the point?

    Flipped me Out when the CURSOR was defined fro RETURN...had never seen that option before...

    So you do have to close the cursor, right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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