Running ETL2 Phase 2

Configuring ETL2 Jobs

The ETL2 bulk loader has two tables for configuring and monitoring load jobs: ETL2_MONITOR and ETL2_CONFIG. Each table has a JOB_ID primary key. The ETL2_CONFIG table is used to provide parameters to the Phase 2 loader; if the loader cannot find a ETL2_CONFIG record for the JOB_ID or if any column in the table is null, the Phase 2 loader will use default values.

Email versus Alternate Customer ID

In Connect, customer records can be uniquely defined by the email field (CUSTOMER_MASTER.ACTIVE_EMAIL_ADDRESS column) or by the Alternate Customer ID (CUSTOMER_MASTER.ALT_CUSTOMER_ID column). ETL2 has two versions of the Phase 2 loader that depend on which column is used to identify customers.

Stored procedures ending in _alt should be used when alt_customer_id is the identify field.
Stored procedures ending in _email should be used when active_email_address is the identify field.

Running ETL2 With Job Monitoring

The ETL2 package contains two entry points which will track status in the ETL2_MONITOR table.

p_connect_etl2.run_phase2_alt is used to run the Phase 2 loader when alt_customer_id is the identify field.
p_connect_etl2.run_phase2_email is used to run the Phase 2 loader when active_email_address is the identify field.

Both entry points take the following arguments:

v_job_id -- Job number.
v_job_name -- Job name; can be anything.
v_step_number -- Optional Step Number (see below).

The job number and job name are both required. The job id is used to locate a record in the ETL2_MONITOR table. If no record is found, the one will be created. The job name is stored in the ETL2_MONITOR but is otherwise unused by the Phase 2 loader.

If the step number is not provided or is -1 (the default value), the Phase 2 loader will execute all steps of the load job and it will update the ETL2_MONITOR with results after each step.

If the step number is between 1 and 8, the Phase 2 loader will execute that specific step only. The table below details the function performed by each step.

Step	Function	ETL2_MONITOR Columns Updated
0	Initialize ETL2_MONITOR record.	Initialize all columns.
1	Load ETL2_CUSTOMER_MASTER records.	STAT_CUSTOMER_MASTER: 1 if success, 3 if failure CUST_MASTER_INSERT: number of records inserted CUST_MASTER_UPDATE: number of records updated CUST_MASTER_ERROR: number of error records copied to ERROR2 table
2	Load ETL2_CUST_ATTRIBUTES records.	STAT_CUST_ATTRIBUTES: 1 if success, 3 if failure CUST_ATTRIBUTE_INSERT: number of records inserted CUST_ATTRIBUTE_UPDATE: number of records updated CUST_ATTRIBUTE_ERROR: number of error records copied to ERROR2 table
3	Load ETL2_CUST_PREFERENCES records.	STAT_CUST_PREFERENCES: 1 if success, 3 if failure CUST_PREFERENCE_INSERT: number of records inserted CUST_PREFERENCE_UPDATE: number of records updated CUST_PREFERENCE_ERROR: number of error records copied to ERROR2 table
4	Load ETL2_CUST_SUBCRIPTION records.	STAT_CUST_SUBSCRIPTION: 1 if success, 3 if failure CUST_SUBSCRIPTION_INSERT: number of records inserted CUST_SUBSCRIPTION_UPDATE: number of records updated CUST_SUBSCRIPTION_ERROR: number of error records copied to ERROR2 table
5	Load ETL2_EVENT_QUEUE records into EVENT_QUEUE table; all records will have STATUS_CODE = 200 (load in progress).	STAT_EVENT_QUEUE: 1 if success, 3 if failure EVENT_QUEUE_INSERT: number of records inserted EVENT_QUEUE_UPDATE: number of records updated EVENT_QUEUE_ERROR: number of error records copied to ERROR2 table
6	Load ETL2_EVENT_QUEUE_DETAIL records.	STAT_EVENT_QUEUE: 1 if success, 3 if failure EVENT_QUEUE_INSERT: add number of records inserted EVENT_QUEUE_UPDATE: add number of records updated EVENT_QUEUE_ERROR: add number of error records copied to ERROR2 table Note: This step adds counts to the monitor fields from Step 5.
7	Load ETL2_EVENT_QUEUE_ATTACHMENT records.	STAT_EVENT_QUEUE: 1 if success, 3 if failure EVENT_QUEUE_INSERT: add number of records inserted EVENT_QUEUE_UPDATE: add number of records updated EVENT_QUEUE_ERROR: add number of error records copied to ERROR2 table Note: This step adds counts to the monitor fields from Step 5.
8	Update EVENT_QUEUE records. In Step 5, new EVENT_QUEUE records are loaded with STATUS_CODE 200, which denotes records that are being loaded. In Steps 6 and 7, additional data relating to each event record is loaded. Once these steps are complete, the status must be changed to 1 so that the Campaign Management engine (i.e. the Conversation Manager) will process the new event records.	STAT_EVENT_QUEUE: 1 if success, 3 if failure Note: This step reuses the monitor fields from Step 5.

Running ETL2 Without Job Monitoring

The Phase 2 loader also contains entry points that do not update status in the ETL2_MONITOR table. All entry points in this section take a single job id argument.

Table Loaded	Alternate Customer Id Entry Point	Email Entry Point
ETL2_CUSTOMER_MASTER	p_connect_etl2.load_cust_master_alt	p_connect_etl2.load_cust_master_email
ETL2_CUST_ATTRIBUTES	p_connect_etl2.load_cust_attr_alt	p_connect_etl2.load_cust_attr_email
ETL2_CUST_PREFERENCES	p_connect_etl2.load_cust_pref_alt	p_connect_etl2.load_cust_pref_email
ETL2_CUST_SUBSCRIPTION	p_connect_etl2.load_cust_sub_alt	p_connect_etl2.load_cust_sub_email
ETL2_EVENT_QUEUE with status 200	p_connect_etl2.load_event_queue_alt	p_connect_etl2.load_event_queue_email
ETL2_EVENT_QUEUE_DETAIL	p_connect_etl2.load_event_queue_detail_alt	p_connect_etl2.load_event_queue_detail_email
ETL2_EVENT_QUEUE_ATTACHMENT	p_connect_etl2.load_event_queue_attach_alt	p_connect_etl2.load_event_queue_attach_email
Update ETL2_EVENT_QUEUE status to 1	p_connect_etl2.update_evq_status_alt	p_connect_etl2.update_evq_status_email
Run all steps in order	p_connect_etl2.load_all_customer_alt	p_connect_etl2.load_all_customer_email