Edward J. Lipchus
Trinzic Corporation

Created: February 16, 1994
Revised: February 16, 1994

Edward Lipchus is a Senior Software Engineer on the InfoHub development group at Trinzic. He has an extensive background in databases ranging from DB2 and IMS to Oracle and SQL Server and in a variety of hardware platforms, from IBM mainframe to PCs.

Abstract

This article details the performance testing of a client/server middleware product using Microsoft Test and ODBC. Sample screens and macros are included.

Elapsed time

Time function

Sometimes, a tool designed for one purpose can be very effective in a completely different way. According to Microsoft, "Microsoft Test for Windows is a new software testing automation tool that makes testing easy". Yet, my first use of MS Test was for performance measurement, not QA testing.

I am a senior software engineer at Trinzic Corporation. Trinzic makes client/server middleware, notably InfoPump and InfoHub. I am a developer on the InfoHub project, primarily responsible for the CICS bridge between the mainframe gateway and the database access engine. The InfoHub product lets a user on a PC running Windows make a SQL command that is applied to an IBM mainframe database such as IMS or DB2 and retreive the results. This is done using an Open Database Connectivity (ODBC) application program interface (api). For example, the user can say "insert into table1 values ('abc', 1, 'def', 2)" and a segment will be inserted into an IMS database or a record into a VSAM file or a row into a DB2 table, depending on the mapping of the request. The mapping is done through a metaschema on the mainframe.

Figure 1. InfoHub System Architecture

top 

I first became aware of Microsoft Test when we needed to do some performance benchmarks. There are several components that must work together for the process to work. Simplifying a bit, the user passes a SQL command to the ODBC api. ODBC passes the command via a network to a network gateway node. The gateway node passes the command using LU6.2 over a token-ring network to the mainframe access server. The mainframe access server passes it to a bridge program. The bridge program passes it to the InfoHub Server. The InfoHub Server passes it to the actual database engine, such as DB2. Results and result sets are passed back in a similar chain.

While, as developers, we are interested in the time spent in each link of the chain, from the user's standpoint, the only performance number they want to see is the time from passing off the request on the pc to receiving the results back on the pc. This is the set of metrics I wanted to get.

One way would be to write a C or Visual Basic program that would go through a sequence of ODBC calls and time them. On the other hand, in the ODBC Software Developer's Kit (SDK) from Microsoft, there is a testing program called ODBC Test (gator.exe) which makes it fairly easy to exercise the ODBC api and see the results. The results can even be saved to a file. I just didn't want to sit at my pc at 2 in the morning typing in a hundred identical requests. I needed something that would drive ODBC Test through its own screens.

First I looked at Windows own Recorder applet. Recorder generally worked ok, but it had some synchronization problems. Worst of all, its scripts couldn't be edited. This meant that I couldn't add new things as I ran the tests without recreating everything that went before. Also, if there was an error in the script, the error couldn't be fixed without redoing the entire script (and thus possibly introducing another error).

We have, in-house, a product called QA Partner which is used for some QA work. It had editable scripts, but the learning curve for me was too steep. I needed something I could get up and going quickly.

An email query for help brought a message from a coworker that they had inherited something called Microsoft Test when another worker had left the company, though they had not used it.

MS Test was what I needed. It can record just like Recorder. This let me run some basic scenarios through ODBC Test by hand and capture them as scripts. Next, the scripts can be edited, so I could clean them up to get the "human" part of my processing as small as possible, then clone the script code for other scenarios. Then, I could use the Basic-like programming language to control scenario execution (such as iterations) and compute some data I needed. Last, MS Test provides a viewport window into which I could write the results (or I could do it to a file).

Let's say I wanted to time the ODBC metaquery function SQLTables. The steps are:

SQLTables metaquery screens

The sequence of screens to do a SQLTables metaquery using ODBC Test is:

c:\winword\data\mst1.bmp

Figure 2. SQLTables - Allocate a Statement

Figure 3. SQLTables - Allocate a Statement Accept

c:\winword\data\mst3.bmp

Figure 4. SQLTables - Function Select

c:\winword\data\mst4.bmp

Figure 5. SQLTables - Parameters

c:\winword\data\mst5.bmp

Figure 6. SQLTables - Select Results

c:\winword\data\mst6.bmp

Figure 7. SQLTables - Results

top   

Initial script

By using the Record feature (Tools/Record Events), MS Test generated an initial script.

c:\winword\data\mst7.bmp

Figure 8. Select Record Events

The initial script looked like:

'$DEFINE TESTEVNT

'$INCLUDE 'MSTEST.INC'

QueKeys "%(s)a"

QuePause 4998

QueKeys "{ENTER}"

QuePause 4284

QueKeys "%(g)"

QuePause 6866

QueKeys "t{ENTER}"

QuePause 5163

QueKeys "%(r)"

QuePause 4559

QueKeys "l"

QuePause 12413

QueKeys "%({TAB})%({TAB})"

QueFlush 1

top 

Script changes

...Which quickly got cleaned up, using the MS Test editor, to:

'$DEFINE TESTEVNT

'$INCLUDE 'MSTEST.INC'

DoKeys "%(s)a"

DoKeys "{ENTER}"

DoKeys "%(g)"

DoKeys "t{ENTER}"

DoKeys "%(r)"

DoKeys "l"

top 

Iteration loop

Then I put it inside a loop:

'$DEFINE TESTEVNT

'$INCLUDE 'MSTEST.INC'

loopsize% = 100

'***Allocate statement***

DoKeys "%(s)a{ENTER}"

start$ = datetime$

for i = 1 to loopsize

'***SQLTables***

DoKeys "%(g)t"

DoKeys "{ENTER}"

DoKeys "%(r)a"

next i

finish$ = datetime$

end

top 

Elapsed time

Next, I wrote a function to compute elapsed time:

static function dtime$(start$, finish$)

'This routine subtracts 2 times. It returns the difference in hh:mm:ss. It will NOT handle

'a difference greater than 24 hours.

'Input date/time strings format is "mm/dd/yy hh:mm:ss"

hrs$ = mid$(start$,10,2)

mis$ = mid$(start$,13,2)

ses$ = mid$(start$,16,2)

hrf$ = mid$(finish$,10,2)

mif$ = mid$(finish$,13,2)

sef$ = mid$(finish$,16,2)

nhrs% = val(hrs$)

nmis% = val(mis$)

nses% = val(ses$)

nhrf% = val(hrf$)

nmif% = val(mif$)

nsef% = val(sef$)

if nsef% < nses% then

nsef% = nsef% + 60

nmif% = nmif% - 1

endif

if nmif% < nmis% then

nmif% = nmif% + 60

nhrf% = nhrf% - 1

endif

if nhrf% < nhrs% then

nhrf% = nhrf% + 24

endif

nhrd% = nhrf% - nhrs%

nmid% = nmif% - nmis%

nsed% = nsef% - nses%

dtime$ = str$(nhrd%) + ":" + str$(nmid%) + ":" + str$(nsed%)

end function

top 

Time function

All that was left was to add the function call, tidy up some of the parameter entries, and add some comments. The '$IFNDEFs are to allow the subroutines to be run either as a group from a master script, or by themselves.

'$DEFINE TESTEVNT

'$INCLUDE 'MSTEST.INC'

'This runs ODBC Test to do 100 iterations of the SQLTable metaquery and time it.

'Set loopsize to the number of iterations you want. The start, finish, and 'elapsed

'times are put in the Viewport window.

'$ifndef dtime_function