I am aware of a number of performance issues with converting a large
amount of transactional data, specifically around the Key Generation
routines and the method whereby these jobs cannot be multi-threaded
(resulting in massive rollback segments as a single SQL statement
attempts to process all transactions in one hit).
As a result, I have looked into the ability to tune this conversion phase and recommend the following:
1. Review the code in CIPVBSGK (via AppViewer for an example of how CC&B conversion generates keys).
2. Consider dropping all indexes on the CK_* tables before running
KeyGen to reduce the amount of I/O (ensure that these are all rebuilt as
soon as KeyGen has completed to ensure that other dependent tables do
not suffer slowdown as a result of full table scans).
3. Ensure that Rollback Segments, Transaction tables (i.e. Bills, FTs,
Payments, Meter Read, etc), Master Data Table (ie Account, SA, etc) and
Indexes are allocated their own table-spaces and I/O Channel at the
database level to ensure that maximum throughput is ensured (my personal
preference is to split the Meter Read and Interval-related Data sets
out to yet another table-space and IO channel, but this will be
dependent on the disk structure on your machines).
4. Assess whether your partitioning is efficient (or even needed),
Partitioning is done to speed up DB accesses where the application is
I/O bound. It is not required in order for CC&B to run. If the
Database is configured correctly on a single table-space, feel free to
leave it as-is.
5. Be aware that running the validation processes for every x records
does expose you to the risk that not all errors will be encountered, but
it does allow you to quickly identify the big ticket items that are
incorrect in your mapping and get these out of the way easily. I
recommend starting out running every x records as part of your initial
conversion runs and setting the value to 1 for all of your
Failing this, some sites have elected to customise the KeyGen, in the event that this is your preferred approach take care to:
1. Consider running custom SQL to perform your key generation, aligned
with your partition ranges (i.e. 10 concurrent SQL statements with high
and low ranges aligned with your 10 partitions, or a factor of this
split (e.g. 20 or 30 SQL statements each performing bite-sized chunks of
each partition range).
2. Consider running the base KeyGen process in De-duplication mode after
your custom SQL has completed to ensure that no duplicates are
encountered (if they are this process will 'bounce' the SQL-generated
value to another unique value).
3. Note that no manipulation of the input keys is expected as part of
your Extract or Load processes in order to complete any of this
4. Make sure that KeyGen (or your custom SQL) is executed in the order
specified by the online help to ensure that inherited keys are generated
correctly (e.g. Account should be run first, since most transactional
data inherits a component of the primary key from the Account Id).
5. Ensure that you create the initial "null-key" value on the CK_* table
to support optional foreign key logic performed as part of the insert
6. Ensure that your key fields are all zero-filled numeric values with
no trailing spaces (see my earlier posting in relation to Staging Table Keys
Regardless of the approach adopted:
1. Consider bringing down the Staging CC&B application during your
runs to reduce the risk of table/record locking by individuals logged on
to the environment while the batches are running (feel free to bring it
up at various points in the overall Conversion process to perform
sanity checks, but these must be well defined milestones in the
conversion plan, and the application should then be shut down again once
this phase has completed).
2. The performance of the Validation steps seems to be affected, to a
large degree, by the existence of Characteristics on entities, as a
result, expect your runs to take longer if your data model calls for a
large amount of characteristics.
3. Financial Transactions that are converted with REDUNDANT_SW = 'Y'
will normally be exempt from all further processing in CC&B (with
the exception of bill segment processing, direct queries and archiving),
as a result the actual key value assigned is of less importance than
4. Cross partition population of transactions for a single account will
not "break" CC&B, it will simply incur a minor performance hit when
retrieving these transactions.
5. Due to the fact that historic Bill Segments are normally accessed
regardless of the REDUNDANT_SW by the next bill creation process, it is
recommended that inherited key structures be retained for this entity.