Regen a database dictionary

You can regenerate an IR index in the database dictionary by using System Definition or by using the Database Dictionary utility.

Choose one of the following methods to regenerate an IR index.

Regenerate an IR index using System Definition

To regenerate an IR index using System Definition:

1. From the System Navigator, click System Definition > Tables.
2. Double-click the table containing the IR index you want to regenerate. The overview of the table opens.

3. In the Table management section, click Regenerate IR Index.

   The Regen IR Index window opens.

4. Specify the schedule parameters, as follows:
   • In the When do you want regen to occur? field, select whether to start Now or Later. If you select later, enter when the mm/dd/yyyy hh:mm:ss format.
   • In the Repeat Interval field, select an option if the database needs to be regenerated on a regular basis. This setting is optional. The interval period starts from the time and date set in the "When do you want regen to occur?" field.
     • Monthly – regenerates once a month.
     • Quarterly – regenerates every three months.
     • Semi-annually – regenerates every six months.
     • Annually – regenerates once a year.
     • Other – regenerates in the specified number of days, at the specified time.
       
       Use the dd hh:mm:ss format, where dd is the number of days from the initial date and time, and hh:mm:ss is the time of day at which the regen is run. For example, 26 10:00:00 sets the regen to run at 10 a.m., 26 days from the initial regeneration set in the Date/Time to run field.
5. Click OK.

Regenerate an IR index using the Database Dictionary utility

Using the Database Dictionary utility to regenerate an IR index enables you to schedule the regen.

To regenerate an IR index using the Database Dictionary utility.

1. Open the database dictionary.

2. Open the More Actions menu and choose Regen IR.

   Note: This option does not appear for files that contain no data records.

   You are prompted to confirm this regen action.

3. From the prompt, do one of the following:
   • Click OK to confirm this regen and erase all records in this file.
   • Click Cancel to quit and return to the database dictionary.
   • Click Schedule (calendar) to schedule the time to begin Regen.
     • In the Date/Time to run field, provide a date in the DD/MM/YYYY format and time in the HH:MM:SS format.
     • If you choose the optional Repeat Interval option to regenerate the database on a regular basis, the interval period starts from the Date/Time to run you just set.
       • Monthly – regenerates once a month.
       • Quarterly – regenerates every three months.
       • Semi-annually – regenerates every six months.
       • Annually – regenerates once a year.
       • Other – regenerates in the specified number of days, at the specified time.
         
         Use the dd hh:mm:ss format, where dd is the number of days from the initial date and time, and hh:mm:ss is the time of day at which the regen is run. For example, 26 10:00:00 sets the regen to run at 10 a.m., 26 days from the initial regeneration set in the Date/Time to run field.
       • Click Schedule. to confirm this database regeneration action.

4. If you clicked OK, the regen performs immediately in the foreground. When completed, Service Management displays a message confirming the time/date of the regen and the removal of all records.

   Note: The asterisk (*) at the beginning of the message indicates there are additional messages related to this operation.

5. View all messages.
6. Read the messages and identify any errors that occurred during the operation.

Note: When you run an IR Regen, Service Management needs to clear the current indexes. This causes a delete to the backend database. When Service Management attempts to delete a large set of records, the database could run out of space for this activity and cause an error. Each supported database platform provides some type of transaction or undo log to back up data changes and allow a rollback in case of errors. If you encounter such an error, please contact your database administrator and ask them to increase the size available for this purpose.

Some examples of the errors you could encounter are:

Oracle:SQL code=30036 message=ORA-30036: unable to extend segment by % in undo tablespace

SQL Server SQL State: 42000-9002 Message: [Microsoft][SQL Native Client][SQL Server]The transaction log for database '%' is full

DB2 SQL State: 57011--964 Message: [IBM][CLI Driver][DB2/NT] SQL0964C The transaction log for the database is full. SQLSTATE=57011