IDXBUILD with -datascanthreads >1 fails when run against table partition enabled database with unique global index.

« Go Back

Information

Title	IDXBUILD with -datascanthreads >1 fails when run against table partition enabled database with unique global index.

URL Name	idxbuild-with-datascanthreads-1-fails-when-run-against-table-partition-enabled-database-with-unique-global-index

Article Number	000181405

Environment	Product: OpenEdge Version: 11.5.x, 11.6.x OS: All supported platforms Other: Table Partitioning

Question/Problem Description

IDXBUILD fails before completion on a Table Partition enabled database when using -datascanthreads with any value higher than 1
Building indexes with -datascanthreads >1 fails when run against a Table with Partitioned data across multiple Storage Areas
IDXBUILD using -datascanthreads fails on UNIQUE GLOBAL indexes

Steps to Reproduce	Configure a database with a TP table using UNIQUE GLOBAL indexes. Partitioned data across several Storage Areas. Run IDXBUILD with multiple sort groups and -datascanthreads greater than 1 STEPS: Find attached 000181405TPrepro.zip $ prostrct create sportstest sports2000.st $ prostrct add sportstest sportstest_add.st $ procopy $DLC/empty sportstest $ pro sportstest -p _dict.p .- Load the order_tp.df .- Load the add_index_ordertp.df $ proutil sportstest -C enabletablepartitioning $ pro sportstest -p create_policy_list_regstatus.p $ proutil sportstest -C load ./order.bd $ proutil sportstest -C idxbuild -TB 4 -TM 4 -SG 64 -B 5000 -SG 64 -TM 32 -TB 64 -TMB 64 -TF 10 -thread 1 -threadnum 4 -TF 5 -datascanthreads 4 -mergethreads 4 .- Choose all indexes: A .- Confirm there is enough space for sorting: Y IDXBUILD fails with the message: Index rebuild did not complete successfully

Steps to Reproduce

Configure a database with a TP table using UNIQUE GLOBAL indexes.
Partitioned data across several Storage Areas.
Run IDXBUILD with multiple sort groups and -datascanthreads greater than 1

STEPS: Find attached 000181405TPrepro.zip

$ prostrct create sportstest sports2000.st
$ prostrct add sportstest sportstest_add.st
$ procopy $DLC/empty sportstest
$ pro sportstest -p _dict.p
.- Load the order_tp.df
.- Load the add_index_ordertp.df
$ proutil sportstest -C enabletablepartitioning
$ pro sportstest -p create_policy_list_regstatus.p
$ proutil sportstest -C load ./order.bd
$ proutil sportstest -C idxbuild -TB 4 -TM 4 -SG 64 -B 5000 -SG 64 -TM 32 -TB 64 -TMB 64 -TF 10 -thread 1 -threadnum 4 -TF 5 -datascanthreads 4 -mergethreads 4
.- Choose all indexes: A
.- Confirm there is enough space for sorting: Y
IDXBUILD fails with the message: Index rebuild did not complete successfully

Clarifying Information

Error Message	Database log contains message: Index rebuild did not complete successfully.

Defect Number	Defect PSC00355202

Enhancement Number

Cause

The per data scan thread sort buffer is not being cleared properly for all data scan threads, which causes an attempt to insert duplicate entries into the unique global index and failing.
Depending on the number of Sort Groups [-SG] specified, building indexes fails with an insertion error or causes the index rebuild to stop prematurely.

Resolution

Upgrade to OpenEdge 11.7.0, 12.0, where the Data Scan Thread sort block entries per thread are cleared after index entries are added to the merge blocks (-mergethreads)

It is recommended to upgrade to at least OpenEdge 11.7.6, 12.2.1.0, 12.3, especially in environments using double-byte codepages due to another issue with the threaded datascan phase, which is reworked to assure each Data Scan Thread doing the sort/convert to build keys acquires a mutex to assure they're thread safe. For further information refer to Article 000118383:

IDXBUILD using -datascanthreads causes corruption in index keys.

Workaround

When running an IDXBUILD against a Partition enabled table which has a Unique Global Index:

Option 1: Use -datascanthreads with a value of 1.
Option 2: Do not use the datascanthreads parameter or explicitly disable: -datascanthreads=0

Notes

Keyword Phrase

Last Modified Date	3/21/2021 1:35 PM