Configure the installdp8DPdirectConfig SQL file

Edit the installdp8DPdirectConfig.sql file. This file contains system specific tuning and configuration.

To configure the installdp8DPdirectConfig.sql file

1. Navigate to the %DNADataRoot%\Historical\config\applicationsql\DataPump\<System Name>\installdp8DPdirectConfig.sql.

2. Group tables for datapumping. Typically, you will have different DPdirects for certain groups of tables. For example:

   Note: More DPdirects does not necessarily mean higher performance. If the disk space layout cannot support concurrent DPdirects, then increasing them will actually slow down performance.

   update xosapp..datapump set DPGroup = 'DEFAULT'

   go

   update xosapp..datapump set DPGroup = 'TIMESERIES' where dbName in ('TimeSeries') and tableName != 'collect'

   go

   update xosapp..datapump set DPGroup = 'TIMESERIES_COLLECT' where dbName in ('TimeSeries') and tableName in ('collect')

   go

   update xosapp..datapump set DPGroup = 'EVENT' where dbName in ('event')

   go

   -- update xosapp..datapump set DPGroup = 'TIMELINE' where dbName in ('timeline') and tableName != 'collect'

   -- go

   -- update xosapp..datapump set DPGroup = 'TIMELINE_COLLECT' where dbName in ('timeline') and tableName in ('collect')

   -- go

   -- update xosapp..datapump set DPGroup = 'LMSPLM' where dbName in ('LMS', 'PLMCollect')

3. To increase performance, you can delay tables that are generated at the top of the hour until Microsoft SQL Server/RealTime is less busy; for example:

   -- Set delayAtTopOfHour

   --

   -- starts at 5 minutes before top of hour

   --

   -- Reduce contention for tables between systems

   -- stagger the DPdirect processes

   --

   -- We can also do this in DPstartup by setting different sleep times

   -------------------------------------------------------------

   update xosapp..datapump set topOfHourDelay = NULL

   go

   -- No need to be too quick with timeline..collect it is buffered in RealTime on the non-owning systems anyway

   --

   -- update xosapp..datapump set topOfHourDelay = 37 where dbName = 'timeline' and tableName = 'collect' and system = 'mcc'

   -- update xosapp..datapump set topOfHourDelay = 27 where dbName = 'timeline' and tableName = 'collect' and system = 'mccdss'

   -- update xosapp..datapump set topOfHourDelay = 37 where dbName = 'timeline' and tableName = 'collect' and system = 'bccdss'

   -- go

   -- update xosapp..datapump set topOfHourDelay = 5 where dbName = 'timeline' and tableName = 'hour' and system = 'mcc'

   -- update xosapp..datapump set topOfHourDelay = 7 where dbName = 'timeline' and tableName = 'hour' and system = 'mccdss'

   -- update xosapp..datapump set topOfHourDelay = 5 where dbName = 'timeline' and tableName = 'hour' and system = 'bccdss'

   -- go

   -- update xosapp..datapump set topOfHourDelay = 8 where dbName = 'TimeSeries' and tableName = 'hour' and system = 'mcc'

   -- update xosapp..datapump set topOfHourDelay = 9 where dbName = 'TimeSeries' and tableName = 'hour' and system = 'mccdss'

   -- update xosapp..datapump set topOfHourDelay = 7 where dbName = 'TimeSeries' and tableName = 'hour' and system = 'bccdss'

   -- go

4. Fine tune the catchup for the desired systems to increase performance.

   Optional: If there are a lot of insert failures (for example a connection loss, deadlock) the increased catchup will actually slow down performance. On a failure, every row in the catchup must be verified on the source and on the destination. If the row does not exist, the row is inserted. If the row does exist, each of the existing rows in the catchup will be updated on the destination; for example:

   -------------------------------------------------------------

   -- Set catchUp

   --

   -- tuning temporary

   --

   -------------------------------------------------------------

   -- update xosapp..datapump set catchUp = 1000 where dbName = 'TimeSeries' and tableName = 'tag'

   -- update xosapp..datapump set catchUp = 1 where dbName = 'TimeSeries' and tableName = 'collect'

   -- update xosapp..datapump set catchUp = 1 where dbName = 'TimeSeries' and tableName = 'hour'

   -- go

   Note: This can also be done in the %DNADataRoot%\Historical\config\applicationsql\DataPump\MASTER_dpx\installdp2<tableName> file. This will affect every system.

5. Disable systems to ensure that data is being sent to the appropriate place. For example:

   -------------------------------------------------------------

   -- Set execHour

   --

   -- Disable tables (-2), or set to cleanup (-5)

   -------------------------------------------------------------

   --

   --

   -- update xosapp..datapump set execHour = -5 where system = 'bccdss'

   update xosapp..datapump set execHour = -3

   -- update xosapp..datapump set execHour = -3 where dbName = 'event'

   go

   --

6. Update the Readme.txt file to show what your system looks like.

   Note: The following is a typical production layout, and is not meant to represent each of the DPdirect paths.

   MCCDSS BCCDSS

   | |

   MCC-----BCC