MAJOR PROBLEM

[tsunami100]

Hi,
I don't think i'm the first who's asking this but this issue drives me crazy.
I have to access a cobol-datafile using ACUodbc.
I've linked the files to an Access-database
The point is that it's very very slow.
I've read a lot about this issue but it seems there's no solution for it.
Does anybody know how to resolve this.

Thanks in advance.

I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[Dimandja]

ODBC is optimized for relational databases. You shouldn't expect the same performance when manipulating flat files.

If you must use this system, look at the type of queries being thrown at it. Try simplifying those queries.

__________________________________________
Try forum1391 for lively discussions

 

[k5tm]

Dimandja,

That is a rather broad statement. After all, one can easily create very bad queries against a relational database by causing a full table scan by a sloppy join on a nonindex column.

tsunami100,

In defense of our competitor, I would say that unless you are using a passthrough query in Access, you cannot really determine what query (or queries!) the AcuODBC SQL engine is seeing. The so-called Jet engine in Access is notorious for its clumsy use of ODBC (which I guess was developed by a different part of Microsoft).

First you must understand exactly what is going on, and for that you can use the ODBC tracing capability (available from the ODBC Data Source control panel applet). You can also force Access to use passthrough queries, which will allow you better to craft the query that is being sent to the datasource. This might allow you better to use the indexes available in the indexed files underneath AcuODBC.

Tom Morrison

http://www.liant.com

 

[Dimandja]

>one can easily create very bad queries against a relational database by causing a full table scan by a sloppy join on a nonindex column.

Fair enough.


tsunami100, can you post a snippet of the code that's giving you problems?

__________________________________________
Try forum1391 for lively discussions

 

[Dimandja]

Also, why won't you access those files directly?

__________________________________________
Try forum1391 for lively discussions

 

[k5tm]

Dimandja,

Guess tsunami100 isn't going to reply....

One uses products like AcuODBC (for AcuCOBOL data files) or Liant's Relativity (for RM/COBOL and Micro Focus files) to 'open up' the data to ODBC-enabled tools, including Crystal Reports, BusinessObjects; Microsoft Access, Excel, Word, PowerPoint, SQL Server, Visual Basic, C#; PHP, Perl, Python, etc. These tools can use COBOL data, live and direct from the files without stopping by a data warehouse, in a manner familiar to anyone that uses SQL tools. It is one mechanism to integrate legacy applications.

Tom Morrison

http://www.liant.com

 

[MuadDubby]

Same problem here, btw. Used AcuODBC in the past in order to let apps like VB, Excel, etc. access ACU Vision files. It works, but it crawls.

The queries were very simple, often without any joins.

AcuODBC works, but I wouldn't expect any miracles.

.DaviD.

 

[k5tm]

David,

I can't speak for AcuODBC, as I have had absolutely no experience with that product.

I do know that there are many optimizations in the Relativity SQL engine that are specific to dealing with COBOL indexed files. Getting a product such as Relativity (or AcuODBC) to perform well 'out of the box' has been an interesting challenge.

I can also personally attest to the fact that dealing with Access and the Jet engine is very frustrating; it is almost as if the Jet engine considers the ODBC SQL engine a hostile enemy. It is very difficult to produce good performance at the ODBC level when the Jet engine has dismembered a join (for example).

Tom Morrison

http://www.liant.com

 

[tsunami100]

Hi,
Sorry for that late reply
Thanks Dimandja and others.
I tried to access the files by using a pass trough.
Accessing very small files and using simple queries is feasable.
But accessing a file with appr. 6000 records wich is still a small file is a nightmare !!
Computer keeps on running but no result.
Ktm5, i don't understand what you are trying to say ??
Is there a better tool ?

Thanks.

I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[Dimandja]

>Accessing very small files and using simple queries is feasable.
But accessing a file with appr. 6000 records wich is still a small file is a nightmare !!

I think this is an indication of that ODBC engine lacking flat file optimization.

Do you have enough memory to use? You may get better performance by increasing (doubling at least) the runtime memory available to your application.

__________________________________________
Try forum1391 for lively discussions

 

[MuadDubby]

...dealing with Access and the Jet engine is very frustrating" ...

Which is probably why Microsoft wants nothing to do with it now, and is phasing it out in favor of MSDE and SQL Server.

If they are washing their hands of the product, it must be for a good reason

.DaviD.

 

[k5tm]

tsunami100,

I will say again: I have had absolutely no experience with AcuODBC. My experience is with Relativity, as a developer, and Relativity does not support AcuCOBOL files. Relativity is very similar in concept, though I am reasonably sure that the underlying technology is very different.

Okay, now that I have said that...

Can you post the COBOL file definition and SELECT statement? Also, please post the SQL SELECT passthrough query that is so slow. Perhaps I can discover something simple to change that might be helpful.

Tom Morrison

http://www.liant.com

 

[tsunami100]

I want to thank all of you for being so helpfull.
This means a lot to me. Thanks !!!

Dimandja,
I will try as you said tommorrow !!

davidk13,
Yep, that's what i have been reading on the internet !!

k5tm,
Maybe i forgot to mention that i have absolutely no experience with programming in COBOL but i do have access to all the files. So if you can tell me what you need i'll post it.
The sql passtrough is very simple :

SELECT NR, NAME, STREET, LOCATION FROM BIWCUSTOMERS

thread183-995331


I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[tsunami100]

I want to thank all of you for being so helpfull.
This means a lot to me. Thanks !!!

Dimandja,
I will try as you said tommorrow !!

davidk13,
Yep, that's what i have been reading on the internet !!

k5tm,
Maybe i forgot to mention that i have absolutely no experience with programming in COBOL but i do have access to all the files. So if you can tell me what you need i'll post it.
The sql passtrough is very simple :

SELECT NR, NAME, STREET, LOCATION FROM BIWCUSTOMERS

When i try to use a WHERE clause i'm getting an error
I don't remember exactly the error-statement but it had something to do with the = operator.
I'll write it down tommorrow.

Maybe you can take a look at a thread i posted in the sql-forum because i'll try to access it with sql-server to.

thread183-995331


I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[tsunami100]

Sorry, posted two times, look at the last one !!

I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[Dimandja]

>SELECT NR, NAME, STREET, LOCATION FROM BIWCUSTOMERS

When i try to use a WHERE clause i'm getting an error
I don't remember exactly the error-statement but it had something to do with the = operator.

That select statement will give you problems on any platform. You want the whole table accessed. You'll find it helpful to either narrow down your query with a WHERE, or better yet, use a cursor.

__________________________________________
Try forum1391 for lively discussions