Consider an environment where a source system is duplicated across 3 sites. Site A, Site B and Site C. There may be 400 load tables that require loading from each of these duplicated source systems, in practical terms they are the same tables but in different implementations of the same application. When building WhereScape objects to load data from these tables it is preferable to build the one set of load objects that provide data to the one set of dimension, stage and fact processes. All objects can then be used regardless of the source system providing the actual data. The key to resolving this problem is the use of a WhereScape RED callable procedure called Ws_Connect_Replace. This procedure allows the caller to replace the contents of a connection from another connection.
For example, four connections are created:
Connection Site A - Stores all connection details for Site A
Connection Site B - Stores all connection details for Site B
Connection Site C - Stores all connection details for Site C
Connection Site Generic - When called Ws_Connect_Replace swaps A, B, or C connection details into this connection
All 400 load tables use Connection Site Generic as the connection defined in the metadata.
Prior to loading the data from the load tables the procedure Ws_Connect_Replace is called swapping site specific connection details into the generic connection. Processing can then continue in the knowledge that the correct data is being loaded.
To automate the processing of each site you can use a custom procedure at the start of a job that contains the call to Ws_Connect_Replace along side some parameter controls. In this way you also have only one set of job metadata to maintain.
If your requirements are smaller and only involve one load table that requires source system or schema swapping, look at the WhereScape callable procedure Ws_Load_Change. This procedure allows the caller to change either the connection or the schema of a load table.
