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 RED 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
Connection Site B
Connection Site C
Connection Site Generic
Stores all connection details for Site A
Stores all connection details for Site B
Stores all connection details for Site C
When called Ws_Connect_Replace swaps A, B or C connectoin 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.If you use a custom procedure at the start of a job that contains the call to Ws_Connect_Replace along side some parameter controls. Automation of which site to process can be achieved. In this way we can also have the 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 RED callable procedure Ws_Load_Change. This procedure allows the caller to change either the connection or the schema of a load table.