DB2 Connect and Large result set problem

David Kenski

DB2 Connect and Large result set problem
Hello List!

I am assisting and application area with a problem on an SQL stored
procedure (define cursor with return, Select multiple columns from 1 table,
open cursor) that returns a large volume of data from and OS/390 DB2
database. When the SP is executed by SPB, it appears to execute
quickly(return output variable is set within seconds), but the result set
takes 5-7 minutes to return. When I observe with our DB2 monitor, I see
FETCHes occurring in increments of 154 every 5-7 secs to SPB. The result set
contains 5551 rows of 221 characters (1.23+ MB). I've taken the query from
the SP and executed it using DB2 Command Center and it executes in 1 min 35
secs. It also fetches the rows in increments of 154, but requests additional
fetches every 1-2 seconds (opposed to the 5-7 seconds from the SP solution).
The application developers feel that the SP time of 5-7 minutes is
unacceptable. What could be causing CC to work 3-4 times faster than the SP
under SPB? Both are using the same DB2 Connect V7.1 Gateway on an HP box. Is
what I am experiencing a fact of life with DB2 Connect, SPB, CC? Are there
any published tests or recommendations that identify the amount of data DB2
Connect can be expected to return over a given period of time? When IBM
states performance improvements were made to DB2 Connect for large result
sets, What do they consider a large result set to be? Has anyone experienced
a similar experiences with SP's and DB2 Connect? Any suggestions to improve
performance of returning the result set? Is anyone out there returning large
result sets via DB2 Connect and care to share data volumes and response
times? Any help or suggestions would be most appreciated.

Thanks,

Dave Kenski
Marriott International Information Resources
Technical Services Database Engineering
[login to unmask email]



Myron Miller

Re: DB2 Connect and Large result set problem
(in response to David Kenski)
We had this problem as well. What version of DB2 Connect are you
using? FP3 helps a lot on this issue. 7.1 without any fixpacks performs
really bad on SPB.

Also, you need to ensure that all your TCPIP settings are set right.
There's an INFO APAR that talks about TCPIP performance and I think
there's one about DB2 Connect performance.
--- "Kenski, David" <[login to unmask email]> wrote:
> Hello List!
>
> I am assisting and application area with a problem on an SQL stored
> procedure (define cursor with return, Select multiple columns from 1
> table,
> open cursor) that returns a large volume of data from and OS/390 DB2
> database. When the SP is executed by SPB, it appears to execute
> quickly(return output variable is set within seconds), but the result
> set
> takes 5-7 minutes to return. When I observe with our DB2 monitor, I
> see
> FETCHes occurring in increments of 154 every 5-7 secs to SPB. The
> result set
> contains 5551 rows of 221 characters (1.23+ MB). I've taken the query
> from
> the SP and executed it using DB2 Command Center and it executes in 1
> min 35
> secs. It also fetches the rows in increments of 154, but requests
> additional
> fetches every 1-2 seconds (opposed to the 5-7 seconds from the SP
> solution).
> The application developers feel that the SP time of 5-7 minutes is
> unacceptable. What could be causing CC to work 3-4 times faster than
> the SP
> under SPB? Both are using the same DB2 Connect V7.1 Gateway on an HP
> box. Is
> what I am experiencing a fact of life with DB2 Connect, SPB, CC? Are
> there
> any published tests or recommendations that identify the amount of
> data DB2
> Connect can be expected to return over a given period of time? When
> IBM
> states performance improvements were made to DB2 Connect for large
> result
> sets, What do they consider a large result set to be? Has anyone
> experienced
> a similar experiences with SP's and DB2 Connect? Any suggestions to
> improve
> performance of returning the result set? Is anyone out there
> returning large
> result sets via DB2 Connect and care to share data volumes and
> response
> times? Any help or suggestions would be most appreciated.
>
> Thanks,
>
> Dave Kenski
> Marriott International Information Resources
> Technical Services Database Engineering
> [login to unmask email]
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com



David Kenski

Re: DB2 Connect and Large result set problem
(in response to Myron Miller)
Myron,
I take it by you response that you are not having this issue now? Was it one
particular piece that was causing the problem or a combination of issues?
What made the biggest impact in getting this to work for you? Our gateway is
at 7.1 FP3. Do you have the info APAR numbers? How much data are you
returning and what kind of response time are you observing?

Thanks,

Dave

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, January 03, 2002 3:50 PM
To: [login to unmask email]
Subject: Re: DB2 Connect and Large result set problem


We had this problem as well. What version of DB2 Connect are you
using? FP3 helps a lot on this issue. 7.1 without any fixpacks performs
really bad on SPB.

Also, you need to ensure that all your TCPIP settings are set right.
There's an INFO APAR that talks about TCPIP performance and I think
there's one about DB2 Connect performance.
--- "Kenski, David" <[login to unmask email]> wrote:
> Hello List!
>
> I am assisting and application area with a problem on an SQL stored
> procedure (define cursor with return, Select multiple columns from 1
> table,
> open cursor) that returns a large volume of data from and OS/390 DB2
> database. When the SP is executed by SPB, it appears to execute
> quickly(return output variable is set within seconds), but the result
> set
> takes 5-7 minutes to return. When I observe with our DB2 monitor, I
> see
> FETCHes occurring in increments of 154 every 5-7 secs to SPB. The
> result set
> contains 5551 rows of 221 characters (1.23+ MB). I've taken the query
> from
> the SP and executed it using DB2 Command Center and it executes in 1
> min 35
> secs. It also fetches the rows in increments of 154, but requests
> additional
> fetches every 1-2 seconds (opposed to the 5-7 seconds from the SP
> solution).
> The application developers feel that the SP time of 5-7 minutes is
> unacceptable. What could be causing CC to work 3-4 times faster than
> the SP
> under SPB? Both are using the same DB2 Connect V7.1 Gateway on an HP
> box. Is
> what I am experiencing a fact of life with DB2 Connect, SPB, CC? Are
> there
> any published tests or recommendations that identify the amount of
> data DB2
> Connect can be expected to return over a given period of time? When
> IBM
> states performance improvements were made to DB2 Connect for large
> result
> sets, What do they consider a large result set to be? Has anyone
> experienced
> a similar experiences with SP's and DB2 Connect? Any suggestions to
> improve
> performance of returning the result set? Is anyone out there
> returning large
> result sets via DB2 Connect and care to share data volumes and
> response
> times? Any help or suggestions would be most appreciated.
>
> Thanks,
>
> Dave Kenski
> Marriott International Information Resources
> Technical Services Database Engineering
> [login to unmask email]
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com








David Kenski

Re: DB2 Connect and Large result set problem
(in response to David Kenski)
Myron,
Could you also elaborate on which TCP/IP settings you are referring to on
Mainframe / Gateway / LAN / PC?
Thanks,
Dave

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, January 03, 2002 3:50 PM
To: [login to unmask email]
Subject: Re: DB2 Connect and Large result set problem


We had this problem as well. What version of DB2 Connect are you
using? FP3 helps a lot on this issue. 7.1 without any fixpacks performs
really bad on SPB.

Also, you need to ensure that all your TCPIP settings are set right.
There's an INFO APAR that talks about TCPIP performance and I think
there's one about DB2 Connect performance.
--- "Kenski, David" <[login to unmask email]> wrote:
> Hello List!
>
> I am assisting and application area with a problem on an SQL stored
> procedure (define cursor with return, Select multiple columns from 1
> table,
> open cursor) that returns a large volume of data from and OS/390 DB2
> database. When the SP is executed by SPB, it appears to execute
> quickly(return output variable is set within seconds), but the result
> set
> takes 5-7 minutes to return. When I observe with our DB2 monitor, I
> see
> FETCHes occurring in increments of 154 every 5-7 secs to SPB. The
> result set
> contains 5551 rows of 221 characters (1.23+ MB). I've taken the query
> from
> the SP and executed it using DB2 Command Center and it executes in 1
> min 35
> secs. It also fetches the rows in increments of 154, but requests
> additional
> fetches every 1-2 seconds (opposed to the 5-7 seconds from the SP
> solution).
> The application developers feel that the SP time of 5-7 minutes is
> unacceptable. What could be causing CC to work 3-4 times faster than
> the SP
> under SPB? Both are using the same DB2 Connect V7.1 Gateway on an HP
> box. Is
> what I am experiencing a fact of life with DB2 Connect, SPB, CC? Are
> there
> any published tests or recommendations that identify the amount of
> data DB2
> Connect can be expected to return over a given period of time? When
> IBM
> states performance improvements were made to DB2 Connect for large
> result
> sets, What do they consider a large result set to be? Has anyone
> experienced
> a similar experiences with SP's and DB2 Connect? Any suggestions to
> improve
> performance of returning the result set? Is anyone out there
> returning large
> result sets via DB2 Connect and care to share data volumes and
> response
> times? Any help or suggestions would be most appreciated.
>
> Thanks,
>
> Dave Kenski
> Marriott International Information Resources
> Technical Services Database Engineering
> [login to unmask email]
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com








Myron Miller

Re: DB2 Connect and Large result set problem
(in response to David Kenski)
David,

We don't use gateways. We run either standalone PC or standalone web
server. For Stored Procedure Builder, they are always standalone PCs
(ie PE version of DB2 Connect) with large amounts of memory - at least
256m-512m per machine on 500-700mz PIIIs.

We normally send back a few 100k but had some tests where we were
sending back meg, plus we ran into serious performance issues (up to
minutes per result set) when we sent back large rows - up to 32k per
row and several of them. By doing a lot of the following, we've cut
this to seconds. I've still found that Command Center is faster than
SPB, but its not as dramatic a difference now as it once was.

See II11710 for TCPIP tuning. Plus we put on a number of DB2 DDF fixes
and I don't have the numbers for them. Basically almost all from 0105
thru 0110 relating to DDF.

I also changed RQRIOBLK from 32767 to 65535.

In addition, see the presentation of Hugh Smith at the 2000 DB2 and
Data Warehouse Conference on Db2 Network Computing Tuning Tips and
Recommendations - Things we've learned. For example, he suggests that
one reviews the Microsoft Tech. Support Ariticles q120642 to change
TCPIP Send/Receive sizes from 8k to 64k.


--- "Kenski, David" <[login to unmask email]> wrote:
> Myron,
> Could you also elaborate on which TCP/IP settings you are referring
> to on
> Mainframe / Gateway / LAN / PC?
> Thanks,
> Dave
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, January 03, 2002 3:50 PM
> To: [login to unmask email]
> Subject: Re: DB2 Connect and Large result set problem
>
>
> We had this problem as well. What version of DB2 Connect are you
> using? FP3 helps a lot on this issue. 7.1 without any fixpacks
> performs
> really bad on SPB.
>
> Also, you need to ensure that all your TCPIP settings are set right.
> There's an INFO APAR that talks about TCPIP performance and I think
> there's one about DB2 Connect performance.
> --- "Kenski, David" <[login to unmask email]> wrote:
> > Hello List!
> >
> > I am assisting and application area with a problem on an SQL stored
> > procedure (define cursor with return, Select multiple columns from
> 1
> > table,
> > open cursor) that returns a large volume of data from and OS/390
> DB2
> > database. When the SP is executed by SPB, it appears to execute
> > quickly(return output variable is set within seconds), but the
> result
> > set
> > takes 5-7 minutes to return. When I observe with our DB2 monitor, I
> > see
> > FETCHes occurring in increments of 154 every 5-7 secs to SPB. The
> > result set
> > contains 5551 rows of 221 characters (1.23+ MB). I've taken the
> query
> > from
> > the SP and executed it using DB2 Command Center and it executes in
> 1
> > min 35
> > secs. It also fetches the rows in increments of 154, but requests
> > additional
> > fetches every 1-2 seconds (opposed to the 5-7 seconds from the SP
> > solution).
> > The application developers feel that the SP time of 5-7 minutes is
> > unacceptable. What could be causing CC to work 3-4 times faster
> than
> > the SP
> > under SPB? Both are using the same DB2 Connect V7.1 Gateway on an
> HP
> > box. Is
> > what I am experiencing a fact of life with DB2 Connect, SPB, CC?
> Are
> > there
> > any published tests or recommendations that identify the amount of
> > data DB2
> > Connect can be expected to return over a given period of time? When
> > IBM
> > states performance improvements were made to DB2 Connect for large
> > result
> > sets, What do they consider a large result set to be? Has anyone
> > experienced
> > a similar experiences with SP's and DB2 Connect? Any suggestions to
> > improve
> > performance of returning the result set? Is anyone out there
> > returning large
> > result sets via DB2 Connect and care to share data volumes and
> > response
> > times? Any help or suggestions would be most appreciated.
> >
> > Thanks,
> >
> > Dave Kenski
> > Marriott International Information Resources
> > Technical Services Database Engineering
> > [login to unmask email]
> >
> >
> >
> > visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> > the list can
>
>
> __________________________________________________
> Do You Yahoo!?
> Send your FREE holiday greetings online!
> http://greetings.yahoo.com
>
>
>
> visit the
> DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can be
>
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com



Hans-Peter Klein

db2 connect
(in response to Myron Miller)
hi all,

we have db2 version 5 on mvs mainfraime os390 and now installed db2 connect
on windows nt workstation. it works fine.

now what we want to try is to get access from excel or access to the db2
database on the mainfraime.

does someone know what we have to do for to get the connection - how to get
a host database in access???

Mit freundlichen Grüßen / Kind Regards

hans-peter klein
------------------------------------------------------------------------
-------------------------------------------------------
> EDS Electronic Data Systems
> Informationstechnologie und Service GmbH
>
>
CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
67435 Neustadt/Weinstraße
[login to unmask email]
------------------------------------------------------------------------
--------------------------------------------------------



Steve Whittaker

Re: db2 connect
(in response to Hans-Peter Klein)
You'll need to create an entry in your 'client configuration assistant'
that will point to the
DB2 subsystem that you want to use. Go through the panels in this
configuration. If this
is the first time your using DB2 Connect you will have to do the 'binds' in
order to get the
packages out there in the DB2 catalog. Once this is done you can then go out
to use
MS Access and then link to the configuration name you created and this will
prompt you
to sign on. Once this is sucessful your in.
I'm assuming you got through all the firewalls when you installed DB2
Connect?

Good luck..

--Steve....

Steve Whittaker
DB2/DBA Progress Energy, Inc.
Mail stop:PEB 14 - Raleigh
Email: [login to unmask email]
Phone: (919) 546-7267


-----Original Message-----
From: Klein, Hans-Peter [mailto:[login to unmask email]
Sent: Wednesday, January 09, 2002 4:59 AM
To: [login to unmask email]
Subject: db2 connect


hi all,

we have db2 version 5 on mvs mainfraime os390 and now installed db2 connect
on windows nt workstation. it works fine.

now what we want to try is to get access from excel or access to the db2
database on the mainfraime.

does someone know what we have to do for to get the connection - how to get
a host database in access???

Mit freundlichen Grüßen / Kind Regards

hans-peter klein
------------------------------------------------------------------------
-------------------------------------------------------
> EDS Electronic Data Systems
> Informationstechnologie und Service GmbH
>
>
CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
67435 Neustadt/Weinstraße
[login to unmask email]
------------------------------------------------------------------------
--------------------------------------------------------



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Myron Miller

Re: db2 connect
(in response to Steve Whittaker)
For Excell, its easy, just go into GET EXTERNAL DATA and when the
screen for external data sources comes up, just pick your mainframe
connection that you've already setup as an ODBC connection in DB2
connect. Then just issue the SQL or whatever.

For Access, you must link the tables, again by picking your external
data source which you should have already defined as a ODBC connection.
--- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> hi all,
>
> we have db2 version 5 on mvs mainfraime os390 and now installed db2
> connect
> on windows nt workstation. it works fine.
>
> now what we want to try is to get access from excel or access to the
> db2
> database on the mainfraime.
>
> does someone know what we have to do for to get the connection - how
> to get
> a host database in access???
>
> Mit freundlichen Grüßen / Kind Regards
>
> hans-peter klein
>
------------------------------------------------------------------------
> -------------------------------------------------------
> > EDS Electronic Data Systems
> > Informationstechnologie und Service GmbH
> >
> >
> CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> 67435 Neustadt/Weinstraße
> [login to unmask email]
>
------------------------------------------------------------------------
> --------------------------------------------------------
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/



Steve Tennant

Re: db2 connect
(in response to Myron Miller)
Hi Myron,

Just for the fun of it I thought I would try this. I get an error from
Excel saying "SQL0104N Unexpected token RTRIM found following....", does
this mean I can't use this with os390 DB2v5 or is there an Excel setting.

Regards,

Steve T

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, 10 January 2002 6:44 AM
To: [login to unmask email]
Subject: Re: db2 connect


For Excell, its easy, just go into GET EXTERNAL DATA and when the
screen for external data sources comes up, just pick your mainframe
connection that you've already setup as an ODBC connection in DB2
connect. Then just issue the SQL or whatever.

For Access, you must link the tables, again by picking your external
data source which you should have already defined as a ODBC connection.
--- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> hi all,
>
> we have db2 version 5 on mvs mainfraime os390 and now installed db2
> connect
> on windows nt workstation. it works fine.
>
> now what we want to try is to get access from excel or access to the
> db2
> database on the mainfraime.
>
> does someone know what we have to do for to get the connection - how
> to get
> a host database in access???
>
> Mit freundlichen Grüßen / Kind Regards
>
> hans-peter klein
>
------------------------------------------------------------------------
> -------------------------------------------------------
> > EDS Electronic Data Systems
> > Informationstechnologie und Service GmbH
> >
> >
> CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> 67435 Neustadt/Weinstraße
> [login to unmask email]
>
------------------------------------------------------------------------
> --------------------------------------------------------
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/


visit the





Steve Tennant

Re: db2 connect
(in response to Steve Tennant)
Looks like I fired off a question too quickly without RTFM %-( I guess I
need other appropriate ODBC drivers to get DB2 stuff into Excel, can anyone
say where I would find these? Is it somewhere on the IBM site?

TIA

Steve T

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, 10 January 2002 6:44 AM
To: [login to unmask email]
Subject: Re: db2 connect


For Excell, its easy, just go into GET EXTERNAL DATA and when the
screen for external data sources comes up, just pick your mainframe
connection that you've already setup as an ODBC connection in DB2
connect. Then just issue the SQL or whatever.

For Access, you must link the tables, again by picking your external
data source which you should have already defined as a ODBC connection.
--- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> hi all,
>
> we have db2 version 5 on mvs mainfraime os390 and now installed db2
> connect
> on windows nt workstation. it works fine.
>
> now what we want to try is to get access from excel or access to the
> db2
> database on the mainfraime.
>
> does someone know what we have to do for to get the connection - how
> to get
> a host database in access???
>
> Mit freundlichen Grüßen / Kind Regards
>
> hans-peter klein
>
------------------------------------------------------------------------
> -------------------------------------------------------
> > EDS Electronic Data Systems
> > Informationstechnologie und Service GmbH
> >
> >
> CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> 67435 Neustadt/Weinstraße
> [login to unmask email]
>
------------------------------------------------------------------------
> --------------------------------------------------------
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/


visit the





Leo Conchello

Re: db2 connect
(in response to Steve Tennant)
Steve,

Go to the Client Configuration Assistant highlight the database you need and select "Properties", in the next screen check "Register this Database for ODBC" and so on.

Regards,

Leo

Steve Tennant wrote:

> Looks like I fired off a question too quickly without RTFM %-( I guess I
> need other appropriate ODBC drivers to get DB2 stuff into Excel, can anyone
> say where I would find these? Is it somewhere on the IBM site?
>
> TIA
>
> Steve T
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, 10 January 2002 6:44 AM
> To: [login to unmask email]
> Subject: Re: db2 connect
>
> For Excell, its easy, just go into GET EXTERNAL DATA and when the
> screen for external data sources comes up, just pick your mainframe
> connection that you've already setup as an ODBC connection in DB2
> connect. Then just issue the SQL or whatever.
>
> For Access, you must link the tables, again by picking your external
> data source which you should have already defined as a ODBC connection.
> --- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> > hi all,
> >
> > we have db2 version 5 on mvs mainfraime os390 and now installed db2
> > connect
> > on windows nt workstation. it works fine.
> >
> > now what we want to try is to get access from excel or access to the
> > db2
> > database on the mainfraime.
> >
> > does someone know what we have to do for to get the connection - how
> > to get
> > a host database in access???
> >
> > Mit freundlichen Grüßen / Kind Regards
> >
> > hans-peter klein
> >
> ------------------------------------------------------------------------
> > -------------------------------------------------------
> > > EDS Electronic Data Systems
> > > Informationstechnologie und Service GmbH
> > >
> > >
> > CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> > Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> > 67435 Neustadt/Weinstraße
> > [login to unmask email]
> >
> ------------------------------------------------------------------------
> > --------------------------------------------------------
> >
> > =======================To change your
> > subscription options or to cancel your subscription visit the DB2-L
> > webpage at http://www.ryci.com/db2-l. The owners of the list can be
> >
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
>
>
>
>
>
>
> ======================
>



Myron Miller

Re: db2 connect
(in response to Leo Conchello)
You don't need other ODBC drivers. You have DB2 Connect. It has all
the drivers needed once you've setup an ODBC connection from your NT PC
to the OS/390 mainframe.
--- Steve Tennant <[login to unmask email]> wrote:
> Looks like I fired off a question too quickly without RTFM %-( I
> guess I
> need other appropriate ODBC drivers to get DB2 stuff into Excel, can
> anyone
> say where I would find these? Is it somewhere on the IBM site?
>
> TIA
>
> Steve T
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, 10 January 2002 6:44 AM
> To: [login to unmask email]
> Subject: Re: db2 connect
>
>
> For Excell, its easy, just go into GET EXTERNAL DATA and when the
> screen for external data sources comes up, just pick your mainframe
> connection that you've already setup as an ODBC connection in DB2
> connect. Then just issue the SQL or whatever.
>
> For Access, you must link the tables, again by picking your external
> data source which you should have already defined as a ODBC
> connection.
> --- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> > hi all,
> >
> > we have db2 version 5 on mvs mainfraime os390 and now installed db2
> > connect
> > on windows nt workstation. it works fine.
> >
> > now what we want to try is to get access from excel or access to
> the
> > db2
> > database on the mainfraime.
> >
> > does someone know what we have to do for to get the connection -
> how
> > to get
> > a host database in access???
> >
> > Mit freundlichen Grüßen / Kind Regards
> >
> > hans-peter klein
> >
>
------------------------------------------------------------------------
> > -------------------------------------------------------
> > > EDS Electronic Data Systems
> > > Informationstechnologie und Service GmbH
> > >
> > >
> > CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> > Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> > 67435 Neustadt/Weinstraße
> > [login to unmask email]
> >
>
------------------------------------------------------------------------
> > --------------------------------------------------------
> >
> > =======================To change your
> > subscription options or to cancel your subscription visit the DB2-L
> > webpage at http://www.ryci.com/db2-l. The owners of the list can be
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
>
>
>
> visit the
> DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can be
>
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/



Steve Tennant

Re: db2 connect
(in response to Myron Miller)
So why do I get the error? I can connect using UDB, look at tables,
bufferpools etc etc but I still get that Access error. The full message is
: SQL0104N An unexpected token "RTIM" was found following'"'. Expected
tokens may include "AVG, SUBSTR,MAX,HEX,FLOAT,DATE,YEAR,ETC.".
SQLSTATE=42601.

As we are on v5(v7 this year, yeah!) on the m/f RTRIM does not exist so I
assumed I would need to get different drivers to suit the version we are
using. What am I doing wrong? BTW I am not putting in any SQL yet, this
error is straight after the connect and before the query window appears.

Steve T

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, 10 January 2002 10:04 AM
To: [login to unmask email]
Subject: Re: db2 connect


You don't need other ODBC drivers. You have DB2 Connect. It has all
the drivers needed once you've setup an ODBC connection from your NT PC
to the OS/390 mainframe.
--- Steve Tennant <[login to unmask email]> wrote:
> Looks like I fired off a question too quickly without RTFM %-( I
> guess I
> need other appropriate ODBC drivers to get DB2 stuff into Excel, can
> anyone
> say where I would find these? Is it somewhere on the IBM site?
>
> TIA
>
> Steve T
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, 10 January 2002 6:44 AM
> To: [login to unmask email]
> Subject: Re: db2 connect
>
>
> For Excell, its easy, just go into GET EXTERNAL DATA and when the
> screen for external data sources comes up, just pick your mainframe
> connection that you've already setup as an ODBC connection in DB2
> connect. Then just issue the SQL or whatever.
>
> For Access, you must link the tables, again by picking your external
> data source which you should have already defined as a ODBC
> connection.
> --- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> > hi all,
> >
> > we have db2 version 5 on mvs mainfraime os390 and now installed db2
> > connect
> > on windows nt workstation. it works fine.
> >
> > now what we want to try is to get access from excel or access to
> the
> > db2
> > database on the mainfraime.
> >
> > does someone know what we have to do for to get the connection -
> how
> > to get
> > a host database in access???
> >
> > Mit freundlichen Grüßen / Kind Regards
> >
> > hans-peter klein
> >
>
------------------------------------------------------------------------
> > -------------------------------------------------------
> > > EDS Electronic Data Systems
> > > Informationstechnologie und Service GmbH
> > >
> > >
> > CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> > Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> > 67435 Neustadt/Weinstraße
> > [login to unmask email]
> >
>
------------------------------------------------------------------------
> > --------------------------------------------------------
> >
> > =======================To change your
> > subscription options or to cancel your subscription visit the DB2-L
> > webpage at http://www.ryci.com/db2-l. The owners of the list can be
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
>
>
>
> visit the
> DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can be
>
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/


visit the





Myron Miller

Re: db2 connect
(in response to Steve Tennant)
Steve,
I'm not sure where the RTRIM is coming from if you're not issuing
any SQL. You can contact me off-line at 201-307-4234 and maybe we can
walk thru. I use EXCEL a lot with DB2 connect.

Here's a copy of my working CLIINI file for my test system.
[DSNT]
PATCH2=1,3,18,20
OPTIMIZEFORNROWS=5000
KEEPCONNECT=2
DB2OPTIMIZATION=4
DB2DEGREE=ANY
UNDERSCORE=0
PATCH1=1171
KEEPSTATEMENT=6
CURSORHOLD=0
DESCRIPTION=Application Test DB2
DBALIAS=XXXX mainframe DB2 alias
--- Steve Tennant <[login to unmask email]> wrote:
> So why do I get the error? I can connect using UDB, look at tables,
> bufferpools etc etc but I still get that Access error. The full
> message is
> : SQL0104N An unexpected token "RTIM" was found following'"'.
> Expected
> tokens may include "AVG, SUBSTR,MAX,HEX,FLOAT,DATE,YEAR,ETC.".
> SQLSTATE=42601.
>
> As we are on v5(v7 this year, yeah!) on the m/f RTRIM does not exist
> so I
> assumed I would need to get different drivers to suit the version we
> are
> using. What am I doing wrong? BTW I am not putting in any SQL yet,
> this
> error is straight after the connect and before the query window
> appears.
>
> Steve T
>
> -----Original Message-----
> From: Myron Miller [mailto:[login to unmask email]
> Sent: Thursday, 10 January 2002 10:04 AM
> To: [login to unmask email]
> Subject: Re: db2 connect
>
>
> You don't need other ODBC drivers. You have DB2 Connect. It has all
> the drivers needed once you've setup an ODBC connection from your NT
> PC
> to the OS/390 mainframe.
> --- Steve Tennant <[login to unmask email]> wrote:
> > Looks like I fired off a question too quickly without RTFM %-( I
> > guess I
> > need other appropriate ODBC drivers to get DB2 stuff into Excel,
> can
> > anyone
> > say where I would find these? Is it somewhere on the IBM site?
> >
> > TIA
> >
> > Steve T
> >
> > -----Original Message-----
> > From: Myron Miller [mailto:[login to unmask email]
> > Sent: Thursday, 10 January 2002 6:44 AM
> > To: [login to unmask email]
> > Subject: Re: db2 connect
> >
> >
> > For Excell, its easy, just go into GET EXTERNAL DATA and when the
> > screen for external data sources comes up, just pick your mainframe
> > connection that you've already setup as an ODBC connection in DB2
> > connect. Then just issue the SQL or whatever.
> >
> > For Access, you must link the tables, again by picking your
> external
> > data source which you should have already defined as a ODBC
> > connection.
> > --- "Klein, Hans-Peter" <[login to unmask email]> wrote:
> > > hi all,
> > >
> > > we have db2 version 5 on mvs mainfraime os390 and now installed
> db2
> > > connect
> > > on windows nt workstation. it works fine.
> > >
> > > now what we want to try is to get access from excel or access to
> > the
> > > db2
> > > database on the mainfraime.
> > >
> > > does someone know what we have to do for to get the connection -
> > how
> > > to get
> > > a host database in access???
> > >
> > > Mit freundlichen Grüßen / Kind Regards
> > >
> > > hans-peter klein
> > >
> >
>
------------------------------------------------------------------------
> > > -------------------------------------------------------
> > > > EDS Electronic Data Systems
> > > > Informationstechnologie und Service GmbH
> > > >
> > > >
> > > CoC Warenwirtschaft Tel.: +49 (0) 6327 39-218
> > > Im Altenschemel 21 Fax. : +49 (0) 6327 39-280
> > > 67435 Neustadt/Weinstraße
> > > [login to unmask email]
> > >
> >
>
------------------------------------------------------------------------
> > > --------------------------------------------------------
> > >
> > > =======================To change your
> > > subscription options or to cancel your subscription visit the
> DB2-L
> > > webpage at http://www.ryci.com/db2-l. The owners of the list can
> be
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send FREE video emails in Yahoo! Mail!
> > http://promo.yahoo.com/videomail/
> >
> >
> >
> > visit the
> > DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> > can be
> >
> >
> > =======================To change your
> > subscription options or to cancel your subscription visit the DB2-L
> > webpage at http://www.ryci.com/db2-l. The owners of the list can be
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
>
>
>
> visit the
> DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can be
>
>
> =======================To change your
> subscription options or to cancel your subscription visit the DB2-L
> webpage at http://www.ryci.com/db2-l. The owners of the list can be
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/