Hi Guru,
I'm the informix 9.40, 64-bit machine. As I do monitor the table and found the two tables reaching 64 gb - (ppoix 49 Gb & bsis 44 Gb). What should I thing now for the two table ? What approach should I decide ? Please help.

fragment.the.tables.

Hi All,
if the table get fragmented. If there effected to application program, eg. Abap/4 program, access method sequential or indexing ?

How could I do fragment the table? Any example could I refer ? could I do fragment the table by reorganization process ?

Hi,

No, infact there will be improvement in performance. You can also frament the index. Fragmentation can be done in 2 ways, one is round robin and other is based on expression. Based on ur requirement, fragment the table on multiple dbspaces(preferably on diff disks) and also the index.

Bye

Nitin

The apps. won't be afected... in most cases they will get better performance!. BTW: if you can put the database in non-logging mode, or get enough logical log space to hold your entire table (before LTXHWM is reached).

The sintax is something like this:
Round robin:
alter fragment on table your_table
init fragment by round robin in dbspace1, dbspace2, etc
(it will keep the table indexes in the first dbspace)
(and I'm not sure if your actual table will be reorganized)

Expression:
alter fragment on table your_table
init fragment by expression
(expression: for example column1<0 ) in dbspace1,
(expression: for example column1>0 and column1 < 100 ) in dbspace2,
...
(optional) remainder in dbspace3;

The table indexes will also be fragmented, unless you fragment them with other expressions:
alter fragment on index your_index
init fragment by expression
index_column1<0 in dbspace1,
index_column1>=0 in dbspace2

Keep in mind that every fragment will inherit the extent sizes you establish in the table creation.

Important: read the IDS Adiminstrator's Performance manual before you decide anything!.

And remember to use the -ss option for your dbexports/dbschemas!

Hope it helps.

Hi All,
I'm very sorry and not very sure about the process.
Q1 Is the dbspace need to be create fist? eg: dbspace1, dbspace2, etc.

Q2: are the existing data records will be automatically copied over as the command "alter fragment on table your_table
init fragment by round robin in dbspace1, dbspace2, etc" ?

Thank you

yes and yes

I would create 8 different dbspaces.

Hi,
The name of dbspaces must be begin with "psap".
Look this scripts:
#!/bin/ksh
export DELIMIDENT=1
log=fragment.log
echo "Inicio procedo de fragment `date`" >$log
dbaccess qp1 1>>$log 2>&1 <<EOF
set pdqpriority 80;
alter fragment on table bsis init
fragment by round robin in
psapbsis1,psapbsis2,psapbsis3 ;
!echo "Fin de fragment `date`"
!echo "Inicio de Indice 1 `date`"
create unique index "sapr3"."bsis~0" on "sapr3".bsis
(mandt,bukrs,hkont,augdt,augbl,zuonr,gjahr,belnr,b uzei) in psapbsisidx;
!echo "Inicio de Indice 2 `date`"
create index "sapr3"."bsis~z01" on "sapr3".bsis
(mandt,bukrs,hkont,gjahr,monat) in psapbsisidx;
!echo "Inicio de Indice 3 `date`"
create index "sapr3"."bsis~z02" on "sapr3".bsis
(mandt,bukrs,hkont,budat) in psapbsisidx;
!echo "Inicio de Indice 4 `date`"
create index "sapr3"."bsis~z99" on "sapr3".bsis (mandt,xarch) in psapbsisidx;
!echo "Fin de Indices `date`"
EOF
echo "Fin proceso de fragment `date`" >>$log

Gustavo.

Hi,
Excuse me, remember that the database must be without logging.

Gustavo.

Dbspaces must be created first.
The dbspaces names don't need anyting special (apart from the care not to use reserved words).
You can fragment a table in 2 to 32767 dbspaces (up to 9.40 you can't create more than 2047).
You can fragment an index in 2 to 32767 dbspaces (up to 9.40 you can't create more than 2047).
You can have 1 index fragment and 1 table fragment in the same dbspace (that's called a detached index - you can create it detached or detach it later).
You can do fragmentation on a logged database. But it's highly advisable to disable it, for the time you will save and the long transactions that could last forever to rollback, or the total size of the logical logs you need: every single row movement will be logged!.

Hope it helps

Hi,
Attention please, if you work with SAP, then the new dbspace must be begin with "psap".

Gustavo.