Re: Enterprise Replication - Recreate 'cdr define'

That's great Jarrod. You should submit it to the IIUG Repository. Just go to the repository main page (www.iiug.org/software) and click the tab that's labeled 'Repository Upload' and floow the instructions there. Art Art S. Kagel AdvanceDataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 6, 2010 at 3:30 PM, Jarrod Teale <Jarrod.Teale@fonterra.com>wrote: > Hey all, > Some time ago I asked if anyone had generated a script to rebuild the "cdr > define" commands for replicates based on what was in the database. > As I got no response, and I got bored, and had a need for it, I wrote one. > > Below is a bash shell script that interrogates the syscdr database and > generates "pretty" cdr define commands (Based on 11.50.xC5 on Redhat 4) . > It doesn't take account of conflict resolution (I don't have any) and > assumes all replicates are "Primary - Recipient", not update anywhere. > > If anyone wanted to extend this to account for the above (easy to do, the > syscdr database is a breeze to navigate): > Conflict resolution - look in the syscdr:repdef table (four rows > starting with 'cr_') > Update anywhere replicates - Remove the "P" section in the query and > review the MULTISET clause > > Things I haven't done: > Found all the other flags from the repdef:flags column (Madison? can > you help there?) > > The bash scriptlet is to format the output 'nicely'. > > I hope this helps someone else. > > > Jarrod Teale > > Team Lead - Manufacturing Execution Systems > > Fonterra > > ******************** SCRIPT START ************************ > #!/bin/bash > dbaccess syscdr <<! 2>/dev/null > UNLOAD TO unformatted_cdr_defines.unl> SELECT "cdr define repl " || > > -- Conflict Resolution > "-C ignore " || > > -- Execution Frequency > CASE > WHEN freqtype = "C" THEN "-i" > WHEN freqtype = "I" THEN "-e " || NVL(f.min + f.hour * 60 + f.day * > 24 * 60, 0) > WHEN freqtype = "T" THEN "-a " || NVL(f.hour, 0) || ":" || CASE > WHEN NVL(f.min, 0) < 10 THEN "0" || NVL(f.min,0) ELSE "" || f.min END > END || > > -- Flags parameters > CASE WHEN BITAND(r.flags, 256) != 0 THEN " -S trans " ELSE " -S row > " END || -- 0x00000100 Scope > CASE WHEN BITAND(r.flags, 1024) != 0 THEN " -R y " ELSE " -R n " > END || -- 0x00000400 RIS > CASE WHEN BITAND(r.flags, 2048) != 0 THEN " -A y " ELSE " -A n " > END || -- 0x00000800 ATS > CASE WHEN BITAND(r.flags, 4096) != 0 THEN " -T y " ELSE " -T n " > END || -- 0x00001000 Triggers > CASE WHEN BITAND(r.flags, 67108864) != 0 THEN " -D y " ELSE " -D n > " END || -- 0x04000000 Ignore Deletes > CASE WHEN BITAND(r.flags, 268435456) != 0 THEN " -f y " ELSE " -f n > " END || -- 0x10000000 Full Row Updates > > -- Replicate Names > r.repname || > > -- Primary server > (SELECT ' "' || p3.partmode || " " || p3.db || "@" || h3.name || > ":" || p3.owner || "." || p3.table || '" "' || selecstmt || '"' > FROM partdef p3, hostdef h3 > WHERE p3.repid = p.repid > AND p3.partmode = "P" > AND h3.servid = p3.servid > ) || > > -- Recipient servers > ' "' || > SUBSTR(REPLACE(REPLACE(REPLACE( > MULTISET( > SELECT p2.partmode || " " || p2.db || "@" || h2.name || ":" || > p2.owner || "." || p2.table || '" "' || selecstmt > FROM partdef p2, hostdef h2 > WHERE p2.repid = p.repid > AND p2.partmode = "R" > AND h2.servid = p2.servid > )::LVARCHAR > ,"'),ROW('", '" "' ),"','", '" "'), "')}", '"') , 15) > > FROM > repdef r, > partdef p, > hostdef h, > OUTER freqdef f > WHERE r.repid = p.repid > AND r.repid = f.repid > AND h.servid = p.servid > ; > ! > > cat unformatted_cdr_defines.unl | sed -e 's/ "P/ \\\\\\\\\\ "P/' | sed -e 's/ "R/ > \\\\\\\\\\ "R/g'| sed -e 's/|/\\ \\ /' >formatted_cdr_defines > cat formatted_cdr_defines > ********************* SCRIPT END ************************* > > DISCLAIMER: > This email contains confidential information and may be legally privileged. > If you are not the intended recipient or have received this email in error, > please notify the sender immediately and destroy this email. > You may not use, disclose or copy this email or its attachments in any way. > Any opinions expressed in this email are those of the author and are not > necessarily those of the Fonterra Co-operative Group. > http://www.fonterra.com/ > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > --0015173fe6e64fef58047c851d6b