DbFit / DB2 / DataStage Proof-of-Concept Write-up

bggibson

unread,

Aug 23, 2012, 7:31:21 PM8/23/12

to db...@googlegroups.com, fitn...@yahoogroups.com

All,

Over the last few weeks I have been working on a proof-of-concept (POC) and taking a lot from the dbfit and fitnesse user communities. Yesterday I was finally successful! This post is to give thanks to the individuals around the world on three continents that help me get this far and try to give a little back.

Thank you,

Brian

As a project manager I wanted a mechanism to help developers effectively unit test DataStage (ETL) code in a DB2 database environment so we could begin to recognize the benefits of test driven development.

I used the 1.1 version of dbfit (dbfit-complete-20080822.zip) to connect to DB2, insert test data and query for expected results & Uncle Bob’s Command Line Fixture to run the DataStage jobs.

I selected the 1.1 version of dbfit because it is the only one that supports DB2. As of 08/23/2012 neither FitSharp nor FitLibraryWeb contains a version of dbfit the supports DB2.

While I would have preferred to use DataStage’s APIs over the command line interface, I decided against it: I am not a coder and I was told I would need to write wrappers for the DataStage C/C++ APIs. For a POC that was just a bridge too far.

NOTE: I think a custom fixture invoking the APIs would provide at least two benefits that the command line does not: a testable return result and leverage of the existing database connection.

Additionally, I selected the java implementation (as opposed to the .NET version) of dbfit 1.1 because Uncle Bob’s Command Line Fixture is written in java. After watching Eclipse and Java for Total Beginners I felt comfortable with the limited effort required by me to build the code and deploy the fixture.

For the POC test itself I believe I am using dbfit in standalone mode: I am a little fuzzy on why. I believe it has something to do with preventing dbfit from controlling the test page, which I had to do in order to run the command line fixture. It also may be necessary because I had to have a couple COMMIT statements in my test because I am using two different database connections (my dbfit fixtures and command line fixture did not share a common database session).

The test:

|import fixture|

|myFixtures|

|import fixture|

|dbfit.fixture|

!|Database Environment|DB2|

|Connect Using File|DB2Connection.properties|

!*****> Data Setup

|Query|Select * from DIM_STORE_SOURCE|

|STORE_IDENTIFIER|STREET_ADDRESS_2|

|Query|Select * from DIM_STORE_TARGET|

|STORE_IDENTIFIER|STREET_ADDRESS_2|

!|Insert|DIM_STORE_SOURCE|

|Brian|Gibson|Brian Gibson|123 Main Street|Suite 100|Roanoke|VA|Virginia|24011|USA|1111111111|1234|12|1|2010-01-01|2010-01-02|2500-12-12|Y|>>id1|

|Brian|Gibson|Brian Gibson|123 Main Street|Suite 100|Roanoke|VA|Virginia|24011|USA|1111111111|1234|12|1|2010-01-01|2010-01-02|2500-12-12|Y|>>id2|

!|Insert|DIM_STORE_TARGET|

|<<id1|Brian|Gibson|Brian Gibson|123 Main Street|Suite 200|Roanoke|VA|Virginia|24011|USA|1111111111|1234|12|1|2010-01-01|2010-01-02|2500-12-12|Y|

**********!

Two records are inserted into the source table:

One that does exist in the tartget (id1) to test it is updated properly

One that does not exist in the target (id2) to test it is inserted properly

|Query|Select * from DIM_STORE_SOURCE|

|STORE_IDENTIFIER|STREET_ADDRESS_2|

|<<id1|Suite 100|

|<<id2|Suite 100|

|Query|Select * from DIM_STORE_TARGET|

|STORE_IDENTIFIER|STREET_ADDRESS_2|

|<<id1|Suite 200|

!|Database Environment|

|Commit|

!|Command Line Fixture|

|command|C:\IBM\InformationServer\Clients\Classic\dsjob.exe -file C:\IBM\InformationServer\Clients\Classic\DSConnection.properties sdissm01:9080 SDISS01 -run -mode NORMAL -jobstatus -warn 10 PROJ_EDW DBFitTest_DIM_STORE|

|Query|Select * from DIM_STORE_SOURCE|

|STORE_IDENTIFIER|STREET_ADDRESS_2|

|<<id1|Suite 100|

|<<id2|Suite 100|

|Query|Select * from DIM_STORE_TARGET|

|STORE_IDENTIFIER|STREET_ADDRESS_2|

|<<id1|Suite 100|

|<<id2|Suite 100|

|Clean|

|table|clean?|

|DIM_STORE_SOURCE|true|

|DIM_STORE_TARGET|true|

!|Database Environment|

|Commit|

!|dbfit.util.ExportFixture|

|dbfit.fixture|

MatMan

unread,

Aug 26, 2012, 2:57:17 PM8/26/12

to db...@googlegroups.com, fitn...@yahoogroups.com

Excellent. Well done Brian!

On DB2 support - this will be activiated soon in FitLibraryWeb as will the other DB types. We'll also add Teradata support.

FitLibraryWeb, as it stands, simply bundles dbfit 1.1 (as as JAR file) then puts a couple of wrappers on top of the Oracle and MySQL fixtures so that, I believe, they work with the current version of FitLibrary/FitLibraryWeb.

You could easily extend Uncle Bob's command line fixture to create a new action or to include a new cell to specify the expected command's exit status. What I did was to chop out small parts of it to just run a command and check the exit status code against the expected value. It might be useful to add this support to FitLibrary's ShellFixture if it doesn't already do this.

If you do down the API route then let me know how you get on. I'd be interested to see what you come up with.

In 'flow mode' all fixture tables (e.g. Insert, Query, Update, Clean,....) are assumed to be methods of the fixture class (e.g. Db2Test, OracleTest) - these are a sub class or the DatabaseTest class which is a sub class of SequenceFixture (read up about this one and DoFixture and how they take over control of a page).

In Stand Alone mode fixture tables are assumed to be actual fixtures not methods of a fixture class that is controlling the page hence you can include other, non dbfit, fixtures on the page.

Best of luck,

Mark

MatMan

unread,

Jan 9, 2013, 4:51:07 PM1/9/13

to db...@googlegroups.com, fitn...@yahoogroups.com

Hi Jaison,

It's gone very quite, hasn't it.

I know Darren has been very busy recently with stuff. I'll catch up with him now and try to publish an ETA for the first release asap.

Regards,

Mark

On Tuesday, 8 January 2013 18:09:16 UTC, jaison.t...@gmail.com wrote:

Mark,

Hello. Is there an ETA of the FitLibraryWeb update that includes DB2 and Teradata support?

Thanks,
Jaison

MatMan

unread,

Jan 9, 2013, 4:51:58 PM1/9/13

to db...@googlegroups.com, fitn...@yahoogroups.com

Hi Jaison,

It's gone very quite, hasn't it.

I know Darren has been very busy recently with stuff. I'll catch up with him now and try to publish an ETA for the first release asap.

Regards,

On Friday, 24 August 2012 00:31:21 UTC+1, bggibson wrote:

MatMan

unread,

Apr 17, 2013, 4:28:06 AM4/17/13

to db...@googlegroups.com

CommandLineFixture is written in Java so can be used with stand-alone DbFit/Java fixtures.

When you say 'only supports .net version' what are you referring to?

Reply all

Reply to author

Forward