| Author |
Topic |
|
Artanis
Starting Member
5 Posts |
 Posted - 2003-04-16 : 15:28:46
|
| Hi there, I need to build a Procedure (or a Trigger, I don't care) that should replicate the data that has just been inserted into my table to a Similar table in a DB2 database (AS400 plataform). I don't have even a small clue of how should I do this... any help will be more than welcome. Thx in advanceArtanisArtaniss.z.artanis@bol.com.br+55 (12)9702-7809 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-16 : 15:48:51
|
| Have you searched the forums on DB2 yet? I believe that someone has already posted the answer for this.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 15:58:54
|
| Tara,If you type in DB2 you get.....nothing.As for how it gets done, depends on your requirements, real time or time interval, or over night?How much data, a little, a lot? Apply transactions or Full replace? Can your data track insert and update events based on time?How many transactions do you have a day, what's the peek, whens off-peak?To replicate, to trigger, to use linked server and sprocs, to dump and load?Got of few questions to answer I think.Brett8-) |
|
|
|
Artanis
Starting Member
5 Posts |
Posted - 2003-04-16 : 16:16:17
|
| Ok.. let's pick just a made up environment to clarify my needs better.I have as AS400 Server and a SQL Server 2000 (Small Business).I need that every row inserted into a table in my SQL Server certain Database to be automatically replicated to an identical table in my AS400's DB2.So, it's going to be a Real Time trans.The amount of transactions my vary.. but I might have from 10 to 1000 transactions in a day. I don't know what you mean by "transaction or full replace", but I'll certainly have to catch inserts / updates and deletes.I'll have to perform an one-time operation that will import all my DB2 data to my SQL Server Database.I don't care if the answer is to put a trigger in the table or use the Replication of the SQL Server (well... I actually don't have much experience with replication.. but I'll learn all of it if needed)...If linked Server is possible, I think that would be a good solution, for I'll have the same database in both plataforms.. and I need them to be fully. I'll have Windows APP Clients and AS400 App clients... Thanks for your help...OBS: I couldn't find anything when looking for DB nor AS400 'cause the server timing out...Artaniss.z.artanis@bol.com.br+55 (12)9702-7809 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 16:29:15
|
| Well, Full replace means what it is. You take the data and fully replace with a copy from the other...not an option for you.Never done replication myself. I seem to get a sense from the site that it's not easy to manage (guys?). I'll have to play with it.Anither question does pop up though. Is this going to bi-directional?10 to 1000 transaction a day? SQL Server could handle that in the wink of an eye.Maybe create DB2 AS400 as a linked server and have a trigger fire everytime and INSERT, UPDATE or DELETE occurs. Shouldn't be too hard.How many tables will there be in the database?Brett8-) |
|
|
|
Artanis
Starting Member
5 Posts |
Posted - 2003-04-16 : 16:34:13
|
| Well... it is going to be bi-directional.. but I'm handling only the SQL Server >> DB2 part of the Problem.. unless SQL Server has enough tools to perfomr both tasks, my function will rely on this.If it's easy to use linked server (and if linked server is what I think it is).. well.. then the problem might be solved by now. I'll do a quick research on this subject and check what I get... About the amount of tables.. I believe it's close to 700.. but this should be no problem once theyll have me on the triggers programming...Thanks for your help.Artaniss.z.artanis@bol.com.br+55 (12)9702-7809 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 16:40:44
|
| Well the bi-directional thing complicates stuff a little, for example a row in DB2 has just been updated and you get an updatye on your side. Is it ok to blow away the other users to update ( and the other way around too).I believe you're going to HAVE to worry about some type of business rule that inmdicates when an update to overlay newer data is ok.You also the have to worry about getting the datetime from DB2, which a select across the network, check the timestamps between rows....yada yada yada...its a bit messyBrett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-16 : 16:46:41
|
quote:
If you type in DB2 you get.....nothing.
How come when I search on DB2, I get tons of threads back? A few of them discussed DB2 and replication, but I don't know if any of them will help Artanis since I didn't have time to read them fully.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 16:50:27
|
| I'm searching in "Article Search" is that where you're searching?Just did it again, nothing. Thought it might be case sensitivity, but nope. I wonder if it's a firewall thing. I do get articles sometime though.graz? rob? Merkin?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-16 : 16:52:53
|
| Nope not article search. Do a forum search. To see that option, you have to be in the forum section first, then the link will appear at the top.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 16:54:49
|
| [homer] Doooh...found at the same time you posted....never saw it there. Thanks a bunch [/homer]Brett8-) |
|
|
|
Artanis
Starting Member
5 Posts |
Posted - 2003-04-17 : 12:03:02
|
Me back =)...I was making a small research about Linked Servers and guess what?? I'm connecting my AS400 server. (look for sp_addlinkedserver for more information)I connected using the MSDASQL Provider (That comes with the IBM's AS400 Client Access Tools) because I couldn't find the DB2OLEDB provider as recommended by Microsoft in all Articles I readed from them.But there's still something missing.. I'm having the following problem when trying to perform a SELECT on my Linked Server.------------------------------------------------------------------------------------- sp_addlinkedserver @server = 'DB2SRV', @srvproduct = 'Client Access ODBC Driver (32-bit)', @provider = 'MSDASQL.1', -- * DB2 Provider @provstr='DSN=DB2Cn;User ID=Artanis; Password=ValidPwd' -- * EveryThing Ok Until now GO SELECT * FROM DB2SRV.MyLib.DBO.MyFile GO -- * Error Message OLE DB provider 'MSDASQL.1' reported an error. Provider caused a server fault in an external process.------------------------------------------------------------------------------------- I've played a little with sp_addlinkesrvlogin.. It doesn't seem to make any effect in this picture I exposed.I will glad if you can still help me.Thnx for your help, It's been very useful.Artaniss.z.artanis@bol.com.br+55 (12)9702-7809Edited by - artanis on 04/17/2003 12:06:18 |
|
|
|
|
Replicate new rows to a DB2 DataBase