Hi,

I frequently see fragmentation in tables and indexes, even though I have activated the auto_crsd task there is still fragmentation.
Are there any other tasks needed?
How can I get the size of tables and indexes, including fragments?

Thanks in advance for any help,

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

#Informix

Hi Sergio,

I guess you're talking about intra-partition fragmentation, so multiple / too many extents in partitions and the (auto-)defrag functionality?

For the auto_crsd task, please be aware that its various capabilities can be turned on/off by specific switches in the ph_threshold table, and most are off by default (since the switches aren't even there in ph_threshold), if I'm right. 
Please refer to Scheduling data optimization for more details and/or ook at the related auto_crsd() SPL UDR definition in $INFORMIXDIR/etc/sysadmin/sch_tasks.sql for how exactly this works.

Regarding size of partitions/tables/indices, I'm sure Art has this in one of his many scripts and utilities ;-)  ... and it probably even will be in one of the discussions here.

BR,
 Andreas

------------------------------
Andreas Legner
------------------------------

Thanks for Reply Andreas,

I am afraid that I am not invoking the process in the correct way, however as I have tables that quickly get more than 30 extents I was convinced that these situations would be detected by auto_crsd...

Thanks for the info, I'll check as I'm using the default values.

Best regards,

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

Original Message:
Sent: Thu June 17, 2021 10:55 AM
From: Andreas Legner
Subject: Fragmentation issues

Hi Sergio,

I guess you're talking about intra-partition fragmentation, so multiple / too many extents in partitions and the (auto-)defrag functionality?

For the auto_crsd task, please be aware that its various capabilities can be turned on/off by specific switches in the ph_threshold table, and most are off by default (since the switches aren't even there in ph_threshold), if I'm right. 
Please refer to Scheduling data optimization for more details and/or ook at the related auto_crsd() SPL UDR definition in $INFORMIXDIR/etc/sysadmin/sch_tasks.sql for how exactly this works.

Regarding size of partitions/tables/indices, I'm sure Art has this in one of his many scripts and utilities ;-)  ... and it probably even will be in one of the discussions here.

BR,
 Andreas

------------------------------
Andreas Legner
------------------------------

So for "tables that quickly get more than 30 extents" you might want to (significantly) increase the "next size", by means of ALTER TABLE?

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Thu June 17, 2021 12:31 PM
From: Sergio Peres
Subject: Fragmentation issues

Thanks for Reply Andreas,

I am afraid that I am not invoking the process in the correct way, however as I have tables that quickly get more than 30 extents I was convinced that these situations would be detected by auto_crsd...

Thanks for the info, I'll check as I'm using the default values.

Best regards,

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------


Original Message:
Sent: Thu June 17, 2021 10:55 AM
From: Andreas Legner
Subject: Fragmentation issues

Hi Sergio,

I guess you're talking about intra-partition fragmentation, so multiple / too many extents in partitions and the (auto-)defrag functionality?

For the auto_crsd task, please be aware that its various capabilities can be turned on/off by specific switches in the ph_threshold table, and most are off by default (since the switches aren't even there in ph_threshold), if I'm right. 
Please refer to Scheduling data optimization for more details and/or ook at the related auto_crsd() SPL UDR definition in $INFORMIXDIR/etc/sysadmin/sch_tasks.sql for how exactly this works.

Regarding size of partitions/tables/indices, I'm sure Art has this in one of his many scripts and utilities ;-)  ... and it probably even will be in one of the discussions here.

BR,
 Andreas

------------------------------
Andreas Legner

Sergio:

The best way to see how much storage your tables and indexes are taking up is to use oncheck -pT. That report will give you everything you need to know.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------