The fabled blue marker, what do you do?!
Our lives are filled with things we often overlook. Case in point, what are the little blue reflectors on the road that you see on the way home from work every day? I've tried to establish a pattern. Are they marking the center of the lane? Sometimes they’re right on the road center line, sometimes they’re right in the middle of your wheel path,
thump thump, and sometimes they’re barely on the road at all. If you look, you'll see them all the time; you just have no earthly clue as to what they actually do.
The OLE DB connection is not so different. It’s always sitting there in ArcCatalog staring right back at you under Database Connections. You usually skip right over it on your way to adding a Spatial Database Connection. Today, however, I urge you to stop, peer under the hood, and look at what an OLE DB connection can do.
The OLE DB connection is a window into the abyss, a.k.a. your geodatabase repository. This area is typically off limits. Perhaps your DBA isn’t a fan of people tinkering around in the RDBMS. Esri typically says “hands off” as well, since we don’t usually support errors caused by messing around with the underlying repository tables. Their sensitive nature aside, these tables can be quite helpful and using an OLE DB connection to access them is read-only, so it’s also safe. Your DBA can rest easy.
Many of the problems you might have during the course of a normal week might be sniffed out by using this connection. I typically use mine at least once a week, maybe even three or four times depending on how heavy my workload is. I’ll give a couple examples on how this connection can help.
Pretend, for a moment, that you’re trying to take your geodatabase to a fully compressed state (i.e., State 0). The dialog indicates that the process has completed successfully, but since you’re a rock star GIS professional, you know that a successful compression is not always indicative of a full compression. So you crack open the OLE DB connection, scroll down to your compress_log table, and look up your end_state_count. It reveals that you have a count of 1. Since 0 is a state and is always present, you know that a count of 1 indicates that you’ve successfully achieved a full compression.
But what if the value isn't 1? What if the end_state_count reveals that there are two states remaining? Not a problem, you scroll down to the state_locks table, revealing that state_id 233 is being locked by sde_id 622. You then deftly maneuver up to the process_information table and you find that sde_id 622 is your SOC account. Then it dawns on you, you never did disconnect your web server, so you stop your web server, freeing up this connection and it’s resulting locks, compress again, and you’ve achieved full compression, all without ever cracking open Management Studio or SQL Developer and freaking out your DBA.
Ye Ole Db Connection
Making the connection is relatively easy. Microsoft OLE DB connection drivers, which can handle Microsoft SQL Server and Oracle databases, are automatically installed. However, you may have to install the drivers for PostgreSQL databases yourself. Once installed, just click on “Add OLE DB Connection” within ArcCatalog, highlight the provider you want to use, fill out the requested information, and test the connection. If successful, click OK and you’re in! I know you 10.1 users are out there too. Don't worry, the link to opening an OLE DB connection is no longer on your ArcCatalog tree, but it's still around. It's now a button for your toolbar, so just click on Customize, go to Customize mode, select the Commands tab, highlight ArcCatalog, and drag the Add OLE DB Connection to your toolbar.
Good luck!
Oh yeah, the next time you see a blue marker on the road, glance to the right. I bet you’ll find a fire hydrant.
Steven E. - Geodata Support Analyst
Edited on 4/27.
