The MTSQL scripting tool is useful for testing and benchmarking your applications. It can be used to write multi-threaded SQL scripts, synchronize separate threads, and capture output from each thread separately.
Like sqlline, MTSQL is automatically configured to connect with s-Server based on the information you supply during client tools installation.
Default connection values can be set for all the tools in the clienttools/default.conn.properties file. The properties are specified there as DRIVER, SERVER, NAME, and PASSWORD.
Each of those default values can be overwritten by passing a command-line argument to the script as follows:
Property | Switch | Argument | Example |
---|---|---|---|
SERVER | -u | URL | -u jdbc:sqlstream:sdp://example.com:5571 |
DRIVER | -d | Driver class | -d com.example.jdbc.Driver |
NAME | -n | User name | n sqlstream |
PASSWORD | -v | Password | -v mypassword |
A MTSQL script is typically divided into sections with the following overall structure. Keywords in mtsql scripts start with an @, and denote special actions to be taken by the test harness, such as repeating a sequence of SQL statements, setting a query timeout, and so on.
Each part is a list of SQL statements and mtsql commands.
Note that mtsql may exhaust memory if the results are prolix. By default it saves every result row from a query.
SQL statements can appear anywhere within the @setup and @thread blocks.
For example:
select * from sales.depts; -- good
@timeout 5000 select * from sales.depts; -- good
@timeout 5000 select *
from sales.depts; -- good
@timeout 5000 select * from sales.depts -- bad, missing semicolon
select * from sales.emps;
select * from sales.depts -- bad, missing semicolon
@timeout 5000 select * from sales.emps;
select * from sales.depts; @sync -- bad, sync must be on next line
Three files are typically involved when using mtsql:
It is conventional to use the extension .mtsql for multi-threaded test scripts. This makes it easier to distinguish them from single-threaded test scripts.
The key mtsql commands are: @sync, @prepare, @fetch and @print which sets the print mode.
The next section presents a complete list of mtsql commands and keywords.
Keyword | Description |
---|---|
@close | Closes the prepared statement (see @prepare). (If @close is omitted, @end would force a close, but as a “best practice”, @close should be used.) |
@include | Indicates external test script to be included, either at the @setup phase or later. |
@disabled @enabled | Controls whether execution of the test is disabled or not. If neither keyword is present, the default is @enabled . These keywords must appear at the start of the file (before @setup or @thread, or not at all. |
@err | Executes the given SQL (DDL only) and print the error class and message. May only appear inside of a @thread or @repeat block. |
@fetch | Opens a cursor and reads rows. |
@lockstep @nolockstep | Controls whether execution of each command in the script is synchronized. If neither keyword is present, the default is @nolockstep. These keywords must appear at the start of the file (before @setup or @thread), or not at all. @lockstep is not the same as synchronized. Instead, @lockstep is equivalent to a @sync after every line, so that all threads “march in lockstep”: all do their 1st line, then all execute their 2nd line, etc. Since this is not very flexible, so the usual is @nolockstep , when the threads meet up only at the @sync commands: all threads pass the same @sync at the same time. |
@prepare <SQL>@sync@fetch [T] @close | Prepares the given SQL (opens a JDBC prepared statement). Executes and fetches the results with an optional query timeout of T milliseconds, writing them into the .ref file. Closes the statement. Only the @sync or @sleep keywords may appear between these three keywords. May only appear inside of a @thread or @repeat block. |
Sets the format for printing the output of a sql select. Parameters: If no parameter is specified, @print prints every row in the result set (this is rarely desirable). every n means print a row after fetching n rows, 2n rows, 3n rows, … total means (also) print a row at the end. time means print the elapsed real time count means print the number of rows (so far). Note: a “(timeout)” in the log file indicates that the query timed out — the elapsed time does not include the timeout so it is valid to conclude that rows were read at the rate count/time, referring to the output of the print command. Examples: @print @print every 3 @print total @print time @print count | |
@repeat N … @end | Specifies that the given commands should be repeated N times. May only appear inside of a @thread block. |
@setup … @end | Defines SQL statements that will execute before the multi-threaded portion of the test is started. Optional. |
@sleep T | Sleeps for T milliseconds. May only appear inside of a @thread or @repeat block. |
@sync | Specifies that the given thread should block and wait for all other threads to reach their @sync commands. The same number of @sync keywords must appear in each thread once all loops have been unrolled. @sync can only be used with @nolockstep. May only appear inside of a @thread or @repeat block. |
@thread X[,Y, …] … @end | Defines one or more threads of execution and their SQL commands. Note that if multiple thread names (X, Y, …) are given, multiple threads will execute the same commands. At least one @thread block must appear in the script. If only one appears, you should probably be using the old single-threaded testing. |
@timeout T | Executes the given SQL with a query timeout of T milliseconds. The statement is prepared and executed, all results are fetched and written to the .ref file, and the statement is closed. May only appear inside of a @thread or @repeat block. |
When setting the fetch timeout parameter, it is important to make sure that the query does not time out before it fetches anything. This means that the fetch timeout needs to be greater than the sum of the time it takes to execute the individual statements in each thread when run simultaneously.
If the query times out before getting any data the .log file will show any empty table. The required fetch timeout value can vary depending on the speed of the machine you are running on, and/or the loads on the system at runtime.
Below are two examples of MTSQL scripts. The first has two pumps and the second one has one pump.
@setup
@include catalog.sql
@end
@thread reader
-- @prepare prepares the query, in sync with the first pump starting up (see thread writer).
@prepare SELECT STREAM FLOOR("test_stream".ROWTIME TO HOUR) AS FLOOR_HOUR, COUNT(\*) as "NumberOfRows", COL1, COL2, COL3, COL4 FROM "SCHEMA1"."test_stream" GROUP BY FLOOR("test_stream".ROWTIME TO HOUR), COL1, COL2, COL3, COL4;
@sync
-- The server is waiting to receive data from the pumps, once it receives data it is waiting for
-- an hour’s worth of data before it spits out a row (see query above). The query is using a promoted
-- rowtime (ie rowtime not equal to current time but rather the time passed in from the data).
-- For this reason the fetch timeout is set to one hour + time to start first pump + time to start
-- second pump = 360000 + 60000 + 30000 = 450000
-- (this is =(time to start pump) + (time to emit first row))
@print total time count every 1
@fetch 450000
@close
@sync
@sync
@end
@thread writer
ALTER PUMP "Stage1" START;
@sync
ALTER PUMP "Stage2" START;
@sync
@sync
@end
@thread cleanup
@sync
@sync
@sync
ALTER PUMP "Stage1" STOP;
ALTER PUMP "Stage2" STOP;
DROP SCHEMA "SCHEMA1" CASCADE;
@end
This is the same MTSQL script with only one pump:
@setup
@include catalog.sql
@end
@thread reader
@prepare SELECT STREAM FLOOR("test_stream".ROWTIME TO HOUR) AS FLOOR_HOUR, COUNT(*) as "NumberOfRows", COL1, COL2,
-- @prepare prepares the query, in sync with the first pump starting up (see thread writer).
COL3, COL4 FROM "SCHEMA1"."test_stream" GROUP BY FLOOR("test_stream".ROWTIME TO HOUR), COL1, COL2, COL3, COL4;
@sync
-- The server is waiting to receive data from the pumps, once it receives data it is waiting for
-- an hour’s worth of data before it spits out a row (see query above). The query is using a promoted
-- rowtime (ie rowtime not equal to current time but rather the time passed in from the data).
-- For this reason the fetch timeout is set to 250000
-- (this is =(time to start pump) + (time to emit first row))
@print total time count every 1
@fetch 250000
@close
@sync
@sync
@end
@thread writer
@sync
ALTER PUMP "Stage1" START;
@sync
@sync
@end
@thread cleanup
@sync
@sync
@sync
ALTER PUMP "Stage1" STOP;
DROP SCHEMA "SCHEMA1" CASCADE;
@end