Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Anyone use DB2 on OS/390

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-17-04, 14:53
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #2 (permalink)  
Old 11-17-04, 16:06
urquel urquel is offline
Registered User
  
Join Date: Aug 2004
Posts: 330
select <owner>.<procname>(parameters) from sysibm.sysdummy1
Reply With Quote
  #3 (permalink)  
Old 11-17-04, 16:51
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #4 (permalink)  
Old 11-17-04, 17:18
n_i n_i is offline
:-)
  
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
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...
Reply With Quote
  #5 (permalink)  
Old 11-17-04, 22:53
urquel urquel is offline
Registered User
  
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.
Reply With Quote
  #6 (permalink)  
Old 11-17-04, 23:08
urquel urquel is offline
Registered User
  
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?
Reply With Quote
  #7 (permalink)  
Old 11-18-04, 10:48
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #8 (permalink)  
Old 11-18-04, 11:11
sathyaram_s sathyaram_s is offline
Super Moderator
  
Join Date: Aug 2001
Location: UK
Posts: 4,534
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.
Reply With Quote
  #9 (permalink)  
Old 11-18-04, 11:24
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #10 (permalink)  
Old 11-18-04, 11:55
sathyaram_s sathyaram_s is offline
Super Moderator
  
Join Date: Aug 2001
Location: UK
Posts: 4,534
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.
Reply With Quote
  #11 (permalink)  
Old 11-18-04, 14:29
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #12 (permalink)  
Old 11-22-04, 09:14
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
  #13 (permalink)  
Old 12-02-04, 18:49
shawn.sullivan shawn.sullivan is offline
Registered User
  
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
Reply With Quote
  #14 (permalink)  
Old 12-03-04, 13:29
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,303
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.
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.