Hi Zhong,
One fairly simple technique is to create a single table to contain only the
key column(s) of the several partitioned tables; then use an insert and
delete trigger on each of the main tables to maintain this table in tandem
with them.
A -803 sqlcode encountered against the "key" table during the trigger
execution will cause the complete INSERT statement to fail.
Examples below from a similar requirement.
regards, Trevor
Trevor Kingsbury
DB2 UDB Consultant
Melbourne, Australia
CREATE TRIGGER TG_BANK_ACCT#01
AFTER INSERT ON TB_BANK_ACCT
REFERENCING NEW AS NEWROW NEW_TABLE AS NEWTAB
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO TB_BANK_ACCT_K
SELECT BANK_ACCT_ID FROM NEWTAB
WHERE BANK_ACCT_ID = NEWROW.BANK_ACCT_ID
AND NOT EXISTS
(SELECT 1 FROM TB_BANK_ACCT_K
WHERE BANK_ACCT_ID = NEWROW.BANK_ACCT_ID);
END$
CREATE TRIGGER TG_BANK_ACCT#02
AFTER UPDATE OF BANK_ACCT_ID ON TB_BANK_ACCT
REFERENCING OLD AS OLDROW NEW AS NEWROW NEW_TABLE AS NEWTAB
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM TB_BANK_ACCT_K
WHERE BANK_ACCT_ID = OLDROW.BANK_ACCT_ID
AND NOT EXISTS
(SELECT 1 FROM TB_BANK_ACCT
WHERE BANK_ACCT_ID = OLDROW.BANK_ACCT_ID);
INSERT INTO TB_BANK_ACCT_K
SELECT BANK_ACCT_ID FROM NEWTAB
WHERE BANK_ACCT_ID = NEWROW.BANK_ACCT_ID
AND NOT EXISTS
(SELECT 1 FROM TB_BANK_ACCT_K
WHERE BANK_ACCT_ID = NEWROW.BANK_ACCT_ID);
END$
CREATE TRIGGER TG_BANK_ACCT#03
AFTER DELETE ON TB_BANK_ACCT
REFERENCING OLD AS OLDROW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM TB_BANK_ACCT_K
WHERE BANK_ACCT_ID = OLDROW.BANK_ACCT_ID
AND NOT EXISTS
(SELECT 1 FROM TB_BANK_ACCT
WHERE BANK_ACCT_ID = OLDROW.BANK_ACCT_ID);
END$
"Shi, Zhong"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
ture.com> cc:
Sent by: Subject: [DB2EUG] What is a Good Way to
Guarantee Record Uniqueness Across All the
[EMAIL PROTECTED] Tables in Partitions?
wash.org
02/10/2003 08:04
AM
Please respond
to "Shi, Zhong"
Ext: Business Area:
Fax: Internet:
--------------------------------------------------------------------------
List Experts:
We need to partition a table to several tables and use UNION ALL to create
the whole table view for users. What is a practical implementation to
guarantee record uniqueness across all the table partitions? Has anyone
overcome this one with ease?
Any suggestions are appreciated.
Thanks.
Zhong
DBA
Incepture, Inc.
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
