RE: Enterprise Replication - Recreate 'cdr define' commands
Thanks Art,
Done.
Jarrod Teale
________________________________
From: Art Kagel [mailto:art.kagel@gmail.com]
Sent: Thursday, 7 January 2010 9:51 a.m.
To: Jarrod Teale
Cc: ids@iiug.org; informix-list@iiug.org
Subject: Re: Enterprise Replication - Recreate 'cdr define' commands
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/ \\\\\\n"P/' | sed
-e 's/ "R/ \\\\\\n"R/g'| sed -e 's/|/\\n\\n/' >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
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/