Enterprise Replication - Recreate 'cdr define' com
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.unlSELECT "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/