I need to replicate data from a DB2 database into a SQL Server Database (along the lines of about 80 tables). I can create an SSIS package, to just flat copy the data from one to another pretty easy, but the amount of records is a couple million, so doing it once takes a while, but for the initial setup its fine. However, I need to do "almost" real-time updates, so this method won't be viable. What would be the best method, and how, to do just add change and delete replication from DB2 to SQL Server?
Sql-server – Replicate DB2 table to SQL Server 2008
db2replicationsql serversql-server-2008
Related Solutions
If you're loading to an empty database, you could/should take steps to avoid requiring any additional maintenance steps post load. Fragmentation is the enemy, that's what you're trying to avoid.
- Drop all NC indexes before loading.
- Post load, add the NC indexes for each table in sequence i.e. don't add an index to TableA, then TableB, then back to TableA. This is has no effect on fragmentation but it can improve the time taken to add the indexes on very large datasets (reduces buffer pool churn).
- If you can insert data to a table in clustered index order, leave the clustered index in place. If you can't, dump the data into a heap and rebuild in to a clustered index post load.
Rather than copy and paste, I'll point you toward the very comprehensive list of references for ETL optimisation @Marian put together in an answer to a question I asked on BCP. Many of these will be equally applicable to your scenario.
You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.
Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.
With only that change, the package will execute successfully
Also, an excellent answer from SO on the same issue
Related Question
- Sql-server – SQL Server 2008, cpu 100% until recreate an index
- Sql-server – Duplicate primary key in bulk insert after truncate in SQL Server 2008
- Sql-server – SQL Server using incorrect plan
- Sql-server – Advice for ETL from Oracle to SQL Server
- Sql-server – Update existing SQL Server 2008 table via Flat File using SSIS
- Sql-server – SymmetricDS Setup – SQL Server 2008 to MySQL
- Sql-server – Can’t install a OLE DB Provider for IBM DB2 ISeries on SQL Servers higher than 2008 version
- DB2 to SQL Server – Data Transfer Between Servers
Best Answer
You could create a hidden timestamp column in DB2 on the replicated tables. When you run the replication job you can pull just the data that has changed since the last replication. This would lower the quantity of data sent.
For instance, run the SSIS package every minute pulling just the last minutes worth of data.
Or, instead of a timestamp field, you could use an auto incrementing
intand keep a small table on the replicated instance that records the lastintvalue replicated. The replication process would grab any new records, and update the small table's value to reflect the last value replicated.