RE: Rumba ODBC bug?

Dave, I found your thread on this problem in the March archives.  And I
looked in our SYSCOLUMNS table and, sure enough, I now know why the columns
are coming out in the sequence they are.  Feature?!?!?!?  IBM can shove that
"feature" up their collective rear!

I emailed Chuck Pence earlier today, haven't yet gotten a response.  My
luck, he's probably on vacation this week!  I'll see if I can locate some
info on comp.sys.ibm.as400.misc as you suggested.

Can you turn off REUSEDLT on the SYSCOLUMS table?  And just make sure you do
a weekly RGZPFM on the file?

Maybe I'll go take a look at APAR II11189.

At least I can go tell our help desk to stop bothering the Rumba folks about
this.

- Dan Bale

> -----Original Message-----
> From: Shaw, David [SMTP:dshaw@spartan.com]
> Sent: Monday, June 12, 2000 10:03 AM
> To:   'MIDRANGE-L@midrange.com'
> Subject:      RE: Rumba ODBC bug?
> 
> > -----Original Message-----
> > From: Bale, Dan [mailto:DBale@lear.com]
> > 
> > We are a Rumba 2000 shop.  We are using Rumba's "optimized" 
> > ODBC to download
> > AS/400 files directly into MS Access97.  (FWIW, we're also 
> > running on NT4.)
> > 
> > A new table is created in Access by using ODBC.  The data is being
> > downloaded into Access.  Everything looks good, EXCEPT that, 
> > intermittently
> > (but more and more often now), the sequence of the fields in 
> > the Access
> > table is not the same as in the AS/400 file.  This is driving 
> > us nuts.  (I
> > am aware of how to manipulate the field sequence in an Access 
> > table via the
> > Design sheet, but I don't want / shouldn't have to do this!)
> > 
> > Usually, the pattern is that the first 'n' columns/fields 
> > will be shifted
> > several columns over; it could be the first one column/field 
> > or the first
> > seven (or anything in between).
> > 
> > The driver is called "RUMBA AS/400 Optimized Server (32bit)", 
> > the version is
> > 1502.05.00.00, the file name is WDODBCOS.DLL, and the date is 
> > 10/5/99.  fwiw
> > 
> > Has ANYBODY run into this problem?  
> 
> This also occurs with the Client Access ODBC drivers, all versions.  It
> sounds like Rumba must be using the same system catalogs on the /400 that
> Client Access uses.  The ODBC specifications do not define what order the
> fields should be presented in (thanks so much for that, Micro$oft!), so
> for
> performance reasons IBM presents them in relative record number order.
> The
> catch is that the file in which this particular information is stored is
> defined with REUSEDLT(*YES), so RRN order frequently doesn't match field
> position order.  Chuck Pence in DB2 development in Rochester has a fairly
> involved work-around that effectively adds the field position to the index
> of the system catalog file.  I don't believe I've ever seen him on this
> list, but he is on the comp.sys.ibm.as400.misc newsgroup frequently, if
> you
> want to contact him about it, or you can try e-mailing him at
> crp@us.ibm.com.  He asks that we users not redistribute the instructions
> among ourselves, so please don't ask me to forward them to you.
> 
> Dave Shaw
> Spartan International, Inc.
> Spartanburg, SC
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---