ROK Technologies ESRI Developer Blog - ArcSDE Oracle

ArcSDE all of the sudden slow??

Mon, 07 Apr 2008 14:46:00 -0400

Hey folks...I have run into this issue twice in the past couple of weeks. 2 public access sites that we developed, all of the sudden came to a screeching halt. For some unknown reason, certain layers would start to draw extremely slow, and bogging down the entire application. Looking in the ArcIMS logs it was pretty easy to see the slowness. Layers that previous had a sub second draw time were now taking 30 secs+ to draw. After lots (and lots) of trial and error we determined the cause to be due to different ArcSDE service pack levels. Yes, I know that ESRI drills it into our heads that we should always keep everything at the same service pack level, and now I really believe them. What was happening was this...An internal-only instance of SDE was creating SDE export files and pushing them to the external instance of SDE. The internal was at SP4 and the external was at SP2. After upgrading the external to SP4, this bizarre behavior disappeared.

A Second Look at ArcSDE and SQL On Virtual Machine

Tue, 23 Oct 2007 10:54:00 -0400

I posted a while back about moving much of our server infrastructure over to a virtual environment. I have been pleased, for the most part with the outcome. However....While I already sorta new the answer to this question before I started, I would now not recommend a ArcSDE / SQL Server instance in a virtual machine. Or at least one where there is serious competition for disk io. So, we have now migrated our production box out of a VM onto a dedicated box. However, we still have a testing environment running ArcSDE and SQL in a VM. Its just for beta applications and has pretty low load. It was just a little too much to ask. Things ran fine, for a while, but as we brought more and more services online, it became obvious that this VM wasn't going to be able to handle the load we were throwing at it. I did even try some intermediate steps, like moving the OS and Host drive to a raid 5 array, but that still wasn't enough. Now, with all that being said...I am still extremely happy with the Virtual Environment for applications like ArcIMS, ArcGIS Server, DNS, etc. These have worked out very well for us.

Database Replication a Win Win

Fri, 24 Aug 2007 12:37:00 -0400

Today we have a guest entry from Steve Eakins, who is the GIS Manager at ETM, an engineering firm from Jacksonville, Florida. ROK and ETM have collaborated often on some large scale projects. Without further delay, here is Steve discussing a recent collaboration on Geodatabse replication. Working with ROK we have been able to setup SDE geodatabase replication using the ESRI 9.2 Desktop product since ESRI added that function in their release last year. Database replication gives us the ability, through a secure VPN connection, to maintain our data on the ROK SDE geodatabase. We can, on an automated regular schedule, synchronize only the changes thereby, minimizing network bandwidth utilization during peak hours while keeping our clients ROK hosted website up to date. Replication for us is enabled one way so that the ROK database is a child of our parent master. Using replication reduces delays in mailing or oversights in forgetting to send all the changed data. This is a huge time saver and is truly a win-win scenario. For more information see ESRI's website on database replication using ArcGIS 9.2 at http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Understanding_distributed_data Then talk to the ROK! Steven

How to manually delete references to an ArcSDE layer

Mon, 17 Apr 2006 13:09:00 -0400

Have you (or one of you users) ever deleted an ArcSDE layer through something other than ArcCatalog or the command line? Well then, you probably figured pretty quickly out that you shouldn't be doing that. The problem with using other deletion methods is that the ArcSDE does not 'know' remove references to the missing tables.. There are several references to the deleted tables stored in various places inside the SDE database. So, if you made the mistake of deleting those tables on your own, you'll need to maually remove these references. Now, I do not recommend playing around in the SDE database. I have hosed up more than one installation playing around in there. However, these command are pretty safe, and shouldnt give you any trouble....But, I cannot stree enough the importance of BACKING UP YOUR SDE DATABASE before you do this. Ok, so that means you cant send me nasty emails if it breaks. While this script is for SQL server, you can adapt it for Oracle as well. Run this in Query Analyser (or sql+) against your SDE database (where your SDE metadata is stored if you are using the multigdb model). Just change dbname to the name of the database where your layer was, and layername to the name of the layer.


DELETE FROM sde.SDE_geometry_columns
WHERE     (f_table_catalog IN ('dbname') AND f_table_name IN ('layername'))

DELETE FROM  sde.SDE_layers
WHERE     (database_name IN ('dbname') AND table_name IN ('layername'))

DELETE FROM  sde.SDE_table_registry
WHERE     (database_name IN ('dbname') AND table_name IN ('layername'))

DELETE FROM   sde.GDB_OBJECTCLASSES
WHERE     (databasename IN ('dbname') AND name IN ('layername'))

Loading Rasters into ArcSDE

Wed, 18 Jan 2006 18:49:00 -0400

I thought that I would share with you the single best pieces of advice that you can get when loading rasters images into ArcSDE. 1. Use either ArcCatalog or Command Line, but not mix and match 2. If you are mosaicing, load the first image without pyramids and do not build statistics. Load all but one of the remaining ones without pyramids or statistics. Load the last one with pyramids and statistics checked. 3. Never, ever ever ever try to do the smart thing and load them in a big batch. That would be too simple. Instead load them in a maximum of 20 increments. Check back every 4 hours or so and repeat. 4. If you have the raw images (like tif's) load those instead of sids. They will load much quicker. I seem to struggle with some issue or another every single tiime I try to load new images. These are my new rules to live by. They certainly are a pain, but it is nice when they are done.

Out_QueryTable

Mon, 03 Oct 2005 17:58:00 -0400

Us Coldfusion ArcIMS folks have grown to love and hate the Out_Querytable variable. For those of you that don't know what Out_Querytable is, its a query object that gets returned from Coldfusion after you send a query (spatial or attribute) to ArcIMS. This is very handy to have - becasue Coldfusion works so well with these types of objects. For you windows based folks, its essentially the same as a recordset. The good: You can loop over them, display them, make a nice value list, query them (with coldfusion's ability to query an existing query object). Basically anything that you can do to a coldfusion query object, you can do to the Out_Querytable variable. The bad: Things tend to get a bit hairy when you use ArcSDE based data. Who uses shapefiles these days anyway...The biggest issue for me are column names that get returned. For example, lets say I query roads. The columns that get returned will be fully qualified - like DBName.Owner.Tablename (in the case of SQl Server). Now try making a nice valuelist with VALUELIST(Out_QueryTable.DBName.Owner.Tablename) and see what evil things happen. Luckily, a few years back, James Welch (where are ya these days James?) created a sweet custom tag called cf_ArcSDEquery, which essentially converts the query to wddx, fixes it, at converts it back to a query object. The tag used to be on ArcScripts, but I haven't seen it there in quite some time. So, I'll attach it for everyone if they don't already use it. I also do lots of multiple spatial queries right after each other - like in a drill down identify. The problem is that lets say I find a result in the first, but not the second, and then the 3rd returns a result. If, after each query, I test to see IsDefined("Out_QueryTable") then run some processing...Its going to bomb after the 2nd spatial query - becasue it does exist - but not from that query. Of course, you can rename your queries and do all sorts of crazy work arounds to compensate for this. However, I recently discovered a fantastic undocumented function - removeRows:


Queryname.removeRows(startrow,endrow)

So, this means now that I can perform a spatial query, test for the results, do something with the results, and then remove the rows from the Out_querytable object. Then, I can move on to the next operation...Something like this:



   
                                       
    
     
         
             
                 
                  
                  
                    
                      
                    
                  
                
            
        
    







SELECT MUName
FROM   SRCSOILS
WHERE ObjectID in (#PreserveSingleQuotes(SOILSValueslist)#)

Then I can do the next drill down spatial query....Hope that makes sense and or helps someone out. Don't forget, here is the code for the cf_ArcSDEquery which will makle your life sooo much easier when working with SDE data:






    ArcSDEquery - #attributes.query# is not a valid Query and cannot be returned as #attributes.output#

ArcSDE and Oracle On Different Servers

Mon, 19 Sep 2005 17:55:00 -0400

Being from the SDE old school, I have had it drilled into my head for years the you should always install ArcSDE and your RDBMS on the same server. The argument is that there is the increase of network traffic between the two boxes would slow everything down. It sounds to me that this is just a leftover from the days of 10-base and 100-base connections. Nowadays, most servers have fiber connections with gigabit+ connections, so, this network traffic argument doesn't mean too terribly much any longer. Many orgs now are really trying to separate DB's and application servers, so the ability to efficiently install SDE and your flavor of RDBMS is going to become increasingly important. So armed with a couple of forum posts I was off. While I have done easily a hundred SDE SQL Server installs, I'm no slouch at Oracle these days either. I cut my teeth with Oracle SDE on a Florida Department of Health 8 cpu Sun Box, many years ago now, which is another story completely... I'm happy to report the install went very very smooth. I only needed to change a couple of things. I used a mix of command line, sql plus and the post-install wizard. I used sql plus, along with the provided install script (in the sdehome\tools dir) to create the SDE user, Schema and set permissions. I then used the post install to 'authorize' SDE and then create the 'repository'. Next was the command line to create the service. The big thing here is the -n option, which tells the sde service not to depend on the Oracle service to start. Since Oracle wasn't on this server, thats obviously pretty important. Now, the only tough part...When I couldn't get the ArcSDE service to start, I went searching. I was luck to find this ESRI Kb article, that outlined the mod in the SDEHOME\etc\dbinit.sde file that I needed to make (set LOCAL=netservicename). Too bad I didnt find that article before, because its quite good.

ArcSDE 9 Reminders

Thu, 07 Jul 2005 18:45:00 -0400

This is just a reminder to all you old school SDE users...This one has caught me more than once. I just bumped up my connection limit, and was hittin my head against the wall as to why they were still being capped at 48. Remeber back in the day when you could just modify your dbtune.sde file or your giomgr.defs file, restart (arc)SDE and that was it? Well, just a reminder that you cant do that anymore. You need to make those changes and then use the appropriate command to import that file into your spatial database. I know, most folks either started at version 9 or actually read instructions and already know to do this. But us old schoolers who started at (arc)SDE at version 3 have to be reminded sometimes. One of these days I'm going to post the SDE Configuration and Tuning Guide for version 3 - all 11 pages of it, which I have saved for sentimental reasons, just to show you how tough we all had it back then. Whats that guide up too now? 200 + pages if I recall...

ArcSDE Direct Connect Using 3-Tier Connections?

Wed, 29 Jun 2005 00:08:00 -0400

Ran into an interesting issue recently. I am using Direct Connect for a pretty high volume ArcIMS mapservice that would otherwise eat a ton of connections because of all the spatial servers its using. They start adding up pretty quick. So I notice that when I added the service, that the expected number of new connections got registered with SDE, even though I'm using direct connect. I checked this using the sdemon -o info -I users command. I check the map service and it works fine. I stop sde, and the mapservice still works ok. However, upon starting SDE back up, these connections from the mapservice register themselves again - or so it seems when I do sdemon...I also tested the same principle with Arccatalog. Same result. Use 2 tier when SDE is stopped and 3 if its running? What gives? Maybe this has been the default behavior all along and I just never noticed it? Well, it seems that Sdemon -o info will report direct connections too - it is just querying the sde.sde_process_information table in the SDE database (or whatever your sde service is using for its database). Look at that table and there is a column called direct_connect that will report whether or not it is a direct connection. However, looks like these connections still 'count against you' so to speak. You still need to bump up your number of connections, but you don't need the hardware to support it.

ArcSDE Based ArcIMS Service Errors - Direct Connect Solution

Tue, 21 Jun 2005 00:10:00 -0400

Howdy Folks....I had a lot of trouble with an app of mine last week. It took me a while to pin this one down. I hadn't seen any info posted anywhere on this before, so here we go... Here is the info... SDE 9 with sp3 SQL Server 2k IMS 9 with sp3 ServletExec 5 Jre 1.4.2 TCP keepalive is set to True on the Arcsde box I have this application that will work fine for a few hours, and then bam, it will start throwing errors. Looking through the ArcIMS logs, I get: [Jun 14, 2005 10:20:00 AM][3468 4000 ERROR] [ERR2407] (SDE error code -10 ) SE_stream_query : Network I/O error I get the above most of the time, but depending on what I'm doing the application at the time (query, map request, etc), I may get the one of the following: [Jun 14, 2005 7:38:47 AM][3500 3684 ERROR] [ERR2402] Field #SHAPE# not found. [Jun 13, 2005 8:11:52 PM][3236 4044 ERROR] [ERR2407] (SDE error code -38 ) SE_stream_execute : Attribute column not found [Jun 13, 2005 7:58:24 PM][3236 4068 ERROR] [ERR2407] (SDE error code -10 ) SE_layer_get_info : Network I/O error The weird part is this...one REQUEST (image or query) will work one second, and the next it will fail with one of the above showing up in the errlog. It almost seems to alternate for a while between working and failing, then it will all seem to work fine for another hour, and then the problem shows up again for a while My best guess is that this is a communication break between ArcIMS and SDE. After a lot of trial and error, we decided to try direct connect a try...Guess what? That did the trick! We have been error free for a week+ now. Here is a quick primer on Direct Connect for those who are unfamiliar. Direct connect is another way of connecting to ArcSDE. All of the ArcGIS clients now (since 8.2 I think?) ship with all the necessary libraries to connect to ArcSDE without going though ArcSDE. In other words, the work that ArcSDE traditionally performs can be 'sidesteped'. There are some finer points to it, but essentially, you just need to add an SDEHOME environment variable to point to the %arcims install directory%\ArcGIS\ArcIMS\Server and then change your ArcSDE AXL connection string to instance="sde:sqlserver:datasourcename" instead of instance="port:5151" (or whatever your port is). So, moral of the story - if you are having ArcSDE - ArcIMS based connectivity issues, it never hurts to try direct connect!

Loading Rasters in ArcGIS 9.1

Sat, 11 Jun 2005 00:15:00 -0400

Came across an issue during ArcGIS 9.1 upgrades. If you are having trouble loading raster images into ArcGIS after you upgrade, there is a known issue. ESRI and some users tracked it to a solution in the following thread... http://forums.esri.com/Thread.asp?c=93&f=1148&t=160054&mc=35 The thread goes into more detail than I wish to here, but it's good to see them post a solution. Mike

8.x to 9.x ArcSDE Upgrade

Wed, 08 Jun 2005 00:19:00 -0400

Just a quick reminder to everyone out there. Make sure to run a sdeversion -o compress operation against your SDE database BEFORE you upgrade. Of course, I uninstalled 8.3 and tried to upgrade to 9 without doing that. Ended up having to restore a backup of the SDE database, reinstall 8.3, run the compress, uninstall 8.3, install 9.1, then Upgrade via post install.

Using SQL Server Roles With ArcSDE

Wed, 04 May 2005 00:02:00 -0400

I know that most of you SQL Server / ArcSDE users probably know this already, but here is just a friendly reminder to use ROLES in conjunction with logins when assigning and managing permissions to your SDE layers. When working with large numbers of users, its always a good idea to create sql ROLES so that you don't have to assign permissions to each individual user. For example, try creating a new role called 'GIS_EDITOR' and add all the logins to that role that will be editing your data. Then, through arcatalog, assign permissions to that role, instead of going through each individual user login. Makes dealing with SDE permissions much easier. You can make a SDE_Users role that has create table permission in the SDE database to streamline that process too. So, when a new person gets hired and joins the planning department and needs editor privledges, just add them to the role instead of setting permissions on all the layers GIS_Dept_Editors, GIS_VIEWERS, IMS_APP_USERS.....Well, you get the point...