Where clause from another table (SQL Remote)

[Baron]

Is it possible to define articles in a publication and let the where clause refer to columns from a foreign table?

For example I have these 2 tables:

create or replace table employees (
empid int, 
withprovision int, 
empname varchar(10), 
departid int);

create or replace table orders (
orderid int, 
empid int);

I want to let sql remote replicate only the employees who make provision along with their orders, also I want to refer the subscription by from a foreign table.

My scripts looks like this:

create publication dba.mypub (table employees where withprovision = 1 subscribe by departid, table orders where exists (select 1 from employees where withprovision = 1 and orders.empid = employees.empid subscribe by ????);

Table employees works exactly as I want (where clause and subscribe by do what I need).

Table orders: the where clause acts as where 1 = 2 and I don't know how to write the subscribe by...

Is this generally possible with SQL Remote?

[Baron]

Actually I have problem even with table EMPLOYEES.

This table is included in a publication in both sides (Cons. and Remote), which means the table replicates in both directions, the creation of publication looks like this:

create publication rem.mypub (table employees);--on remote
create publication cons.mypub (table employees);--variant1 on cons.
create publication cons.mypub (table employees where 1=1);--variant2 on cons.

The problem which I have is as follows (only in case of variant2).

I insert a new employee on rem., then dbremote replicates it to cons., and the next dbremote deletes the same inserted employee on rem.!! but, one moment this is what the log of dbremote says, however the delete statement on rem. will be actually rolled back so that at the end the inserted employee remains in both cons. and rem. (i.e. the end result is OK, but here many questions!!)

• Should I trust dbremote that the end result will be always ok? why the second dbremote tries to delete the employee on rem.? how can the rem. manage to roll this (false) delete statement back?
• the where clause is simplified to 1=1, because it doesnt matter which condition is it.
• in variant1 there is no problem at all, and the cons. knows that it should not return the same employee to remote (in order to avoid echo).

[VolkerBarth]

I do not understand why your cons has to different publications/variants for the same table.

How do you create the remotes?

When using the default tool DBXTRACT, publications on cons and remotes should be identical - with the exception of the SUBSCRIBE BY clause - which would be used to send updates from the cons to the remotes only for those rows satisfying the clause... (And you would use that SUBSCRIBE BY clause to "partition data" for remotes, not a WHERE clause. AFAIK, a WHERE clause is focussed on generally excluding/including rows for replication but not to direct whether rows are published to particular remotes or not.)

[Baron]

the mentioned two variants are not on the same time, they are just two different cases.

In my case I need the WHERE clause only on the cons., so the publication on rem. and cons. are not identical.

For the sake of simplicity I am posting here with the employees table as example.

[VolkerBarth]

Do you use DEFAULT GLOBAL AUTOINCREMENT (or GUIDs) for primary keys? Or could it happen there is a PK conflict when several remotes enter new data?

By default, SQL Remote doesn't send operations back to the origin database, unless there's an UPDATE conflict.

[Baron]

the PK in my case consists of 2 columns, one is sequential number (not auto increment but self calculated max+1) and the other is an identifier of the database.

Moreover, in my last test I was using only one rem., so there is no PK conflict.

When I embed my WHERE clause inside the SUBSCRIBE BY clause then I dont have the problem, something like this

create publication cons.mypub (table employees subscribe by (select departid from employees where 1=1));--variant3 on cons.

[VolkerBarth]

self calculated max+1

I would very strongly suggest to use AUTOINCREMENTs here, unless you can absolutely make sure PKs are never re-used. PK re-use will usually happen when the maximum PK is deleted, so the next entry gets the same PK as the deleted row before... additionally, the max calculation also bears the risk of PK violations by two parallel select-max-and-insert transactions.

[regdomaratzki]

This sounds like it’s working as designed, with one exception that I’ll address later in my response (*).

First, let’s consider the following situation with the employee table, whose schema is exactly the same at the consolidated and remote, but whose publication definitions are slightly different.

CREATE TABLE employees (
  empid    bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT,
  withprov integer NOT NULL,
  empname  char(64) NOT NULL,
  deptid   integer NOT NULL,
  PRIMARY KEY (empid)
);

SQL Remote definitions at Consolidated:

CREATE PUBLICATION p1 (TABLE employees WHERE withprov=1);
CREATE USER cons IDENTIFIED BY ‘sql';
GRANT PUBLISH TO cons;
CREATE USER rem1 IDENTIFIED BY ‘sql';
GRANT REMOTE TO rem1 TYPE FILE ADDRESS 'rem1';
CREATE SUBSCRIPTION TO p1 FOR rem1;
START SUBSCRIPTION TO p1 FOR rem1;

SQL Remote definitions at Remote:

CREATE PUBLICATION p1 (TABLE employees);
CREATE USER rem1 IDENTIFIED BY ‘sql';
GRANT PUBLISH TO rem1;
CREATE USER cons IDENTIFIED BY ‘sql';
GRANT CONSOLIDATE TO cons TYPE FILE ADDRESS 'cons';
CREATE SUBSCRIPTION TO p1 FOR cons;
START SUBSCRIPTION TO p1 FOR cons;

Next, let’s insert two rows in the remote database.

INSERT INTO employees VALUES (DEFAULT,0,’Reg’,0);
INSERT INTO employees VALUES (DEFAULT,1,’Volker’,0);
COMMIT;

When you run dbremote against rem1, both rows are sent to cons, since the publication at the remote simply says all rows on the employee table replicate. Dbremote runs on the consolidated, picks up the message from rem1, and happily inserts both rows. However, the row for employee ‘Reg’ has a value of withprov=0, and the publication definition in cons says that the rem1 remote database should only have rows where withprov=1, so it determines that rem1 should not have this row and sends a delete for the ‘Reg’ employee to rem1.

(*) The only thing I find odd about your post is that you say the delete on the employee table is rolled back when dbremote attempts to apply it at the remote database. I suspect this is because there is a foreign key relationship to the employee table at the remote, and there are child records associated with the row dbremote is trying to delete.

The key point here is that if a remote sends up a row to the consolidated and the consolidated determines via the where clause or subscribe by clause on the publication that the remote that sent the row should not have that row, a delete is sent back to the remote.

I’d be happy to be proven wrong, but I don’t believe this is related to how primary keys are defined at the consolidated and remote. Given the lack of information on why the delete may have been rolled back when sent down to the remote database, Volker and I both making educated guesses at what the problem might be.

Reg

[regdomaratzki]

I think Volker intended to say :

I would very strongly suggest to use GLOBAL AUTOINCREMENTs here, unless you can absolutely make sure PKs are never re-used.

[VolkerBarth]

While I certainly would very strongly suggest to use those GLOBAL AUTOINCREMENTs with SQL Remote, I had taken into account that Baron already seems to have a "database identifier", so I was only suggesting to alter the "sequential number" to be an always incrementing (non-global) AUTOINCREMENT at least, assuming changing from a composite two-field PK to a single PK would require to re-setup the complete SQL Remote setup...:

the PK in my case consists of 2 columns, one is sequential number (not auto increment but self calculated max+1) and the other is an identifier of the database.

But I guess I'm not sure whether the (non-global) DEFAULT AUTOINCREMENT would work sufficiently when different values from different databases would be inserted - that might drive the next default increment way higher than necessary for the current database...

[regdomaratzki]

I'd missed that it was a two-column primary key. Sorry Volker.

[VolkerBarth]

No need to worry, I'm fine, I got the provision in your sample:)

[Baron]

Thank you Reg for the reply. Your assumption/explanation sounds very reasonable. The thing what you find odd is also clear to me, since my employees table has a child table (PK-FK relation), so that is not possible to delete the parent.

What I find odd: the phenomen of deleting rows on the rem. happens even if the where clause on cons. is 1=1.

I'll test it once more next week and write the results again her.

[Baron]

Thanks Volker, but there is definitely no PK conflict/reuse

[Baron]

Hello Reg, with your example my expression above ist not correct:

"...even if the where clause on cons. is 1=1."

I don't know why in my example was deleted even with 1=1

Your example is correct, and I get the same response as you expected, the first employee will be deleted on rem. (as you said, it is according to the design), but I need the following:

Employees with withprov=1 should be replicated among all other remotes, but the employees with withprov = 0 may NOT be deleted on the publisher remote. In other words, each rem. should have all employees inserted locally(regardless whether withprov = 0 or withprov = 1), in addition to all employees withprov=1 (regardless whether inserted locally or on other remotes)!!

With something like this I could solve it:

CREATE PUBLICATION p1 (TABLE employees subscribe by (select '1' from dummy where employees.withprov=1));
CREATE USER cons IDENTIFIED BY ‘sql';
GRANT PUBLISH TO cons;
CREATE USER rem1 IDENTIFIED BY ‘sql';
GRANT REMOTE TO rem1 TYPE FILE ADDRESS 'rem1';
CREATE SUBSCRIPTION TO p1('1') FOR rem1;
START SUBSCRIPTION TO p1('1') FOR rem1;

[VolkerBarth]

A subscribe_by value of NULL or an empty string can be used (in confunction with the subscribe_by_remote option set to "On") to leave an entry at the remote it has been created at without publishing it to other remotes. This could do the trick here.

[regdomaratzki]

If all rows on the employee table in the consolidated database replicate to all remote database regardless of the value of the withprov column in the table, then IMHO, this column should not be referenced in any way in the publication, either in the where clause or the subscribe by clause. Just let the whole table replicate in both directions.

If the rule is that at the remote database you cannot delete a row in the employee table when withprov=0, then you should implement that in a before delete trigger, and throw an exception if you determine that there is a row in the SYSREMOTEUSER where consolidate='Y' (i.e. we are at a remote database).

It sounds like you are trying to solve an issue with publication design that should be solved using another manner.

Reg

[regdomaratzki]

I don't know why in my example was deleted even with 1=1

I would need to see the entire article clause in the publication for the employee table, as well as the data that was sent down as a delete to answer this question.

[VolkerBarth]

In my (limited) understanding of Baron's description, it's different:

1. Some employees must be replicated to all remotes,
2. and others must only be replicated between the remote they were inserted at and the cons.

(Of course I do not know whether employees might change from the first to the second group or vice versa.)

Therefore I still do think this should be handled by publication design but the with an empty subscription value for the 2. group.

(Of course, in case those 2nd group employees should not be replicated to the cons at all, a publication with a WHERE clause like "keep_local = 0" would suffice if those employees are marked with "keep_local = 1".

[Baron]

No, I didnt say that all employees should replicate to all remotes.

Maybe I couldnt explain it well, but I try with other words.

The employees are inserted only on the remotes, and there are two types of employees (with and without provision).

All employees (with and without provision) should then be collected on the cons., later the cons. sends some employees (only those with provision) to all remotes.*

So at the end the cons. has all employees, each remote has its own employees in addition to employees with provision from other remotes*.

---

This is the first phase, as the second phase, even employees with provision should not replicate to all remotes *, as the remotes are grouped into different departments, so it is more preciesly so:

The cons. has all employees, each remote has its own employees in addition to employees with provision from other remotes within the same department

With your hints I could solve it, and my script looks something like this:

CREATE PUBLICATION p1 (TABLE employees subscribe by (select deptid from departments where employees.deptid = employees.deptid and employees.withprov=1));
CREATE SUBSCRIPTION TO p1('1') FOR rem1;--rem1 belongs to deptid = '1'
CREATE SUBSCRIPTION TO p1('2') FOR rem2;--rem2 belongs to deptid = '2'
START SUBSCRIPTION TO p1('1') FOR rem1;
START SUBSCRIPTION TO p1('2') FOR rem2;
....

[VolkerBarth]

Hm, but then again according to Reg's explanation the cons would have to delete those employees with withprov = 0 on the remotes they were created on (unless a FK violation does prevent that there, but that would be bad design IMHO).

I guess a subscriby by clause that really expresses your (very reasonable!) business logic would look something like this - assuming the employees table has a column createdAt identifying the according remote, say being filled with DEFAULT CURRENT PUBLISHER:

... Subscribe by (deptid and withprov = 1 or createdAt = [remote name])

The actual condition would relate how you can identify the according remote - the sample just might give you an idea.

---

Or, as suggested above, you would leave the deptid null for employees withprov = 0 because then they would be replicated to the cons but not to other remotes. Personally, I would prefer the logic to be expressed in the subscribe by, in my mind the "subcribe by NULL" behaviour is rather obscure...

[Baron]

according to Reg's explanation the cons would have to delete those employees with withprov = 0

logically yes, but it does not, not because of FK violation, but it does not try to delete them at all.

apparently it makes a difference whether the condition withprov=1 is written in the where clause or in the subscribe by clause.

[VolkerBarth]

Hm, I don't think so. (*)

subscribe by (select deptid from departments where employees.deptid = employees.deptid and employees.withprov=1)

According to your current SUBSCRIBE BY clause, I would think it will make a difference if there are any employees in a particular department with withprov = 1, it doesn't matter whether the current employee has withprov = 1 or not. If there is at least one such employee in the according department, all employees of that department will be replicated to all remotes, otherwise none - and in the latter case the cons would send a delete to the origin remote.

That's at least my understanding of that condition. Of course checking the transaction log with DBTRAN -sr will help to understand the distribution.

(*) I'm relating to the actual effect. I surely do agree that it's a different whether a condition is put in a WHERE or SUBSCRIBE BY clause of an article...

[Baron]

there was a misstyping in the above script, it should be like this:

CREATE PUBLICATION p1 (TABLE employees subscribe by (select deptid from departments where employees.deptid = departments.deptid and employees.withprov=1));
CREATE SUBSCRIPTION TO p1('1') FOR rem1;--rem1 belongs to deptid = '1'
CREATE SUBSCRIPTION TO p1('2') FOR rem2;--rem2 belongs to deptid = '2'
START SUBSCRIPTION TO p1('1') FOR rem1;
START SUBSCRIPTION TO p1('2') FOR rem2;
....

[Baron]

sorry Volker, there was a misstyping in my script, I corrected it above.

[VolkerBarth]

Well, I didn't notice that typo (and assumed it the way you have now corrected to, i.e. with the join condition "with employees.deptid = departments.deptid").

Therefore my thoughts still apply that this condition probably does not work as you seem to expect.

[Baron]

So my scripts look exactly so:

--Cons.
drop publication if exists p1 ;
drop publication if exists p2 ;
create or replace table employees (
  empid    bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT,
  withprov integer NOT NULL,
  empname  char(64) NOT NULL,
  deptid   integer NOT NULL,
  PRIMARY KEY (empid)
);
create or replace table departments (
    deptid integer not null, 
    tel varchar (20), 
    city varchar(100), 
primary key (deptid)
);
create publication p1 (table employees subscribe by (select deptid from departments where employees.deptid = departments.deptid and employees.withprov=1));
create publication p2 (table departments);
--
CREATE SUBSCRIPTION TO p1('1') FOR rem1;
CREATE SUBSCRIPTION TO p1('1') FOR rem2;
CREATE SUBSCRIPTION TO p1('2') FOR rem3;
CREATE SUBSCRIPTION TO p2 FOR rem1;
CREATE SUBSCRIPTION TO p2 FOR rem2;
CREATE SUBSCRIPTION TO p2 FOR rem3;
--
start SUBSCRIPTION TO p1('1') FOR rem1;
start SUBSCRIPTION TO p1('1') FOR rem2;
start SUBSCRIPTION TO p1('2') FOR rem3;
start SUBSCRIPTION TO p2 FOR rem1;
start SUBSCRIPTION TO p2 FOR rem2;
start SUBSCRIPTION TO p2 FOR rem3;
--
insert into departments values (1, 'Tel1', 'city1');
insert into departments values (2, 'Tel2', 'city2');
commit;
--Remotes
drop publication if exists p1 ;
create or replace table employees (
  empid    bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT,
  withprov integer NOT NULL,
  empname  char(64) NOT NULL,
  deptid   integer NOT NULL,
  PRIMARY KEY (empid)
);
create or replace table departments (
    deptid integer not null, 
    tel varchar (20), 
    city varchar(100), 
primary key (deptid)
);
create publication p1 (table employees );
create SUBSCRIPTION TO p1 FOR cons;
start SUBSCRIPTION TO p1 FOR cons;
commit;

--remote1
insert into employees values (10001, 1, 'rem1dep1_w', 1);
insert into employees values (10002, 0, 'rem1dep1_wo', 1);
--remote2
insert into employees values (20001, 1, 'rem2dep1_w', 1);
insert into employees values (20002, 0, 'rem2dep1_wo', 1);
--remote3
insert into employees values (30001, 1, 'rem3dep2_w', 2);
insert into employees values (30002, 0, 'rem3dep2_wo', 2);

At the end I have following results:

• cons has all 6 employees
• remote1 has 1001 + 10002 + 20001 --OK
• remote2 has 10001 + 20001 + 20002 --OK
• remote3 has 30001 + 30002 --OK

AND, there is no any delete statement in the log files of DBREMOTE on any of the remotes! --OK

[VolkerBarth]

Ah, I see, I finally understand that your subscribe_by clause really does what I was suggesting: As the condition returns no value for all rows with withprov=0, it's the case of a NULL or an empty subscribe_by value, and as such the subscribe_by_remote option will control what has to be done at the remote.

You might try to set that option to "Off" to see if then employees with withprov=0 are deleted at the remotes (unless a FK violation prevents that).

[Baron]

@Volker Barth

 ..it doesn't matter whether the current employee has withprov = 1 or no

I think here the current row in employee will be evaluated.

..and in the latter case the cons would send a delete to the origin remote.

I think the only way to enforce the delete of an employee on its origin DB is to insert an employee with an incorrect deptid (what in our case will never happen), for example:

--remote1
insert into employees values (10006, 1, 'empwithfalsdeptid', 2);

In this case this employee will be "moved" (cut+paste) from remote1 (deptid=1) to remote3(deptid=2)

[Baron]

in my case this will not happen at all.

Anyway, when I delete 30001 on remote3 then it will be deleted on cons. too, nothing more -- this is also OK

[Baron]

But the condition was written intentionally because I want to prevent the cons. of replicating employees without provision.

In other words, in case withprov=0, then the employee should be maintained (and not deleted) in its origin DB and also should exist in cons.

[Baron]

@Volker Barth How can I find an explanation what does the mentioned option subscribe_by_remote?

[Baron]

These both statements make big difference, despite at the first look they look very similar.

create publication p1 (table employees where withprov=1 subscribe by deptid );

The cons. ensures that each remote has only the following employees (withprov = 1 && belong to the same department), it takes also care to prevent remotes maintaining employees who withprov <> 1.

It deletes explicitely each employee on its origin DB if it violates the where clause (withprov <> 1).

create publication p1 (table employees subscribe by (select deptid from departments where employees.deptid = departments.deptid and employees.withprov=1));

The cons. sends to each remote the following employees (withprov = 1 and belong to the same department).

The explicit delete of an employee on its origin DB happens only if the cons. detects a violation in subscribe by (if the employee belongs another department).

[VolkerBarth]

As stated somewhere in a comment above, see the link:

A subscribe_by value of NULL or an empty string can be used (in confunction with the subscribe_by_remote option set to "On") to leave an entry at the remote it has been created at without publishing it to other remotes. This could do the trick here.

Also see the doc on that mentioned option.

In my understanding, if you would set the option to OFF, SQL Remote would delete those rows on the remote where the subscribe_by clause is empty, and that would apply to those with withprov=0. (And if this turns out to be true, I would recommend to document the significance of that option for your setup...)

[VolkerBarth]

I think here the current row in employee will be evaluated.

Thanks for the pointer, that was my obvious misunderstanding here: I had thought that the SUBSCRIBE BY clause would be evaluated for the whole table as such, not for a particular row. But apparently I was wrong. (And gladly my own SQL Remote setups had not been dependent on that difference 😉 )

[VolkerBarth]

One approach is to use a join within your WHERESUBSCRIBE BY subquery, i.e. for say, table order_items, you would use something like

subscribe by
   (select e.departid
    from order_items oi
       inner join orders o on oi.order_id = o.id
       inner join employees e on o.empid = e.empid
    where e.withprovision = 1)

In other words: You can use the same subscribe by condition for all dependent tables but have to join them to the table the subscribe by relates to.

FWIW, this is also (and naturally in a better fashion) documented in the docs:

Disjoint Data Partitions.