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 Infor10 ERP Enterprise Server (LN) Technical Manual (U8172 US).

Selection Range

Company, Package, Table

Fill in a range where you want to reorganize the tables for. It is also possible to fill in 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

If you select this check box, you must also select Repair Reference Counter. For performance reasons, also select Repair only reference of parent.
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.

Path Dumps

If this check box is selected, you can specify a directory where the session stores its temporary dumpfiles. By default $BSE/tmp is used.

Path Dumps

The directory where the session stores its temporary dumpfiles. By default $BSE/tmp is used.

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 the following form:

The following table lists 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 will contain 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 will contain error code 114.

For more information on error codes, refer to Chapter 6 "Error Codes" in the Infor10 ERP Enterprise Server (LN) Technical Manual (U8172 US).

Note

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

Repair Indices Only

Recreates the indexes of the selected tables by dropping and creating them again. The Package Combination of the selected Companies must be the same as the current Package Combination. Only one of the checkboxes “Data and Indices” and “Repair Indices only” 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. Existing indices are not deleted and recreated, only non existing indices are created.

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.
after you have rebuilt the data and indices of the selected tables. If you select the Data and Indices check box in this session, you must also select Repair Reference Counter.

Check/Repair only reference of parent

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, e.g. 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.

Notes

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 following field.

File for undefined references

The name of the file in where the session stores the undefined references.