Reorganize Tables (ttaad4225m000)

Use this session to rebuild data and indices and/or to check and rebuild the referential integrity.

Rebuild data and indices

Rebuilding data and indices is useful for tables on which many delete actions are performed. To use this option, you must select the Data and Indices check box. The data will be temporarily stored in a sequential dump file. Subsequently the data and indices will be rebuilt.

Check and rebuild the referential integrity

A table field may have a reference to another table. For example, the "Invoice-from Business Partner" (ifbp) field in the Material Costs (tpppc211) table refers to the Business Partners (tccom100) table. If you try to delete a business partner, the system checks whether the business partner is used in other tables. This is done by checking the reference counter for that business partner. If the reference counter is zero, the record is not used anymore and can be deleted. In this session, you can repair these reference counters, check referential integrity and nullify undefined references. Refer to the field help for details.

You must repair the reference counters, for example, after running the Create Table from Sequential Dump (ttaad4227m000) session with the Ignore Referential Integrity Constraints check box selected. The import process sets the reference counters of all imported records to 0. This is a wrong situation, because now you can delete records, even if they are used in other tables. To repair the counters, you must run the current session with the Repair Reference Counter check box selected.

Executables

Internally the session uses the following executables:

  • bdbreconfig6.2 (-Z option): to rebuild data and indices
  • refint6.2: to check and rebuild the referential integrity

For more information on these executables, refer to the Infor Enterprise Server Technical Manual.

Selection Range
Company, Package, Table

Specify a range where you want to reorganize the tables for. You can specify one company number or one table.

Options
Data and Indices

Select this check box to rebuild the data and indices of the selected tables. This is useful for tables on which many delete actions are performed.

Note: The data of the selected tables will be temporarily stored in a sequential dump file. Subsequently the data and indices will be rebuilt. This may take a lot of time.
Print results (bdbreconfig)

If this check box is selected, the session prints a report that describes, for each table and company, the result of the reconfiguration.

Each line in the report has this form:

<tablename><compnr><space><status code><space><error><space><message text><newline>

This table shows the possible status codes:

Status Code Description
R0 The table is reconfigured successfully. The Error field contains the value 0, or contains error code 506 if the table does not exist.
R1 The reconfiguration has failed. The Error field contains the code of the error that caused the failure.
R2 The table is reconfigured successfully, but one or more indexes could not be created. The Error field contains error code 114.

For more information on error codes, see "Error Codes" in the Infor Enterprise Server Technical Manual.

Note

You can only access this field if the Data and Indices check box is selected.

Path Dumps

If this check box is selected, you can specify a directory where the session stores its temporary dumpfiles.

Path Dumps

The directory where the session stores its temporary dumpfiles. By default $BSE/tmp is used. You must specify the ${BSE}/appdata folder when working in the Cloud.

Exclude Logical Tables

If this check box is selected, data and indices of logical tables are not rebuilt.

Drop and recreate Indices Only

Recreates all indices of the selected tables.

The package combination of the selected companies must be the same as the current package combination. Only one of the Data and Indices and Repair Indices only check boxes can be selected.

Logging can be found in the logfile: ${BSE}/log/log.ttaad4225

Create Missing Indices Only

If this check box is selected, missing indices are created.

Exclude Logical Tables

If this check box is selected, indices of logical tables are not recreated.

Compress/Uncompress Data and Indices

If this check box is selected, compressed data or indices in the database become uncompressed; uncompressed data or indices become compressed.

Note: This takes a substantial amount of time, during which the system must be offline. Therefore you must plan this action carefully. While the session creates the tables and indices anew, the compress status of the tables and indices is also changed. Therefore the processing takes longer.

You can use the Compress/Uncompress Data and Indices option to compress or uncompress existing tables. The Storage Parameters Optimization (ttdba0132m000) session also contains a Compress option. This option determines how new tables are created: compressed or uncompressed. After this option is switched on, and a Convert to Runtime is performed in the Storage Parameters Optimization (ttdba0132m000) session, new tables are created with the compress option.

Print results (bdbreconfig)

If this check box is selected, the session prints a report that shows the results of the compress or uncompress action.

Exclude Logical Tables

If this check box is selected, data and indices of logical tables are not compressed or uncompressed.

Reference Integrity
Check Validity of Reference

If this check box is selected, the Check Validity of Reference is checked. You can specify the output file in the File for undefined references field.

Repair Reference Counter

To guarantee referential integrity, the LN Database stores reference counters, which indicate how many times a record is used in a parent child relation. The parent record can only be deleted if the reference counter is zero. (Reference counters are only applicable if the Delete Mode or Update Mode in the relation is Restricted (with counter).

It is essential to repair these reference counters:

  • after you have changed the logical table structure through the Logical Tables (ttaad4120m000) session.
  • after you have run the Create Table from Sequential Dump (ttaad4227m000) session or the Convert / Recover Tables (ttaad4228m000) session, with the Ignore Referential Integrity Constraints check box selected.
Repair Reference of Parent Only

If this check box is selected, the session repairs only the reference counters of tables in the selection range: for each record in a parent table, the session counts the corresponding records in the related child tables, and subsequently updates the reference counter in the parent table.

If this check box is cleared, the session repairs the reference counters for more tables than the ones that are specified in the selection range: counters are repaired for all tables being referred by the child tables of the tables in the selection range. For example: the selection range consists of (parent) table A only. A is referred by the child table B. B however also refers to another parent table C. The session repairs the reference counters for parent tables A and C. For more details, refer to the example below.

Example

The tttxt001 table has a lot of child tables. One of them is the Material Costs (tpppc211) table. The Material Costs table contains references to multiple tables: not only to tttxt001 (Language Independent Text Data), but also to various other tables, for example to tccom100 (Business Partners).

  • If the selection range is tttxt001 – tttxt001, and the Repair only reference of parent check box is selected, the session only repairs the reference counters for the tttxtt001 table.
  • If the selection range is tttxt001 – tttxt001, and the Repair only reference of parent check box is cleared, the session repairs the reference counters for tttxt001 and for all other tables that are referred by the child tables of tttxt001. Counters are repaired, for example, for tccom100, because tccom100 is referred by tpppc211, which is a child table of tttxt001.
Note: 
  • Many LN tables contain references to multiple parent tables, which in turn also contain references to multiple parent tables, and so on. Therefore, repairing the counters with this check box cleared can be very time consuming.
  • If this check box is selected, while the Repair Reference Counter check box is cleared, the session only checks the validity of reference for the specified parent tables to their direct child tables. Relations from these child tables to other parent tables are not checked.
  • If this check box is cleared, while the Repair Reference Counter check box is cleared, the session checks the validity of reference for the specified parent tables to their direct child tables. The session also checks relations from these child tables to other parent tables.
File for Undefined References

If this check box is selected, undefined references are logged in a file. You must specify the file name in the next field.

File for Undefined References

The name of the file in where the session stores the undefined references. You must specify the ${BSE}/appdata folder when working in the Cloud.

Soft Delete

If this check box is selected,'soft deleted' data is purged. You must specify a date and time in the next field.

For details on 'soft deleted' records, see Table Timestamp and Soft Delete Definitions (ttadv4136m000).

Purge data before

Specify a date and time. Only records that were 'soft deleted' before the specified timestamp are purged.