Skip to Content
Technical Articles
Author's profile photo Mark A Parsons

Data Assurance (DA) agent : external sorting


DA       : SAP Replication Server Data Assurance Option

           DA Server/15.7.1/SP200

DA setup : Single-Server deployment

Source   : Adaptive Server Enterprise/15.7/EBF 22281 SMP

           SP110 ONE-OFF/P/RS6000/AIX 6.1/ase157sp11x/3546/64-bit

Target   : Adaptive Server Enterprise/15.7/EBF 22281 SMP

           SP110 ONE-OFF/P/RS6000/AIX 6.1/ase157sp11x/3546/64-bit


The boss dumped DA into my lap with the instructions: Get it running, and don’t bog down my dataservers!

Started looking through the SCC Installation and SCC for DA manuals, got a headache, and went to the DA Installation and DA Users Guide manuals. [Command line’s gotta be easier than GUI, right?  Thankfully the answer is ‘yes’.]

Got the software installed, configured and started a DA server instance (and the associated agent), and successfully used isql to connect to my DA server instance. That was easy. [I won’t bore you with the all the details, especially since that’s not the topic of this blog, eh.]

Ten minutes later I’ve got a pair of source/target connection profiles setup, a single compareset defined (3.5 million row table, ~500MB of raw data), and a basic row comparison job configured.

The first run of my job took 3 minutes to complete and verified the source and target tables were in sync. [That’s good, especially seeing how I set up replication!]


Just one problem … the agent is sorting the table in the dataserver (via a order by clause). [NOTE: A query of monSysSQLText showed the agent using the order by clause.]

A quick search of the DA Users Guide and I figure out I have to explicitly enable the agent’s external sort capability (by default the agent uses the order by clause to sort the data in the dataserver):


alter job job_mydb_mytable

alter comparison cmp_mydb_mytable

set EXTERNAL_SORT to true



Run the job again and … *dang* it, still using the order by clause.

Another search of the DA Users Guide turns up a small troubleshooting section that suggests lowering the threshold at which an external sort is triggered (by default the agent won’t perform an external sort if the table rowcount < 10million).

OK, I can do this.  My table is 3.5 million rows so I’ll just lop a ‘0’ or two off the current threshold:


config external_sort_activate_size 100000



Run the job again and … *shoot*, still using the order by clause.

No more useful troubleshooting steps in the manual.

Wait a minute … if the trigger for performing an external sort is based on a rowcount … then … ummm … how does the agent get the rowcount in the first place?

Another search of the manual and I trip across the ENABLE_ROW_COUNT job setting, which in turn jogs my memory … in an attempt to limit the impact on the source/target dataservers (don’t bog down my dataservers!) I had made the ‘smart’ decision of disabling the job’s ability to perform a rowcount.

Once more unto the breach:


alter job job_mydb_mytable

alter comparison cmp_mydb_mytable

set ENABLE_ROW_COUNT to true



That’s gotta do it! Right?

Run the job again and … YES! … the agent does a straight select without an order by clause [verified per monSysSQLText] … but … the job finished in just a couple seconds. The Sybase SAP salesperson did say DA was fast but that was a bit too fast …

Checking the history for this latest job run shows an error:

====================; /opt/sybase/DataAssurance/DA-15_5/agent/myagent/data/sort/mydb_connection_mydb-mytable1397160641891_1 (No such file or directory)


Oh, you’ve gotta be kidding!

It would appear that when the DA installation process created the agent installation it failed to create the ‘data/sort’ subdirectory structure.  Great, so how do I go about creating a directory in UNIX … … ‘how to create a directory in UNIX’ … JUST KIDDING! I know how to create a directory! [give me some credit!]

So I create the subdirectory structure [actually, I created a soft link to a filesystem with a large chunk of free space => wouldn’t want to fill up my /opt/sybase filesystem with a bunch of sort files for some of the larger tables I’m going to need to sort], and run the job again …

FINALLY!  The agent uses the new ‘data/sort’ subdirectory structure to perform an external sort and … tada … the job run time is comparable to the previous internal sort (via order by), namely, ~3 minutes.


Steps to insure the DA agent will perform an external sort (ie, disable use of the order by clause when pulling data from the source/target tables):

  • – make sure a ‘data/sort’ directory is created under the agent installation directory [if your agent installation is on a smallish filesystem then create a soft link for ‘data’ to a directory on a larger filesystem, then create the ‘log’ subdirectory out under the new ‘data’ directory]
  • – make sure your job is configured to perform a rowcount [create/alter jobset ENABLE_ROW_COUNT to true]
  • – make sure your job is configured to perform an external sort [create/alter job … set EXTERNAL_SORT to true]
  • – reduce the DA server’s threshold for allowing an external sort to a value less than the current rowcount of your table [config external_sort_activate_size <new_threshold_value>]; if you want to use external sorting for all tables then set to 0

NOTE: A few years after writing this blog … 2017? 2018? *shrug* … I eliminated all the hassles re: running counts and internal vs external sorting; since then I force all sorts to be done by the DA agent; settings of interest: ENABLE_ROW_COUNT = false, EXTERNAL_SORT = true. DA agent sorting is as fast (if not faster) than the database engine (especially if the database engine opts for a slow-running index scan in lieu of a select-n-sort), and with these ‘default’ settings I no longer have to worry about inadvertently running a ‘select / order by‘ query that bogs down a (production) database engine or worse fills up temporary space while sorting a large data set.



The boss will have to live with the ‘select count(1)’ query overhead until DA figures out how to pull a close-enough rowcount value from systabstats.

Assigned tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mark A Parsons
      Mark A Parsons
      Blog Post Author


      I've been working with some not-publicly-released-yet patches to DA that includes a new option in the server and agent config files (tentatively called which allows (actually requires) the operator to designate a directory the server and agent can use for data storage (eg, intermediate sort sets for agents; job reports and reconciliation scripts for the server).

      This new configuration setting eliminates the need to setup the 'data/sort' directory for the agent, and a 'data' directory for the server.  In my environment I go ahead and setup these directories anyway (actually the 'data' directory is a link to the same directory value from the config file).  This allows me to jump to the data directory from the agent/server directories without having to take a peek at the config file to figure out where I placed the data directories.