Re: SQL, Logical Files, Unions & Indexes. -- MIDRANGE-L

Hi,

as you ask for, I will try to describe as short as possible our environment:
iSeries, each with there database, are installed in 20 different regions.
All databases are analogous.
Each relational database contains on average about 100000 records in a main-table and about 100 dependent tables.
Par year on average 12 paiement-data are added per main-record. (each paiement consists of up to 10 dependent tables)

All databases are continuously replicated via Remote journaling to a central site.
First of all, the 20 replicated libraries at the central site are a exact copy and so a backup of each region.

Second, a general library is defined that contains all the necessary logical files, with the same name as the logicals in each regional library,
and these general logical files point "overall" to all the physical files are tables of all the region libraries.
So, via a simple librarylist switch, all overall data are accessible for the central site (control)-people,
with exactly the same programs as used in the regions, and this as well for online direct access, as for general statistics work, as for any other process .

Now, we want to change our classic programs and open our databases to an application server, using hibernate ( and SQL ).
Only, I do not see how I can create the same setup and the same possiblities, as I have with our current setup...

1) I can replace our 'overall-logcal files' with SQL Views, but I cannot create an index over multiple tables (as I can via a logical file),
so these views will not give me the sub-second accesstimes I have now (or am I wrong in this?)
2) Hibernate creates "joins" over multiple tables under the covers, but -as far as I know- this cannot be done on the current "overall-logcial files",
or on any alternative using "union"- views (I hope I am wrong on this one too) ?

luc

----- Original Message ----- From: "Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Monday, April 02, 2007 7:30 PM
Subject: Re: SQL, Logical Files, Unions & Indexes.

The one piece of information that you did not give us was how long does
it take to run the query over a single library and how much data does it
return?

If this is very quick, the union would do the trick pretty quick except
you end up writing 50 SQL Statements.

I am not sure whether putting an index over the fifty libraries would
help much. I wonder if SQL would even be able to use it. I would think
it would simply drop through to one physical.

One solution to this would be in code. Just loop around writing dynamic
SQL statements to retrieve each library and write to a Global Temporary
Table. Then one SQL to present the results.

This would prevent you from having to write 50 SQL Statements for each
library.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.