f



ODBC doesn't support stored procedures? This can't be true

In Chip Irek's "A PRIMER ON USING DB2 WITH .NET" (www.15seconds.com),
he states that "...ODBC doesn't support DB2 stored procedures. So if
you are building an application heavily dependant on stored
procedures, you need to eliminate ODBC as your access method and
consider OleDb or the managed provider."

Mr Irek is an IBM Architect with Global Services and according to the
article, someone with .NET experience.

Now, I'd ask Mr Irek this question myself, but my email comes back
undeliverable.

Does he mean this for just for .NET applications? This can't mean for
all applications.

Any IBM colleague of Mr Irek able to clarify this?
0
benjamin
4/21/2004 6:08:44 PM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

12 Replies
574 Views

Similar Articles

[PageSpeed] 51

Looks to me like both ODBC and OLE-DB both don't support result sets for 
DB2 stored procs. Search http://www.microsoft.com on "ODBC DB2 Stored 
Procedures" and "OLE-DB DB2 Stored Procedures".

Larry Edelstein

Benjamin Doyle wrote:
> In Chip Irek's "A PRIMER ON USING DB2 WITH .NET" (www.15seconds.com),
> he states that "...ODBC doesn't support DB2 stored procedures. So if
> you are building an application heavily dependant on stored
> procedures, you need to eliminate ODBC as your access method and
> consider OleDb or the managed provider."
> 
> Mr Irek is an IBM Architect with Global Services and according to the
> article, someone with .NET experience.
> 
> Now, I'd ask Mr Irek this question myself, but my email comes back
> undeliverable.
> 
> Does he mean this for just for .NET applications? This can't mean for
> all applications.
> 
> Any IBM colleague of Mr Irek able to clarify this?

0
Larry
4/22/2004 2:56:33 AM
The MS information you reference only applies to using the MS Host
Integration Server drivers, which use a totally different connectivity
system, and these drivers are ludicrously crippled. DB2 Connect provides a
much better solution for accessing your mainframe DB2.

I'm not sure Benjamin was even asking about access mainframe data, and I'm
reasonaby certain he isn't using HIS, as he posted his question here.

"Larry" <Larry@nospam.net> wrote in message
news:BvGhc.89720$_g4.19680279@news4.srv.hcvlny.cv.net...
> Looks to me like both ODBC and OLE-DB both don't support result sets for
> DB2 stored procs. Search http://www.microsoft.com on "ODBC DB2 Stored
> Procedures" and "OLE-DB DB2 Stored Procedures".
>
> Larry Edelstein
>
> Benjamin Doyle wrote:
> > In Chip Irek's "A PRIMER ON USING DB2 WITH .NET" (www.15seconds.com),
> > he states that "...ODBC doesn't support DB2 stored procedures. So if
> > you are building an application heavily dependant on stored
> > procedures, you need to eliminate ODBC as your access method and
> > consider OleDb or the managed provider."
> >
> > Mr Irek is an IBM Architect with Global Services and according to the
> > article, someone with .NET experience.
> >
> > Now, I'd ask Mr Irek this question myself, but my email comes back
> > undeliverable.
> >
> > Does he mean this for just for .NET applications? This can't mean for
> > all applications.
> >
> > Any IBM colleague of Mr Irek able to clarify this?
>


0
Mark
4/22/2004 6:20:09 AM
Not sure how reliable your source is, but:
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/t0007399.htm

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
4/22/2004 1:24:45 PM
Hello Serge,

IIRC we went over a closely related topic (not ODBC but static SQL) in a
slightly different context a while back, when I indicated my wish for IBM to
implement the same functionality on DB2 UDB as had been available on DB2 for
MVS
(OS/390, z/OS) - in particular, for result sets to CALL statements. That is,
support for the ASSOCIATE LOCATORS statement in conjunction with the related
CALL statement enhancements
(http://publib.boulder.ibm.com/epubs/pdf/dsnsqj10.pdf).

I'd still like to see this support. Its omission makes porting code
unnecessarily "hard" (impossible might be more accurate).

"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:c68h3j$roc$1@hanover.torolab.ibm.com...
> Not sure how reliable your source is, but:
>
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/t0007399.htm
>
> -- 
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab



0
Mark
4/23/2004 8:35:57 AM
Must have been a while ago. Associate locator went into V7.1  GA
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0005645.htm

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
4/23/2004 5:21:21 PM
Thanks everyone for you input.

As it happens, I have programmers writing Java and .NET apps and
planning to connect to mainframe DB2 z/OS v7.1 through DB2 Connect /
DB2 UDB v8 Federated.

When one of them came across this article by Mr Irek, there were
concerns about how to proceed. It didn't seem to make sense that
there'd be this stored procedure limitation, but there it was from an
IBM .NET master.

Benjamin Doyle
0
benjamin
4/23/2004 6:38:06 PM
ASSOCIATE LOCATORS isn't in the UDB 8.1.4 documentation under DB2
Information Centre - Reference - SQL - SQL Statements (jumps from ALTER VIEW
to BEGIN DECLARE SECTION). Although it is listed in the section on SQL
Control Statements (your URL also points here), it is documented there as
only being permitted inside an SQL procedure (Note 1 under SQL control
statements - SQL Procedure). Also the documentation for ASSOCIATE LOCATORS
is missing the standard Invocation and Authorization sections that are
typical of normal SQL statements that I can embed in a host program. A quick
check with a test program verifies that the documentation is correct.

To clarify, I'm talking about the DB2/MVS ASSOCIATE LOCATORS statement
(introduced in DB2 V5, wasn't it?) that allows an application with an
embedded CALL statement to process the result sets returned by that CALL.
This functionality is missing in UDB embedded SQL, although it's available
in the CLI API. The UDB ASSOCIATE LOCATORS is semantically something
different, it merely has the same spelling.

"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:c6bjb1$b2v$1@hanover.torolab.ibm.com...
> Must have been a while ago. Associate locator went into V7.1  GA
>
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0005645.htm
>
> Cheers
> Serge
>
> -- 
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab


0
Mark
4/25/2004 8:10:02 AM
ASSOCIATE LOCATOR in DB2 for Multiplatforms is indeed not a regular SQL 
Statement (similar to SET).
I don't think it is semantically different though. Can you clarify?

I'm trying to run down whether the CLI-API is the only interface.
My asumption was that associate locator is parsed by PREP in e.g. SQL 
embedded in C.

Cheers
Serge
-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
4/25/2004 12:50:40 PM
On DB2 for (MVS, OS/390, z/OS), I can process the result set of a CALL in a
(PL/I, COBOL, C) program.

On DB2 UDB I cannot do this, as ASSOCIATE LOCATOR is only permitted in an
SQL Procedure, and cannot be embedded in a host program. That is, your
assumption is incorrect.


"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:c6gc73$qhp$1@hanover.torolab.ibm.com...
> ASSOCIATE LOCATOR in DB2 for Multiplatforms is indeed not a regular SQL
> Statement (similar to SET).
> I don't think it is semantically different though. Can you clarify?
>
> I'm trying to run down whether the CLI-API is the only interface.
> My asumption was that associate locator is parsed by PREP in e.g. SQL
> embedded in C.
>
> Cheers
> Serge
> -- 
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab


0
Mark
4/27/2004 6:53:22 AM
Indeed it is. :-(

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
4/27/2004 11:45:45 AM
Now that we agree that DB2 UDB canot process stored procedure result sets in
embedded SQL, in contrast to host DB2, which has been able to do so for a
long time, I obviously need to ask:

Are ther any plans to enhance DB2 UDB to give it host compatibility for
processing results sets from stored procedures via embedded SQL?

"Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message
news:c6lh5d$fv8$1@hanover.torolab.ibm.com...
> Indeed it is. :-(
>
> -- 
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab


0
Mark
4/28/2004 5:36:28 AM
Not to my knowledge.
IBM claims to be customer driven.
Feel free to put that claim to the test with a DCR.

Cheers
Serge
-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
4/28/2004 1:21:07 PM
Reply: