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"
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:


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:




Oops…I Broke the SQL Server

So this happened.  In an attempt to give my SQL Server Instance access to more memory, I set the Max Memory to zero, expecting that to mean infinite.  No luck, SQL Server Management Studio (SSMS) set the max memory to 16MB instead and broke the instance.  I could not do anything more in SSMS because the instance did not have enough memory.

Setting the Max Memory too low

Note:  The screenshots in this article are from a SQL Server 2014 instance, which has apparently fixed this problem so that the Max Server Memory setting defaults to 128MB when you set it to zero and you can still connect with SSMS at 128MB.  In 2012 and prior versions, the setting defaults to 16MB, which is what causes all the trouble.

So I googled for: “accidentally set max server memory to 0”.  This turned up a ton of useful links, but since I had to piece my solution together from various posts, I have created this blog entry to hopefully help someone else get to the solution more quickly.

How to Increase SQL Server Max Memory in a Named Instance

  1. First, you will need to be an administrator on the SQL Server Instance you want to fix.
  2. Now, from the Start | Run menu in Win 7, or Start | Search in Win 10, look for CMD.
    Start Run CMD   Start Search CMD
  3. Now RIGHT-Click on cmd.exe or Command Prompt and select Run As Administrator.
  4. Repeat steps 2 and 3, so you have two command windows open. Like so:
    Two Command Windows
    In the left window, we will start the instance we need to repair.  In the right window, we will connect to that instance with SQLCMD and fix the memory setting.
  5. In the both windows, you need to change to the Binn directory of the SQL Instance that you want to repair.  In my case, this instance is on the D: drive so I have to switch to that first.  Also, this instance is found in the D:\Program Files\Microsoft SQL Server\MSSQL12.DUMBO folder.  Lastly, the Binn folder is below the instance atInstance\MSSQL\Binn path.  So I enter these two commands:

    D: <enter>


    cd D:\Program Files\Microsoft SQL Server\MSSQL12.DUMBO\MSSQL\Binn <enter>

    Change Directory

  6. Now that I am in the Binn folder, I can start the SQL Server Instance.  Note:  This assumes the instance is stopped.  Go to Start | Control Panel | Administrative Tools | Services and find the SQL Server (InstanceName) Service and make sure the Status column is blank.  If it says ‘Started’, then right-click it and Stop the service.
    Administrative Tools
  7. So, back to the command window.  On the left side, we need to start the SQLSERVR service in single user mode, being sure to name the correct instance, like so:

    sqlservr -f -sDUMBO (where DUMBO is the name of the instance to fix)

    You should see SQL Server display a ton of messages.
    SQL Server Running

    If you get this error message:

    SQL Server installation is either corrupt or has been tampered with. Error getting instance id from name.

    Then check the instance name and try again.

  8. Now that SQL Server is running in the left window, go to the right window and start SQLCMD.  We need to use a trusted connection (-E parameter) and we need to specify the server so we can pick the right instance.  Like so:

    sqlcmd -E -sSHQBT0084\DUMBO   (where SHQBT0084\DUMBO is the server\instance to repair)

  9. The SQLCMD prompt 1> should appear:
    SQLCMD Connected
  10. Now enter the following SQL Server commands, pressing <enter> at the end of each line:

    1> sp_configure 'show advanced options', 1;
    2> go
    1> reconfigure;
    2> go
    1> sp_configure 'max server memory', 64000;
    2> go
    1> reconfigure;
    2> go

    Your screen should look like this:
    After the SQL Commands

  11. Assuming that there were no errors when you ran the reconfigure commands, you have fixed the server.  Now we need to clean up a bit.  If you did get an error, let me know via comment below.
  12. At the 1> prompt, type exit and press <enter>.  You can now close the window on the right.
  13. On the left, press Ctrl-C to stop the instance and enter Y when prompted.  You can now close the left window.
  14. Finally, restart the service in the Administrative Tools | Services window by right-clicking it and selecting Start.
  15. The End.






Removing Windows.old folder in Windows 2012 R2

It turns out that all the tips for Windows 7 and 8 rely on assumptions that are not valid in Windows Server 2012.

See here for some good Win 8 tips:

Removing Windows.old from Windows 8

For Windows Server 2012, I eventually figured out this kludge to get rid of the 16GB Windows.old folder:

  1. Take a backup of the OS Drive on your Server (Usually C:).
  2. As an Administrator on the local machine, or as a Domain Administrator, right-click on the Windows.old folder and choose Properties.
  3. Choose the Security tab.
  4. Choose the Advanced… button
  5. Beside Owner at the top of the dialog, click Change.
  6. Enter your own User Id.  Check Names.  Ok.
  7. Now check the box that appeared for Replace owner on subcontainers and objects.
  8. Click Apply.
  9. When it’s done, Cancel out of the Advanced Security Settings and Windows.old Properties dialogs.
  10. Then, reopen the Properties and select Security and select the Advanced… button.
  11. Now hit the Change Permissions button.
  12. Now click the Add button.
  13. At the top, click Select a principal and put your own User Id in.  Check Names.  Ok.
  14. Under Basic Permissions, click Full Control.  Click Ok.
  15. Back on the Advanced Security Settings dialog, in the lower left check the Replace all child object permissions with inheritable permission entries from this object.
  16. Click Apply.  Click Yes when it asks you to confirm.
  17. Click Ok when it’s done.  Close the Properties dialog.
  18. Now, close any other windows you happen to have opened in your frustrated attempts to get rid of Windows.old (I had many).
  19. In Explorer, right-click the Windows.old folder.  Hold down Shift and select Delete.  (Shift will skip the Recycle Bin and just delete the folder and it’s contents).  Some time later, the Windows.old folder “should” be gone.  I did this on two servers and on one it disappeared, on the other two files were in use somehow.  Even after a reboot, those two files are still in use.  Weird.  They only take up 8K, so I’m going to ignore them.  One day, I might go after them again, but I doubt it.
  20. It’s probably a good idea to force a reboot after deleting the Windows.old folder to make sure you didn’t just hose your server.  If you did, then you have a backup to restore from, right?

Mounting ISO Files

An ISO file is an image of a DVD or CD. In order to use its contents, you need to mount the ISO so that your computer can treat it as another disk drive. These instructions tell you how to install the necessary software to Mount the ISO.

Can you Mount the ISO file?

First, you want to know if you have a utility for mounting the ISO file or not.

In Windows Explorer, right-click on the ISO file and see if you have an option to Mount the file.

3-13-2013 4-17-11 PM

If you do not see a Mount option, as shown above, you will need to install a utility such as MagicDisk or Virtual Clone Drive.

The setup file for Virtual Clone Drive is available at

Mounting the ISO File

Now that Virtual Clone Drive is installed, you can mount the ISO file.

Warning:  If you are working with an ISO file on a network drive, copy the file to your local desktop or using the ISO will be very slow.

To Mount the File:

  1. Right-click on the ISO file you want to use.  (The one you just copied to your local desktop.)
  2. Select Mount (Virtual CloneDrive E:)  Note:  Your particular machine might not use E:, it could use F: or X: or any drive letter.
    3-13-2013 4-17-11-2 PM
  3. The ISO is now mounted as the E: drive (or whichever letter was selected when you chose the Mount option in step 2.
    3-13-2013 4-55-51 PM
  4. Now the ISO is mounted, you can access the files inside it.  Typically this will be a setup.exe or other .exe file on the newly mounted E: drive.  Locate the file and double-click it to run it.
    3-13-2013 4-59-10 PM


When Microsoft ate Winternals, where did Disk Commander go?

So I’m trying to make a recovery disk using Bart PE.  My Windows Vista (I know, I know, mock me later) boot partition is HOSED and I can’t get in to Windows.  So I thinks to meself, “this ’ere Bart PE ’as a plugin fer Disk Commander, I wonder if’n that’ll help.”  Turns out Disk Commander used to be sold by Winternals.  (You probably knew that already.)  If you google for it, you will find a zillion links to all sorts of versions that all take you here.  Yay.  Not helpful.

So, reading “Customers looking for desktop diagnostic and recovery tools,” I dutifully click the link, which takes me to the Microsoft Windows Enterprise home page.  Not helpful.

If I had only read the second half of that sentence, I would have saved myself considerable grief:  “Customers looking for desktop diagnostic and recovery tools similar to Administrator’s Pak should refer to the Microsoft Desktop Optimization Pack.”

In fact:  Disk Commander is part of the Microsoft Desktop Optimization Pack (MDOP).  Actually, it’s part of the Microsoft Diagnostics and Recovery Toolset (DaRT) which is part of the MDOP, and it is called ERD Commander.

There’s a very Windows 7-ish TechNet site devoted to MDOP.  Which didn’t help me much because I needed the Vista version.

It turns out that the XP, Vista and Win7 versions of MDOP are all available for “trial” in MSDN.  Listed as “Desktop Optimization Pack” in the “Servers” category.  (As of this writing.)

I think the MDOP 2007 is XP, MDOP 2008 is Vista and MDOP 2009 is Win7.

Woohoo.  Mischief managed.