Donna Zehl

Forum Replies Created

Viewing 15 posts - 1 through 15 (of 343 total)
  • Author
    Posts
  • in reply to: General #8660
    Frank Knot
    Participant

    Welcome to the HVR Forum

    in reply to: Welcome to the HVR Forum #8661
    Frank Knot
    Participant

    We have setup this new forum.

    This forum is intended to allow us (HVR Software) to share detailed technical info with our experienced users.

    This will include hints, tips & best practices for using HVR and also background info about common problems or new features.

    Users are also welcome to post technical questions and/or answers to this maillist.

    This could be useful if the problem [appears] generic or to seek input from the wider HVR community.

    We will respond to such posts, but it’s not a replacement for HVR Technical Support.

    Most problems are best logged via tickets to support.hvr-software.com

    in reply to: Community Discussions #8884
    Frank Knot
    Participant

    Can SQL Server log-based capture be used when source database is in the Simple recovery model?

    SHORT ANSWER:

    It should be possible when using the old transaction log access method (“SP”). By default, however, HVR uses a newer t-log access method (“FN”) which requires that the source database is in the Full recovery model.

    FULL ANSWER:

    Currently, HVR supports two method of reading data from the SQL Server transaction log. Internally we call the new one “FN”, while the old one is called “SP”. HVR uses FN method by default.

    When using “FN” t-log access method, it is mandatory that source database is in the Full recovery model and a proper log archiving (log backup) procedure is setup. This is required to control transaction log retention- switching database to Full recovery model and making a full database backup ensures that transaction log will never be truncated off the data that has not been backed up (archived) yet. We then read the log backups if the data we need is no longer available in the current (active) transaction log file.

    According to some online sources, SQL Server native transactional replication works with database that’s in either replication model (including the Simple model), so I guess our old t-log access method (“SP”) should work with database in Simple recovery model as well (though we have never tested this).

    It may also be possible to implement an alternative log-retention (or, rather, log-release) logic for the “FN” by calling sp_repldone manually, rather then relying on the SQL Server Agent job. It may allow using “FN” method to capture changes from database in Simple recovery model. This approach has its own drawbacks, though.

    in reply to: Community Discussions #8886
    Frank Knot
    Participant

    HVR log-based capture from DB2 LUW tables can give a NULL values for out-of-row LOB datatypes.

    Root cause

    HVR uses IBM’s DB2ReadLog API for log based capture (LBC). This API can be used in unfiltered or filtered mode.

    DB2LOB

    In unfiltered mode, DB2ReadLog API returns all log records, many of them internal to DB2 and of no interest to HVR. A major limitation of this mode is that column data is not uncompressed.

    In filtered mode, DB2ReadLog API returns only the log records marked as propagatable, by which IBM means log records that would be of interest to replication solutions such as HVR. Filtered mode also decompresses column data.

    Starting with versions 4.7.1/29 and 4.7.3/3, HVR defaults to filtered mode (HVR-785).

    This change was motivated by the realization that DB2 compression is commonly used, yet HVR would only support compression if the environment variable ZIZ_READ_FILTER was set to 1.

    What are my possible recourses?

    Upgrade to DB2 10.1 or higher

    Do not use DB2 compression in DB2 versions prior to 10.1, and add an environment action with /Name=ZIZ_READ_FILTER and /Value=0 to your channel.

    Move all tables containing out-of-row data to a new channel, disable compression for these tables, and add an environment action with /Name=ZIZ_READ_FILTER and /Value=0 to this channel. This is not an option if on the integration side there exist referential constraints between tables captured in separate channels.

    If none of the above options is suitable, please contact HVR Technical Support.

    in reply to: Community Discussions #8888
    Steve Sheil
    Participant

    Background (for all DBMS)

    HVR typically ‘streams’ data into its target databases; the data goes straight over the network pipe into the DBMS’s bulk loading interface (e.g. Oracle direct-path-load) without touching disk.

    We dislike putting data into a temporary file before loading data (this is called ‘staging”), but this is necessary for some DBMS’s that lack a high speed streaming interface.

    HVR bulk refresh streams data straight over the network into the target table via its bulk-load interface. If bulk-load streaming is not available then a staging file is used.

    HVR row-wise compare and refresh streams data straight over the network into row-wise ‘diff’ logic (no table bulk-load). The fact that HVR does this diff without any staging is quite elegant.

    HVR integrate with /Burst streams data straight over the network into the ‘burst’ table via bulk-load interface. If bulk-load streaming is not available then a staging file is used. Afterwards burst uses a ‘set-wise’ insert, update or delete to apply these changes to the target table.

    HVR integrate without /Burst (we call this ‘trickle’ internally) streams data straight over the network into HVR’s row-wise ‘integrate’ logic (no table bulk-load)

    ODBC, LIBPQ and Bulk-load into PostgreSQL

    In the past HVR used ODBC to talk to PostresSQL, but in 4.7.3 we switched to using LIBPQ interface.

    One reason that we switched to LIBPQ is because we wanted to use (abuse!) PostreSQL’s “copy (c1, c2, c3) from stdin” statement.
    This allows HVR to stream data into PostgreSQL at maximum speed, without having to write data to a temporary file (staging ).
    Another reason to switch from ODBC to LIBPQ is that we are building log-based capture from PostgreSQL and that requires LIBPQ too.

    Work on building log-based capture from PostgreSQL is proceeding now.

    Bulk-load into Greenplum

    HVR uses ODBC to talk to Greenplum.

    Greenplum does not have a suitable (e.g. sufficiently fast) ‘streaming’ bulk-load interface, so does HVR needs to do staging (load from a temp file) instead.

    In the past (early HVR 4.7.1 versions) HVR did this staging-bulk-load by writing into a file and then calling ‘copy (c1, c2) from ‘, but this had disadvantageous;

    (a) HVR had to be installed on the Greenplum head node machine.

    (b) HVR needed special privileges for this ‘copy’ interface (a stored procedure could be used to avoid this privilege).

    (c) It was slower.

    So HVR has now dropped this interface.

    HVR for Greenplum now uses the ‘gpfdist’ interface.

    This requires action LocationProperties with parameters /StagingDirectoryHvr and /StagingDirectoryDb (see http://www.hvr-software.com/wiki/locationproperties).

    HVR does bulk-load by writing the data into a temporary file in a staging directory on the machine where the HVR process which is connected to GPDB is running. This directory does not have to be on the GPDB machine; typically it’s not.

    This temp-file is written in ‘TEXT’ format and is compressed.

    Then HVR tells GPDB to bulk-load the data in this file by doing SQL ‘insert into as select * from ‘. This requires that a special Greenplum ‘external table’ exists for each target table which HVR loads data into. HVR will create these when it needs them and it should reuse them afterwards. These external tables are named ‘__x’ or ‘__bx’. They don’t really contain data; instead they are created with a URL which tells them to load data from a URL which points to the staging directory which HVR just filled with new data. The URL used (supplied with /StagingDirectoryDb) should start with ‘gpfdist://’. This requires that the user is running a Greenplum ‘gpfdist’ daemon on the machine that HVR is using; the GPDB nodes then connect to this gpfdist daemon and use it as a file-server to pull the compressed data into the target table.

    Bulk-load into Redshift

    Redshift is a postgres-based distributed DBMS that is hosted in Amazon’s cloud.

    HVR uses ODBC to talk to Redshift.

    Redshift does not have a suitable (e.g. sufficiently fast) ‘streaming’ bulk-load interface, so does HVR need to do staging (loading from temp files).

    For Redshift HVR these staging files are located in an ‘S3’ directory (S3 is Amazon’s big-data file system).
    This requires action LocationProperties with parameters /StagingDirectoryHvr, /StagingDirectoryDb and /StagingDirectoryCredentials (see http://www.hvr-software.com/wiki/locationproperties).

    HVR does bulk-load by first writing the data into temporary S3 staging files.

    This S3 temp-file is written in a form of CSV format (currently it is not compressed).

    Then HVR uses Redshift SQL ‘copy from s3://’. The Redshift data nodes contact S3 to load the data from those temp files.
    No ‘external tables’ are necessary.

    We recommend that the HVR process which is talking to Redshift and S3 is running inside the Amazon AWS, so that the network performance is optimal.

    Regards, Steve

    in reply to: Knowledge Base #8705
    Simon Faas
    Keymaster

    The Ingres database has had a change in its logging system with patch level 14983: introducing the new REPLACE type (for Ingres Bug #130681).

    This causes compatibility issues with older HVR versions. Versions before HVR 4.7.3/33 encounter error:
    F_JT0400: Log scanning error F_JZ2204. Unexpected Ingres record type 179 encountered while processing record with LSN [..]

    Whereas HVR version 4.7.3/34 & /35 would silently ignore updates to source tables.

    The is bug report HVR-1458 which was fixed in HVR 4.7.3/36

    Please contact customer support if you encounter problems.

    in reply to: Knowledge Base #8710
    Simon Faas
    Keymaster

    Due to a problem with compatibility between HUB and certain GUI versions, several problems can occur:

    Wrong matching of parameters to actions, causing, e.g. the gui to display: Environment /Name /NoBeforeUpdate
    Crashed on actions using very new parameters.
    GUI not showing some actions (e.g TableProperties)

    This can occur when an HVR GUI version older than 4.7.3/8 connecting to a hub version newer than version 4.7.3/8

    If that is the case in your environment then please upgrade the HVR GUI to a version greater than 4.7.3/8 (e.g. 4.7.3/25).

    This is bug-report HVR-957 which was fixed in versions 4.7.1/40 & 4.7.3/9

    Please contact customer support if you encounter problems

    in reply to: Knowledge Base #8762
    admin
    Member

    When trying to connect to a location with correct ORACLE_HOME set, the following error “F_JG202F: Attempt to load shared library failed” can happen even though environment variable LIBPATH (AIX) is set for HVR Operating System user.

    The workaround is different depending on whether the remote connection uses an hvrremotelistener or an inet-daemon.

    For the hvrremotelistener make sure that LIBPATH is set for the HVR Operating System user BEFORE the hvrremotelistener is started. The environment of the running process can be checked using command: ps eww {pid of hvrremotelistener}
    If LIBPATH is not returned from previous command, set the correct LIBPATH and restart the hvrremotelistener.

    For the inet-daemon it can happen that the environment variable LIBPATH is not passed to the HVR process and then the workaround is to add the following in /etc/inetd.conf on the line of the HVR service: -ELIBPATH={path to oracle_home/lib}

    (example -ELIBPATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib)

    After this the inet-daemon needs to be restarted as root by the following;

    stopsrc -s inetd

    startsrc -s inetd

    Re-test connection and now it should work.

    • This topic was modified 2 years, 9 months ago by HVR Software.
    • This topic was modified 2 years, 9 months ago by HVR Software.
    • This topic was modified 2 years, 9 months ago by HVR Software.
    • This topic was modified 2 years, 9 months ago by HVR Software.
    • This topic was modified 2 years, 9 months ago by HVR Software.
    in reply to: Knowledge Base #8768
    Frank Knot
    Participant

    Question: What are these files?

    Answer: These are dumps of the contents of the Hvr catalogs, created by Hvr Load so that capture and integ jobs can inspect actions without having to make an expensive connection to the hvr hub database.

    Question: Where are they?

    Answer: Hvr Load creates these in $HVR_CONFIG/router///catalog

    Question: How their contents be inspected?

    Answer: To see the contents of these files, use this command;

    $ hvrrouterview hub chn 53ba7f7e_6a5a4.cache > cache.xml

    Question: Why are there sometimes multiple files?

    Answer: Hvr Load does not want to delete an old file and create a new one because it’s worried that a running job may be reading that file.

    Instead it creates a new file with a bigger timestamp in its basename (“53db3914” means “Aug 1 2014” in hex), leaves the file that was ‘current’ and deletes other cache files.

    At the start of each cycle the running jobs should check if a new cache file has been created and then reload it.

    • This topic was modified 2 years, 9 months ago by HVR Software.
    • This topic was modified 2 years, 9 months ago by HVR Software.
    in reply to: Knowledge Base #8758
    Frank Knot
    Participant

    HVR is able to capture (log based) from an Oracle Materialized View.
    This type of view is actually just a table which is updated periodically by Oracle using the view definition.

    To capture from such a view, its definition has to be entered manually (as a table) in the HVR catalogs. Table select / table explore currently only selects regular tables.

    On the target, the contents of the materialized view will be replicated to a regular table.

    in reply to: Knowledge Base #8760
    Simon Faas
    Keymaster

    For certain datatypes (like SQL Server geometry types) HVR uses “extended datatype support” instead of native datatype support.

    Here is how to setup replication (including capture and refresh) of such datatypes.

    Note that extra configuration is needed to replicate these columns.

    In this example have a table t1:

    CREATE TABLE [dbo].[t1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [geom1] [geometry] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    The table is added the channel. After adding this table to the channel you will see that the column is not present. You will need to add it manually in the Table Properties dialog. It needs to be added with the same name but with datatype clob.

    The following actions are needed for HVR 4;

    { group=SRC table=* DbCapture /LogBased }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="geom1.ToString()" /Context=refresh }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="select t.geom1.ToString() from t1 t where t.id={id}" /Context=!refresh }
    { group=TGT table=* DbIntegrate }
    { group=TGT table=t1 DbIntegrate /DbProc /DbProcDuringRefresh }
    { group=TGT table=t1 ColumnProperties /Name=geom1 /CaptureExpression="geom1.ToString()" /IntegrateExpression="geometry::STGeomFromText({geom1},0)" }

    The actions for HVR 5 are nealy the same;

    { group=SRC table=* Capture }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="geom1.ToString()" /Context=refresh }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="select t.geom1.ToString() from t1 t where t.id={id}" /Context=!refresh }
    { group=TGT table=* Integrate }
    { group=TGT table=t1 Integrate /DbProc }
    { group=TGT table=t1 ColumnProperties /Name=geom1 /CaptureExpression="geom1.ToString()" /IntegrateExpression="geometry::STGeomFromText({geom1},0)" }

    As you see, I have 2 capture expressions on the source; with different context. One will be used during refresh (remember to activate it in the Context tab); the other one is used for replication.

    On the target you need to use integration and refresh with database procedures for table t1

    On the integrate we have an integrate expression and also a capture expression. The latter is needed to make HVR compare work.

    in reply to: Knowledge Base #8756
    admin
    Member

    This article describes how to setup parallel refreshes for large tables in HVR using a refresh condition with user variables to split the refresh load. For each refresh the content of the user variables will be changed, so the result will be multiple scheduled refreshes, each with it’s own range of rows to be refreshed for a specific table.

    View PDF here

    in reply to: Community Discussions #8875
    Mark
    Keymaster

    What does the error mean, and how can I get around it?

    F_JG2901: UNKNOWN string ” containing invalid sequence encountered while encoding table ” column ”

    in reply to: F_JG2901: invalid sequence encountered #8877
    Mark
    Keymaster

    This error is a likely indicator of data corruption in the database.

    Internally HVR uses UTF8 to exchange data and hence there should be no data that cannot be transferred. As a result HVR will translate every string it finds in the database into a UTF8 string. HVR identifies character encoding in the database and will take encoding into consideration when the translation happens. The error “F_JG2901: UNKNOWN string ” containing invalid sequence encountered while encoding table ” column ”” (with values for the indicators in angle brackets) in indicates that somehow a string could not be translated into a valid UTF8 string.

    To get around this error you can use the action TableProperties /IgnoreCoerceError. This action will ignore the error and simply put in a valid character (like an upside-down question mark) for whichever character did not translate into a UTF8 string. This means that the actual data changes, but given it is invalid data to begin with this may be much better than having the replication fail.

    You may however want to do some root cause analysis when you run into a problem like this. I have seen this a few times on an Oracle Database. A couple of ways I have used in the past to find offending/corrupted data are:

    • Export (e.g. using expdp) the data and import it again. This has in some cases exposed the corrupted data.
    • Perform computations on the corrupted data when the data was numeric. This gave very weird results (e.g. 10x a positive number resulted in a negative amount); note that the error for this corruption was invalid target numeric size i.e. a different error than in this question.

    Of course when the corrupted data has been identified you can go back to the application/users to fix the data.

    in reply to: Community Discussions #8882
    Mark
    Keymaster

    Can I use HVR to replicate case sensitive table or column names?

    Yes, the action to use if at least one of the tables in the replication setup (channel) has a case sensitive name or column name is LocationProperties /CaseSensitiveNames. This action is particularly important against Oracle Databases when by default Oracle will assume table and column names are case insensitive unless quoted. The action LocationProperties /CaseSensitiveNames will ensure that table and column names will be quoted.

    Please note that the action LocationProperties /CaseSensitiveNames:

    1. Always applies to all tables in the channel.
    2. Is required before tables are added to the channel, since by default HVR will assume that table and column names are case insensitive. If tables were already added (through the Table Explore/Table Select function in the GUI) then tables will have to be removed and re-added to the channel.

    You may not realize that a table uses a case sensitive name or column name until you see errors like:

    • F_JD0006: A table named [.] does not appear in the DBMS catalogs during Table Explore/Table Select.
    • ORA-00942: table or view does not exist during a Refresh.
    • ORA-00904 “”: invalid identifier, e.g. during a Refresh. This may be an indication that the column reported in uppercase may in fact be in mixed case in the database.
Viewing 15 posts - 1 through 15 (of 343 total)

© 2019 HVR

Try now Contact us