Re: Archive Tables View? or better option.

[Charles Wilt <charles.wilt@xxxxxxxxx>]

The SQL way of handling this is a "partitioned table"...

Very similar to a multi-member DDS PF...
Except, that
  select * from my_partitioned_table
will look at all the members..(if needed)

Used to be, you had to pay for Db2 Multisystem to create these, but that's
now no-charge at 7.5.

The other related SQL concept is a "temporal table"

Charles

On Fri, Jul 12, 2024 at 2:33 PM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

>  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.