Union queries in DB2 Newby

[thendrickson]

And by Newby, I mean I am starting on chapter one with a book I have so bear with me please.

I am looking at a project which uses Access pass through queries as well as some linked tables and Access queries.
(To a DB2 database on an AS400)

Some of the documentation indicates that there is a problem with the number of Unions that can take place on the AS400 database (DB2) which is why the project exists in the manner that it does.

Performance, bluntly, is worse than poor.

I am reasonably certain the problem lies with the linked tables and am trying to find my way around this.

Simply put: How many unions can one query have in DB2?

 

[Agro42]

I've hit the limit on mainframe DB2 (on older versions of DB2 admittedly) of 16 unions.

 

[thendrickson]

Thanks a lot. I would guess that this would be an old version of DB2.

Now I need to find my way around this

 

[MarcLodge]

Keep in mind that every union will cause db2 to go to the data, and add to the interim results table that it builds. If the query is made up of 3 unions, and each of the SELECTs perform a full table space scan, then the full query will make 3 table space scans, which will obviously impact on performance. Unions are great, but should be used with care when there is no other method available.

 

[thendrickson]

Thanks Marc,

I am in truly a bit over my experience with this.

I accepted a temp job to fix a "Poorly performing Access Database" and now find that the Access DB is the front end for a db2 database.

The Access DB uses quite a number of pass through queries, then uses some Access Queries and linked tables.

Very confusing to say the least.

I understand Access well enough to know that the linked tables and the Access Queries are killing performance by "clogging the network". This network traffic problem needs to be addressed.

Some documentation I received made the statement that the linked tables were needed due to a limit on Union Queries.

As I have looked over all the pass through queries, I am finding many sub queries and not Union queries so I am a bit uncertain what the documentation is refering to.
(I suspect the origional developer was as confused or more so as I)

As of yet, I have not found the version of DB2 used, but also have not found any place where 16 unions would occurr even counting sub queries as Unions.

But I can fix the major performance issues by doing away with the Access linked tables and converting the Access queries to pass through queries.

I know this might still be slow, but this should satisfy them and not clog the network. I also need to be certain that they actually need all the data being returned. I have never soon so many "Select * from" with any database I have worked with in the last 6 years.

 

[MarcLodge]

It definitely sounds as if you need to look at you data/outputs and decide what is required. You may also wish to consider whether that data is static, or frequently changed. If static, it might be worthwhile investigating whether this static data can be held locally.

Access and DB2 are pretty good at this, but it sounds as if you've got a bit of detective work to do. Good luck, and come back to us if you need any further help.

Marc

 

[blom0344]

I accepted a temp job to fix a "Poorly performing Access Database" and now find that the Access DB is the front end for a db2 database."

thendrickson,

I have been playing with the idea of using my DB2 databases as back-end for Access applications, but never got it to work properly. Would you mind sharing the code involved, etc? Would be mighty pleased to get hold on any usefull info .....

thanx in advance...


T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

TBlom,
I did this at a previous place I worked at. They had a DB2 system which had a lot of old poorly designed CICS screens which I wrote an Access front end for, with tabs and radio buttons etc. If I can hunt out the mdb, I'll send it over.
Marc

 

[thendrickson]

In my case they are not using an actual "front end" as I would call it.
They seem to run the reports by opening Access and running the reports.

I have aquired more information that, if accurate (and at this point, I have no reason to suspect the info to be fully accurate), Leads me to the conclusion that the "Big Problem" as they see it is having the ODBC time out set to a low value.

The ACCESS DB is very inefficiently designed as well as the reports.

Each report uses the DLookup function 6 times to aquire data from the linked tables. Marc is most likely aware of how slow this can be, but I will point out for others that these functions are very very slow.

There are additional design problems.

I will actually start this Monday. As soon as I get to the bottom of what is actually occuring, I will post and let any body interested know what I found out

 

[blom0344]

Marc & THendrickson,

I will be grateful with anything on the subject. Though we will probably loose DB2 as a platform in the future, I want to resolve the combination Access/DB2 in any case, particularly if I have found a new job with an employer that does value DB2 .........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[thendrickson]

Well, the job was delayed again so I am not too certain what will happen.

What I have fiqured out for certain is that you can use a DSN connection to the DB2 connection and a pass through query. (From Access). Pretty simple actually. Look at help.

I am uncertain if you can use a connection string and a DSN less(???terminology may be "shakey" here, but I hope you understand my meaning???) from VBA to DB2, but if you can I would suspect that would be better. (Using embedded SQL Statements).

Probably should use an Access Project, but I have not tried yet

 

[MarcLodge]

I have hunted out and found the Access DB. I built panels(forms) that requested certain Key information from the users, and then dynamically built the SQL in Access, and passed it via ODBC to the mainframe. The data back was then displayed on the subsequent panels. I seem to recall that I had panels that displayed mulptiple rows of data and single rows too. If anybody is interested in the MDB (which is fairly large), let me know you email address and I will mail it to you.
Marc