Data Express parameter Commit Size(MBs) in the dxeconfig.cfg file

Problem:

During the masking/extraction/subsetting  of some real production databases there are troubles with the large number of inserts on the target database within the same transaction.

The biggest tables in the customer’s databases have millions of records and it is very common to have huge transaction logs (many Gigabytes each).  The customers can have a very limited temporary area and there are  no intermediary commits before fulfilling this area on large datasets/tables. More frequent commits on the databases can help not to impact the destination server.

Resolution:

A new parameter  is added  in the dxeconfig.cfg file, identifying how often we want the commit to happen, in MBs.

The format of the parameter is: 

Commit Size(MBs) [user range = from 1 to 4000, default range = 0]:0    

The default value is 0, the accepted values are from 1 to 4000. If a value greater than 4000  is  specified, the value 4000  will be used to calculate the commit size in megabytes. 

Currently the default commit size is about 95 MB (100,000,000 bytes), so if the value of the new parameter is left to 0,  the commit will be performed every 95 MBs.

Example of dxeconfig.cfg: 

* Data Express Configuration File

LogLevel:1

Always Drop Table:N

CSV Data Store:N

COBOL Masking:Y

Validate Target Table:Y

Validate Source Schema:Y

Enable Logging:N

Hide Progress:N

Hide Log Message:N

Use Extended C Masking:Y

Trim Spaces for ODBC Char Columns:N

Commit Size(MBs) [user range = from 1 to 4000,default range= 0]:50     

* These entries are only applicable to the Oracle Extension.

Text Output:N

Data Separator:,

Fields Enclosed By:"

Identify Externally:N

Use Append Condition:N