How to auto-increment dbspace size?

[Former Member]

Hi, is there any option to auto-increment all the sizes related to database files like the database itself, the transaction log and the dbspace files?

Thank you

[tayeb_hadjou · Accepted solution]

Hello Pedro,

db file and transaction log files are self-expanding up to their file system size limit.

For user dbspace files, no autoincrement option in create/alter dbspace commands.

Alternativally, you can create IQ events to auto-manage space.

Regards,

[c_baker]

Keep in mind that when increasing IQ dbspace size you can increase the original dbspace dbfiles if they were created with reserve space and are on file system (IQ will take the complete partition for raw devices).

When increasing dbspace size by adding dbfiles, don't just add 1 at a time.  You need to consider the I/O and best practices and perhaps add more than one to ensure I/O is striped properly across the dbfiles otherwise, you run the risk of a bottleneck as all I/O targets the new dbfile while the others are full.

You may need to also consider recreating/rebuilding indexes to stripe the I/O across the new and existing dbfiles.

This is part of the reason why an auto-increment of IQ dbspaces is not a simple process - you need to understand the underlying I/O and how your existing dbspace dbfiles are used.  e.g. if you are building up versions, and not actually adding more data, then you would need to look instead at the applications and the 'autocommit/autoprecommit' settings of a connection, etc.to perhaps reduce the version build up instead.

For the .db and .log  files, they are on file system - both increase their sizes automatically as required.  For the .log, you can reduce its size by using one of the backup options (i.e. command line backup or dbbackup executable).  Although you can add another SYSTEM dbspace for the .db file in SQLA, it is really unnecessary for IQ.  IF you are actually storing tables in SYSTEM dbspace instead of IQ for transactionality, then you should be looking into the RLV dbspace instead.  It is not generally recommended practice to use SYSTEM dbspace for table storage

Chris

[kimon_moschandreou]

Hi,

I don't think there is any option for this, however you can use events. Concerning the .db and .log files there are system events, check DocCommentXchange and DocCommentXchange for a list of the events. For dbspaces I think the only solution could be to write an event that caclulates the free space and then expands (if possible) the dbspace.

Regards,

Kimon