DBUtility Overview.

DBUtility is a Java application containing several facilities for executing stored procedures or SQL against 1 or more relational databases.

Options in DBUtility includes:

Display and execute stored procedures (SPRunner)
Execute one or more stored procedures in a script (BatchSPRunner)
Compare the results of matched stored procedures or SQL across the same (test to prod) or different (DB2 vs Oracle) relational databases (DBCompare)
Transfer data from one system to another (prod to test or Oracle to DB2) (DBTransfer)
Run one or more sets of SQL statements (select, insert, delete) against a relational database (DBQuery)

System Prerequisites

DBUtility was written to Java 1.4.2 and it requires that version (at a minimum) of the JRE.

For DB2, UDB or DB2 Connect must be installed. For Oracle or MySQL the equivalent jar/zip plus connection software must be installed. This connective software is not provided with DBUtility and must be supplied by the user.

In addition, for each relational database you want to execute against you will need their corresponding zip/jar file for JDBC connect. For example, for DB2 UDB V7 you need db2java.zip.

System Limitations

Printing of results is currently not supported. Use copy option to paste results into a text application (Notepad, WordPad, etc.) and print from there.

Support for BLOB’s, CLOB’s and comparision of long varchars is either not implemented and/or limited.

Program Installation

Unzip the install file into a new directory (eg. DBUtility). The following files should be present.

DBUtility.jar - contains the Java application
xercesImpl.jar – used for parsing xml
jdom.jar - used for reading xml
jakarta-poi.jar – used to write query results to an Excel file
run.bat – example Windows startup file to execute DBUtility. Modify classpath as needed to include needed database JDBC zip or jar files.
startup.xml – some user startup parms
db.xml – list all database types and names and database drivers
keywords.xml – list keywords used on keyword menu and mouse over help text

Program Execution

The application can be run from a network directory or copy all contents to your local drive and run it from there.

Create an execution batch file to provide the appropriate database jars or zip files needed to connect to the corresponding database. (See run.bat as an example)

Update db.xml with the database names and connection information that you will want to connect to.

The first time the script is executed there will be a delay in execution as a connection(s) is established. The connections are maintained after the script executes so subsequent executions start faster. The connections may drop however if there is an appreciable lag between running scripts (don’t know what the time limit is). If you execute a script and it doesn’t run properly try clicking on DBUtilities/Reset All DB Connections on the menu. This actually closes all connections so the application will reopen connections from scratch.

If you have run a large number of scripts together(see below) you may also want to use the menu option to close the connections when done to free them up for other users (future enhancement – provide a keyword to do this)

Use of Symbolics

Symbolics start with a ‘&’ and can be used anywhere in SQL or stored procedures. If the replacement value of the symbolic contains specials characters, eg. a ‘$’ symbol preface the special character with a ‘\’ (backslash). Consult java.util.regex.Pattern for the list of special characters.

SPRunner General Overview

There are 2 ways of executing stored procedures, prompted and via a script. The following covers the prompted method using SPRunner. The scripted method is next.

Click on DBUtilities/List Stored Procedures
Select the DBType (names from db.xml) for listing the available databases of that type.
Select the system that you want to get a list of stored procedures from.
Type in a new schema and or stored procedure prefix (defaults that are in startup.xml). Either entry has a wildcard ‘%’ character appended to the end.
Select a stored procedure from the list of stored procedure names based on the criteria specified above.
Once a stored procedure is selected, enter the appropriate input parms on the displayed input panel.
Some simple validation occurs when parameter values are entered, based on the parm data type. (eg. an integer parameter requires all numbers)
Click Execute to run the stored procedure and list results in the output text area

BatchSPRunner General Overview

BatchSPRunner provides options to:

Load and save test scripts that contain 1 or more stored procedures with parms to be executed
Execute a script containing one or more stored procedures and display the results
Execute a file containing a list of test script files. Each script file runs as a separate thread so volume testing can be conducted.

Select BatchSPRunner from the DBUtilities menu. A text area is on the left/top where the script is entered and the results are displayed in the right/bottom window.

The menu bar is modified to include the regular edit functions. The Keyword menu provides a list of valid keywords for creating a batch script. Leaving the mouse over a keyword will display a short explanation on the use of the keyword. If you wish update the keyword.xml file to change the descriptive text. Symbolics may be used and are started with a ‘&’ symbol.

With regards to the check boxes, the ‘Display Batch Output’ will display the execute results of the script in the lower window if checked. This is most useful when doing interactive testing and debugging of the script.

The ‘Input is File List’ check box indicates that the upper window contains a script containing a list of files to be executed, rather than the script itself. In general if you are executing a list of files, do not check the ‘Display Batch Output’. It is not a problem if it is checked but the results window will contain a mix of output from each individual script file being executed and may not be very useful.

By default script files are held in the BatchSPFiles subdirectory relative to whatever directory the application runs from. Script files can be loaded, modified and saved within the application.

In general, a script is made up of key words and (usually) one or more values. Keywords are separated by their values by an ‘=’ sign and the end of the values must end with a ‘;’. If a keyword allows multiple values, the values are comma separated. Keyword values can run over multiple lines. There is no continuation character. End the ‘line’ with a ‘;’.

Lines starting with ‘--‘ are comments and are ignored.

The key words (case insensitive) and values are:

Keyword	Description
&symbolic	Create a symbolic and assign a value. The value will be substituted when the symbolic is found in the script. Currently symbolic substitution is done on LogFile, SPName, and Parms values; &emplid=123456789; &owner=TEST; -- SPNAME=&owner.GET_EMPLID_DETAILS;
System	System the script is to execute in System=MySQL;
ListOutputParm	Used to display output and input/output parm value(s) after the execution of a stored procedure. Separate multiple parameter names by a ‘,’. Reset the list to empty by specifying an empty list. Print all output parms by specifying “all” (case insensitive) ListOutputParm=ERROR_TEXT,TOTAL_AMT; ListOutputParm=; reset list to empty ListOutputParm=all; print all output and input/output parms.
Logfile	File name where results should be logged relative to where the program executes Logfile = BatchSPFiles\substitutiontext.log.txt;
Parms	Parms to be passed to the stored procedure. The parm values are for the input or input/output parms only. The parms can be entered two ways. List the parms. In this form, the parm list is comma separated. Use double quotes around a parm if it contains a comma or quote. If you like you can also surround parms that are spaces with double quotes. Parms = . " " , 1, 0, 0, 2001-11-30,"'6178151086086'"," " , " " , " " , " " , 1, 10; List a file containing the parms to be executed. Parms of this type must start with a ‘\’ . Do not mistake it for an initial directory path command. Parms=\BatchSPFiles\EmplidParms.txt; Parms within this file do not start with ‘Parms=’. Simply list the values ending each line with a ‘;’ and starting the next set of parms on a new line A parm value identified by ‘\last\parm name’ will take the parm value for that parm name from the previously executed stored procedure and substitute it in. Parms= " " , 1, 0, 0, 2001-11-30,"'6178151086086'"," " , " " , " " , \ last\TOKEN_ST , 1, 10 ; A parm identified by ‘\sp name\parm name’ will take the previously executed parm value for the parm name of the last execution of that named stored procedure and substitute it in. Parms= " " , 1, 0, 0, \OWNER.GET_EMPLID\HIRE_DATE, '123456789' ," " , " " , " " , " " , 1, 10 ;
Password	Password used to sign on to the system. You will be prompted if not provided. Password=mypassword;
Quit	Stop execution of the batch file. This is sometimes useful to insert in the middle of a script if you just want to execute the first part for some testing. Quit;
ReadCursor	Set to true if returned result set (if any) should be read or false if not. Default value is true. ReadCursor=true;
SPElapsedTime	Name of input/output or output parm that holds a DB2 duration representing the SP elapsed execution time. Used to report total and avg SP execution time at the end of the execution of the script. (Your SP should get a timestamp immediately on startup and then calculate a duration just before exiting) SPElapsedTime=DURATION_TEXT;
SPName	Name of the SP parm to execute, eg. SPName=list_cities;
Userid	Userid to be used to sign on to the system. You will be prompted for signon if not provided. Userid=myuserid;
Validate	Set to true if the script is to just check parameter value assignment and not execute the SPs. The default value is false (execute the SPs). Validate=true;

Running Multiple Batch SPRunner Scripts

(Temporarily unavailable – rewriting logic)

A script file can contain a list of script files. Each file listed gets its own execution thread. If needed, adjust ‘maxconnections’ in db.xml accordingly.

Keywords (case insensitive) and their values within a script file of this type are:

Keyword	Description
Iterations	Iterations tells BatchSPRunner how many times to repeat the running of the script file. The default, if the keyword is absent, is 1. Iterations=4;
Validate	Set to true if the script is to just check parameter value assignment and not execute the SPs. The default value is false (execute the SPs). This value will be assigned to all batch script files executed. It will be overridden if the Validate keyword is used in the individual script files. Validate=true;
N/A	One or more script file names each on a separate line. The file name of script file relative to where the program is executing BatchSPFiles\BillAddress.txt

Again, lines starting with ‘--‘ are ignored.

BatchSPRunner Helpful Tips

Set up subdirectories under the directory when the application resides. This subdirectory will hold subdirectories that contain the scripts and parameter files. Each script file should be in a directory that indicates the system the script executes against. Create a log directory under that to contain any logged output. The parameter files should be in a separate subdirectory as in general they are system independent.

For example

Subdirectory contains

DBUtility - application

BatchSPFiles - all scripts and parm file subdirectories

TEST - scripts to DB2 test system

Log - log files from TEST scripts

PROD - scripts to DB2 DB2B

Log - log files from PROD scripts

TestParms - all parameter files

When developing a script it is best to create it in the input window and use just one Parms input line. Once the script is running as you like, then substitute in a parm file in place of the Parms line.

Here’s an example script

Logfile=BatchSPFiles\TEST\Log\Test-1.txt;

System=TEST;

Userid=xxxxx;

Password=xxxxx;

ListOutputParm=ERROR_TEXT;

SPElapsedTime=DURATION_TEXT;

SPName=OWNER.SPNAME;

Parms=\BatchSPFiles\TEST\Test-1.txt;

And the test parms file =\BatchSPFiles\TEST\Test-1.txt would look like:

1,0,0,,2, 123456789, ,1,10;

1,0,0,,2,987654321, ,1,10;

This of course is just a suggestion. You can do it any way you want.

Creating BatchSPRunner Test Data

Here’s a handy way to create test parameters if the SP’s are mainly retrieval SPs. Of course the same applies to updates if the data is entered initially from unit testing and the parms are generated prior to re-initializing the database.

The method is to create a SQL query to format a parm string with the necessary values. An example below creates a mix of parm values, some fixed and some dependent on the underlying DB2 records.

SELECT DISTINCT

'" ",1,0,0,' || CHAR(BILL_DATE,ISO) || ',' || ACCOUNT || ',M, , , ,1, 10;'

FROM

OWNER.TABLE

WHERE

BILL_DATE = '2001-10-31'

ORDER BY 1

Cut and paste the resultant records into a text file to create your parm list

DBCompare General Overview

DBCompare allows the execution of matched sets of SQL or stored procedures against the same or different DB2 subsystems. Some examples of exercising this option are comparing:

Data between systems to ensure identical values across systems.
System tables to ensure identical setup across systems.
Regression testing between existing and modified stored procedures to ensure existing functionality is unimpaired.

The DBCompare interface is similar to BatchSPRunner.

A comparison script is made up of either 2 SQL statements, or 2 stored procedures. The 2 statements or procedures are executed and the output parms (for stored procedures) and/or result sets are compared. Only the differences in output parms and/or result sets are shown.

DBCompare

By default, DB2 Compare files are held in the DB2Compare subdirectory relative to whatever directory DBUtility runs from. Comparison files can be loaded, modified and saved within DBUtility. The files are run interactively with the deltas displayed in the right/lower results window.

A comparison file is made up of key words and values in the same manner as a BatchSPRunner script. The comparison executes when either the last line of the script in the input window is read and no execution has yet occurred OR the ‘Execute’ keyword is hit. So multiple comparisons can be done in one script. (Currently there is no option to read and execute scripts from a separate file like BatchSPRunner)

The SQL or SP called in each environment must of course have the same number of columns returned and in the same sort order.

The key words (case insensitive) and values are:

Keyword	Description
&OWNER	This symbolic is not assigned a value. It is used in the SQL/SP statement. The symbolic is replaced by &OWNER1 and &OWNER2 symbolics. See SQL keyword (see SQL keyword below)
&OWNER1	The owner of the 1^st SQL or SP to be used in comparision. Is case sensitive &OWNER1=TEST;
&OWNER2	The owner of the 2^st SQL or SP to be used in comparision. Is case sensitive &OWNER2=PROD;
&symbolic	A symbolic variable. Is case sensitive. &emplid=123456789;
ContinueOnDBError	Useful when running a series of SQL or SP comparisions. If set to true then and error in one comparision will not stop the script. The default is false – so any errors will stop script execution ContinueOnDBError=true;
CompareOutputParms	List the output or input/output parms between 2 stored procedures. Multiple parms are comma separated. Use a value of ‘all’ to compare all parms returned in the stored procedure CompareOutputParms=emp_address1,emp_emp_address2;
DbType1	Only needed if system value is non-unique value in db.xml. (ie. If you have a ‘world’ system in both DB2 and MySQL) DbType1=DB2;
DbType2	Only needed if system value is non-unique value in db.xml. DbType2=DB2;
Execute	Execute the comparison. Use if the SP does not have any parameters or if there are multiple SP or SQL comparisions in the script. Also use if you want to execute the same SP’s with different parms. SP1=X; SP2= Y; Execute; SP1=A; Parms1=Set1; SP2=B; Parms2=Set2; Execute; Parms1=Set3; Parms2=Set4; Execute;
File	Process a file containing the store procedure or SQL comparision script. The file can contain symbolics.
IgnoreColumns	Ignore columns in the comparision, Mulitple column names are comma separated.
MaximumDeltas	Maximum number of differences allowed before program stops MaximumDeltas=20;
MaxRows	Stop the comparision when more than this number of rows are read in a result set. This is useful for testing. MaxRows=100;
NumberKeyColumns	First n (key) columns of SQL to be compared between systems NumberKeyColumns=4;
Parms	Use the parameter values listed for both stored procedures. This will overlay previously Parm1 and Parms2 values; Parms=123456789,10/19/2006;
Parms1	Parameter list for SP1 Parms1=123456789,10/19/2006;
Parms2	Parameter list for SP2 Parms2=123456789,10/19/2006;
Print1Input	Will only print the first SQL (SQL1) in the output. This can be helpful to reduce the size of the report when running a large number of comparisons. The default is to print both. Print1Input=true;
Quit;	Stop execution of the script.
ResetDeltaCounter	Set to true if you want to reset the delta counter after each comparision. The default is false – so once the number of deltas equals the MaximumDeltas value the script will stop. ResetDeltaCounter=true;
SP	Stored procedure to excute on both systems
SP1	Name of stored procedure to be executed on system 1 (new)
SP2	Name of stored procedure to be executed on system 2 (new)
SQL	SQL to execute on both systems SQL= SELECT NAME, PRECOMPDATE, PRECOMPTIME, PDSNAME from sysibm.SYSDBRM where plcreator = &OWNER and plname like ‘DO%’
SQL1	The SQL to be executed on system 1 (new) SQL1=valid SQL string;
SQL2	The SQL to be executed on system 2 (old) SQL2=valid SQL string;
System1	System to execute first SQL or SP on. Consider this system as having the new version or results. For backward compatibility, the Sys1 keyword can still be used. System1=TEST;
System2	System to execute 2^nd SQL or SP on. Consider this system as having the old version or results. For backward compatibility, the Sys2 keyword can still be used. System2=PROD;

DBQuery General Overview

DBQuery allows the execution of select, insert, and/or delete SQL.

Select the database and system from the drop down lists (The values are based on entries in db.xml)

Type your SQL in the left/upper text input window. If multiple SQL statements are listed end each with a ‘;’. When the ‘Execute’ button is pressed, each SQL will be executed in turn.

Results are displayed in the right/lower window.

Note that a delete SQL statement without any where phrase (to delete all rows in a table) may return 0 rather than the actual number of rows deleted – which can of course be misleading.

&symbolic	A symbolic variable that gets substituted into any of the SQL. Is case sensitive. &where= where emplid=123456789 and work_date = ‘10/22/2006’;
AutoCommit	If set to false, you will be promoted to confirm the commit of any inserts, updates, deletes. The default is false. Setting to true will cause commits to be done as needed without asked for confirmation. Autocommit=true;
DbType	Override the dropdown DbType value. Use this if you want to run a subsequent query on another database. Reset value to spaces to again use the dropdown value. DbType=MySQL;
Delete …	Delete SQL to execute. Delete from owner.employees &where;
Insert ….	Insert SQL to execute. The columns and attributes must match those in the select SQL result set. Insert into testowner.employees (emplid, emplname) values (‘123456789’,’Doe, John’);
MaxRows	Stop reading when more than this number of rows are in a result set. MaxRows=100;
OutputExcel	Output the results of a select to an Excel file. The results still displayed in the output window. There is no limit checking on the number of columns or rows written to the spreadsheet. OutputExcel=c:\sqlresults.xls;
Quit	Stop execution of the transfer script. Useful if you only want to execute the first part of script; Quit;
Select ….	Select sql to execute. Select emplid, emplname from owner.employees &where;
System	Override the dropdown system value. Use this if you want to run a subsequent query on another database system. Reset value to spaces to again use the dropdown value. System=world;
Update ….	Update SQL to execute. Update testowern.employees set work_date = ‘10/23/2006’ &where;

DBTransfer General Overview

DBTransfer allows a select SQL statement to be run on one database/system with the result set data either being used to insert new rows or update existing rows on another database/system.

The key words (case insensitive) and values are:

Keyword	Description
&symbolic	A symbolic variable that gets substituted into any of the SQL. Is case sensitive. &where= where emplid=123456789 and work_date = ‘10/22/2006’;
AutoCommit	If set to false, you will be promoted to confirm the commit of any inserts, updates, deletes. The default is false. Setting to true will cause commits to be done as needed without asked for confirmation. Autocommit=true;
CommitEvery	Will commit every n records inserted as specified. A value of -1 (the default) will cause a commit only at the end of the read/insert cycle. If AutoCommit is set to false a confirmation box will be displayed and you will need to confirm the commit each time the count is hit. CommitEvery=200;
Delete ….	Delete SQL to execute. Always runs against the Destination database. Delete from testowern.employees &where;
Destination	Database that the insert statement will execute in Destination=DB2TEST;
DestinationDBType	The type of destination database to connect to. Only needed if the ‘destination’ name is non-unique among the listed database types in db.xml. DestinationDBType=MySQL;
DisplayStatusEvery;	Displays a message for every n records inserted. (Just to ensure the process is running). The default is 200. DisplayStatusEvery=50;
Execute	Execute the previously defined SQL. Execute;
ExecuteBatchAt	If the database supports batch inserts/updates, execute the batch command after this many SQL statements execute. (Currently executing against DB2 causes JVM to crash) ExecuteBatchAt=200;
Insert …	Insert SQL to execute. The columns and attributes must match those in the select SQL result set. Insert into testowner.employees (emplid, emplname) values (?,?);
Select …	Select sql to execute. The columns and attributes must match those in the insert. Select emplid, emplname from prodowner.employees &where;
Update …	Update SQL to execute. Always runs against the Destination database. An update statement can be run on its own, or executed in tandem with a select statement. If an update is run with a select, placeholders (symbolics) in the update statement must match the columns retrieved from the select. Update run without select: Update testowern.employees set work_date = ‘10/23/2006’ &where; Execute; Update matched to select: Select emplid, yearly_salary, vacation_days from prod.employees; Update test.employees Set yearly_salary = &yearly_salary, Vacation_days = &vacation_days Where emplid = &emplid; Execute;
UseBatch	If database supports it, batch multiple insert or update statements and execute all at once (see ExecuteBatchAt keyword). Default is false. UseBatch=true;
Quit	Stop execution of the transfer script. Useful if you only want to execute the first part of a transfer script. Quit;
Source	Database that the select SQL will execute in. Source=PROD;
SourceDBType	The type of source database to connect to. Only needed if the ‘source’ name is non-unique among the listed database types in db.xml. SourceDBType=MySQL;

Future Enhancements

Edit XML files from application.
Add connection parms for database connection to db.xml.
Improve keyword help display and prompting.
Add online help manual
Add email link on Help menu
Print support
Full support for BLOB’s, CLOB’s, etc.
Edit and compare data via JTables.

Bug Reports and Enhancement Requests

Please send all bug reports and/or suggestions for enhancements to Eric Foertsch (efoertsch@fisincorporated.com)