Test Structure

A single file test is a JSON file that specifies a test parameter that contains an object with a connection parameter, a statements parameter, an optional seed parameter and an optional threading parameter. The following example can be used as a outline for writing single file tests. There is also a complete example at the end of this document.

{
  "test": {
    "description": "A test description",
    "connection": {...},
    "statements": [
      {...},
      {...},
      {...}
    ]
  }
}

Description

The description string is an optional test parameter that provides a basic textual description of the test. The description is currently not used by the framework, but may be used in the future for diagnostic and reporting purposes.

Adding a short textual description of the test or test suite is recommended.

{"description": "A textual description for a test."}

Connection Information

Connection Object

Valid Parameters

The connection parameter object specifies the ssid and userid to be used for this test. Each test establishes it's own thread that will be used for all the the statements specified in the statements array. These connection parameters can be overridden at the test suite level but must be specified to give the test values to default to.

Example

{"connection": {
  "ssid": "D11B",
  "userid": "QADBA01"
}}

Statements

Statements Array

The statement array contains a JSON array of statement objects that will be sequentially executed.

{"statements":[
  {...},
  {...},
  {...}
]}

Statement Object

Valid Parameters

Example

{
  "text": "WITH PROCS (name, parms) AS (SELECT NAME, PARM_COUNT FROM SYSIBM.SYSROUTINES) SELECT * FROM PROCS WHERE NAME = ?",
  "type": "PREPARE",
  "subtype": "SELECT",
  "repeat": 100,
  "sleep": 1,
  "args": [
    {
      "value": "ADMIN_EXPLAIN_MAINT",
      "type": "static",
      "datatype": "varchar"
    }
  ]
}

Text

The text parameter specifies the text of the SQL or DDL Statement to be executed. JSON does not allow for multi-line strings so writing in an editor with line wrapping and the ability to unwrap or fold lines will help you construct longer statements before copying them into your test. You do not need to include a semicolon at the end of the statement.

Types

Type specifies how you would like the framework to execute your statement. The following are the valid values for type and a few notes about each.

SubTypes

SubType specifies the kind of statement you are executing. Currently, the subtype parameter is not used by the framework. Regardless, subtype should be specified for all statements in case a need arises in the future, such as reporting or logging. Examples of subtype are: SELECT, CREATE, INSERT, DROP, etc.

Expect

Expect specifies an object containing the expected results from the execution of the statement. Expect should be specified to allow the test tracker to accuratly report whether a test achieved a Passed or Failed status. The following parameters are currently supported:

Examples

"expect": {
  "sqlerror": -104
}

"expect": {
  "rows": 138,
  "columns": 1
}

"expect": {
  "ignore": true,
}

Repeat

The repeat parameter specifies the number of times the framework should execute the statement.

Note that when used on a statement with the type prepare or callable the statement will only be prepared once. This means that the dynamic SQL's access path will only be determined once.

The repeat parameter also has special interactions with incremented and generated argument types.

Sleep

The sleep parameter specifies the number of seconds to pause the test execution after executing the given statement.

If the sleep parameter is omitted the default is 0 seconds.

Arguments

Arguments Array

The arguments array contains a JSON array of argument objects that will be set to the parameter markers, in the statement, in the order that they appear.

"args":[
  {...},
  {...},
  {...}
]

Argument Object

Valid Parameters

Arguments (args) allow you to specify values to be inserted into the parameter markers of the statment. Arguments and parameter markers can only be specified on prepare or callable type statements.

Types

Just like statements, arguments have a type parameter. There are three types of parameters and two subtypes.

Static

For parameters where you want to specify an explicit value.

Example

{
  "value": "ADMIN_EXPLAIN_MAINT",
  "type": "static",
  "datatype": "varchar"
}

Incremented

For parameters where you want the datatype to increment for every repetition starting at the given value. Currently only the INTEGER datatype is supported.

Example

{
  "value": 1,
  "type": "incremented",
  "datatype": "integer"
}

Generated

For parameters that you would like randomly generated data of the given datatype. If a value parameter is specified it will be ignored and the generated value will be used.

Currently, generation is supported for the following datatypes:

* length parameter must be specified or else generated value will be truncated when set

If a seed value is provided on the test object each execution of the test will generate the same random data. This does not mean that every repetition of the statement will have the same value generated for it; rather, all repetitions will produce the same random data every time the test is executed.

Example

{
  "type": "generated",
  "datatype": "char",
  "length": 10
}

Subtypes

Subtype only needs to be specified when using Statement Type: Callable when calling a stored procedure.

In

In is the implied default for all parameters (even for non callable statements). An in parameter is expected, by the framework, to provide a value to be set to the corresponding parameter marker.

Out

Out should be specified for output-only parameters. The out parameter should not specify a value. Note that the length parameter does not have to be set in the example. Out parameter's returned value will be logged.

Example

{
  "type": "static",
  "subtype": "out",
  "datatype": "varchar"
},

InOut

InOut should be specified for input and output parameters. InOut parameters should be used like In parameters and follow all the required rules of the parameter's type. InOut parameter's returned value will be logged.

Example

{
  "value": 2,
  "type": "static",
  "subtype": "inout",
  "datatype": "smallint"
}

literal

literal is used when certain values are to be inserted directly into the sql statement by the java program.

Example

"args": [
  {
    "type": "generated",
    "datatype": "integer",
    "subtype": "literal",
    "key": ":H1"
  }

key

key is used only when literal is the subtype. The string designated by the key parameter will be replaced in the SQL statement by the value indicated in the argument. This can be static, incremented, or generated parameter.

Example

 "statements": [
      {
        "text": "SELECT CREATOR,NAME FROM SYSIBM.SYSTABLES WHERE COLCOUNT < :H1 FETCH FIRST 1 ROWS ONLY",
        "type": "IMMEDIATE",
        "subtype": "SELECT",
        "repeat": 100,
        "args": [
          {
            "type": "generated",
            "datatype": "integer",
            "subtype": "literal",
            "key": ":H1"
          }
        ]
      }
    ]

Max

Sets the maximum number of incremented values for a single argument. Once the maximum number of values has been reached the value will be reset to the original value.

This parameter is designed to support separation of repeating literal values from the "repeat" parameter at the statement level.

Example

In the below example the Table literal counter argument will cycle through values (1-10). This will cause values to be inserted into tables (RTPJTB1 - RTPJTB10). Since the statement is repeated 10 times and in batches of 10, this will result in 10 rows being inserted into ten tables.

    "text": "INSERT INTO RTPJTB:H1 (id, firstname, lastname, title, salary, resume) VALUES (?, ?, ?, ?, ?, ?)",
    "type": "BATCH",
    "subtype": "INSERT",
    "repeat": 10,
    "batches": 10,
    "args": [
      {
        "type": "incremented",
        "datatype": "integer",
        "subtype": "literal",
        "description": "Table literal counter.",
        "value": 1,
        "key": ":H1",
        "max": 10
      },

Batches

The batches parameter is only valid when the statement type is "BATCH". The parameter is used for executing batch statements. The batches value indicates the number of times a statement should be added to the batch. For example, A statement with a repeat value of 1 and a batches value of 10 will cause the statement to be added 10 times and executed during one batch execution.

Example

In the below example the statement will be executed 100 times in 10 execution batches. Since the statement is repeated 10 times and in batches of 10, this will result in 10 rows being inserted into ten tables.

    "text": "INSERT INTO RTPJTB:H1 (id, firstname, lastname, title, salary, resume) VALUES (?, ?, ?, ?, ?, ?)",
    "type": "BATCH",
    "subtype": "INSERT",
    "repeat": 10,
    "batches": 10,
    "args": [
      {
        "type": "incremented",
        "datatype": "integer",
        "subtype": "literal",
        "description": "Table literal counter.",
        "value": 1,
        "key": ":H1",
        "max": 10
      },

DataTypes

DataType specifies the DB2 type of the parameter. Currently, the following datatypes are supported for static types; generated types and incremented types only support subsets of this list:

Value

The value parameter allows you to provide values for static parameters and starting values for incrementing parameters. Note that values like integer should not be specified within quotes.

Length

The length parameter is, currently, only used for generated parameters. There is no harm in specifying it for static parameters as it could be used by the framework in the future.

Multi Threaded Tests

Threading Object

Valid Parameters

The threading parameter object is used to execute multiple instances of a test either in parallel or sequentially. The threads parameter specifies the number of threads to be created; each thread will have it's own connection. The parallel parameter specifies whether the threads should execute in parallel or sequentially waiting for each to finish before the next is created.

Example

"threading": {
  "threads": 100,
  "parallel": "true"
  "sleep": 1
}

Complete Example

The following is an example that creates a table, inserts 50 rows to it using all three type of parameters, selects the contents of that table, and drops the table.

{
  "test": {
    "description": "This is an example test file.",
    "seed": 42,
    "threading": {
      "threads": 2,
      "parallel": "false"
    },
    "connection": {
      "ssid": "D10A",
      "userid": "QADBA01"
    },
    "statements": [
      {
        "text": "CREATE TABLE RTPJSONTEST ( id INTEGER, myint INTEGER, mystring VARCHAR(30), mydouble DOUBLE, myfloat FLOAT)",
        "type": "IMMEDIATE",
        "subtype": "CREATE"
      },
      {
        "text": "INSERT INTO RTPJSONTEST (id, myint, mystring, mydouble, myfloat) VALUES (?, ?, ?, ?, ?)",
        "type": "prepare",
        "subtype": "INSERT",
        "repeat": 50,
        "args": [
          {
            "value": 1,
            "type": "incremented",
            "datatype": "integer"
          },
          {
            "type": "generated",
            "datatype": "integer"
          },
          {
            "type": "generated",
            "datatype": "varchar",
            "length": 30
          },
          {
            "type": "generated",
            "datatype": "double"
          },
          {
            "type": "generated",
            "datatype": "float"
          }
        ]
      },
      {
        "text": "SELECT * FROM RTPJSONTEST",
        "type": "IMMEDIATE",
        "subtype": "SELECT",
        "expect": {
          "columns": 5,
          "rows": 50
        }
      },
      {
        "text": "DROP TABLE RTPJSONTEST;",
        "type": "IMMEDIATE",
        "subtype": "DROP"
      }
    ]
  }
}

Some Notes About this Example