Tag Archives: Network

ETL Method – Fastest Way To Get Data from DB2 to Microsoft SQL Server

For a while, I have been working on figuring out a “better” way to get data from DB2 to Microsoft SQL Server. There are many different options and approaches and environments, and this one is mine, your mileage may vary.

Usually, when pulling data from DB2 to any Windows box, the first thing you might think of is ODBC. You can either use the Microsoft DB2 driver (which works, if you are lucky enough to get it configured and working), or the IBM iSeries Client Access ODBC Driver (which works well), or another 3rd party ODBC driver. Using ODBC, you can access DB2 with a ton of different clients. Excel, WinSQL, any 3rd party SQL Tool, a MSSQL linked server, SSIS, etc. ODBC connects just fine, and will work for “querying” needs. Also, with the drivers you might install, you can usually set up an OLE DB connection if your client supports it (SSIS for example) and query the data using OLEDB – this works as well, but there are some caveats, which I will talk about.

In comes SSIS, the go to ETL tool for MSFT BI developers. You want to get data from DB2 to your SQL Server Data Warehouse, or whatever. You try with an OLEDB connection source, but it is clunky, weird, and sometimes doesn’t work at all (PrimeOutput Errors Anyone?). If you do manage to get OLEDB configured and working, you still probably will be missing out on some performance gains compared to the method I am going describe.

Back to SSIS, using ODBC. It works. You have to create an ADO.NET ODBC connection, and use a DataReader source instead of an OLEDB source. Everything works fine, except one thing. It is slow! Further proof?

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/162e55e5-b64b-423e-94c1-dd764ca1f683

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96977

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cfade7e7-50d5-4447-9821-35c5d5ae1b66

http://www.sqlservercentral.com/Forums/Topic702042-148-1.aspx

http://www.sqlservercentral.com/Forums/Topic666993-148-1.aspx

Ok, enough links. But if you do read those. SQL 2000 DTS is faster than using SQL 2005/2008 SSIS. WTF? The best I can guess is that it is because of the .NET wrapper around ODBC. DTS is using “native” ODBC.

So, now what? Do we want to use DTS 2000? No. What to do though?

Well, after a few days of research, and just exploring around, I think I have found a good answer.. Replace DB2 with SQL Server.. just kidding. Here is what you need to do:

Install the IBM Client Access tools. There is a tool called “Data Transfer From iSeries Server” which the actual exe is "C:Program FilesIBMClient Accesscwbtf.exe"

image

This little tool allows you to set up data transfers from your DB2 system to multiple output choices (Display, Printer, Html, and Text). We want to export to Text file on our filesystem. You have to set up a few options, like the FileName, etc. In “Data Options” you can set up a where statement, aggregates, etc.

If you output to a file, you can go into “Details” and choose a file type, etc. I use ASCII Text, and then in the  “ascii file details” I uncheck all checkboxes. You set up your options and then hit the “Transfer data from iSeries” button and it will extract data to the file you chose in the filename field. Pretty sweet. But this is a GUI, how can I use this tool? I am not going to run this manually. Well, you are in luck.

If you hit the “Save” button, it will save a .dtf file for you. If you open this .dtf file in a text editor, you will see all options are defined in text, in a faux ini style. Awesome, we are getting somewhere.

Now, how do you run this from a cmd prompt? Well, we are in luck again. Dig around in C:Program FilesIBMClient Access and you will find a little exe called “rxferpcb”

image

What this tool allows you to do, is pass in a “request” (aka a DTF file), and a userid/password for your DB2 system, and it will execute the transfer for you. Sweet!

Now what do we do from here?

1) Create an SSIS package

2) Create an execute process task, call rxferpcb and pass in your arguements.

3) Create a BULK Insert task, and load up the file that the execute process task created. (note you have to create a .FMT file for fixed with import. I create a .NET app to load the FDF file (the transfer description) which will auto create a .FMT file for me, and a SQL Create statement as well – saving time and tedious work)

Now take 2 minutes and think how you could make everything generic/expression/variable driven, and you have yourself a sweet little SSIS package to extract any table from DB2 to text and bulk load it.

image

What is so great about the .DTF files is that you can modify them with a text editor, which means you can create/modify them programmatically. Think – setting where statements for incremental loads, etc.

image

 

You can see from the two screenshots above, that is all there is. Everything is expression/variable drive. Full Load, and Incremental Load. Using nothing but .dtf files, rxferpcb, a little .NET app I wrote to automatically create DTF’s for incremental (where statements), truncate, delete, and bulk insert. I can load up any table from DB2 to SQL by just setting 3 variables in a parent package.

After you wrap your head around everything I just went over, then stop to think about this. The whole DTF/Data Transfer/etc is all exposed in a COM API for “Data Transfer Automation Objects’”

http://www-912.ibm.com/s_dir/slkbase.NSF/643d2723f2907f0b8625661300765a2a/0c637d6b03f927ff86256a710076ab22?OpenDocument

With that information at your disposal, you could really do some cool things. Why not just create a SSIS Source Adapter that wraps that COM object and dumps the rows directly to the SSIS Buffer, and then does an OLEDB insert or Bulk Insert using the SQL Server Destination?

I have found in my tests that I can load over 100 million row tables – doing a full complete load, in about 6-7 hours. 30-40 million row tables in 4 hours. 2 to extract, 2 to BULK insert. Again, your mileage may vary depending on the width of your table, network speed, disk I/O, etc. To compare, with ODBC, just pulling and inserting 2 million records was taking over 2 hours, I didn’t wait around for it to finish. Pulling 2 Million records with my method described in this blog takes about 3-5 minutes (or less!)

I know I have skimmed over most of the nitty gritty details in this post, but I hope to convey from a high level that ODBC/OLE DB just aren’t as fast as the method here, I have spent a lot of time over the last few weeks comparing and contrasting performance and manageability. Now, if I could just get that DB2 server upgrade to SQL Server 2008. . . Happy ETL’ing!

Ubuntu 8.04 Hardy Heron – Second Chance, Wireless Works and was Easy!

So, last night I decided to give Ubuntu 8.04 Hardy Heron another try. I blogged previously about setting it up and getting wireless to work, and what a nightmare it was

What I did this time was this. First off, I have computers everywhere, a MacBook, MacBook Pro, Dell Desktop and Dell Laptop. I have Vista on the desktop, and the Dell laptop I wasn’t doing much with, so I decided to load up Ubuntu on it. After installing, I connected it to a wired connection, downloaded the OS updates and let is sit for a while. After a few minutes it popped up with an alert telling my that there were updated drivers for the BroadCom wireless nic card. Wha?!? I open the alert, hit enable, and it downloads new drivers and fwcutter and lo and behold, wireless starts working with no hassles. I am on Ubuntu right now dinking around. So what is the first thing I do? Well make it look like a Mac of course.

So now I have Mac OS 10.5, Vista, and Ubuntu 8.04 all running on different machines. I would say right now though I do like the Mac the best. I use Vista for anything that really needs Windows. I will probably setup VMWare Fusion again on the MBP, but a smaller partition. I moved all my music there (80+ GB) and had to free up some space, my Vista partition was 60 GB.

Ubuntu 8.04 Hardy Heron – Linux is Cool, Linux Wireless is Not – 10 Step Program

Gah. I have a love hate relationship with Linux. It is pretty cool, can do pretty much everything. But.. But.. wireless support is just a joke. Same issues with Yellow Dog Linux on the PS3.

Wireless should JUST WORK.

I downloaded the 8.04 iso, and burnt it to cd. Installed it in windows, which is cool, a 10 GB partition. rebooted and the windows boot manager lets you choose , Vista or Ubuntu.

After getting set up, logged in, I tried to get on wireless. Doesn’t work. The thing is with Linux, is if you start configuring stuff here and there, it can get WAY out of hand, and then its just wacked. That happened, so I reboot to Vista, uninstall Linux, reinstall.

Now, lets search the forums, blogs and what not to get wireless to work. These are the steps I took to get it to work. My laptop is Inspiron E1705 with Broadcom wireless..

Fire up terminal..

1) sudo apt-get install build-essential

2) wget http://bu3sch.de/b43/fwcutter/b43-fwcutter-011.tar.bz2

3) tar xjf b43-fwcutter-011.tar.bz2

4) cd b43-fwcutter-011

5) make

6) cd..

7) wget http://downloads.openwrt.org/sources/broadcom-wl-4.80.53.0.tar.bz2

8) tar xjf broadcom-wl-4.80.53.0.tar.bz2

9) cd broadcom-wl-4.80.53.0/kmod

10) sudo ../../b43-fwcutter-011/b43-fwcutter -w “/lib/firmware� wl_apsta.o

Now, reboot a few times, and then maybe.. just maybe your wireless will connect and work. Once it latches on, it seems to be fine. I am on Ubuntu right now, writing this post.

Only 10 steps to get wifi working, all manual, and just a PITA. Granted it took me about an hour to patch together 18 different ways to get it to work..

Now I know why people use Mac and Windows. There is now way regular users are going to put up with that. Its like having to turn a crank to get your engine in your car to start. Just ain’t going to happen. Maybe in version 9 :)

HowTo: Hack Your PS3: Installing Yellow Dog Linux 6.0, and Actually Getting It To Work!

So, a couple of weeks ago, I bought a PS3, for the Blu-Ray disc capabilities. Since HD-DVD has died and I can get Blu-Ray discs through Netflix, and since the PS3 can be updated with new features, I figured I would give it a try. (The 100 dollar EB games credit I had made the decision easier as well)

After playing around with the PS3 (I bought one game disc ; Uncharted, Drakes Adventures, and one Online game; Pain) I figured it would be good to hack around and install Linux on it. With the PS3, Sony makes it really easy to install Linux.

linux_on_ps3

I decided to go with Yellow Dog Linux (YDL), version 6.0 (the latest). Other distros will work, but this is the main one they support – you can download it here – http://www.terrasoftsolutions.com/support/downloads/

After getting the 3.7 GB file, which took forever, I burnt the ISO to DVD using Active@ ISO-Burner (a sweet ISO burner BTW). Ok, all set to install. A few things to consider

1) You need a USB keyboard and mouse

2) You need a HDTV for this to work

In the PS3, go to settings, system settings, and then format utility. You can format a 10 GB partition for “Other OS”.  Restart the PS3

Then put in the Linux DVD, go back to settings, system settings, Install Other OS. YDL said /ps3/otheros/otheros.bld  … so, hit start. Then it will tell you how to get to the other OS.

Go then to “Default System” menu and choose Other OS, and then restart..

It should restart into Linux. It will be at a prompt, “kboot:” – hit enter, it should get into setup, you can follow the onscreen instructions from there. Everything is pretty much default and hitting yes to any prompts, a basic install. You set up a root password, etc. Reboot after it is done, it will go through some final setup steps (checking sound, etc), after that, you should be able to login.

Logging in, first thing I wanted to do was get networking set up. I am not using the wired connection, rather I am using wireless, with WPA. Here is where the fun starts, and why I hate Linux.

YDL doesn’t support WPA out of the box, isn’t that awesome? The wifi was there in network config, etc, and I could put in settings, but it would never connect. I do love the error it gives you as well… “Check Cable?” umm… well, I would but Linux, remember we are configuring a Wi-Fi Connection? No Cable?

Anyway’s.. debacle time.. found this after some searching – http://dachaac.blogspot.com/2007/08/guide-to-get-wpa-psk-working-on-ps3-ydl.html

This works… I just connected to a “linksys” for 2 seconds to download the RPM (or you can just put it on a flash drive as well), and then disconnected. Going through that tutorial though,there was one thing I was getting hung up on. The networking commands were not working for me. I was logged in a regular user, so I tried “su” to switch to root. Still no dice. I had to logout, and login as root, and run through that tutorial (unpacking the RPM with –force works with “su”).

Awesome, my networking is working and I am using WPA… yesssss! Now, just check a few things, email, reader, Facebook, cool. How about YouTube… wait… no flash. Check Adobe’s site.. no flash for PPC architecture.. WTF? So no flash at this point…which stinks..

Whew, all the Linux hacking is making me thirsty.. ok, lets boot back to the PS3 OS…wait.. how?

After doing some more digging, there should be a command “boot-game-os” you can run from the terminal and it should reboot. Wait, trying to run that command, it says “command not found”… another WTF…

After some more digging, it turns out the command resides in the “/sbin” directory, which users don’t have access to… (even root? man…)

So we need to make a sym-link. You can do this as a regular user, but you need to “su”, then run

cd /bin

ln -s /sbin/boot-game-os boot-game-os

Then, you can just go to the “start” menu in TDL, and there is an option then to “Boot Game OS” – works like a charm, reboot and you are back to the PS3

Salon Transcripts (STX) – Using Remotely – Opening Ports, Port Forwarding

A little background: Salon Transcripts (STX) is a Salon Management program for Mac’s. It is used by salon’s all over the country, small and large shops. The site they have is basically for sales. Tech support is 18 dollars per call/email, etc. They don’t have much for documentation, much less connecting remotely. I was tasked with getting it working remotely.

My first step was get remote access to a machine inside the network. For this I did 4 things.

1) Install DynDNS client for Mac

DynDNS (Dynamic DNS) allows you to create a faux domain name for an IP behind a NAT, and the little client will update DynDNS service so it is always up to date. Basically make a dynamic IP static. I installed this on a computer at the salon that is always on.

2) Install Vine VNC Server for Mac

Vine is a VNC server, I installed this on the same computer as the DynDNS client on the internal network

3) Install Chicken of the VNC for Mac

On the client computer (laptop) I installed Chicken of the VNC (love the name). Read somewhere this was the best VNC client for Mac, so I went with it.

4) Port Forward Port 5900 on the AirPort Express to the Machine Internally

Using the app in the Utilities folder on the Mac internally, AirPort Utility, I forwarded port 5900 (vnc port) to the computer with Vine and DynDNS

So far so good. I could then connect to the server on the internal network, change any settings, open more ports, etc. I could just use STX on the server machine, but it was really slow over VNC. Initially I was going to just have them use VNC and then FolderShare or email PDF reports back to the laptop so they could print them remotely, but just to slow.

I decided to get STX on the laptop to connect remotely over the Internet to the server on the internal network. Since there is NO documentation on this, I had to do some digging. First, what ports does STX use, and what is the STX server IP internally? Getting the IP is trivial, there is a setting in STX->Preferences->System where you can set the IP, so I had that. Finding out the ports, I fired up Terminal on the server, and ran

netstat -na | grep ESTA

To see what connections were established

stx_netstat

As you can see there are 3 ports that were going the server (the server was 10.0.1.100) – 9090, 4000 and 20260. These are the 3 ports you need to open up on a router and use port forwarding to the server to connect STX remotely.

Once again, the ports to use Salon Transcripts (STX) remotely are:

Port 9090
Port 4000
Port 20260

Those 3 tidbits would have saved me some time :)

After port forwarding those to the server, I tried connecting the laptop client over the net to my DynDNS host and it worked!

One thing I noticed though is running reports is VERY SLOW over the Internet, almost locks up STX.

Google Apps For Your Domain – Google Talk, Federation, SRV Records, Domain Transfers, DNS

Well, I want my Google Apps (GAFYD) account to be able to use Google Talk with other users (using Federation) besides Gmail and GAFYD (ex: Twitter, Meebo, etc). To do this, you need to add SRV records to your DNS (http://www.google.com/support/a/bin/answer.py?answer=34143) – no problem right?

Well, my current hosting (hostmysite) doesn’t allow SRV records, so after going at it with Tech Support the answer was to "switch to another DNS provider". Yikes. My current registar is active-domain, which doesn’t support SRV records either (from what I can tell). GoDaddy does, so I will transfer my domain there. Another no problem, right?

I initiate the transfer from active-domain to GoDaddy, but then read in the FAQ on active-domain I have to manually request to be transferred to get the auth code. Still waiting for that. Then I will have to make sure GoDaddy is set up as active-domain was so my stuff still works. Then, I can finally change GoDaddy to run all the DNS for the domain instead of just pointing to hostmysite’s nameservers.

I tell ya, nothing can be easy. :)

How To: Connect to SQL Server, VS TFS, etc using Windows Authentication when computer is not on Active Directory Domain (XP and Vista!)

Whew, long title, amazing results!

Problem: You have a laptop or computer and you are working remotely for a company. You VPN in. Your computer is not on their Active Directory (AD) domain. You try to connect to SQL Server using SSMS or Analysis Services using Excel, but it doesn’t work because it is using your user, not a domain user. How do you get around this?

Answer: Well, this is what I have found (tested on XP only) – start->run: \computernamec$ – then it prompts you to login. Use your AD username and password, so

domainusername and password, and check the box to save password.

Seems that XP will save that in your authentication list somewhere, and then you can use SSMS or Excel to connect to the SQL Server via Windows Authentication!

This trick also works for TFS Build Servers/Team Explorer (tested with VS2005 Team Explorer) ..

Now for the fun part – Vista. The tricks above don’t work on Vista, but you can still get it to work. Here is what you do…create some shortcuts…

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft OfficeOffice12excel.exe”

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft Visual Studio 8Common7IDEdevenv.exe”

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDESqlWb.exe”

 

Replace “domainusername” with your info. So if your domain is mycompany then it would be mycompanysteve.novoselac for example.

What happens is that then when you run those apps from those shortcuts it will prompt you for your domain password, you put it in, and it runs the app in the context of your domain user. You can then change the icon for each of these pretty easy, just browse to the exe in the second part when clicking the change icon button on the shortcut properties (the shortcuts are actually links to runas.exe which is a generic icon)

In Vista, for instance, if you are testing SQL (SSMS), you might get this error:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

The shortcuts above will get you around it in the situation where your computer is not not on the domain or you are not logged in as a domain user..

These tricks above are especially good if you need to connect to SSAS (Analysis Services) since it is only Windows Authentication. And also, the IT department doesn’t really need to have consultant machines on the domain, or VM’s set up, etc, instead they can use these workarounds