SAP Mix

RE: 32 GB per dbspace limitation

Posted By:
Date:

This weekend I did fragmented CDCLS table w/ 28GB size, and it took 6 hours
(reduced from 32 hours) with following parameters changes, and using alter
fragment statement instead of "insert into select * from"

Changed CLEANERS from 96 to 128
Changed LRUS from 48 to 512
Changed LRU_MAX_DIRTY from 2 to 99
Changed LRU_MIN_DIRTY from 1 to 98
Changed PHYSBUFF from 2488 to 3140

I hope this is helpful.

Luke
-----Original Message-----
From: Krijt, Marco [mailto:Marco.Krijt@VendexKBB-ITS.nl]
Sent: Tuesday, April 08, 2003 3:44 AM
To: sapmix@iiug.org
Subject: RE: 32 GB per dbspace limitation [99]


You are absolutely right!

-----Original Message-----
From: ERNST,CHRIS.... [mailto:christoph_ernst@hp.com]
Sent: dinsdag 8 april 2003 8:57
To: sapmix@iiug.org
Subject: RE: 32 GB per dbspace limitation [98]


Hi,

if you create a copy table, you don't need to drop the indexes in your
original ( especially if they are attached ). That's a waste of time as you
drop the table eventually anyway.
I usually use sapdba to have the scripts for the reorg generated ( not
executed ). I than change the fragmented clause manually and execute the
scripts. This way, you easily have the views script ready as well.

Christoph



-----Original Message-----
From: Tseng, Luke [mailto:Luke.Tseng@BancTec.com]
Sent: Monday, April 07, 2003 6:32 PM
To: sapmix@iiug.org
Subject: RE: 32 GB per dbspace limitation [95]


Marco,

I did use method 1 & 2 to fragment large tables, size >24GB, and it took 3+
hours
to finish on our production system. I would like to learn more about your
quick way, using "ipload and piping the data", please advise me where I can
find more information about it and why it limited to tables without BLOBS
fields? I'm having trouble to fragment CDCLS table which have byte field
(it'll take > 12 hours to fragment 24GB size). Your help will be my highly
appreciation.

Luke

-----Original Message-----
From: Krijt, Marco [mailto:Marco.Krijt@VendexKBB-ITS.nl]
Sent: Monday, April 07, 2003 1:22 AM
To: sapmix@iiug.org
Subject: RE: 32 GB per dbspace limitation [94]


You can use more options:

Not quick:

run dbschema;
drop index <tableindexes>;
alter table * fragment by round robin in <dbspace1>, <dbspace2>......;
create indexes in <indexdbspace>; (you have to use a detached index) use
pdq.

Not quick:

run dbschema;
drop index tableindexes;
create a copytable (by example table_x), use dbschema for that and don't use
the indexrows yet.
select * from <org. table> insert into <copy-table>;
check rows in both tables;
drop table <org. table>;
rename table <copy-table> to <org. table>;
create indexes;

QUICK
run dbschema (dbschema -d <db> -t <table> -ss <table.schema>)
drop indexes from the table (if it are attached indexes it can run for a
long time)
create a copytable (by example table_x)
Use ipload and pipes to copy the data from the source tabel to the target
table,
the table may not have blobs.
(This manner is very quick, but I can't explain all of it here I had by one
table 90GB/hr)
create indexes (from the dbschema with PDQ), that very quick, mostly minutes
instead of hours). You can use onmode -Q, onmode -M, onmode -S to chnega the
PDQ-values, which you can see with onstat -g mgm.

export PSORT_NPROCS=4
export DELIMIDENT=1
export PDQPRIORITY=100

dbaccess <db> create_indexes

Marco Krijt
Medewerker Operationeel Beheer SAP
Vendex KBB IT Services b.v.
Frankemaheerd 6
1102 AN Amsterdam Z.O.
Postbus 12644
1100 AP Amsterdam Z.O.
> Locatie: 6.1.13
Tel Spaklerweg: 020-5959672
> Tel: +3120-3129254
> Tel afdeling: +3120-3129291
> E-mail: marco.krijt@vendexkbb-its.nl
> E-mail afdeling: sapbeheer@vendexkbb-its.nl
>






-----Original Message-----
From: rarmour@superioruniformgroup.com
[mailto:rarmour@superioruniformgroup.com]
Sent: vrijdag 4 april 2003 18:54
To: Krijt, Marco; sapmix@iiug.org
Subject: RE: 32 GB per dbspace limitation [91]


How long does it take to fragment a 27GB table?

> -----Original Message-----
> From: Krijt, Marco [SMTP:Marco.Krijt@VendexKBB-ITS.nl]
> Sent: Friday, April 04, 2003 2:10 AM
> To: sapmix@iiug.org
> Subject: RE: 32 GB per dbspace limitation [91]
>
> All 731 Versions (or older):
>
> There is a limitation of 32GB, but this is not a dbspace limitation,
> but a TABLE or INDEX limitation.
>
> If you have a table which will be bigger then 32GB,
> you have to fragment it by round robin, or expression based,
> among more dbspaces. If an index will be bigger then 32GB you have to
> fragment it expression based (round robin is not possible for an index).
>
> We have dbspaces which are about 90 GB.
> So don't look at your dbspace size but at your table size.
>
> Some limitations:
> Index 32 GB
> Table 32 GB
> chunks 633 active, 2047 maximum (onstat -d|tail)
> Memory 32-bits informix (about 3,75 GB) SHMVIRTSIZE+BUFFERS
>
> Marco Krijt
> Medewerker Operationeel Beheer SAP
> Vendex KBB IT Services b.v.
> Frankemaheerd 6
> 1102 AN Amsterdam Z.O.
> Postbus 12644
> 1100 AP Amsterdam Z.O.
> > Locatie: 6.1.13
> Tel Spaklerweg: 020-5959672
> > Tel: +3120-3129254
> > Tel afdeling: +3120-3129291
> > E-mail: marco.krijt@vendexkbb-its.nl
> > E-mail afdeling: sapbeheer@vendexkbb-its.nl
> >
>
>
> -----Original Message-----
> From: Tseng, Luke [mailto:Luke.Tseng@BancTec.com]
> Sent: vrijdag 4 april 2003 0:42
> To: sapmix@iiug.org
> Subject: 32 GB per dbspace limitation [89]
>
>
> I keep hearing the limitation of 32 GB per dbspace, I wonder any defect or
> document referred to? I asked Informix Tech Support about it, they said
> there are only 2GB per chunk and 1023 chunks per IDS instance limitations
> for versions before 9.4(9.3?). Can anyone share some light on it? Thanks.
>
> Regards,
> Luke
>

----------------------------------------------------------------------------
-------------------------------------
This email transmission and any documents, files or previous email
messages attached to it may contain information that is confidential or
legally privileged. If you are not the addressee or authorized to
receive this message for the addressee, you may not use, copy or
disclose to anyone this message or its contents. If you have received
this message in error, please notify the sender immediately and
permanently destroy any copies you may have.

Messages In This Thread

• RE: 32 GB per dbspace limitation
  -- Monday, 21 April 2003, at 4:36 p.m.

SAP Mix is maintained by Administrator with WebBBS 5.12.