Customizing - Advanced
Use the following parameters to customize the selected data source before being cloned.
Configuration file parameters are included below with the following_font.
Note
Changing the parameters described in this page requires that you run a Customize or Redefine command to propagate the changes to the corresponding bits in the Client control tables various options columns. If you use a Redefine command you need to use Redefine with options from the Advanced button on the data sources page and enable the Redefine all data sets (-R) option.
Global data set options
Clear duplicate records encountered during data extraction
Parameter: clr_dup_extr_recs
This parameters defines the initial value for the data set property that causes the Client to remove false duplicate records after the index creation fails. The duplicate records are caused by long cloning of an active DMSII database. Clearing the duplicate records allows the index creation and fixup phases to continue. If false duplicate records aren't manually removed, index creation will fail. Running a Customize or Redefine with options command sets the ds_options bit that corresponds to the data set property Clear Duplicate Extract Records for all DATASETS table entries. Using the Customize command allows this option to control individual data sets.
Caution
Using this option is not recommended when you created a composite key or a GenFormat primary key, as the keys you created might result in duplicate records. Running the script in this case will get rid of all the duplicate records and force you to re-clone to recover the duplicate records. It is recommended to find out what the duplicate records are and determine if your index is good before running this script.
Force AA Values as indexes
Parameter: force_aa_value_only
This set of buttons allows you to define the default value of the property that makes datasets use the AA values or record serial numbers (RSNs) as the source of the index instead of the SET picked by the Databridge Engine. See below for an explanation of the available options.
- Do not force: the Define/Redefine and Customize commands will honor the Databridge Engine's decision on whether or not to use AA Values.
- Always force: forces the Define/Redefine and Customize commands to use AA values as the index, even if the data set has a SET that qualifies for use as an index.
- Force only if RSN: forces the Define/Redefine and Customize commands to use RSN values (when they exist) as the index.
Ignore new columns
Parameter: suppress_new_columns
When this parameter is set to True, the Define/Redefine and Customize commands
set the active column to 0 for new DATAITEMS and DATATABLES entries that are associated
with the reorganization. If you later decide that you want to include
such columns, you must disable this parameter and run a Redefine
command by clicking Redefine (with options) from the Advanced button on the data sources page
and enable the Redefine All Data Sets (-R) option, unless you are using the Customize command.
Update changed columns only
Parameter: minimize_col_updates
This option specifies whether the Define/Redefine and Customize commands should set the ds_options bit that corresponds to the data set property Update changed columns only for all DATASETS Client control entries. This bit indicates that Client will only update columns whose values have
changed. To do this, stored procedures are abandoned in favor of pure
SQL without the use of host variables. This usually slows down the update
speed of the Client considerably. However, when using SQL Server or Oracle replication,
the overall process ultimately takes less time because significantly
less data is sent to the remote database during the replication.
Caution
Using this parameter will significantly slow update processing by the Client. If you are replicating your relational database, enabling this feature may improve performance if replication is very slow. Do not set this parameter to True under any other circumstances.
Optimize SQL updates
Parameter:optimize_updates
This option eliminates all redundant updates. Use this option when there is a large number of
occurrences for items when OCCURs clauses are not flattened. An OCCURS clause is a DMSII construct that describes the
number of times an item is present in a data set. This parameter
globally sets the ds_options bit that corresponds to the data set
property Optimize SQL Updates for all DATASETS table entries.
Split variable format data sets
Parameter: split_varfmt_dataset
The Client normally stores each record type in a table whose name is the data set name followed by "_typennn" (where nnn is the variable format record type). The table for type 0 records, which have no variable part, does not have a suffix like the tables for all other record types. All record types start off with the fixed part which is followed by the variable part, except the type 0 records that have no variable part.
This option provides an alternate way for mapping variable format data sets to tables in the relational database. The fixed parts of all variable format records are stored in the table for type 0 records. The keys and variable parts of all the remaining record types are stored in the corresponding tables.
This option globally sets the ds_options bit that corresponds to the
data set property Split variable format data sets for all DATASETS Client control table entries.
Use stored procedures in updates
Parameter: use_stored_procs
This parameter makes the process and clone commands generate the
actual SQL command instead of a stored procedure call to perform an
update. The Client still uses host variables, as was the case with
stored procedure calls. Executing the SQL directly eliminates some
overhead and makes processing the update quicker.
After executing a Redefine or Customize command, the Administrative Console will ask you to run a Reorganize command, which generates a new set of scripts for creating the tables. It also refreshes the stored procedures for all data sets by dropping them if no longer needed, or by recreating them if needed.
Table layout
Flatten all OCCURS
Parameter: flatten_all_occurs
Defines the initial value for the di_options bit in the DMS_ITEMS table entries that corresponds to the Flatten all OCCURS property..
This option creates a new column in the primary table for each OCCURS item. Enable this option if the DMSII data contains a lot of OCCURS clauses that you want to flatten. When this parameter is disabled, a DMSII data set that has an OCCURS clause in an item will be placed in a secondary table in the relational database. As a result, a single DMSII update can lead to updating multiple tables multiple times, as each occurrence of the item is placed in a separate row in the secondary table.
Maximum columns in tables (1 minimum to 1024 maximum)
Parameter: maximum_columns
Use this option to limit the maximum number of columns that can be created in split tables. Split tables are created from a data set that exceeds the maximum number of columns allowed by the relational database. For SQL Server, this number is 1024. If you set this parameter to 1000, the split will occur after 1000 columns.
Indexes
Use clustered indexes
Parameter: use_clustered_index
(SQL Server only) Enable this option to use clustered indexes for all tables. To override this option for a single table, disable the Use Clustered Index check box in the Relational Properties page for the table when using the Customize command.
Use Primary Keys
Parameter: use_primary_key
This option creates a primary key instead of using a unique index for all tables. To override this option for a single table, disable the Use Primary Keys option in the Relational Properties page for the table when using the Customize command.
Table reorganization options
Use internal clone for reorganizations
Parameter: use_internal_clone
This option affects the Redefine, Customize and Reorganize commands. Instead of using ALTER commands to add, delete or modify columns in tables, the Client uses a combination of scripts and table renaming commands to create new copies of the tables with new layouts. The Client then copies the data using SELECT INTO for SQL Server and CTAS (Create Table As Select) for Oracle.
This operation works like the bulk loader and is faster than using ALTER and UPDATE commands on large tables, but more importantly, the command is not logged.
Note
The only drawback to this method is that it requires increased free disk storage to hold a second copy of the table for the duration of the operation. This method is recommended over re-cloning.
Reorg command update batch size (5000 minimum to 100000 maximum)
Parameter: reorg_batch_size
This option determines the size of the transactions that the Client uses during a Reorganize command to set the value of newly-added columns to their initial values, as defined in the DASDL. The Redefine and Customize commands create a reorg script that uses a stored procedure to do the updates in batches that are executed as transactions. For large tables, this process can require more time, but it does not run the database out of log space. Consider using the internal clone option instead.
Do not set initial values for new columns
Parameter: inhibit_init_values
Inhibits the Reorganize command from setting new columns to their INITIALVALUE in DMSII. As a result, the new columns will start off as being NULL.