Workload capture & replay for SQL Anywhere 12

[former_SQLA_member326303]

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)?

[jeff_albion · Accepted solution]

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.

[former_SQLA_member326303 · Accepted solution]

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

[VolkerBarth]

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.

[former_SQLA_member326303]

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?