Are there "database workload capture & replay"-products for SQL Anywhere 12 (like, for example, "Real Application Testing" for Oracle databases or iReplay for Sybase ASE)?

asked 11 Feb '13, 06:59

Ilia63's gravatar image

Ilia63
736253149
accept rate: 16%


Internal database client packet-level replay tools do exist in technical support. However, we do not publicly distribute these tools with SQL Anywhere - they are generally for internal use (or would be provided directly to customers via a technical support case). Externally, we expect these tools to be used for diagnostic purposes only, in scenarios where the interaction between the database server and database client isn't fully understood and we are trying to capture the client-level protocol behaviour for later analysis.


May I ask what the background is for this question? Is this intended for a performance test?

If you are trying to create a performance test, you may want to take Volker's advice but modified slightly: you will likely also want to consider all requests to the database server (including SELECTS), so relying on just the transaction log will miss out on any operations that do not change data. You should run a request-level log capture (dbsrv12 -zr SQL+HOSTVARS -zo rll.txt ...) and run the \Samples\SQLAnywhere\PerformanceTraceTime\tracetime.pl Perl script over the resulting request-level log log in order to extract all of the database operations to a SQL script that you can use for repeated client testing.

permanent link

answered 11 Feb '13, 09:15

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

Thank you. This is a very interesting and important tool.

(11 Feb '13, 09:45) Ilia63

What about translating the transaction log with DBTRAN (possibly for a certain log offset range and including/excluding particular users) and run the generated SQL against a backup of the database that does not already contain these operations?

I.e. in a very simple scenario:

  • To capture: Make a full database backup with log truncation and then run your original queries against the database, and then stop that database and DBTRAN the active log into a replay.sql

  • To replay: Run a copy of the database backup (so it doesn't contain your test statements) and use a SQL tool like DBISQL to run the replay.sql.

permanent link

answered 11 Feb '13, 07:30

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

Thank you for Your reply. But I would like to have a tool, for example, to test the patches.

Suppose I need to test the new patch (EBF). I keep a copy of the production database (= backup of the database), delete the log-file and start production database to capture the workload. Running the dbtran for the new log-file (for example, dbtran -a -t db.log db.sql).

Then I apply a new patch to the backup of the database. Can I apply this script to verify of the patched database? Let I get the script for each user (for example, dbtran.exe -a -t -u "surg01" db.log db_surg01.sql, ...). Can I use them to test the performance change or deadlocks for patched database?

In my opinion, to test the patched database, we should apply the script is not for every user, but for each connect. And we must do it in sync. Can I do this using a SQL Anywhere or SQL Anywhere - utility?

(11 Feb '13, 09:35) Ilia63
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406

question asked: 11 Feb '13, 06:59

question was seen: 496 times

last updated: 11 Feb '13, 09:45