Connecting to IBM DB2 zOS from Azure Data Factory v2

Connecting to IBM DB2 zOS from Azure Data Factory v1 was a matter of setting up the Azure Data Gateway on an on-prem server that had the IBM DB2 Client installed; creating an ODBC connection to DB2 (I called it DB2Test).  Then, in the Data Factory v1 Copy Wizard, Select the ODBC source, pick the Gateway, and enter the phrase:  DSN=DB2Test into the Connection String.  This worked for us.

Azure Data Factory v2

First, the Azure Data Gateway is now called “Hosted Integration Runtime”.  So download and install the IR client on your on-prem gateway machine.  On my machine, it auto-configured to use the existing Data Factory Gateway configuration, which is NOT what I wanted.  After uninstalling and reinstalling the IR client a couple of times, it stopped auto-configuring and asked me for a key.  To get the key, I had our Azure Dev configuration guy run the following PowerShell:

Import-Module AzureRM
$dataFactoryName = "myDataFactoryv2NoSSIS"
$resourceGroupName = "myResourceGroup"
$selfHostedIntegrationRuntimeName = "mySelfHostedIntegrationRuntime"
Login-AzureRmAccount
Set-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $resouceGroupName -DataFactoryName $dataFactoryName -Name $selfHostedIntegrationRuntimeName -Type SelfHosted -Description "selfhosted IR description"
Get-AzureRmDataFactoryV2IntegrationRuntimeKey -ResourceGroupName $resourceGroupName -DataFactoryName $dataFactoryName -Name $selfHostedIntegrationRuntime

I then pasted the Key into the Integration Runtime Configuration screen, and it connected properly to myDataFactoryv2NoSSIS.  Tada:

snip_20180124073749

Next, is to test the connection to DB2.  I went to the Diagnostics tab, entered the DSN and credentials, just like I did for Data Factory V1:

Failed to connect to the database. Error message: ERROR [HY010] [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010

Dang! Much googling later, I found this obscure note.

I added the phrase “Autocommit=Off” to the DSN in the connection string, and voila, the connection worked.  So my final diagnostic looked like this:

snip_20180124074603

YAY!!

 

Why can’t I debug into my Windows Workflow?

I had a test project that I was using to Unit Test my Windows Workflow activities. Green across the board.

I put all the activities together into a workflow and set up the test for the workflow, but the Output Parameters were always empty.

I put breakpoints in the code. The debugger never stopped at them.

I created a console app to host the workflow and made the EXE for the console the startup app for the workflow project. Several sites said this would enable debugging inside the workflow. Nope! The debugger never stopped inside the workflow.

And the Workflow never terminated, or cancelled, in fact, it seemed to be dying and disappearing. WTF!?!

After much anguish, I looked through every single method and property of my WorkflowManager class and my WorkflowWrapper class. And then I saw it…

The WaitAll method in the Manager class sits around using the manual WaitHandles on the workflows it is managing. When they all complete, the manager returns control to the calling code.

The unit test says:
1) Set up the Manager
2) Set up the Workflow
3) WaitAll(5000)
4) Assert.IsTrue(workflow.OutputParamaters.Count()>0);

I finally (as in DOHN!) realized that the WaitAll method takes a timeout parameter that is in milliseconds. Each activity was tested separately and could complete in less than 5s. The entire workflow, however, needed more time. The WaitAll was dumping out of the thread when the time limit was hit, disposing of the workflow runtime before it could complete and without raising ANY other events. Grrrr…

So, now I have upped the wait time in my test to 120,000 (2 mins) and, lo and behold, the test passes. Grrrr again…

So, just a little tip:

TIP: When debugging workflows, make sure your WaitHandle timeout is set to longer than the combination of the time for the process to execute plus the time you will spend stepping through the debug code, otherwise, the WaitHandle will trigger and dump you out of the WorkflowRuntime before the Workflow actually gets a chance to finish.