<div>
tune the report<br />
that'll be way less work than the direction you are going<br />
<br />
the application team obviously has no idea of how a database works
</div>


Angela Allen

<div>
Hi,<br />
Care to elaborate on your response? &nbsp; &nbsp;Are you just saying tune the report to get it to run in less time?
</div>


<div>
it doesn't matter towards updates how long the report runs<br />
once the report is started, if written correctly, it won't see any more updates<br />
<br />
a report is usually a select query<br />
a select query starts at certain point in time, and all data comes from that point in time<br />
it disregards all changes from later in time<br />
<br />
so in that context, why would devs want to stop systems updating items ?<br />
<br />
i hope you aren't replicating a database just to run reports<br />
because that would undermine the whole concept<br />
<br />
it might be better for performance to run the reports on the replicated db<br />
but you'd have to consider running the report at the correct time, ie when the replication is finished for that report<br />
<br />
i could give an example of a report running for 21 hours ...<br />
after we tweaked it, basically rewrote it from the ground up, it took 0.3 second<br />
we deleted all replication/copying stuff and only left the selects in which were needed<br />
replication/copying takes time and is usually not needed for reports
</div>


<div>
What you've been asked to do can't really be done without breaking replication.<br />
<br />
Think about it - replication doesn't copy data, it copies and reapplies transactions, in the order that they committed. Transactions cannot be skipped - that breaks replication consistency and now the replicate doesn't match the source. Transactions cannot be only partially applied - that breaks the definition of a database transaction.<br />
<br />
As soon as there is a transaction that writes to any of the tables they care about, you have to pause all replication. Anything else falls into one of the two scenarios above - it breaks transactional consistency (and now the database is entirely worthless), or it breaks replication consistency (and now must be fully resynced from the source).<br />
<br />
IF the tables for these reports are entirely separate to every other table in the database - and I seriously doubt it, but let's imagine this is true - then you could define two separate database repdefs for the source and two separate db subscriptions, one to each db repdef. The first db repdef would be for all of the tables in the reports; the second would be for all other tables.<br />
<br />
That would allow you to replicate one set independently of the other... but it only works if the two sets of tables have zero overlap. And if they have zero overlap... I would instead suggest you make them separate databases on separate ASEs.<br />
<br />
TL;DR - you can strictly speaking stretch what MSA is capable of to do this, but you shouldn't, and you will regret it if you do.<br />
<br />
I agree with the first responder - tune the replicate database. This is a standard problem with many standard answers. I would also ask what's the big deal with replication getting one hour behind?
</div>


<div>
Thanks Joe,<br />
<br />
fyi: Here is the request:<br />
Every night at 11 pm we run a scheduled task that runs several stored procedures used to capture the state of the products at that moment in time. The job takes about an hour or so to run, so the state of things can change while we’re running it. Accounting is pushing us to find a better way to take the inventory snapshot each night to reduce any data 'inconsistencies' caused during the long run time. <br />
<br />
I agree, the simplest solution is suspend replication for the entire database, run the reports &amp;&nbsp;resume replication.<br />
We definitely will not intentionally skip any transactions. &nbsp;These are not independent/separate tables - There is relational data in various tables.<br />
<br />
The shop treats the current replicated database like it is critical production database so I wanted to provide them with what they need with the least possible impact to the current replicated environment.<br />
<br />
But, it seems the best option may be the one you mentioned ...but requires more effort to implement and maintain.<br />
&quot;...then you could define two separate database repdefs for the source and two separate db subscriptions, one to each db repdef. The first db repdef would be for all of the tables in the reports; the second would be for all other tables.&quot;
</div>


<div>
You've said that the tables are not independent or separate though, so you cannot use two separate repdef/subscription pairs. Your database will quickly go out of sync and have incorrect data if you go down that path. It is not an option.<br />
<br />
The best you can do without significant report tuning or changing how your databases work is to manually lock the tables needed for the snapshot report: put a BEGIN TRAN and LOCK TABLE ... EXCLUSIVE at the start of the report, and ensure you've locked each table. You can let replication run as normal. It will get as far as it can until it needs to write to one of the tables, and then it will be blocked and will wait for the report to complete.<br />
<br />
A better solution is to make the report faster. It seems unlikely they are as fast as they can possibly get. Things to look at here are mostly indexes... the replicate database does not have to have the same indexes as the source, a trick that isn't used often enough.<br />
<br />
If they really need to be able to run these reports without any interference with replication and vice versa then I suggest a second database (can be in the same ASE), sized only large enough to fit what's needed for these reports, and replicate out of your current replicate into that, and only for the tables required for the reports.<br />
<br />
SOURCE -&gt; MSA (all tables) -&gt; REPLICATE -&gt; MSA (reporting tables only) -&gt; REPORTING<br />
<br />
i.e. Report database is a subset of Replicate database. This allows you to always run replication into the replicate, but as required pause the further replication into the reporting database. Because this is a subset of tables it can be much smaller, and doesn't have to be a dump &amp;&nbsp;load compatible size.
</div>


<div>
Thanks,<br />
<br />
I like this solution - but please see question/issue below:<br />
SOURCE -&gt; MSA (all tables) -&gt; REPLICATE -&gt; MSA (reporting tables only) -&gt; REPORTING<br />
<br />
Please advise:<br />
I would create/configure new replication database, <br />
create database repdef for Report tables only<br />
create subscription <br />
BUT<br />
How does this subscription materialize? &nbsp;Do I have to do a dump/load of full database?[/b]<br />
Are there any other options? &nbsp;I believe if I am doing a bcp ( which will take some time)<br />
I need to manually stop replication and I really need to suspend Production operations- &nbsp; Sybase DOC includes the following:<br />
&quot;When you use the no materialization method, you can materialize the replicate databases using bcp, dump and load, mount and unmount, or other methods. Because Replication Server does not coordinate the initial database synchronization process, you will likely need to suspend database applications.&quot;
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
I am working with Sybase Replication 12.6 using MSA. &nbsp;There are reports that run against about 60 tables in the replicated database.<br />
The application team wants to suspend updates to those tables while the reports &nbsp;are &nbsp;running. &nbsp;The report takes approximately one hour.<br />
There are about 100 other critical tables in this database and they prefer not to suspend the connection for the entire database.<br />
<br />
It seems I can create a 2nd database and define a subscription but there are a few tables that are extremely large that are not part of the report.<br />
If &nbsp;I define a subscription for a 2nd replicated database, I need to perform a dump/load- so this replicated database will need to be as large<br />
as the source database and unfortunately with Sybase 12.6, I can't shrink the database - totally wasted space.<br />
<br />
Basically, what I need in addition to my MSA replicated database is a snapshot of the database or a subset of the database.<br />
Is there a way to suspend updates for only the tables in the report for my current replicated database?<br />
<br />
I thought about creating a database at primary with only views for the tables in the report then trying to replicate that database.<br />
Then I can suspend connections to that database. &nbsp;Any reason this will not work?<br />
<br />
Any other ideas on how to accomplish this?<br />
<br />
<br />
THANK YOU!!!
</div>
</div>


Hi,
I am working with Sybase Replication 12.6 using MSA.  There are reports that run against about 60 tables in the replicated database.
The application team wants to suspend updates to those tables while the reports  are  running.  The report takes approximately one hour.
There are about 100 other critical tables in this database and they prefer not to suspend the connection for the entire database.

It seems I can create a 2nd database and define a subscription but there are a few tables that are extremely large that are not part of the report.
If  I define a subscription for a 2nd replicated database, I need to perform a dump/load- so this replicated database will need to be as large
as the source database and unfortunately with Sybase 12.6, I can't shrink the database - totally wasted space.

Basically, what I need in addition to my MSA replicated database is a snapshot of the database or a subset of the database.
Is there a way to suspend updates for only the tables in the report for my current replicated database?

I thought about creating a database at primary with only views for the tables in the report then trying to replicate that database.
Then I can suspend connections to that database.  Any reason this will not work?

Any other ideas on how to accomplish this?


THANK YOU!!!

How to suspend replication to a table with MSA database replication

Distributed File System Replication (DFSR)

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

SAP makes enterprise software products, including Customer Relationship Management (CRM), Enterprise Resource Planning (ERP), Product Lifecycle Management (PLM), Supply Chain Management (SCM) and Supplier Relationship Management (SRM) titles, the database product Sybase, along with numerous other products specific to various industries, using a variety of platforms and frameworks.

Sybase, a subsidiary of SAP, builds a client/server relational database management system. Products include Adaptive Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase Unwired Platform (SUP) for mobile applications, Afaria for enterprise mobile device management and IQ for data warehouse and big data applications.