page220
306
With the stored procedure created and an understanding of how streams operate,
let’s now test.
Testing a Single Load
Now test a single load:
CALL sp_merge_test_load ( 'ASIA' );
The stored procedure should return a message similar to this:
Success:
Rows
Affected:
60417
Deleted:
20304
Updated:
20067
Inserted: 20046
Confirm the stream contents have been consumed.
SELECT count(1),
metadata$action,
metadata$isupdate
FROM strm_part_customer_order_asia
WHERE region_name = 'ASIA'
GROUP BY metadata$action,
metadata$isupdate;
Check the number of records in our table partition.
SELECT count(1)
FROM part_customer_order_asia; //300094738
Confirm you have the correct results from the
MERGE
stored procedure. The values are
the before and after row counts from the table partition.
SELECT 300094738 - 300094996; //-258
Using the stored procedure return values, subtract the
DELETED
row count from the
INSERT
row count. This number represents the net difference. You can ignore the
UPDATE
row counts as these do not change the number of rows in the table partition.
SELECT 20046 - 20304; //-258
While the stored procedure’s before and after checks work for my tests, your values
will differ because of the use of
LIMIT
when generating test data.
Chapter 8
parallelization