Page Comparison

Table of Contents

Overview

...

...

Anchor
stage1 stage1

Stage 1 - Import and parse reference dataset (Optional)

This optional stage is run once per Bulk Loader process. This stage can be skipped if the reference dataset is already available or if the optional 'filter by reference' step (Step 2.5) is skipped.

...

Anchor
stage3 stage3

Stage 3 - Geocode and filter

•  

  Step 3.1 - Geocode source dataset

...

Anchor
stage4 stage4

Stage 4 - Export full set (single batch) or subset (multiple batches)

...

Anchor
stage5 stage5

Stage 5 - Run the Bulk Loader

 For a complete set of steps and background about the Bulk Loader, see also Running the Bulk Loader, a page dedicated to its input, operation and results.

...

1. Connect to the database, <environment>_DB, and clear any leftover records from previous Bulk Loader batches.

   Code Block
   language sql title truncateTRUNCATE linenumbers true
   TRUNCATE bulkloader.address_extract, bulkloader.blocks_nearest;

   
   

   Code Block
   language sql title vacuumVACUUM linenumbers true
   VACUUM FULL ANALYZE bulkloader.address_extract;
   Code Block
   language sql title vacuum linenumbers true
   VACUUM FULL ANALYZE bulkloader.blocks_nearest;

   
   

   
   

2. Make note of EAS record counts before the Bulk Loading operation.

   Code Block
   language sql firstline 1 title Record Counts linenumbers true
   SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup

   1. Save results in artifact as record-counts-before.csv 
   2. Also save results in Excel spreadsheet artifact as bulkloader_results_YYYYMMDD.xlsx (TODO provide path to a template)

3. Make note of the database partition size on the file system at the current point in time.

   Code Block
   language bash firstline 1 title disk usage linenumbers true
   date; df /data

   
   

...

Anchor
stage6 stage6

Stage 6 - Extract results

•  

  Step 6.1 - Archive exceptions

...

1. Archive the entire address_extract table.

   1. Use a query tool such as pgAdmin to query and save the table as a CSV file.

      Code Block
      language sql firstline 1 title bulkloader.address_extract linenumbers true
      SELECT * FROM 'bulkloader.address_extract';

      
      

   2. Save the file in the network folder dedicated to artifacts for the Bulk Loader iteration.
      • Save as artifact address_extract.csv
2. Archive the addresses that raised exceptions during the Bulk Loader process

   1. Query subtotals and save as artifact exception_text_counts.csv

      Code Block
      language sql firstline 1 title Count/view unit addressesexception_text_counts linenumbers true
      SELECT exception_text, Count(*) FROM bulkloader.address_extract GROUP BY exception_text ORDER BY exception_text;

      
      

   2. Query all exception text records and save as artifact exception_text.csv

      Code Block
      language sql firstline 1 title Count/view new base addressesexception_text linenumbers true
      SELECT * FROM bulkloader.address_extract WHERE NOT(exception_text IS NULL) ORDER BY exception_text, id;

      
      

3. Artifacts
   1. address_extract.csv - Results of every address submitted to the Bulk Loader.
   2. exception_text_counts.csv - Counts of the records that were not loaded due to the error indicated in the 'exception_text' field.
   3. exception_text.csv - Subset of the just the records that were not loaded due to the error indicated in the 'exception_text' field

...

1. Get all the address records (including units) added to the EAS during the Bulk Loader operation..
   

   1. Query the public.addresses table on the new change_request_id value.

      Code Block
      language sql firstline 1 title public.addresses linenumbers true
      SELECT * FROM public.addresses WHERE activate_change_request_id = <change_request_id>;

      
      

   2. Save the file in the network folder dedicated to artifacts for the Bulk Loader iteration.
      • For example, R:\Tec\..\Eas\_Task\path\to\archive\bulkloader_YYYYMMDD\bulkloader\batch_002\addresses.csv
2. Artifacts
   1. addresses.csv - All the address records (including units) added to the EAS during the Bulk Loader operation.

...

1. Get all the base records added to the EAS during the Bulk Loader operation.
   

   1. Query the public.address_base table on the new change_request_id value.

      Code Block
      language sql firstline 1 title public.address_base linenumbers true
      SELECT activate_change_request_id, address_id, public.address_base.* FROM public.address_base, public.addresses WHERE public.address_base.address_base_id = public.addresses.address_base_id AND public.addresses.address_base_flg = TRUE AND public.addresses.activate_change_request_id = <change_request_id>;

      
      

   2. Save the file in the network folder dedicated to artifacts for the Bulk Loader iteration.
      • For example, R:\Tec\..\Eas\_Task\path\to\archive\bulkloader_YYYYMMDD\bulkloader\batch_002\address_base.csv
2. Artifacts
   1. address_base.csv - All the base records added to the EAS during the Bulk Loader operation.

...

1. If the Bulk Loader Process was run on the production server then restore services

   1. SKIP Turn on production-to-replication service

      • Re-enable database replication by restarting the database service on the replication server (DR PROD DB).

        
        

        Code Block
        language bash title Stop PostgreSQL linenumbers true
        sudo -u postgres -i /usr/pgsql-9.0/bin/pg_ctl -D /data/9.0/data start

        
        

   2. SKIP Turn on downstream database propagation service(s)

      • Resume downstream replication to internal business system database (SF PROD WEB).

        Code Block
        language text firstline 1 title start xmit
        sudo /var/www/html/eas/bin/xmit_change_notifications.bsh start

        
        

   3. Enable front-end access to EAS

      • Place the Web servers into live mode (SF PROD WEB, DR PROD WEB).

        Code Block
        language bash linenumbers true
        cd /var/www/html sudo ./set_eas_mode.sh LIVE

        
        

...