DB2保持n列在两个表之间同步 [英] DB2 keeping n columns in sync between two tables
问题描述
如果为什么重要（也许是因为显示复制数据显然不是很好），请参阅（ PS ）部分下面的另一个问题的底部）： DB2更新和插入触发器，引用很多字段，我可以使用*来缩短它们（在iSeries中可能不重要）
为了做到这一点， m将插入，更新和删除触发器放在我称为主要设置的位置上，这将在复制集中插入，更新和删除记录。
我的问题是如果我现在要保持复制集与主集同步？我正在查看文档，看看最好的方式来做到这一点。我不希望插入触发器尝试插入到最初插入的表中，因为我预期会导致一个错误，该错误将被报告回到创建插入的程序，除非触发器有一个方法抛出这样的报告？此时最好的方法就是检查删除前是否存在该记录，与插入相同。
然后在更新触发器上有递归的问题，我想知道是否有更优雅的解决方案，而不是检查值是否相同，然后不更新？ / p>
主集不能以任何方式进行更改（除了不以任何方式修改表的输入/输出的触发器），而复制集的模式为完全灵活
优化懒惰（我可能会自动创建这些触发器，但如果您有关于如何保持紧凑的建议，则可以加快我的开发速度）。
系统没有大量使用，它具有免费资源的色调...几乎不需要保存内存，处理和磁盘空间。
而不是使用复制的表，你将会更好地使用视图。这将允许您将数据库访问与基础数据库设计完全分离。结合可更新/可删除的视图和替代触发器，您应该能够很好地做事情。请注意，这是建议的设置，即使您仅使用 SQL（和任何语言），因为它允许您将程序设计与数据库设计分离。事实上，它允许你脱离iSeries PFs只是一个好处。
好消息 - 你不必将所有东西都切换到使用SQL - iSeries实际上是设置为允许您访问SQL对象（如视图和触发器），就像它是一个物理文件，具有本机RPG文件访问。
坏消息 - 与逻辑文件不同，视图无法排序。如果您使用了大量的逻辑文件访问权限，您的努力可能会变得复杂...我没有尝试使用CHAIN操作访问SQL索引，以查看是否允许我访问其余记录，因此我不知道这是否会奏效。
编辑：
终于到了这个测试。事实证明，访问它们的排序索引（好像对于一个键控的逻辑文件）确实会授予访问记录的其余部分的权限。不确定是否帮助您。
I have a two tables (actually sets of tables) which may need to be kept in sync (The first set of tables I need all the data replicated to the second set where this data will be a subset).
If why matters (perhaps because it is obviously not very good form having replicated data) see (very bottom of this other question under the 'PS' section): DB2 update and insert trigger, references a lot of fields can I use * to shorten them (in iSeries but might not matter)
To do this I'm placing insert, update and delete triggers on what I'll call the 'primary set' which will insert, update and delete records onto the 'replicated set'.
My question is what if I want to now keep the replicated set in sync with the primary set? I'm looking at documentation right now to see the best way to do this. I don't want an insert trigger to try to insert into what ever table was initially inserted into, as this I expect will cause an error which would be reported back to the program making the insert, unless there is a way in the trigger to throw out such reporting? The best way at this time looks like checking if the record exists before the delete, same for insert.
Then there is the issue of recursing on an update trigger, I was wondering if there is more elegant solution than checking if the values are the same and then not updating?
The primary set can not be changed in any way (excepting triggers which do not modify input/output from the table in any way), while the schema of the replicated set is completely flexible.
Optimize for lazyness (I'm probably going to automate the creation of these triggers but if you have suggestions on how to keep them compact it will speed up my development).
The system is not heavily used, it has a tone of free resources... there is little need to be conservative with memory, processing and disk space.
Instead of using replicated tables, you're going to be far better off using views. This will allow you to completely decouple your database access from your underlying database design. With a combination of updateable/deletable views and 'instead-of' triggers, you should be able to do things nicely. Keep in mind that this is the recommended setup, even if you're using only SQL (and in any language) as it allows you to decouple program design from database design. The fact that it allows you to decouple from iSeries PFs is just a bonus.
Good news - you don't have to switch everything to use SQL just yet - the iSeries is actually setup to allow you to access SQL objects (like views and triggers) as if it was a physical file, with native RPG file access.
Bad news - unlike logical files, views can't be ordered. If you're using a lot of keyed access over logical files, your efforts could be complicated... I haven't tried accessing an SQL index with a CHAIN operation to see if it gives me access to the rest of the record, so I have no idea if that will work.
EDIT:
Finally got around to testing this. It turns out that accessing indexes for their ordering (as if for a keyed logical file) will indeed grant access to the rest of the record. Not sure if that helps you or not.
这篇关于DB2保持n列在两个表之间同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
