ADO active connections to Sybase

First off I want to tell everyone that I am not a DBA. I am a programmer using Visual Basic 6.0 and Sybase System 10. Here is my question:

Our current Sybase license runs out in 2 years. We have to upgrade to Sybase System 11, but the problem resides in the amount of active connections to Sybase. We can currently have in the neighborhood of 300 concurrent active connections to Sybase. The new license limits us to 128 with a $1000 charge for every user over that. As you can probably tell, my boss is very concerned.

There has been MAJOR talk of moving our database to DB2, since the entire corporation uses DB2 (besides a few systems that use MS SQL Server. We would be using MQ-Series to talk with DB2. This does not appeal to the programmers in our group, but at the same time makes upper management happy to see more applications going to the corporation database (DB2).

Our only hope is to stay with Sybase (even though we would love to go to SQL Server or Oracle). We would upgrade to Sybase System 11. We would have to our own type of message queing application that queues up the users before connecting to Sybase.

We thought it would be as easy as wrapping our connection around the recordset calls. I thought if we could just open the connection before we Execute our ADO command and close it immediately afterwards, this would prevent more then 128 users having an active connection open at the same time. The problem is that if we close the connection right after we get our recordset, the recordset is killed as well.

So, I guess here are my questions:

1. DB2.... is it as bad as everyone says it is? Copybooks? What a pain.

2. Sybase..... how can I go with a 128 user license and be able to queue up the users?

3. Other options..... Can anyone convince me (actually my boss) that another Database is better? Oracle? MS SQL Server?

Thanks for EVERYONE's time and help!!!

I work with Access, SQL server 6.5 and DB2. The bind of Sybase you're running is similar to SQL server, so a move to that platform would not likely be prohibitive. DB2 is fast and, depending on the version you're running, is accesible via tcp/ip and ODBC (I am not sure there is an OLEDB driver for it yet). Finding a common denominator for some of the data types may present a challenge (DB2 uses a different start point for dates than SQL server, for example).


Your boss should inform your Sybase rep that your company is soliciting bids for the database this application uses and then do so. You'll be better off, one way or another.

Can anyone else post their opinions? I would love to hear from anyone and everyone. This has a REALLY high importance level and a decision needs to be made quick. If we do goto DB2, we have 2 years (which isn't enough time) to convert our current application to support the DB2 data architecture.

If you can't do a conversion in less than two years, it's simply not worth doing. The reason is simple, business conditions do not remain static and the tool you're working on may become obsolete.

Converting to SQL Server, unless you've put a lot of Sybase specific stuff in your code, should be only a couple of months (and if you pay M$oft contracting/consulting, they'll do it for you). DB2 should be about the same.

I figured it out myself. ODBC pooling will take care of the connections and ado disconnected recordsets will take care of recordsets being lost.

Hi!

As a matter of fact, you don't even have to use ADO...
It seems that, bad use of DAO in the past by a lot of programmers, create this unfortunate scenario with connections...

but u can (with a little change in code) take care of
the whole business.. then again, using ADO will, as u pointed out, take care of it...

Remember though to resync the disconnected recordsets for possible updates

Make it a good day!