[DB2-L] SQL Stored Procedures and the C compiler

Mary Ellen Archer

[DB2-L] SQL Stored Procedures and the C compiler








Does that mean we won't need to run the sp wlm regions anymore?

Mary Ellen



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Tuesday, January 08, 2008 8:56 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Stored Procedures and the C compiler




I don't think it was ever "included" but there is still good news ... I
saved a lot of ... oops, just kidding. The good news is that in DB2 9,
you won't need a C compiler at all AND these SQL SP's run in DBM1 rather
than a WLM address space.
Khalid







The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

M. Khalid Khan

Re: FW: [DB2-L] SQL Stored Procedures and the C compiler
(in response to Mary Ellen Archer)
You won't need wlm regions for SQL stored procedures with some
efficiencies due to avoidance of cross memory overheads. For stored
procedures written in any other language you will still need them.
Khalid




"ARCHER, MARY-ELLEN H" <[login to unmask email]>
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
01/08/2008 09:23 AM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] FW: [DB2-L] SQL Stored Procedures and the C compiler










Does that mean we won?t need to run the sp wlm regions anymore?
Mary Ellen

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Tuesday, January 08, 2008 8:56 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Stored Procedures and the C compiler


I don't think it was ever "included" but there is still good news ... I
saved a lot of ... oops, just kidding. The good news is that in DB2 9, you
won't need a C compiler at all AND these SQL SP's run in DBM1 rather than
a WLM address space.
Khalid




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services


**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Seibert

Re: FW: [DB2-L] SQL Stored Procedures and the C compiler
(in response to M. Khalid Khan)
Hi Mary Ellen,

I want to expand a little on Khalid's response.

As he said, you'll still need the WLM address spaces for Stored
Procedures written in other languages (Cobol, Rexx, Java, etc).

In DB2 version 9, the SQL procedures can be either External, which are
the old-style flavor requiring the C-compiler, and Native.
The new Native SQL procedured do not require the C compiler and run in
the DBM1 address space and don't require the WLM address spaces. If you
have External SQL procedures which you haven't recreated as the Native
flavor, you'll still need the WLM address spaces for them.

Dave

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of ARCHER, MARY-ELLEN H
Sent: Tuesday, January 08, 2008 10:23 AM
To: [login to unmask email]
Subject: [DB2-L] FW: [DB2-L] SQL Stored Procedures and the C compiler

Does that mean we won't need to run the sp wlm regions anymore?

Mary Ellen

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: SQL Stored Procedures and the C compiler
(in response to David Seibert)


But remember, it's possibly a very non-trivial task to turn a COBOL SPC
into an SQL one.... You basically have to write it again

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: 08 January 2008 13:56
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Stored Procedures and the C compiler



I don't think it was ever "included" but there is still good news ... I
saved a lot of ... oops, just kidding. The good news is that in DB2 9,
you won't need a C compiler at all AND these SQL SP's run in DBM1 rather
than a WLM address space.
Khalid





[login to unmask email]
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>

01/07/2008 04:06 PM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc
Subject
Re: [DB2-L] SQL Stored Procedures and the C compiler




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Tom Wies

SQL stored procedure
(in response to Phil Grainger)
Can the load module for an SQL stored procedure that is created from the
DSNHSQL proc, be called from another program (ie. Cobol) as though the
SQL stored procedure's load module is like any other callable subprogram
?



Regards,

Tom Wies


DISCLAIMER:
This communication, along with any attachments, is covered by federal and state law governing electronic communications and may contain confidential and legally privileged information. It is intended solely for the addressee. If you are reading this message, but you are not the intended recipient, you are hereby notified that you may not disclose, print, copy, store, forward or otherwise use or disseminate the information contained in this transmission. If you have received this message in error, immediately contact the sender and destroy all copies of the original. Thank you.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: SQL stored procedure
(in response to Tom Wies)
I am not sure. How can I found out? and why does it matter?

On Wed, 23 Jan 2008 12:55:51 -0600, Wies, Tom <[login to unmask email]>
wrote:

>Can the load module for an SQL stored procedure that is created from the
>DSNHSQL proc, be called from another program (ie. Cobol) as though the
>SQL stored procedure's load module is like any other callable subprogram
>?
>
>
>
>Regards,
>
>Tom Wies
>
>
>DISCLAIMER:
>This communication, along with any attachments, is covered by federal and
state law governing electronic communications and may contain confidential
and legally privileged information. It is intended solely for the addressee. If
you are reading this message, but you are not the intended recipient, you are
hereby notified that you may not disclose, print, copy, store, forward or
otherwise use or disseminate the information contained in this transmission. If
you have received this message in error, immediately contact the sender and
destroy all copies of the original. Thank you.
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: SQL stored procedure
(in response to SUBSCRIBE DB2-L Anonymous)
The short answer is a load module is a load module is ...

The problem is that SQL stored procedures need to run under RRS which is NOT
the default mode for most other DB2 applications. You can get lots of
interesting abends by mixing db2 access modes.



The longer answer is that DSNHSQL is optional in V9 and the entire module is
stored in DB2 without the benefit of any external load module see "internal
SQL stored procedure" in the application program guide.



NO, I have not attempted to test this.



Mike

HLS Technologies



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Wies, Tom
Sent: Wednesday, January 23, 2008 12:56 PM
To: [login to unmask email]
Subject: [DB2-L] SQL stored procedure



Can the load module for an SQL stored procedure that is created from the
DSNHSQL proc, be called from another program (ie. Cobol) as though the SQL
stored procedure's load module is like any other callable subprogram ?



Regards,

Tom Wies


DISCLAIMER:
This communication, along with any attachments, is covered by federal and
state law governing electronic communications and may contain confidential
and legally privileged information. It is intended solely for the addressee.
If you are reading this message, but you are not the intended recipient, you
are hereby notified that you may not disclose, print, copy, store, forward
or otherwise use or disseminate the information contained in this
transmission. If you have received this message in error, immediately
contact the sender and destroy all copies of the original. Thank you.


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Agus Kwee

Re: SQL stored procedure
(in response to Mike Bell)
Tom,

Following are the results of my test:

1. The calling program is a batch cobol DB2 program:
In theory this is required because the calling program is the one that must connect to
the DB2 subsystem and the stored procedure will be executing under the thread/connection
that was created by the calling program.
Under DB2 z/OS V8 my result was abend with a message: CAF "Open failed" using system
XXXX Plan 'DSNACLI'.
2. When I used a non DB2 batch cobol calling program the job step abended S806.

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com


----- Original Message -----
From: "Wies, Tom"
Date: Wednesday, January 23, 2008 2:02 pm
Subject: [DB2-L] SQL stored procedure
To: [login to unmask email]

> Can the load module for an SQL stored procedure that is created
> from the
> DSNHSQL proc, be called from another program (ie. Cobol) as
> though the
> SQL stored procedure's load module is like any other callable
> subprogram?
>
>
>
> Regards,
>
> Tom Wies
>
>
> DISCLAIMER:
> This communication, along with any attachments, is covered by
> federal and state law governing electronic communications and
> may contain confidential and legally privileged information. It
> is intended solely for the addressee. If you are reading this
> message, but you are not the intended recipient, you are hereby
> notified that you may not disclose, print, copy, store, forward
> or otherwise use or disseminate the information contained in
> this transmission. If you have received this message in error,
> immediately contact the sender and destroy all copies of the
> original. Thank you.
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG.
> DB2-L list archives, the FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the
> site, you can also access the IDUG Online Learning Center, Tech
> Library and Code Place, see the latest IDUG conference
> information, and much more. If you have not yet signed up for
> Basic Membership in IDUG, available at no cost, click on Member
> Services at http://www.idug.org/lsms
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms