So, I was recently working on a client engagement and was needing to update the WebSphere Commerce SHPARRANGE table, to enable certain functionality in an external application. Not knowing what data already existed in the production system, I wanted the ability to write a query that would update existing or insert new data, to verify the records that I needed to exist.
I decided to use a DB2 MERGE statement. Per IBM: “The MERGE statement updates a target (a table or view, or the underlying tables or views of a fullselect) using data from a source (result of a table reference). Rows in the target that match the source can be deleted or updated as specified, and rows that do not exist in the target can be inserted. Updating, deleting or inserting a row in a view updates, deletes or inserts the row in the tables on which the view is based.”
You’ll find an example of the MERGE query below that I used to add/update records into the SHPARRANGE table, combining the proper store, fulfillment center, and shipping mode.
The following variables were used in the query below:
- FFMCENTER_NAME: The fulfillment center I want to associate with the store identified by the variable STORE_IDENTIFIER_HOSTED.
- FFM_ORGENTITY_NAME: The ORGENTITY name that is used to resolve the ORGENTITY_ID (MEMBER_ID) of the fulfillment center store.
- FFM_ORGENTITY_DN: The ORGENTITY DN that is used to resolve the ORGENTITY_ID (MEMBER_ID) of the fulfillment center store.
- STORE_IDENTIFER_HOSTED: The identifier of the hosted store I wanted to associate with the fulfillment center, and shipping code.
- STORE_IDENTIFIER_CATALOG: The identifier of the catalog store I wanted to associate with the store identified by the variable STORE_IDENTIFIER_HOSTED.
- SHIPPING_CODE: The shipping code that I want to associate to the fulfillment center, and store identified by the STORE_IDENTIFIER_HOSTED variable.
- CARRIER_NAME: The carrier name of the shipping code associated with the fulfillment center and store.
The query below will generate a temporary table, using the query defined, which will generate a temporary ID, pull a store ID, a fulfillment center ID, and a shipping mode ID. The query below, will only create a single record in the temporary table; however, the MERGE query could work with multiple records in that temporary table, but for my purposes I kept it at a single record.
If the data matches data existing in the table as specified by the ON query part, then the WHEN MATCHED query takes effect, if no data matches existing data in the table, the WHEN NOT MATCHED query takes effect.
Its also important to note, that the UPDATE and INSERT statements aren’t traditional statements, they’re specific to the MERGE statement.
MERGE INTO SHPARRANGE AS SA
USING (
SELECT
MAX(SHPARRANGE_ID) + 1 AS TEMP_ID,
(SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFER_HOSTED}') AS STORE_ID,
(SELECT FFMCENTER_ID FROM FFMCENTER WHERE NAME = '{FFMCENTER_NAME}' AND MEMBER_ID = (SELECT ORGENTITY_ID FROM ORGENTITY WHERE ORGENTITYNAME = '{FFM_ORGENTITY_NAME}' AND DN = '{FFM_ORGENTITY_DN}')) AS FFMCENTER_ID,
(SELECT SHIPMODE_ID FROM SHIPMODE WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER_CATALOG}') AND CODE = '{SHIPPING_CODE}' AND CARRIER = '{CARRIER_NAME}') AS SHIPMODE_ID
FROM SHPARRANGE
) AS TP (TEMP_ID, STORE_ID, FFMCENTER_ID, SHIPMODE_ID) ON (SA.STORE_ID = TP.STORE_ID AND SA.FFMCENTER_ID = TP.FFMCENTER_ID AND SA.SHIPMODE_ID = TP.SHIPMODE_ID)
WHEN MATCHED THEN
UPDATE SET
STORE_ID = TP.STORE_ID,
FFMCENTER_ID = TP.FFMCENTER_ID,
SHIPMODE_ID = TP.SHIPMODE_ID
WHEN NOT MATCHED THEN
INSERT (SHPARRANGE_ID, STORE_ID, FFMCENTER_ID, SHIPMODE_ID, STARTDATE, ENDDATE, PRECEDENCE, FLAGS)
VALUES (TP.TEMP_ID, TP.STORE_ID, TP.FFMCENTER_ID, TP.SHIPMODE_ID, '1970-06-22 23:00:00.0', '2999-06-22 23:00:00.0', 0, 0)
;
If you see that I’ve misspoke or stated something incorrectly, please let me know by using the comment box below.
Additional information on the MERGE query can be found using the Info Center: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0010873.html
