Re: ER and ats/ris Files

Madison Pruet wrote: > It's been quite some time since I used it.... > > -------------------------------------------------------------------- > #include <stdio.h> > #include <stdarg.h> ... Nice code, two comments: On 9.30 and 9.40 hangs all queries with er group name (with 10 no problem), for example: database <database>@<er_group_name>; select count(*) from <database>@<er_group_name>:'<owner>'.<table> WHERE <primary_key> = xxx;DELETE from <database>@<er_group_name>:<owner>.<table> where <primary_key> = xxx; When i change "er_group_name" (part->server) to $INFORMIXSERVER no problem... The second is, that this C-program convert ris-files with inserts to updates (no error when row no exist, thats ok), but on ris-files with deletes only do an delete. I mean the better variant is to do on every event an delete with a following insert . For example, when a delete transaction fails (ris file), but an following insert transaction (same primary key) work without problem (no ris file) your c-program would delete an correct inserted row - and do out of sync this table(s)... With the method delete+insert i have resynced several tables without problems (primary key from ris-file, data from source). Regards, try_and_err

<try_and_err@web.de> wrote in message news:1140033776.705565.239640@f14g2000cwb.googlegroups.com... > > Madison Pruet wrote: > > It's been quite some time since I used it.... > The second is, that this C-program convert ris-files with inserts to > updates (no error when row no exist, thats ok), but on ris-files with > deletes only do an delete. If I remember correctly, I was trying to do the update on the source and the delete on the target. The reason was that it's highly risky to try to simply do an insert on the target server because there could have been a lot of changes which occured to the row since the failure. By getting it from the source and pushing it through the system, you eliminate that problem. But like I said - it's been a long time since I used the tool. I mean the better variant is to do on every > event an delete with a following insert . For example, when a delete > transaction fails (ris file), but an following insert transaction (same > primary key) work without problem (no ris file) your c-program would > delete an correct inserted row - and do out of sync this table(s)... > With the method delete+insert i have resynced several tables without > problems (primary key from ris-file, data from source). > > Regards, > try_and_err >

Madison Pruet wrote: > <try_and_err@web.de> wrote in message > news:1140033776.705565.239640@f14g2000cwb.googlegroups.com... > > > > Madison Pruet wrote: > > > It's been quite some time since I used it.... > > The second is, that this C-program convert ris-files with inserts to > > updates (no error when row no exist, thats ok), but on ris-files with > > deletes only do an delete. > > If I remember correctly, I was trying to do the update on the source and the > delete on the target. I'am sure that your c program do an update on the primary key on the target - with the data from the source. ;-) But the problem still exists, the c program do only a delete on a failed transaction, it do not consider that there might be a later successful transaction (without ris file). Therefore again, i would recommend that all inserts/updates/deletes should be handled as delete/insert (and again, only the primary key from the ris file, the data for insert must be from the source), that protects against errors and error messages (for example row exists). Concerning 9.30/9.40 problem: To be more exactly, both versions have problems with the syntax "<database>@<er_target_group_name>.<owner>:<table>" when you are already connected to the target server (sql hangs, reproducibly with dbaccess). With removing target server (only statements, not connect database "target server") from the c code it work on 9.30 + 9.40 + 10 (not testet on 7.x): diff original.ec original.ec.changed: 688,689c688,689 < "INSERT into %s@%s:%s.%s (\\n\\t", < rep->target->db, rep->target->server, --- > "INSERT into %s:%s.%s (\\n\\t", > rep->target->db, 723,724c723,724 < "DELETE from %s@%s:%s.%s where ", < rep->target->db, rep->target->server, --- > "DELETE from %s:%s.%s where ", > rep->target->db, 746,747c746,747 < "UPDATE %s@%s:%s.%s \\n SET (", < rep->target->db, rep->target->server, --- > "UPDATE %s:%s.%s \\n SET (", > rep->target->db, 791,792c791,792 < sprintf(stmt,"select count(*) from %s@%s:'%s'.%s WHERE %s", < part->db, part->server, part->tabowner, part->tabname, --- > sprintf(stmt,"select count(*) from %s:'%s'.%s WHERE %s", > part->db, part->tabowner, part->tabname, Regards, try_and_err

<try_and_err@web.de> wrote in message news:1140056482.408891.249430@f14g2000cwb.googlegroups.com... > > Madison Pruet wrote: > > <try_and_err@web.de> wrote in message > > news:1140033776.705565.239640@f14g2000cwb.googlegroups.com... > > > > > > Madison Pruet wrote: > > > > It's been quite some time since I used it.... > > > The second is, that this C-program convert ris-files with inserts to > > > updates (no error when row no exist, thats ok), but on ris-files with > > > deletes only do an delete. > > > > If I remember correctly, I was trying to do the update on the source and the > > delete on the target. > > I'am sure that your c program do an update on the primary key on the > target - with the data from the source. > ;-) Could be - like I said it's been a long time since I wrote the early prototypes for sync. > But the problem still exists, the c program do only a delete on a > failed transaction, it do not consider that there might be a later > successful transaction (without ris file). Therefore again, i would > recommend that all inserts/updates/deletes should be handled as > delete/insert (and again, only the primary key from the ris file, the > data for insert must be from the source), that protects against errors > and error messages (for example row exists). There are a bunch of problems using this technique. 1) delete/insert triggers 2) referential integrety problems 3) cascading deletes.... There are also some problems which are not quite so obvious such a what if the primary key is updated. ;-) In the code that we put into v10 (especially v10xC4), I think that we addressed all of these types of issues. > Concerning 9.30/9.40 problem: > To be more exactly, both versions have problems with the syntax > "<database>@<er_target_group_name>.<owner>:<table>" when you are > already connected to the target server (sql hangs, reproducibly with > dbaccess). Can you get a reproduction of this? The cdr utility is always connecting via the group name and I have always used the group name. I know there was a problem back in 7.22 with connecting by the group name, but I fixed that years ago. If you can get a repro, I'd appreciate it. > With removing target server (only statements, not connect database > "target server") from the c code it work on 9.30 + 9.40 + 10 (not > testet on 7.x): > diff original.ec original.ec.changed: > 688,689c688,689 > < "INSERT into %s@%s:%s.%s (\\n\\t", > < rep->target->db, rep->target->server, > --- > > "INSERT into %s:%s.%s (\\n\\t", > > rep->target->db, > 723,724c723,724 > < "DELETE from %s@%s:%s.%s where ", > < rep->target->db, rep->target->server, > --- > > "DELETE from %s:%s.%s where ", > > rep->target->db, > 746,747c746,747 > < "UPDATE %s@%s:%s.%s \\n SET (", > < rep->target->db, rep->target->server, > --- > > "UPDATE %s:%s.%s \\n SET (", > > rep->target->db, > 791,792c791,792 > < sprintf(stmt,"select count(*) from %s@%s:'%s'.%s WHERE %s", > < part->db, part->server, part->tabowner, part->tabname, > --- > > sprintf(stmt,"select count(*) from %s:'%s'.%s WHERE %s", > > part->db, part->tabowner, part->tabname, > > Regards, > try_and_err >

Madison Pruet wrote: > > But the problem still exists, the c program do only a delete on a > > failed transaction, it do not consider that there might be a later > > successful transaction (without ris file). Therefore again, i would > > recommend that all inserts/updates/deletes should be handled as > > delete/insert (and again, only the primary key from the ris file, the > > data for insert must be from the source), that protects against errors > > and error messages (for example row exists). > > There are a bunch of problems using this technique. > > 1) delete/insert triggers > 2) referential integrety problems > 3) cascading deletes.... Also your c program do convert some statements (insert->update, update->insert), so there is the same problem. Because i know there is a problem when you do only a delete i use generally the delete/insert solution, for my environment it's the best way. > > Concerning 9.30/9.40 problem: > > To be more exactly, both versions have problems with the syntax > > "<database>@<er_target_group_name>.<owner>:<table>" when you are > > already connected to the target server (sql hangs, reproducibly with > > dbaccess). > > Can you get a reproduction of this? The cdr utility is always connecting > via the group name and I have always used the group name. I know there was > a problem back in 7.22 with connecting by the group name, but I fixed that > years ago. > > If you can get a repro, I'd appreciate it. cat sqlhosts: er_grp1 group - - i=1 test_tli onsoctcp test_er1 1234 g=er_grp1 oninit -iselect count(*) from sysmaster@er_grp1:systablesQuery hangs (9.40.UC4). Regards, try_and_err