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. Make note of the database partition size on the file system. Compre with size of partition prior to loading to get the total disk space used as a result of running the Bulk Loader.

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

   
   

2. Make note of EAS record counts after the Bulk Load 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

   
   

   • Save results as artifact record-counts-after.csv
   • Also save results in Excel spreadsheet artifact as bulkloader_results_YYYYMMDD.xlsx

   • In the spreadsheet, calculate the difference between the 'before' and 'after' record counts. The results will indicate the number of new base addresses added to the table `public.address_base` and the number of new addresses and units added to the table `public.addresses`.

   •  See dedicated Bulk Loader page, Running the Bulk Loader, for more analysis options.
     
     

3. Make note of then remove records from the bulkloader.blocks_nearest table and then make note of the disk usage once again:

   Code Block
   language sql linenumbers true
   SELECT COUNT(*) FROM bulkloader.blocks_nearest;

   
   

   Code Block
   language sql linenumbers true
   TRUNCATE bulkloader.blocks_nearest;

   
   

   Code Block
   language sql linenumbers true
   VACUUM FULL ANALYZE bulkloader.blocks_nearest;

   
   

   Code Block
   language bash linenumbers true
   df /data

   Artchive bulkloader.address_extract table.

   Query all records and save as artifact address_extract.csv

   Code Block
   language sql firstline 1 title Count/view new base addresses linenumbers true
   SELECT * FROM bulkloader.address_extract;

   Query subtotals and save as artifact exception_text_counts.csv

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

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

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

   
   

Anchorstage6stage6Stage 6 

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.For example, R:\Tec\..\Eas\_Task\path\to\archive\bulkloader_YYYYMMDD\bulkloader\batch_002\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 runQuery the 'bulkloader.address_extract' table for any value in the 'exception_text' field.process

   1. Query subtotals and save as artifact exception_text_counts.csv

      Code Block
      language sql firstline 1 title Count/view unit addresses 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 exception_textCount/view new base addresses linenumbers true
      SELECT * FROM bulkloader.address_extract WHERE NOT(exception_text IS NULL) ORDER BY exception_text;

      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\exceptions.csv

      , 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.

•  

  Step 6.2 - Archive unique EAS change_request_id associated with the Bulk Load

...

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

        
        

...