June 21, 2012 at 6:19 am
Hi,
I am currently working for retail client and primarily on SQL 2008 DB designs though their applications are spread across 3 different DBs (SQL Server 2008, DB2, ORACLE)
I have received 3 new CR estimation requests which made me to post this topic 🙂
CR requests are expecting me to give estimate for providing Delta records (only insert/modify/soft deleted) every day as pipe delimited .TXT file. This CRs are looking for key table extracts in each DB (SQL , DB2 & ORACLE).
I have experience working in SQL but limited knowledge in DB2 and ORACLE in terms of tool avaialbility for data extraction like SSIS in SQL. However, was thiking of below possibilities/designs to track the delta records.
1. Maintain LastUpdated datetime column in each table to check for today's changes and include those rows in my delta extracts
(not sure if this column can be set up such a way that it is internally updated if any other columns updated in that row)
2. maintain History table for each key tables and use the main and history tables to compare and get the only the changed rows
Now, I needed you guys suggestions if there are any better ideas to achieve this?
Also, can i make use of SSIS to connect to DB2 and ORACLE to prepare pipe delimited files or I am open to accept your suggestions to use for each DB.
Thanks in advance.
Regards,
Suresh
Regards,
Suresh Arumugam
June 21, 2012 at 8:17 am
This was removed by the editor as SPAM
June 21, 2012 at 10:16 am
Suresh Kumar-284278 (6/21/2012)
Hi,I am currently working for retail client and primarily on SQL 2008 DB designs though their applications are spread across 3 different DBs (SQL Server 2008, DB2, ORACLE)
I have received 3 new CR estimation requests which made me to post this topic 🙂
CR requests are expecting me to give estimate for providing Delta records (only insert/modify/soft deleted) every day as pipe delimited .TXT file. This CRs are looking for key table extracts in each DB (SQL , DB2 & ORACLE).
I have experience working in SQL but limited knowledge in DB2 and ORACLE in terms of tool avaialbility for data extraction like SSIS in SQL. However, was thiking of below possibilities/designs to track the delta records.
1. Maintain LastUpdated datetime column in each table to check for today's changes and include those rows in my delta extracts
(not sure if this column can be set up such a way that it is internally updated if any other columns updated in that row)
2. maintain History table for each key tables and use the main and history tables to compare and get the only the changed rows
Now, I needed you guys suggestions if there are any better ideas to achieve this?
Also, can i make use of SSIS to connect to DB2 and ORACLE to prepare pipe delimited files or I am open to accept your suggestions to use for each DB.
Thanks in advance.
Regards,
Suresh
For MSSQL, there are several approaches. The most basic is to create an Audit table and then place procs on each table that needs to be "audited" that stores only the changes, inserts, deletes etc into the audit table. You would then report off this table. The procs for this are readily available in the scripts portion of this site.
June 21, 2012 at 10:38 am
DiverKas (6/21/2012)
Suresh Kumar-284278 (6/21/2012)
Hi,I am currently working for retail client and primarily on SQL 2008 DB designs though their applications are spread across 3 different DBs (SQL Server 2008, DB2, ORACLE)
I have received 3 new CR estimation requests which made me to post this topic 🙂
CR requests are expecting me to give estimate for providing Delta records (only insert/modify/soft deleted) every day as pipe delimited .TXT file. This CRs are looking for key table extracts in each DB (SQL , DB2 & ORACLE).
I have experience working in SQL but limited knowledge in DB2 and ORACLE in terms of tool avaialbility for data extraction like SSIS in SQL. However, was thiking of below possibilities/designs to track the delta records.
1. Maintain LastUpdated datetime column in each table to check for today's changes and include those rows in my delta extracts
(not sure if this column can be set up such a way that it is internally updated if any other columns updated in that row)
2. maintain History table for each key tables and use the main and history tables to compare and get the only the changed rows
Now, I needed you guys suggestions if there are any better ideas to achieve this?
Also, can i make use of SSIS to connect to DB2 and ORACLE to prepare pipe delimited files or I am open to accept your suggestions to use for each DB.
Thanks in advance.
Regards,
Suresh
For MSSQL, there are several approaches. The most basic is to create an Audit table and then place procs on each table that needs to be "audited" that stores only the changes, inserts, deletes etc into the audit table. You would then report off this table. The procs for this are readily available in the scripts portion of this site.
Do you mean to say that I need to main Audit Table for each table to be reported and have triggers of Main table populates the corresponding Audit table?
Sorry. If i understood wrongly. By the way, does it make sense to add one LastUpd column in each main table and have them updated when any columns change? then use that new column to pull records for reporting?
Please let me your thoughts?
Regards,
Suresh Arumugam
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply