Month: December 2018

Getting SQL Replication Info Programmatically from Java

winterWisteria Wisteria leaves in winter.



SQL Server has a process called replication, whereby you can automatically copy records from one database to another, and keep them in-sync.  One use for this is to have one database as the transactional database, and another to report from, with the reporting database having indexes and constraints tailored more toward faster reporting (reads) than fast input.

Sometimes replication gets a little behind, and if you are running reports on the database that is a little behind, you might like to know.

The stored procedure sp_replcounters will tell you the count of transactions awaiting transfer to the distribution database (which lies between the transaction database and the destination database in a replication process).  You can call this stored procedure, but you want it to return the result set to Java.

One way to achieve this is to wrap your own stored  procedure around it, put the records into a temp table and query the temp table to return the result set.  This only works if you specify SET NOCOUNT  ON, otherwise the Java Persistence API (JPA) gets confused by the multiple queries and you don’t get the result set in your Java code.

CREATE PROCEDURE GetReplicationLatency


--This is important.  Results from multiple queries within stored procedures throw JPA off unless SET NOCOUNT ON is used.

declare @db varchar(50)
set @db = db_name()

create table #tempTable ([database] varchar(50), [replicated transactions] int, [replication rate trans/sec] int,
 [replication latency (sec)] decimal(16,6), replbeginlsn varchar(40), replnextlsn varchar(40))
insert into #tempTable
exec sp_replcounters
select * from #tempTable where [database] = @db

drop table #tempTable


Then your Java code looks like this:

private static final String REPLICATION_LATENCY_QUERY = "exec GetReplicationLatency ";Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
List<Object[]> results = query.getResultList();




Hibernate Dynamic Update

ThorAndMe  That’s a pic of me with Thor.

I had a situation come up where every time a particular table was updated, an indexed view was also getting updated.  That view was intended to speed up a report, but it made the table update really horrible.  The view would only get updated if one particular column in the table was being updated.  That column never got updated through the application, because once the record was created that column’s input field got grayed out.  But Hibernate’s default behavior is to update all columns rather than to check which ones changed and only update the changed columns.

There is a performance trade-off.  Usually you probably want the default behavior.  But in this case, definitely not.

But Hibernate has an option to allow for updating only the columns that changed.  This option is called dynamic-update.  Different versions of Hibernate implement it differently.  In the example in this blog post, hibernate-annotations version 3.4.0.GA was the dependency brought in.

Because Hibernate’s org.hibernate.annotations.Entity class is not a replacement, but rather a supplementation of javax.persistence.Entity, you have to implement it like this:

import javax.persistence.Entity;
@org.hibernate.annotations.Entity(dynamicUpdate = true, selectBeforeUpdate = true)
@Table(name = "blah")
public class Blah...

The selectBeforeUpdate option is not required with the dynamicUpdate.  It basically states that before doing an update, do a select to make sure you have the most recent copy of the table, then the dynamicUpdate option says only update changed columns.