ER Error Enterprise Replication

Enterprise Replication is giving the following message when I do a simple cdr list server ndl12:ndl12a common_reporting$ cdr list server SERVER ID STATE STATUS QUEUE CONNECTION CHANGED ----------------------------------------------------------------------- cdr error: select bytesqueued from syscdrqueued SQL code -1215 :Value exceeds limit of INTEGER precision ISAM 0 : I think it is because I have more than 2 gigs queued to send. I am experimenting with it and tried to push all of my data to an empty database but it seems that it is going very slowly. I have a 1 gig ethernet connection and will have 10 gigs in production. I know I don't have it tuned correctly because I know I don't know what I am doing. So any help figuring out how to tune, monitor or diagnose what is going on would be appreciated. All comments welcome, since I don't know that much about ER, I am sure any comment will be helpful, except comments about me being an idiot ( I already know this so you are just wasting your time. ;-) ) I will open a case with informix because this is pretty crappy behavior that could be fixed easily enough. Below onstat -c output that shows version and settings. Thanks for any help ndl12:ndl12a common_reporting$ onstat -c IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 1 days 22:49:03 -- 4004260 Kbytes Configuration File: /usr/local/informix/etc/onconfig.ndl12a ## onconfig file for instance ndl12a # servernums in stage should be 200+ a=1, b=2, c=3, .. # # instance type: EN SERVERNUM 201 # Unique id corresponding to a OnLine instance DBSERVERNAME ndl12a # Name of default database server # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name # ROOTPATH /usr/local/informix_dev/eease_en/root_001 # ROOTPATH /usr/local/informix_dev/ndl12a/root_001 ROOTPATH /usr/local/informix_dev/ndl12a/root_001 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)# -c3 using 80,000 for EN, should do same for SED, which is currently 200,000 # (or vice-versa). ROOTSIZE 800000 # Size of root dbspace (Kbytes)0 # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration # -c3 for install PHYSDBS physdbs # Location (dbspace) of physical log # -c3 for install PHYSFILE 2097046 # Physical log file size (Kbytes) PHYSDBS physdbs # Location (dbspace) of physical log # -d3 take value from prod # PHYSFILE 2097046 # Physical log file size (Kbytes) PHYSFILE 599894 # Physical log file size (Kbytes) # Logical Log Configuration # -c3 for install LOGFILES 82 # Number of logical log files # -c3 for install LOGSIZE 2000 # Logical log size (Kbytes) LOGFILES 64 # Number of logical log files LOGSIZE 4000 # Logical log size (Kbytes)LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) # Tablespace Tablespace Configuration in Root Dbspace TBLTBLFIRST 1600 # First extent size (Kbytes) (0 = default) TBLTBLNEXT 800 # Next extent size (Kbytes) (0 = default) # Security # DBCREATE_PERMISSION: # By default any user can create a database. Uncomment DBCREATE_PERMISSON to # limit database creation to a specific user. Add a new DBCREATE_PERMISSION # line for each permitted user. #DBCREATE_PERMISSION informix # DB_LIBRARY_PATH: # When loading a (C or C++) shared object (for a UDR or UDT), IDS checks that # the user-specified path starts with one of the directory prefixes listed in # the comma-separated list of prefixes in DB_LIBRARY_PATH. The string # "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order for # extensibility and IBM supplied blades to work correctly. # DB_LIBRARY_PATH $INFORMIXDIR/extend # IFX_EXTEND_ROLE: # 0 (or off) => Disable use of EXTEND role to control who can register # external routines. # 1 (or on) => Enable use of EXTEND role to control who can register # external routines. This is the default behaviour. # IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role. # Diagnostics MSGPATH /usr/local/eease/log/online_ndl12a.log # System message log file path # -c3 try this out CONSOLE /usr/local/eease/log/online_ndl12a.console # System console message path # CONSOLE /dev/null # To automatically backup logical logs, edit alarmprogram.sh and set # BACKUPLOGS=Y ALARMPROGRAM /usr/local/informix/etc/alarmprogram.sh # Alarm program path ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any eventoccur TBLSPACE_STATS 1 # Maintain tblspace statistics # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 5120 # Tape block size (Kbytes) TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 5120 # Log tape block size (Kbytes) LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server staging area # System Configuration DBSERVERALIASES # List of alternate dbservernames NETTYPE soctcp,15,100,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor #v9#NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one #v9#NOAGE 0 # Process aging #v9#AFF_SPROC 0 # Affinity start processor #v9#AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters # 2008.6.18 -c3 for rkumbhar # LOCKS 2000000 # Maximum number of locks LOCKS 4000000 # Maximum number of locks PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes) CLEANERS 128 # Number of buffer cleaner processes SHMBASE 0x44000000L # Shared memory base address SHMVIRTSIZE 1500000 # initial virtual shared memory segmentsize SHMADD 100000 # Size of new shared memory segments (Kbytes) EXTSHMADD 100000 # Size of new extension shared memory segments (Kbytes) SHMTOTAL 6000000 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) TXTIMEOUT 300 # Transaction timeout (in sec) STACKSIZE 256 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) #@

bozon wrote: > Enterprise Replication is giving the following message when I do a > simple cdr list server > > ndl12:ndl12a common_reporting$ cdr list server > SERVER ID STATE STATUS QUEUE CONNECTION > CHANGED > ----------------------------------------------------------------------- > cdr error: select bytesqueued from syscdrqueued > SQL code -1215 :Value exceeds limit of INTEGER precision > > ISAM 0 : > > I think it is because I have more than 2 gigs queued to send. I am > experimenting with it and tried to push all of my data to an empty > database but it seems that it is going very slowly. I have a 1 gig > ethernet connection and will have 10 gigs in production. I know I > don't have it tuned correctly because I know I don't know what I am > doing. If you use 'cdr sync' to push all of the data, then you will be coordinating the 'push' with the allowable size of the queue, and thus avoid spooling to disk. You would also have the advantage of dynamically increasing the queue size for the duration of the sync. Also - if you use 'cdr sync' to populate a new node, the data in the table is not logged to perform the prorogation. If you do dummy updates, then the logical log will be impacted. Generally speaking, spooling (stable storage of the queue) is not a good thing as it means that additional work is done to 1) read the replicated transaction and 2) to remove the replicated transaction when it has been ACKed. You might consider having multiple sbspaces for the stable queue - some logged (for smaller transactions) and some unlogged (for larger transactions). > > So any help figuring out how to tune, monitor or diagnose what is > going on would be appreciated. All comments welcome, since I don't > know that much about ER, I am sure any comment will be helpful, except > comments about me being an idiot ( I already know this so you are just > wasting your time. ;-) ) I will open a case with informix because this > is pretty crappy behavior that could be fixed easily enough. > > Below onstat -c output that shows version and settings. > > Thanks for any help > > ndl12:ndl12a common_reporting$ onstat -c > > IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 1 > days 22:49:03 -- 4004260 Kbytes > > Configuration File: /usr/local/informix/etc/onconfig.ndl12a > #> # onconfig file for instance ndl12a > # servernums in stage should be 200+ a=1, b=2, c=3, .. > # > > # instance type: EN > > SERVERNUM 201 # Unique id corresponding to a OnLine instance > DBSERVERNAME ndl12a # Name of default database server> > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name > # ROOTPATH /usr/local/informix_dev/eease_en/root_001 > # ROOTPATH /usr/local/informix_dev/ndl12a/root_001 > ROOTPATH /usr/local/informix_dev/ndl12a/root_001> # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)> # -c3 using 80,000 for EN, should do same for SED, which is currently > 200,000 > # (or vice-versa). > ROOTSIZE 800000 # Size of root dbspace (Kbytes)0> > # Disk Mirroring Configuration Parameters > > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored> root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > # -c3 for install PHYSDBS physdbs # Location (dbspace) > of physical log > # -c3 for install PHYSFILE 2097046 # Physical log file > size (Kbytes) > PHYSDBS physdbs # Location (dbspace) of physical log > # -d3 take value from prod > # PHYSFILE 2097046 # Physical log file size (Kbytes) > PHYSFILE 599894 # Physical log file size (Kbytes)> > # Logical Log Configuration > > # -c3 for install LOGFILES 82 # Number of logical > log files > # -c3 for install LOGSIZE 2000 # Logical log size (Kbytes) > LOGFILES 64 # Number of logical log files > LOGSIZE 4000 # Logical log size (Kbytes)> LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) > > # Tablespace Tablespace Configuration in Root Dbspace > > TBLTBLFIRST 1600 # First extent size (Kbytes) (0 = default) > TBLTBLNEXT 800 # Next extent size (Kbytes) (0 = default)> > # Security > # DBCREATE_PERMISSION: > # By default any user can create a database. Uncomment > DBCREATE_PERMISSON to > # limit database creation to a specific user. Add a new > DBCREATE_PERMISSION > # line for each permitted user. > > #DBCREATE_PERMISSION informix > > # DB_LIBRARY_PATH: > # When loading a (C or C++) shared object (for a UDR or UDT), IDS > checks that > # the user-specified path starts with one of the directory prefixes > listed in > # the comma-separated list of prefixes in DB_LIBRARY_PATH. The string > # "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order > for > # extensibility and IBM supplied blades to work correctly. > > # DB_LIBRARY_PATH $INFORMIXDIR/extend > > # IFX_EXTEND_ROLE: > # 0 (or off) => Disable use of EXTEND role to control who can register > # external routines. > # 1 (or on) => Enable use of EXTEND role to control who can register > # external routines. This is the default behaviour. > # > IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.> > # Diagnostics > > MSGPATH /usr/local/eease/log/online_ndl12a.log # System message log > file path > # -c3 try this out > CONSOLE /usr/local/eease/log/online_ndl12a.console # System console > message path > # CONSOLE /dev/null > > # To automatically backup logical logs, edit alarmprogram.sh and set > # BACKUPLOGS=Y > ALARMPROGRAM /usr/local/informix/etc/alarmprogram.sh # Alarm > program path > ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event> occur > TBLSPACE_STATS 1 # Maintain tblspace statistics> > # System Archive Tape Device > > TAPEDEV /dev/null # Tape device path > TAPEBLK 5120 # Tape block size (Kbytes) > TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 5120 # Log tape block size (Kbytes) > LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes)> > # Optical > > STAGEBLOB # Informix Dynamic Server staging > area > > # System Configuration > > DBSERVERALIASES # List of alternate dbservernames > NETTYPE soctcp,15,100,NET # Configure poll thread(s) for nettype > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-> processor > #v9#NUMCPUVPS 1 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > #v9#NOAGE 0 # Process aging > #v9#AFF_SPROC 0 # Affinity start processor@

On Jan 30, 11:07 am, Madison Pruet <mpru...@verizon.net> wrote: > bozon wrote: > > Enterprise Replication is giving the following message when I do a > > simple cdr list server > > > ndl12:ndl12a common_reporting$ cdr list server > > SERVER ID STATE STATUS QUEUE CONNECTION > > CHANGED > > ----------------------------------------------------------------------- > > cdr error: select bytesqueued from syscdrqueued > > SQL code -1215 :Value exceeds limit of INTEGER precision > > > ISAM 0 : > > > I think it is because I have more than 2 gigs queued to send. I am > > experimenting with it and tried to push all of my data to an empty > > database but it seems that it is going very slowly. I have a 1 gig > > ethernet connection and will have 10 gigs in production. I know I > > don't have it tuned correctly because I know I don't know what I am > > doing. > > If you use 'cdr sync' to push all of the data, then you will be > coordinating the 'push' with the allowable size of the queue, and thus > avoid spooling to disk. You would also have the advantage of > dynamically increasing the queue size for the duration of the sync. > > Also - if you use 'cdr sync' to populate a new node, the data in the > table is not logged to perform the prorogation. If you do dummy > updates, then the logical log will be impacted. > > Generally speaking, spooling (stable storage of the queue) is not a good > thing as it means that additional work is done to 1) read the replicated > transaction and 2) to remove the replicated transaction when it has been > ACKed. You might consider having multiple sbspaces for the stable queue > - some logged (for smaller transactions) and some unlogged (for larger > transactions). > > > > > So any help figuring out how to tune, monitor or diagnose what is > > going on would be appreciated. All comments welcome, since I don't > > know that much about ER, I am sure any comment will be helpful, except > > comments about me being an idiot ( I already know this so you are just > > wasting your time. ;-) ) I will open a case with informix because this > > is pretty crappy behavior that could be fixed easily enough. > > > Below onstat -c output that shows version and settings. > > > Thanks for any help > > > ndl12:ndl12a common_reporting$ onstat -c > > > IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 1 > > days 22:49:03 -- 4004260 Kbytes > > > Configuration File: /usr/local/informix/etc/onconfig.ndl12a > > #> > # onconfig file for instance ndl12a > > # servernums in stage should be 200+ a=1, b=2, c=3, .. > > # > > > # instance type: EN > > > SERVERNUM 201 # Unique id corresponding to a OnLine instance > > DBSERVERNAME ndl12a # Name of default database server> > > # Root Dbspace Configuration > > > ROOTNAME rootdbs # Root dbspace name > > # ROOTPATH /usr/local/informix_dev/eease_en/root_001 > > # ROOTPATH /usr/local/informix_dev/ndl12a/root_001 > > ROOTPATH /usr/local/informix_dev/ndl12a/root_001> > # Path for device containing root > > dbspace > > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)> > # -c3 using 80,000 for EN, should do same for SED, which is currently > > 200,000 > > # (or vice-versa). > > ROOTSIZE 800000 # Size of root dbspace (Kbytes)0> > > # Disk Mirroring Configuration Parameters > > > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > > MIRRORPATH # Path for device containing mirrored> > root > > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > > # Physical Log Configuration > > > # -c3 for install PHYSDBS physdbs # Location (dbspace) > > of physical log > > # -c3 for install PHYSFILE 2097046 # Physical log file > > size (Kbytes) > > PHYSDBS physdbs # Location (dbspace) of physical log > > # -d3 take value from prod > > # PHYSFILE 2097046 # Physical log file size (Kbytes) > > PHYSFILE 599894 # Physical log file size (Kbytes)> > > # Logical Log Configuration > > > # -c3 for install LOGFILES 82 # Number of logical > > log files > > # -c3 for install LOGSIZE 2000 # Logical log size (Kbytes) > > LOGFILES 64 # Number of logical log files > > LOGSIZE 4000 # Logical log size (Kbytes)> > LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) > > > # Tablespace Tablespace Configuration in Root Dbspace > > > TBLTBLFIRST 1600 # First extent size (Kbytes) (0 = default) > > TBLTBLNEXT 800 # Next extent size (Kbytes) (0 = default)> > > # Security > > # DBCREATE_PERMISSION: > > # By default any user can create a database. Uncomment > > DBCREATE_PERMISSON to > > # limit database creation to a specific user. Add a new > > DBCREATE_PERMISSION > > # line for each permitted user. > > > #DBCREATE_PERMISSION informix > > > # DB_LIBRARY_PATH: > > # When loading a (C or C++) shared object (for a UDR or UDT), IDS > > checks that > > # the user-specified path starts with one of the directory prefixes > > listed in > > # the comma-separated list of prefixes in DB_LIBRARY_PATH. The string > > # "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order > > for > > # extensibility and IBM supplied blades to work correctly. > > > # DB_LIBRARY_PATH $INFORMIXDIR/extend > > > # IFX_EXTEND_ROLE: > > # 0 (or off) => Disable use of EXTEND role to control who can register > > # external routines. > > # 1 (or on) => Enable use of EXTEND role to control who can register > > # external routines. This is the default behaviour. > > # > > IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.> > > # Diagnostics > > > MSGPATH /usr/local/eease/log/online_ndl12a.log # System message log > > file path > > # -c3 try this out > > CONSOLE /usr/local/eease/log/online_ndl12a.console # System console > > message path > > # CONSOLE /dev/null > > > # To automatically backup logical logs, edit alarmprogram.sh and set > > # BACKUPLOGS=Y > > ALARMPROGRAM /usr/local/informix/etc/alarmprogram.sh # Alarm > > program path > > ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event> > occur > > TBLSPACE_STATS 1 # Maintain tblspace statistics> > > # System Archive Tape Device > > > TAPEDEV /dev/null # Tape device path > > TAPEBLK 5120 # Tape block size (Kbytes) > > TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes)> > > # Log Archive Tape Device > > > LTAPEDEV /dev/null # Log tape device path > > LTAPEBLK 5120 # Log tape block size (Kbytes) > > LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes)> > > # Optical > > > STAGEBLOB # Informix Dyn

On Jan 30, 6:04 pm, bozon <cur...@crowson1.com> wrote: > On Jan 30, 11:07 am, Madison Pruet <mpru...@verizon.net> wrote: > > > bozon wrote: > > > Enterprise Replication is giving the following message when I do a > > > simple cdr list server > > > > ndl12:ndl12a common_reporting$ cdr list server > > > SERVER                 ID STATE    STATUS     QUEUE  CONNECTION > > > CHANGED > > > ----------------------------------------------------------------------- > > > cdr error: select bytesqueued from syscdrqueued > > > SQL code -1215 :Value exceeds limit of INTEGER precision > > > > ISAM 0 : > > > > I think it is because I have more than 2 gigs queued to send. I am > > > experimenting with it and tried to push all of my data to an empty > > > database but it seems that it is going very slowly. I have a 1 gig > > > ethernet connection and will have 10 gigs in production. I know I > > > don't have it tuned correctly because I know I don't know what I am > > > doing. > On both servers (i.e. source and target) provide the following : onstat -g rqm briefcdr list server On the source provide : onstat -g rqm sendq On the target provide : onstat -g rqm recvq onstat -g dss onstat -g rcv

Bozon, if you are establishing a new ER node, in primary-target scenario it seems in your case AND you can allow for a really short downtime I would approach it differently: first setup HDR pair, it's non- disruptive to your production-to-become-primary instance and pretyy much bullet (and idiot :-)) proof. If all goes fine prepare your scripts to build ER, so servers, replicates/sets or replication templates, whichever you like better, test them on your two test instances with same schema as production just to make sure they work as expected. Then, take that short downtime, disconnect all apps, break HDR and establish both nodes as standard. Now you know they're perfectly in sync. Apply your ER scripts, start the apps and off you go. HTH Cheers Davorin

On Jan 30, 1:35 pm, TBP <TheBigPota...@Nothere.Co.Uk> wrote: > On Jan 30, 6:04 pm, bozon <cur...@crowson1.com> wrote: > > > > > On Jan 30, 11:07 am, Madison Pruet <mpru...@verizon.net> wrote: > > > > bozon wrote: > > > > Enterprise Replication is giving the following message when I do a > > > > simple cdr list server > > > > > ndl12:ndl12a common_reporting$ cdr list server > > > > SERVER ID STATE STATUS QUEUE CONNECTION > > > > CHANGED > > > > ----------------------------------------------------------------------- > > > > cdr error: select bytesqueued from syscdrqueued > > > > SQL code -1215 :Value exceeds limit of INTEGER precision > > > > > ISAM 0 : > > > > > I think it is because I have more than 2 gigs queued to send. I am > > > > experimenting with it and tried to push all of my data to an empty > > > > database but it seems that it is going very slowly. I have a 1 gig > > > > ethernet connection and will have 10 gigs in production. I know I > > > > don't have it tuned correctly because I know I don't know what I am > > > > doing. > > On both servers (i.e. source and target) provide the following : > > onstat -g rqm brief> cdr list server > > On the source provide : > > onstat -g rqm sendq> > On the target provide : > > onstat -g rqm recvq > onstat -g dss > onstat -g rcv I just blanked everything to try it another way. I'll update you with this information when I try it again. Thanks for your help.