You want to have a View where all Tables starting with ETBL followed by a 3
digit number in a specific schema are put together by a UNION Statement.
But you need only the existing files.
Since number can be skipped (because a table does not exist) and new numbers
are added it cannot be done with a view.
However it should be possible with a dynamic SQL in an UDTF.
With the following Statement you may be able to generate the complete
SELECT-Statement (with all existing tables including the UNION ALLs)
With x as (Select System_Table_Name,
'Select Right(Trim(System_Table_Name), 3) Nbr, a.* from '
concat
Trim(System_Table_Schema) concat '.' concat
Trim(System_Table_Name) concat ' as a' as
SelectStmt
from Systables
Where System_Table_Schema = 'YOURSCHEMA'
and System_Table_Name Between 'ETBL000' and
'ETBL999')
Select ListAgg (Cast(SelectStmt as Clob(256)), ' Union All ') Within Group
(Order By System_Table_Name)
from x;
So you either can use it directly with Dynamic SQL or you write an UDTF
where you can use this dynamic SQL Statement
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K
Crawford
Sent: Friday, 12 July 2024 22:33
To: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
Subject: Archive Tables View? or better option.
I have a series of tables that are created to archive tables, the max
number of archive tables is 999. These tables exist in a library called
ARCHIVE. When a new table needs to be archived the process is to increment
the table number by one and drop off 999. The new table is number 000.
The list would look like this:
ETBL000
ETBL001
ETBL002
.
.
.
ETBL997
ETBL998
ETBL999
I have a program that reads each of the tables searching for a string. The
overhead of open/close is making it slower than the users like.
I am thinking about creating a view like this.
create or replace view archive.v@etbl_0
as (
select '000' as TblNumber, e.* from archive.etbl000 e
Union all
select '001' as TblNumber, e.* from archive.etbl001 e
Union all
select '002' as TblNumber, e.* from archive.etbl002 e
.
.
.
Union all
select '997' as TblNumber, e.* from archive.etbl997 e
Union all
select '998' as TblNumber, e.* from archive.etbl998 e
Union all
select '999' as TblNumber, e.* from archive.etbl997 e
)
RCDFMT v@etbl_0R
;
Downside to this is that writing the view will be a pain. but I only have
to do it once.
Another gotcha is that it is possible that a table may not exist. For
example etbl002 may have gotten deleted. This should make the SQL statement
fail.
Anyone have any better ideas?
--
Kerwin Crawford
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
