Re: Database partitioning

Why?

In addition to Single Level Storage, the AS/400 automatically moves
frequently accessed data to locations on disk to optimize it's access. 
You state that your "potential" table sizes are too large.  Too large
for what?  The query (sql) optimizer on the AS/400 works quite well at
selecting the most efficient access plan for the data.  If you find some
queries needing a little more speed, you can use debug to determine
additional indexes to be added to speed access.  You can also use sql
delete statements to purge data as easily (and with as much control) as
with the oracle partition scheme outlined.  

So, what is it you think you need to control that the system does for you?

-Bob Buchanan

> Sandeep Potnis wrote:
> 
> Alexei,
> 
> I understand that AS/400 has single level storage but I cannot control
> it. Besides Oracle type of partitioning makes it easy to purge uneeded
> data - you can just drop the partition and create a new one for new
> data.
> 
> So -  I understand what single level storage is but I am looking for
> more control.
> 
> Thanks for your response
> 
> Sandeep
> 
> ------Original Message------
> From: pytel@us.ibm.com
> To: MIDRANGE-L@midrange.com
> Sent: April 25, 2000 11:25:02 PM GMT
> Subject: Re: Database partitioning
> 
> If you are talking about single system, then AS/400 database does not
> require partitioning - data will be spread across all available drives
> 
> automatically.
> 
> If you have multiple systems in mind, you can do it on AS/400
> (partition
> single table over several AS/400 systems).
> This is supported by OS/400 option 27 - DB2 MultiSystem.
> 
>     Alexei Pytel
> 
> Sandeep Potnis <potnis@india.com> on 04/25/2000 01:28:29 PM
> 
> Please respond to MIDRANGE-L@midrange.com
> 
> To:   MIDRANGE-L@midrange.com
> cc:
> Subject:  Database partitioning
> 
> Hello Everyone,
> 
> We are in the process of creating a Data warehouse on an 8-way 650
> with
> V4R4.
> 
> The potential table sizes are too big and we need to partition the
> tables
> for performance improvement.
> 
> Oracle has a partition clause in the create table command as shown
> below:
> 
> CREATE TABLE stock_xactions
>    (stock_symbol CHAR(5),
>     stock_series CHAR(1),
>     num_shares NUMBER(10),
>     price NUMBER(5,2),
>     trade_date DATE)
> STORAGE (INITIAL 100K NEXT 50K) LOGGING
> PARTITION BY RANGE (trade_date)
>   (PARTITION sx1992 VALUES LESS THAN
> (TO_DATE('01-JAN-1993','DD-MON-YYYY'))
>      TABLESPACE ts0 NOLOGGING,
>    PARTITION sx1993 VALUES LESS THAN
> (TO_DATE('01-JAN-1994','DD-MON-YYYY'))
>      TABLESPACE ts1,
>    PARTITION sx1994 VALUES LESS THAN
> (TO_DATE('01-JAN-1995','DD-MON-YYYY'))
>      TABLESPACE ts2);
> 
> Does UDB have something similar to this ? If not any suggestions with
> regard to dB partitioning on the same system are greatly appreciated.
> 
> Thanks
> 
> Sandeep Potnis
> ______________________________________________
> FREE Personalized Email at Mail.com
> Sign up at http://www.mail.com/?sr=signup
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> 
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> 
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> ______________________________________________
> FREE Personalized Email at Mail.com
> Sign up at http://www.mail.com/?sr=signup
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> 
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---