March 1, 2009 at 6:23 pm
Hi All
We port data from our legacy mainframe system to SQL Server to support ad-hoc queries, Data Warehousing etc.
We currently have the ability to do this in near real-time, however proposed changes to the legacy system may adversely affect this. Essentially the legacy system team want to stop populating a field on their side because they think they don’t need it and that it slows down their system. Instead they want us to populate it on the SQL Server side.
So the challenge for us is how best to populate the field on the SQL Server side, without adversely affecting the process of near real-time porting over data, and while maintaining near real-time data availability. i.e. we need to update existing records using new records, in the same tables that our porting process is updating, at about the same time, and in near real-time.
Does anyone have any suggestions on the best way to do this?
Cheers
Nik
March 1, 2009 at 7:33 pm
You will have to supply much more specific information about you data and the feeds, before you will get an answer of any value.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 1, 2009 at 9:56 pm
Agreed. You need to give specifics on where the data resides; help us understand the origination of the data, and then we can offer assistance in fetching from the source of the data rather than the MF.
Best regards,
Lee
March 2, 2009 at 6:04 pm
Thanks for taking a look at this.
We use a product called Attachmate DATABridge to port the data in near real-time from a DMS database to a SQL Server database. In the background DATABridge is using BCP to copy the data accross, but I'm not sure what kind of locks BCP is holding to do it's table inserts.
Many of the tables in the DMS database are stacked, with a new record created each time a change is made. Each record has a datetime stamp, one unique identifier for itself, and another unique identifier for the previous record in the stack. On the DMS side they only ever use the latest record in the stack, but on the SQL Server side we do quite a bit of snapshot analysis.
To facilitate the SQL Server side snapshot analysis we need to have not only the datetime stamp for when the record was created, but also an end datetime value. This is so that we can avoid the substantial processing cost of having to walk each stack every time we want to find which record was in effect for each stack, for the date and time we are interested in. We can do this by using each new record in a particular stack to find the previous record and update the end datetime value.
An example scenario would be...
For customer "Nik" in the SQL Server "customer_details" table there is a single record on 2 Feb 2009.
unique_record_iddatetime_stmpend_dtmcust_nmcust_adsprev_unique_record_id
------------------------------------------------------------------------------------
102Feb2009 14:46Nik7 Somewhere Pl
202Feb2009 15:12Tim1 Elsewhere St
302Feb2009 16:15Jei1001 Anywhere Rd
Then on 3 Feb 2009 his address is changed, generating a new record in the stack of records for him, and updating the end_dtm of the previous record.
unique_record_iddatetime_stmpend_dtmcust_nmcust_adsprev_unique_record_id
------------------------------------------------------------------------------------
102Feb2009 14:4603Feb2009 12:27Nik7 Somewhere Pl
202Feb2009 15:12Tim1 Elsewhere St
302Feb2009 16:15Jei1001 Anywhere Rd
403Feb2009 12:27Nik7 Nowhere Pl1
What I'm trying to determine is how best to update the previous record in the stack as soon as the new record is received, in near real-time, and while DATABridge is continuously updating the same table.
Cheers
Nik
August 26, 2009 at 10:47 am
Nik,
We use Databridge here as well to populate a SQL database from our Unisys Mainframe.
Is the field that the mainframe guys want to delete a calculated field? If so you have a few options to populate it.
- You could simply create a View to look at the table and calculate the field for you. This may not work if you have a lot of queries/users pointing at the base table.
- You can put a calculated field on the table definition with something like: CREATE TABLE Contact(
FirstNameVARCHAR(200),
LastNameVARCHAR(200),
DateOfBirth DATETIME,
Age AS Cast(DateDiff("mm", DateOfBirth, GetDate())/12 AS INTEGER)
)
You may have to re-add the calculated field if you re-clone the SQL database.
- Lastly, if Databridge if executing sprocs to insert/update/delete data you should be able to modify that code to populate your field.
Hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply