Method for performing a batch process on structured data

A method for performing a batch process on transactions in multiple tables, the multiple tables including a first and a second source table, and a first and a second processing table which are generated from the respective first and second source tables, the first source and first processing tables having a plurality of account balances posted thereto in respective rows of the tables, each account balance being associated with a natural key, the second source and second processing tables having a plurality of transactions posted thereto in respective rows of the tables, each transaction being associated with a natural key, and each transaction having at least (i) a transaction date, (ii) a transaction amount, and (iii) a transaction identifier, the method comprising:

• (a) for the second processing table, generating sequence values for each set of transactions in the second processing table that has the same natural key, the sequence values being ordered by their respective transaction date, the sequence values beginning with (i) a lowest value, followed by sequentially increasing values, or (ii) a highest value, followed by sequentially decreasing values;
• (b) ignoring for further processing any rows in the first processing table that do not have account balances with the same natural key as a transaction in the second processing table, and ignoring for further processing any rows in the second processing table that do not have transactions with the same natural key as an account balance in the first processing table;
• (c) for all rows in the second processing table wherein the sequence value equals (i) the lowest value for sequentially increasing values, or (ii) the highest value for sequentially decreasing values, updating the transaction amount for each natural key in the second processing table, and updating the account balance for each natural key in the first processing table, the updated transaction amounts and account balances in each of the first and second processing tables being either a zero, or non-zero value;
• (d) updating the account balance for each natural key in the first source table using any updated account balances in the first processing table, and updating the transaction amount for each natural key in the second source table using any updated transaction amounts in the second processing table,
• wherein the updating in the first source table uses the natural keys of the first source table and the first processing table to identify the appropriate rows in the first source tables for updating, and wherein the updating in the second source table uses the natural keys and the transaction identifiers of the second processing table and the second source table to identify the appropriate rows in the second source tables for updating;
• and
• (e) iteratively repeating steps (a)-(d), the iteration including regenerating the first processing table with any rows in the corresponding first source table wherein the account balance is a non-zero value, and regenerating the second processing table with any rows in the corresponding second source table wherein the transaction amount is a non-zero value, the regenerated first and second processing tables being used in steps (a)-(d), wherein the iteration repeats until either the first processing table has no further rows wherein the account balance is a non-zero value, or the second processing table has no further rows wherein the transaction amount is a non-zero value.

A method for performing a batch process on structured data in multiple tables, the multiple tables including a first and a second source table, and a first and a second processing table which are generated from the respective first and second source tables, the first source and first processing tables having one or more fields of structured data posted thereto in respective rows of the tables, the one or more fields of structured data being associated with a natural key, the second source and second processing tables having a plurality of structured data table entries posted thereto in respective rows of the tables, each structured data table entry being associated with a natural key, the method comprising:

• (a) for the second processing table, generating sequence values for each set of structured data table entries in the second processing table that has the same natural key, the sequence values being ordered by a sort order of one or more columns of the second processing table other than the natural key, the sequence values beginning with (i) a lowest value, followed by sequentially increasing values, or (ii) a highest value, followed by sequentially decreasing values;
• (b) ignoring for further processing any rows in the first processing table that do not have structured data with the same natural key as a structured data table entry in the second processing table, and ignoring for further processing any rows in the second processing table that do not have structured data table entries with the same natural key as structured data in the first processing table;
• (c) for all rows in the second processing table wherein the sequence value equals (i) the lowest value for sequentially increasing values, or (ii) the highest value for sequentially decreasing values, updating the structured data for each natural key in the first processing table with the corresponding structured data table entries having the same natural key in the second processing table;
• (d) marking the row of each updated structured data table entry in the second processing table with a status indicating that the structured data table entry was processed;
• (e) updating the structured data for each natural key in the first source table using any updated structured data in the first processing table, and updating the structured data table entry for each natural key in the second source table using any updated structured data table entries in the second processing table,
• wherein the updating in the first source table uses the natural keys in the first source table and first processing table to identify the appropriate rows in the first source tables for updating, and wherein the updating in the second source table uses the natural keys and one of the columns of the second processing table and the second source table to identify the appropriate rows in the second source tables for updating;
• and
• (f) iteratively repeating steps (a)-(e), the iteration including regenerating the first processing table with the updated rows of the first source table, and regenerating the second processing table with any rows in the corresponding second source table wherein the structured data table entry is not yet marked as being processed, the regenerated first and second processing tables being used in steps (a)-(e), wherein the iteration repeats until the second processing table has no further rows with a structured data table entry that is not yet marked as being processed.