Solved

Connecting to Sybase ASE 12.5 and retrieving large datasets

Posted on 2008-10-29
10
813 Views
Last Modified: 2010-05-18
Hiya - I can't quite believe how hard I am finding this.

I just need to run SQL against an Adaptive Server Enterprise 12.5 server and have the results (however large - base tables 35 M records) saved to a file.
I also need an ODBC driver so I can connect from MS Access - this will make everything soooo much easier.

Research led me to 'Workspace', which gives me a good view of the database objects and lets me create what it calls 'SQL files' to run ad-hoc SQl against the database.
It bombs out on large datasets though (Java Heap exceeded) and I can't see how to tell it to save the results straight to file.

I have tried to download Open Client from the Sybase website, but it tells me I need an account.
I have loaded the whole of SQL Anywhere 11 but it only loads ODBC drivers for 'iAnywhere Solutions 11 - Oracle', 'SQL Anywhere 11' and 'ultraLite 11'.
I have seen a suggestion to load the development version of ASE, which I will try next, but talk about a sledgehammer to crack an ODBC nut!
0
Comment
Question by:BaffledAgain
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 400 total points
ID: 22837182
First, if you really want to get 35M rows from an ASE database to a flat file, using ODBC and Access is not very practical.  It will take freak'n forever.

The fastest and easiest way to do this is with the Sybase BCP (Bulk CoPy) utility.  It is designed to stream large amounts of data into and out of the database at very high speeds.  You might easily get 20,000 Rows per second out to a flat file even on a modest system.  It will take easily 10 times as long to do it with ODBC/Access.  Take a look at the Sybase website for all the docs at sybooks.sybase.com  It is all there either online or downloadable as PDFs.  You will want to look at the Utility Reference Manual.

If you must use a Lawnboy to harvest a thousand acers of wheat, I would get the ODBC drivers installed on the PC, create a System DSN for the connection to Sybase, then make the ASE tables of interest "linked" tables.  Then you can refer to them in Access as if they were local and write append queries or extraction queries as you need to.  You can (don't try this please) create queries that join Sybase and Access tables.

As far as getting the client components goes, you need the ASE Windows Client installation.  You can generally do this from the Developer's Edition of ASE, installing just the client pieces and not the server, however, if you have to do the whole install, it is not that big a deal; The ASE footprint is only about 80MB after you take all the common and shared stuff into account.

Best of luck,

Bill
0
 
LVL 7

Expert Comment

by:jdietrich
ID: 22844014
I agree with grant300, BCP is the fastest way.  However, if you are looking for a result set based on where clauses you might need to do it via SQL.  Is the environment Unix or Windows?  You can pull the data to a flat file, then transfer or FTP the data to the machine you are working from.  Then import to Access.  You definitely do not want to pull a large volume of data over the network via a query or using a tool like access.  It can have adverse affets on the sybase DB for other users, as the bottleneck will be the network and your PC, which I am sure is not as robust as the server that Sybase is running on.
0
 
LVL 19

Expert Comment

by:grant300
ID: 22845136
If you need the flat file on the client side, you can install the ASE client software and run BCP on Windows.  That way you will wind up with the flat file where you need it.  You still have the network bottleneck so it won't be as fast as running on the server but it is still pretty darn fast and you would have to wait for the network for the FTP or mapped network drive anyway.

I go the impression that you wanted all 30M rows from the table(s) but maybe not.  As far as a WHERE clause goes, there are a couple of alternatives.  First, you can create a view and BCP from the view.  The view can contain the WHERE clause and fixed SARGs.  Now if each extract is different, you can use one of two tricks.  You can create the view as needed in the tempdb the drop it when you are done or you can put arguments in a little helper table and have the view join to the table to get the equivalent of the SARG values.

There are tricks you can do with stored procs and proxy tables as well but I suspect your requirements are much simpler than that.

Regards,
Bill
0
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 100 total points
ID: 22902426
Forget about ODBC, etc (that is trying to crack a coconut with a pin).  The missing piece you need is:
1  You need to find the ASE 12.5 Open Client software that was delivered with the CD.  You get two versions, e.g let's say you bought ASE 12.5 for Solaris: you get a Solaris version and a Windows version.  In that /bin or \bin directory, you should find two programs: bcp and isql.  35 M or 35 B rows is no problem (but do consider what others have posted re network load).
2  If you run it on the server, you will not use the network; if you run it on the desktop, obviously it will.
3  bcp: extract the entire table at blinding speed.  
Use -F and -L to constrain rows;
use -c to get an ascii file;
use a view if you need convert()
4  isql: very fast but nothing like bcp.  write your sql and put it in a file e.g. my_query.sql, then exec isql
> isql -Uxxx -Pppp -Ssss -imy_query.sql -omy_data.txt
>
> more my_data.txt  # or
> wordpad my_data.txt

Cheers

0
 
LVL 9

Expert Comment

by:TheSloath
ID: 22914164
Hiya - thanks for the replies.

I shouldn't have put so much focus on the 30M record aspect - I litterally just want to easily get at the data in the database to explore it, and the best tool for that is MS Access, so I need to get the data into it.

I was looking for comments on 'Workspace' as to whether in your experience it was worthwhile persuing, seeing as it is failing me currently?

I actually downloaded a 3rd party ODBC driver 14 day free trial in the end - which allowed me to run my SQL against the Sybase database and return 10M records (overnight :-) to MS Access for analysis. I still find it baffling that I cannot just download an ODBC drive quickly and easily on the Sybase website: it is as if they don't want to you to be able to connect to their database and are making it as difficult as possible.

I am amazed the team running the database I am connecting to cannot just supply me with the Client setup files?! They must have them surely?! It must be a bog standard requirement?!

Bill - Thanks for the tip about downloading the Developer's Edition of ASE - I hope that will provide me with the ODBC driver I need to continue, and ISQL and BCP.

IncisiveOne - thanks for the detail on using BCP and ISQL. I think I will try and use them for retrieving large datasets from my PC, hoping this will be faster than the ODBC route for large result sets (though all the ODBC (passthrough) is doing is passing the SQL to the server to run and retrieving the result. I suppose there may be some unecessary processes wrapped up in it's implementation that may produce an overhead that BCP and ISQL will cut out though).

This whole experience really has just highlighted the quick and easy of use of SQL server, especially with Enterprise Manager, for me.

Thanks again
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 9

Expert Comment

by:TheSloath
ID: 22914191
I can't see the button for allocating points for your answers. Am I being dim...?
0
 

Author Comment

by:BaffledAgain
ID: 22914236
Oops - I was logged in as the wrong user name :-)
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22914702
We were answering your original question (save to a file).  Now that I have a better understanding of what you need ...  you definitely should not have a copy of the tables on your PC, just access the server and look at the data that resides in it, and that remains current.

> and the best tool for that is MS Access <

Definitely not.  Sybase ASE (and MS SQL Server) are pure Client <--> Server architectures.  The <--> is TDS over TCP or Winsock.  There are hundreds of good reporting programs from Crystal to BusinessObjects.

When you get into the MicroShaft world, everything is ODBC.  You now have Client <--> ODBC <--> Server.  Sure you can run MS Excel or Access, but it is a hog, you will be running at ODBC speeds not server speeds.

> WorkSpace <

New product, many bugs, wait for it to settle.

Understand that is a mostly existing separate programs bundled together as a package (plus more).  Therefore if you are willing/capable, look at those programs.  isql at the char-level and DBisql at the window level (shipped on the CD).  

Plus there are 3rd party programs that fill this space.  E.g. RapidSQL or DBArtisan from Embarcadero; SQLProgrammer from Sylvan Faust.

> I am amazed the team running the database I am connecting to cannot just supply me with the Client setup files?! They must have them surely?! It must be a bog standard requirement?! <

Yes, very suspicious.  Either pure incompetence or resistance.  Not acceptable in a commercial org.  They need to central/control licences as well.

AFA ODBC is concerned, it is normal practice for the org to choose one ODBC driver that they supply/support, and have that too, available for installation on any users PC.

> asy of use of SQL server, especially with Enterprise Manager, for me <

Sure the paths have diverged afterward, but SQL Server was a copy of Sybase, the architecture is not the same, it is identical.  

Well, if that's what you want, look at the equivalent on the Sybase side, it is called SybaseCentral.  Also free on the original CD, part of Open Client.

For more grunt but staying in the exact product/capbilit category, use DBArtisan.

Cheers
0
 

Author Comment

by:BaffledAgain
ID: 22914852
Fab - cheers for all the info IncisiveOne!

When I say MS Access is the 'best' tool I mean purely for being able to pull apart/aggregate/investigate data quickly and easily. I've been writing databases for 15 years now and I haven't come across another peice of software out there that is quite as easy for getting to grips with datasets. I must admit to not conscientiously keeping my finger on the pulse, but would much appreciate any pointers to other products.

I also happen to think anyone creating a Windows front end for a database (that does not require web) is mad not to use MS Access. Programmed properly I find it fabulous for presenting data to users on screen, which is pretty standard stuff 95% of the time: datasheets and forms. And using passthrough over ODBC obviously gives server speeds.

I was aware that MicroPants bought the technology behind Sybase - so I even tried the SQL Server ODBC driver to connect to Sybase, but no joy :-)

So you're saying Sybase Central is Sybase's equivalent of Enterprise Manager? I thought that was what Workspace was - doh. I'm loading up the Development ASE now so hopefully I'll get to see everything.

I first had to resort to DBArtisan 10 years ago with another Sybase project, I couldn't believe there still wasn't a graphical front end for Sybase when I received this project - or maybe that's what Sybase Central is and again these guys seems to be, as u say, resistively unhelpful or incompotent.
The amount of faffing around this I think it would have been worth the 800 quid for DBArtisan.

I will check out SQLProgrammer and RapidSQL now though - cheers.

What do you use to explore and administrate Sybase then - Sybase Central?


Cheers again
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22918660
> When I say MS Access is the 'best' tool I mean purely for being able to pull apart/aggregate/investigate data quickly and easily. <

That's exactly what I mean as well.

>I've been writing databases for 15 years now and I haven't come across another peice of software out there that is quite as easy for getting to grips with datasets. <

I've been writing databases (half the time including the front end app & reports as well) for 32 years, and I have never used MS Access for the job.  I have used Access for other purposes (prototyping, looking at a cust' s existing Access "database") and I am well aware of its functionality.  Let's say that it is a perfectly acceptable tool for a power user to access the Db.  But really, from your sttmts it only means that you are unaware of other tools, which have a hell of a lot more click-and-go grunt.  There are hundreds, I have already listed a few.

> And using passthrough over ODBC obviously gives server speeds. <

No. it gives faster ODBC speeds, but nowhere near direct client-server speeds.  You cannot squeeze a fire hose into a garden hose.

So you're saying Sybase Central is Sybase's equivalent of Enterprise Manager? I thought that was what Workspace was - doh.

SybaseCentral is the equivalent of EM, it is graphical, with an explore plus SQL window.  Period.  There are diffs, see for yourself.
Both are legless compared to DBArtisan.
WorkSpace is not SC; it is a whole integrated work sace of many (previously separate) Sybase products, one of which is a ramped up SC.

What do you use to explore and administrate Sybase then - Sybase Central?

> What do you use to explore and administrate Sybase then - Sybase Central? <

When I am at cust sites, W have to use SybaseCentral as a rule, which means we have to keep current with it.  We have our own DBArtisan licences, so in-house, we use DBArtisan only.  For substantial assignments, I install DBArtisan at the cust site.

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now