Hi Melissa,
We have been using Oracle Spatial with ArcSDE for many years and have never had the SE_ANNO_CAD_DATA column inserted. It has been a very clean separation between ArcSDE and Oracle Spatial and we would really like to keep it that way. When you register an Oracle Spatial table using the command line, everything ArcGIS needs goes into the SDE schema and the business data stays as-is and untouched.
We are a large agency with many program areas developing datasets with Oracle Spatial and there are hundreds of scripts and processes built around these datasets that could break if an additional, and unneeded column is thrown in there.
So far technical support has said there is no workaround and that column is required. As we’ve shown for many years that column really isn’t required, so please add the option to NOT have that column inserted.
We appreciate any help you can provide with this!
I can bring this up with a wider audience, but I think that we made a decision a long time ago to ALWAYS enable all the entity types like cad & multipart. Users are not always aware of which editing tools will write to the CAD_ANNO_DATA column, and I do not believe that we have any tools that allow you to enable it later if you realize that you need it.
I'll let you know if I get a clear response on this. In the meantime it will be useful to understand why they cannot add another field to a table that is now part of the geodatabase.
-Shannon
April - I tested this out and am finding the same thing. As long as you don't include the entity type of c for CAD when using the sdelayer -o register the SE_ANNO_CAD_DATA column is not created during registration with SDE or subsequently with the registration of the geodatabase. Is there a reason you cannot have this column stored in the table even if it remains empty?
I see you have a support case open on this - 01702061
We are having similar issues with the SE_ANNO_CAD_DATA column being created in tables that contain the Oracle Spatial geometry type when we use the Register with Geodatabase tool rather than the SDE command line sdelayer -o register. Having a new column added to these tables is not ok.
If we register the layer with the command line THEN right click the layer in ArcCatalog -> manage -> Register with Geodatabase, the SE_ANNO_CAD_DATA column is not created.
Is there a way to use the Register with Geodatabase tool and NOT have the SE_ANNO_CAD_DATA column added to the table?
Thanks.
Hi Johan,
Just wanted to let you know that there is a KB article that has come out recently about using command line tools (10.x) against a 10.3 geodatabase is not a supported workflow.
44300 - Can ArcSDE command line tools be used against 10.3 and newer geodatabases?
-George
We upgraded from 10.1 to 10.3: ArcGIS Desktop, ArcGIS Server, Geodatabase on SQL Server. We still use the sdelayer 10.1 tool to register the multiple (spatial) views on the 10.3 Geodatabase, and some people still use the existing sde service 10.1 connecting to the 10.3 Geodatabase. - See more at: http://ideas.arcgis.com/ideaView?id=087E00000005A9t&returnUrl=%2Fapex%2FideaList%3Fc%3D09a300000004xET%26p%3D6%26sort%3Dtop#comments
In my case I ended up using a stored proceedure to create a temp view and insert from the temp table into a feature class. So, I'm creating a temp table (with OBJECTID set to int (identity 1,1), truncating a feature class then inserting from the temp table into the feature class. I scheduled this to run nightly and though not as clean as having a spatial view that users can add directly, it works to have nightly updated features AND I can export from the feature class without the unique identifier error. - Thank you Dave Watson!
I had the exact same problem with my objectids showing up as a long int (and they wouldnot work in AGO either).
I ended up having duplicate objectids. Once i cleared those up, i was able to manually add a primary key to the objectid field. My spatial views now show up with the objectid field correctly identified as "objectid" as the data type
Thank you for the clarification! My intention was not to go into the technical details of it however all I was trying to say was with respect to John Baleja's comment:
"I've been following this thread and first I want to make clear that all of the ArcSDE command line tools are available up through the ArcGIS 10.2.2 release. We have done a lot of work at 10.3 to fill gaps, but spatial views is an area where we still have work to do. I will post back to this thread as I have more information regarding our plans regarding spatial views."
I just wish ESRI comes up with something as easy as steps I mentioned above so the prompts for UID and Spatial extent would just go away and the 'views' will work as smoothly as they used to when created using 'sdetable -o create_view'.
This is something users are eagerly waiting for and it would be great if ESRI clarifies their stance on this. Is this something ESRI is working upon? Or adding them as query layer or drag/drop and then specify UID (assuming spatial reference is already defined) is the process they would like users to adopt? Or as Joshua Bixby says order your fields such a way that the desired field would be considered by ArcGIS as UID?
I hope I am clear this time, sorry for the confusion earlier.
Asrujit SenGupta, good points of clarification. Taking those points into consideration, it doesn't change my belief that I don't want ArcGIS assuming the first non-null field it finds is the unique identifier for a database view. Embedding special meaning to column ordering doesn't seem like a sound solution.
I think this may have been mentioned earlier as well. There are 2 sets of geodatabase repository tables - one for SDE and one for GDB.
The sdetable\sdelayer commands are used to register with SDE, but not register with GDB.
Creating a spatial view from ArcSDE commands will automatically register the view with SDE, however using ArcCatalog to register with Geodatabase, is to register with both SDE and GDB.
Database views are read only, they should NOT be registered with Geodatabase (as registering with Geodatabase needs to create an OBJECTID column).
I agree with Joshua Bixby here, fixing NIM085661 may not address the exact issue being discussed here. I am not sure why isn't ESRI coming up with an alternative to 'sdetable -o create_view?.
At 10.1, I remember there was a way to register table or spatial table created from backend (using SQL or DBMS tools) with geodatabase from ArcCatalog.
You just had to right click on the table - regster with geodatabase - specify unique identifier - shape field - spatial reference.
Is it too much complicated to incorporate similar method for 'views'? Or may be provide an option of registering view with geodatabase during its creation itself?
Does ESRI have an acceptable clarification on why they have not came up with an alternative for this commond? Or will this be served with ArcGIS 11?
Nate - To answer part of your post - the 3rd bullet: This bug for the unique identifier prompt has been brought up as a high priority for a fix and is planned for the 10.3.1 currently. This is a major usability defect that many users have been asking for. If you have an association to this defect then please keep track of this and verify when the release date is closer, but I am pretty sure it will make it into the 10.3.1 release.
UPDATE to this: This fix did not make it into the 10.3.1 release.
NIM085661 - ArcGIS does not set the first not-null field it finds as the unique identifier for a database view.
Without providing detailed specific examples, we use spatial views (and I include materialised views as well as standard views) from a simple join (for example a look up table to point/line/poly feature classes) right through to incorporating st_geometry functions including st_buffer, st_intersects etc.
Sure I can do a join using layer files, or build python scripts and create scheduled tasks to do my geoprocessing, but that's nowhere near as elegant (or simple) as doing it at the database level.
I really don't know what else we as users (and customers) need to do to convince Esri Inc of the importance of being able to register views with the geodatabase. And I'm still baffled as to why it was taken out.
Regards
-David
To Mody Buchbinder
Running compress on Linux/Unix
We have ArcSDE in Oracle 11g on a Linux server. We perform database maintenance operations (including compress) with a Python script executed by Task Scheduler on a Windows Server 2012r2 machine. We use a process very similar to the one Esri recommends and it works quite well; no SDEMON required. However, we did find that killing all users did not clean up the processes and sessions correctly on the Linux server. See my thread here for more information.
Chet,
From my perspective, situations where a view needs to be registered with the gdb are:
Maarten --
I wouldn't recommend the procedure David outlined to clients, for the very reason Chet mentioned -- ArcGIS sets different properties in the geodatabase for views than tables; by creating a table and replacing it with a view there's a great deal of "undefined behavior" possible -- While generating a micro black hole that consumes the server room is unlikely, some of the more likely complications could be indistinguishable.
- V
Hi Chet,
I've posted a comment regarding 'Create SDE spatial view from ArcGIS Desktop' on the Ideas website a year ago:
https://c.na9.visual.force.com/apex/ideaView?id=08730000000884aAAA
This idea has been marked as implemented, but as per my comment, the functionality of a query layer differs from an SDE (or as ArcSDE is gone, a geodatabase) view. I have never understood why a tool like 'sdetable -o create_view" does not have an ArcToolbox counterpart as nearly all my clients use geo views for performance reasons. For over 10 years I have explained my clients how to use the create_view command as a hacky workaround and now it seems I have to explain them the even hackier workaround David Gifford suggested. Last year I spoke to Sannon Shields at the Esri UC who told me that there are quite a lot of customers who worry about losing the create_view command.
Hi David,
The workflow for users who require the ability to work with views, is to access the view as a query layer or simply drag/drop the view (assuming it has a spatial attribute) into the map and define the required properties when necessary.
The workflow you have described can lead to unknown behaviors and errors in the application (ArcObjects) when working with this object. The reason is because, the object they are deleting is a geodatabase table and we support additional capabilities on tables that we cannot support on views.
With that said i would be interested in hearing more about situations where a view needs to be registered with the geodatabase. Could you describe workflows where you have required this capability?
I've been watching the spatial view discussion with quiet amusement, hoping that Esri would come to the rescue at the 11th hour and with the release of 10.3 and make it possible to register a view with the geodatabase again. I was wrong, and my faith has been shattered. Still can't believe that they got rid of something so important that's been around for at least 13 years that I know of. Shame.
But here's how it can be done using an Oracle database (must be similar for other flavours), I'll automate it eventually, just need to work out how to generate layer_id and object_id from stored procedures. I also did it in 10.1 I'm guessing it'll work in 10.3, if you try it let me know.
I also altered the view to add a new column, and the SDE schema updated accordingly in the column_registry table.
We will however be keeping to SDE 10.1 for a while yet, even though we're moving Desktop and Server to 10.3.
Just had another issue where only solution was sde commandline
https://community.esri.com/message/454895?sr=search&searchId=0e2452ab-cfa5-489b-b3ab-1137b53a42f6&searchIndex=1#454895
We have an ArcSDE on Linux/Unix. We run a process that compresses the database every night.
Now that we cannot use the command line to do it we should find a different way.
It can be run from windows compressing the database on Linux but we would like it to be Linux process only.
There is a compress GP tool but we need to run it on Linux.
Should we install ArcGIS Server Standard for Linux?
Should we buy a license for Server Standard only for this command?
Thanks
Mody
Thanh - Why are you having issues using the sdeconfig commands at 10.2? Are you having issues with the syntax for this?
A note about 10.3:
In previous releases of the enterprise geodatabase, the default number of connections allowed was 64. This limit helped prevent overloading the server when all or most connections to the geodatabase were made through an application server running on the same server as the geodatabase. Beginning with 10.3, there is no limitation imposed on the number of connections to new 10.3 geodatabases or geodatabases upgraded to the 10.3 release.
Thanh There is no tool as of now to change the max no. of connections limit. The 'sdeconfig' command is the only way.
There is a tool planned in the upcoming 10.3 for this which will help in these scenario. Just FYI.
How do I change the max number of connections to an SDE without the sdeconfig commandline tools? I'm using 10.2 with a postgres rdbms. I don't know why this is such a difficult task.
Obviously, it is helpful, no matter whomever you are, ‘dedicated SDE + DB developer’ administrator, data warehouse administrator (SDE + ArcGIS Server + developer), or geospatial analyst…
In fact, in many organizations, those roles are hardly differentiated, because the managements mostly want the projects done as effectively as possible, in addition to data administration…
+++++++++++
Bo,
I don't see the workscope for this project. However, I believe that SDE team is moving in this direction.
Traditionally, Bash or Perl are the tools of choice when operating systems need some scripting. Given their ease of use, they have become virtually ubiquitous and seeped into other software, including Oracle Database - which relies on them extensively for all kinds of administrative and management tasks.
Recently, however, this trend has shifted in favor of newer programming tools like Python, which offers intuitive development and a variety of flexible data structures and libraries. All modern Unix and Linux systems come with Python on board; for example, Oracle Linux 6.1 ships with Python 2.6.6.
Managing SDE from the ArcGIS Desktop UI is fine for interactive uses.
But for administrators who need automate the tools via a script, the UIs
are of little use. Can someone from Esri confirm that most if not all
the SDE command line functions are exposed via Arcpy?
Good & correct effort from SDE. We like very much! Thx a lot, team.
With the interface to those SDE admin tools in Catalog/ ArcCatalog, it will make geospatial professionals have more time on data quality, data integrity, application development and analysis, ....
Personally, it also offers more adavantages over other spatial engines like Oracle Spatial & SQL Server Spatial.
Alternatives to using SDE command line tools in ArcGIS | Support Services Blog
I agree that those tools would be nice, but fear for the privileges that would be necessary to run them -- both the scanner and updater user would need SELECT ANY TABLE access, and the updater would need to be run as SDE or a DBA with UPDATE ANY TABLE.
If i change the schema i am happy to be able to tell SDE the an update needs to occur. I am not saying SDE needs to do it automatically, I am saying there needs to be a workflow. Basically throw the "schema out of date" error, drag layer into arcmap as data owner to update schema, etc.. Is what we do now.
It would be nice to be able to run a "update schema to registry" tool or a validate all schemas type tool.
How could ArcGIS know that schema changes have occurred without notification? Doing a complete inventory on all tables to see if changes have been made before each query is made would cripple performance, even in a small database. The only way the SDE.* tables can know that changes have occurred is to tell them (SE_table_describe the table as the owner). The performance benefits of registration are significant, but they are based on a contract that extra-geodatabase changes are limited, or require notification.
We use oracle 11gr2 (11.2.0.3) . ST_GEOMETRY type, correctly configured. Abstract data type error everytime at version 10.
I just tested this since we took the database to 10.2.2 and it seems to work.
In response to "Editing the view after registration can create column registry issues." This is something SDE NEEDS to handle.
I totally agree with Jeff. We have always created our spatial views in CommandLine and then modified them as necessary in SSMS since 2006, and since moving to SqlGeometry in 2010. We have never gotten consistent results using Vinces' method becasue of the behavior diffculties with SQL Geometry Shape column. So, I would say that I don't agree with his 'rarely_used' assessment.
Jeff, this workflow isn't anything I've ever seen to be required. Have you configured the ST_GEOMETRY support library through hs/admin? I've never had an ADT error with respect to CREATE VIEW. I expect it's the database link that's the source of the errors (and they're very difficult to work with). Editing the view after registration can create column registry issues.
Thanks for this update.
Spatial views are a biggest use, and the biggest need. We cant use sql straight up (we get an error about abstract data types from oracle). So we have to create a spatial view with the commandline tools and then modify the view in sql. That frequently includes inner and outer joins, data accessed via database links, etc. Things that are absolutely essential to day to day function
I've been following this thread and first I want to make clear that all of the ArcSDE command line tools are available up through the ArcGIS 10.2.2 release. We have done a lot of work at 10.3 to fill gaps, but spatial views is an area where we still have work to do. I will post back to this thread as I have more information regarding our plans regarding spatial views. John
Agreed. We need a "purely with SQL" workflow
I'll add to this thread that in May of 2013, I submitted an incident which got tagged onto NIM085579, which is currently listed as Open and Assigned. This bug was created TWO YEARS AGO and it has still not been fixed. The analysts found that yes, a view created with CreateDatabaseView_management against a feature class using geometry storage (which is the default now) in SQL Server 2012 is indeed slower than command line spatial views against feature classes using sdebinary storage. The analyst I was working with at the time said (c/p from an email):
So the slower drawing time seen in the view created with SQL is expected. Please let me know if you have any questions about this.
Please, ESRI, don't be surprised when users like Joshua Bixby, me, and everyone else in this thread get frustrated on the subject of spatial views, when "it's slower now by design" is the type of response we get from Tech Support. Add to that command line tools going away, and what are we to do???
Please, please, please share the suggested workflow for making spatial views at 10.2+. And don't say saving a .lyr file is part of the solution.
Nate -
I am not aware of a method to take database views and register with the geodatabase at 10.3.
I do not believe that database views were intentionally allowed to register with the geodatabase. It seems that there is somewhat of a loop hole that allows for this if the view is created with the SDE commandline tools or registered with the commandline (sdelayer -o register) first and then register with geodatabase is available in the client UI.
What is your intention or need for the database view to be registered with the geodatabase?
The administration reference you are sharing states 'table' and not view. I did find a reference in the 10.0 help regarding registering views with the geodatabase.
There is a note within the help document about registering views with the geodatabase ‘Registering a table with the geodatabase’ version 10.0
“Note: Because ArcGIS cannot add an object ID field to a view or update the values in an existing object ID field in a view, you cannot register a view with the geodatabase.”
NOTE that registering with the commandline does not also register with the geodatabase as the commandline tools are not geodatabase aware.
Vince, the admincmdref documentation for 'sdelayer -o register' states:
I've got feature class A stored in SQL Server with geometry storage, and table B. I created a view (ABView) in SSMS that joins the table to the feature class. The view includes A.Shape. I'm using Desktop 10.2.2 against SDE 10.2(.0).
First, the Register with Geodatabase context menu item is disabled for this view. Second, when I run RetisterWithGeodatabase_management against ABView I receive:
The command line help documentation does not seem to be correct, at least for views created in SQL.
The bigger issue is that (like you suggested) I am able to use 'sdelayer -o register' to register the view with the geodatabase. So how are you going to be able to register views at 10.3+? Has the behavior for RetisterWithGeodatabase_management changed at 10.3?
I'm trying to register an Oracle (11.2.0.1g) materalised view (polygons with multipart) with SDE 10.2 but when using, right-click>Register with Geodatabase, the table is basically corrupted.
The view is fine before the register and I can view the geometries in the preview panel but after registration, it becomes unusable.
Some of the unexpected issues encountered include:
Previously we would use the sdelayer - register command and were able to tell SDE to register the table with support for multipart geometries (na+) and had better control over the ObjectID. These options seems to be entirely gone now unless i'm missing something. Some help would be great if anyone has any ideas.
Oracle, with ST_GEOMETRY
Did you give the schemalockingenabled a try yet? I did a quick test and this worked for my services accessing data that I wanted to update the spatial index on. The documentation does not state that there is a requirement for an exclusive lock for the 'Remove Spatial Index' tool, but this is in lines with a schema change. This changing from the older commandline tools to be more restrictive would make sense that you would want to direct this to an administrative windows to avoid possible performance issues for users accessing those datasets.
I am wondering if the error message was :
ArcGIS Help (10.2, 10.2.1, and 10.2.2) - Rules for working with Schema locks
ArcGIS Help (10.2, 10.2.1, and 10.2.2) - Error 001166
ArcGIS Help (10.2, 10.2.1, and 10.2.2) - Schemalockingenabled
Sorry for my delay in response. I agree that the remove spatial index tool does appear to need an exclusive lock from my testing. I do not know if the commandline tool removes existing connections - maybe did not check for shared locks against the datasets before dropping the spatial index previously. Please try the schemalockingenabled parameter within your map service to see if this alleviates the issues you are experiencing when trying to update the spatial index, etc.
ArcGIS Help (10.2, 10.2.1, and 10.2.2)
What RDBMS are you using? I always define my views with SQL, then register with 'sdelayer -o register', and I've never seen that error. 'sdetable -o create_view' is only really for use with the rarely-used SDEBINARY geometry storage.
For me its create_view
When we try to create_views directly in sql with st_geometry we get "abstract_data_types not supported". Our only work around the commandline tools. Until then, we will not upgrade.
We've encountered similar circumstances with complex objects like topologies and geometric networks. Once a delete or rename fails, the only way to clear the layer out of the table registry is sdetable -o delete. If the table is not found, you're prompted to continue to remove metadata about the table (which removes the entry from the sde table registry if possible):
If not for command line, how are we to remove orphaned table registry items? Does Delete_management have a similar table registry removal function?
Hi Melissa:
Thank you for your response. I hadn't used the schemalockingenabled configuration for services and will definitely look into it. However, the comment made by Tom Weisenberger was that command line sde doesn't care about map service locks in order to drop a spatial index. Does RemoveSpatialIndex_management need an exclusive lock or can it proceed with clients using the target layer?
Here is another scenario we ran into today that required command line tools. ArcSDE 10.0 sp4 on Oracle 11.2.0.3.0
AIX 6.1.Client is ArcGIS desktop 10.2.
1) Existing feature class xyz, versioned, used to be part of a replica but isn't any more (deregistered on both ends)
2) New feature class loaded to replace it, different table structure, copied into geodatabase as xyz_n.
3) Operator tries to rename xyz to xyz_o without de-versioning (oops). Multiple problems ensue, and xyz_n cannot be renamed to xyz. No combination of delete, reload, rename using ArcCatalog is solving. Looks like corruption of repository.
4) After "deleting" feature class via ArcCatalog, sde command line sdetable -o create used to create table xyz, and sdelayer -o add used to add shape field.
5) Back in ArcCatalog, register xyz as versioned, deregister, rename as xyz_o. Rename xyz_n to xyz, delete xyz_o. All is well.
This sort of problem with renaming objects happens with some frequency (at least once or twice a year). How are we going to recover when command line goes away? Using a new name for the object in many cases would be extremely painful due to multiple map and application dependencies across several organizations.
Andrew - I believe you need to login to the Ideas portal first and then use the URL - this will work.
Here is the title of the Idea on the site that you can also search for.
The inclusion of a sales force URL rather than the support URL highlights another interface problem (this time in support rather than SDE)that apparently affects internal users as much as it affects non-Esri users. Any chance for a direct URL?
Nate - Thanks for bringing these concerns to our attention based on your comments.
1 - The commandline tools are deprecated starting at 10.3 release
2 - If you have specific workflows that can only be done through the commandline then please submit a support ticket for these to be investigated.
3 - Have you tried the schemalockingenabled within the map service to see if this helps? I would try this and if not submit this to support so that they are aware the new workflow for setting a layer in load only mode is not comparable to the older commandline tools with the flexibility of data that is being served in a service.
4 - There is a tool for this at 10.3 'Export/Import geodatabase configuration keywords'
For your comment about the database views prompting for the unique identifier field - there is an existing bug for this and we are aware of this issue. I would suggest that you contact support and ask to be added to this bug since there is only the workaround of using the commandline tools to create the view or saving out as a layer file.
Yes – I also noticed that these values are not being updated on the external facing sites. We will work on getting these updated as soon as possible. Please post back next week with your testing results. Thanks Joshua
Melissa, thanks for the update. Interestingly enough, I looked up NIM096391 on Esri Support, and it has no information about it being fixed in any SP or version. Hopefully the review you speak of will also look at updating the Status information of the bugs as well. I just downloaded 10.3 pre-release and should be able to install it this weekend. I look forward to testing the fix.
Joshua - All of the defects that were created that have workarounds closely linked to using command line tools are being reviewed to be resolved. This bug appears to be a duplicate of NIM096391 and has been included as a fix for the 10.3 release. We are looking at getting this verified and documented as a duplicate.
This is a great thread, but also very troubling. The most recent info we have from ESRI is:
I have to say that ESRI has been most unhelpful in providing info on what's coming up at 10.3 - just look at the number of threads there are on this topic!! Your users rely on the type of spatial views that command line provided, and there is no combination of GP tools that allow you to create an "sde-registered" view. Anyone who says using query layers in place of spatial views is an option seriously needs to go through this dialog when adding 30+ views to an mxd - you'll understand our pain:
It's a joke that ESRI marked this idea as "implemented": ArcGIS Idea - Create SDE spatial view from ArcGIS Desktop
Please ESRI let us know what you have upcoming for 10.3 so we can plan accordingly.
This is an area of concern that users have voiced - It is taken into consideration in the following enhancement. You may also try logging an Idea or promote the existing Ideas out there that are similar -
eg: https://c.na9.visual.force.com/apex/ideaView?id=087E00000005AYZIA2
NIM095919 – SDESETUP: Create geoprocessing tools or arcpy functions for operations performed when using 'sdesetup' command
Jessica - There are plans to incorporate a user interface tool to export and import DBTUNE configuration files in the 10.3 release. 'Export/Import Geodatabase Configuration Keywords'.
we have a user/client with a trial license x days, now they purchased a permanent license, as we do to update it (GDB conection) without waiting to happen maturity, that can do the update recently ? .In AGServer is simple as software Authorization (option) is OK , but the connection to GDB ? We used sdesetup ... Before http://blogs.esri.com/esri/supportcenter/2013/06/04/updating-the-license-for-enterprise-geodatabases-at-10-1/
how am I to
sdeconfig -o export and sdeconfig –o import ?
I found a bit of irony in the following bug description my agency recently ran into:
NIM103380 : Unable to identify or select features from a view created using the Create Database View tool on ST_GEOMETRY feature class.Status: NewWorkaournd : Use the ArcSDE command line to create the view.
NIM103380 : Unable to identify or select features from a view created using the Create Database View tool on ST_GEOMETRY feature class.
Status: New
Workaournd : Use the ArcSDE command line to create the view.
It seems removing ArcSDE command line tools makes the Esri Support toolbox a bit smaller for offering workaround suggestions.
Melissa, thanks for your response.
I will investigate if this alternative will work for us. This is the main parcel feature class used by several map services and by users throughout the enterprise - so lots of connections to it. My main concern using this method is schema locks. Even though the parcel load script runs at night I would need to add code to remove all connections/locks first in order to run Remove Spatial Index. I'm concerned about failures if any locks remain. The command line would do it all regardless of any locks. (or does it remove connections/locks itself?)
Tom Weisenberger
Los Angeles County - Enterprise GIS
Tom - Give the 'Remove Spatial Index' tool a try - this should replace the commandline version of the sdelayer -o load_only / normal_io mode.
Within the current commandline administrators guide there is a statement about this tool with a globe indicating there is a recommended workflow within the user interface to try -
eg: load_only_io
Sets the input/output (I/O) mode of the layer to load-only, dropping the spatial index, and allowing only store and replace I/O operations
<esri globe> You can use the Remove Spatial Index geoprocessing tool or a Python script to drop the spatial index, placing the layer or feature class in load only I/O mode instead of this operation.
We require an alternative to load_only_io mode if Esri is going this route of abandoning SDE command line and offering geoprocessing alternatives. Fortunately SDE command line is still installed from SDE 10 on our server and it still works with our newly upgraded 10.2 geodatabases.
I have a weekly update script that loads 2.3 million parcels from a spatial view to a stand-alone repository feature class.
When performing a simple truncate (TruncateTable) and load (Append), without setting the parcel feature class in load_only_io mode first - load time is over 7 hours! Indexes rebuilt by RebuildIndexes then AnalyzeDatasets as recommended.
As we've traditionally done, by using SDE command line in python script subprocess, setting the feature class into load_only_io mode first, then back into normal_io mode - load time is less than an hour. This works great and rebuilds indexes at the same time. We've had no problems with this process for years and are worried that progressive upgrades are actually moving somethings backwards without appropriate consideration of customers requirements and established processes.
Please provide either a separate "bulk-load" tool that efficiently loads data into SDE at the same rate or provide a geoprocessing tool that does the same SDE command line for load_only_io and normal_io so these can be accessed through arcpy.
sdelayer -o si_stats is indispensible for maintaining some feature classes, and no equivalent is available in desktop at the current version. The GDBT spatial index examination tool at 10.0 sort of works, but command line works better and faster. Today I had to use sdelayer -o si_stats to examine and redesign the spatial indexes for statewide parcels. Before change, took four minutes to draw at a reasonably large scale. After change, 6 seconds or less. The before-change spatial index was the one "estimated" automatically by ArcGIS desktop when the data were loaded. Take away this command line tool and our ability to provide well-performing data will be crippled.
In the meantime, I'm working on a python script to read the SDE.LAYER table, feed the appropriate fields into the sdelayer -o si_stats command, and strip the output to usable info and pluck that in a database.
We specifically use the sde command-line scripting to handle loading data into sde from non-Windows platforms such as Linux systems. All in all this move away from sde command line is not very helpful for those of us that are just trying to automate loading of shapefiles into SDE/geodatabase in a shell scripting environment on a Linux platform.
# Name: FeatureClassToGeodatabase_Example2.py # Description: Use FeatureClassToGeodatabase to copy feature classes # to geodatabase format # Import system modules import arcpy from arcpy import env # Set environment settings env.workspace = "C:/data" # Set local variables inFeatures = ["climate.shp", "majorrds.shp"] outLocation = "C:/output/output.gdb" # Execute TableToGeodatabase arcpy.FeatureClassToGeodatabase_conversion(inFeatures, outLocation)
# Import system modules import arcpy from arcpy import env # Process: Create Database Connection File... # Usage: out_file_location, out_file_name, DBMS_TYPE, instance, database, account_authentication, username, password, save_username_password(must be true) arcpy.CreateDatabaseConnection_management("/net/shared/location/connections/", "zion.sde", "ORACLE", "zionserver/ORCL") # Set environment settings # Set the workspace to the specified data location env.workspace = "/net/sharedata/location/data" outLocation = "Database Connections\zion.sde" # Get all the feature classes in the environment # list of fc's should be similar to this: ["accident.shp", "veg.shp"] inFeatures = arcpy.ListFeatureClasses() # Process: Load Data (Use the "Feature Class To Geodatabase" Tool...) try: # Execute FeatureClassToGeodatabase print "Loading data...\n" arcpy.FeatureClassToGeodatabase_conversion(inFeatures, outLocation) except: for i in range(arcpy.GetMessageCount()): arcpy.AddReturnMessage(i)
Examples: (Linux) /tmp>loaddata_sde_params.py --DBMS ORACLE -i myserver/orcl -u user1 -p user1 --dataloc /net/sharedata/location/data (Windows) c:\tmp>loaddata_sde_params.py --DBMS ORACLE -i myserver/orcl -u user1 -p user1 --dataloc \\sharedata\location\data >loaddata_sde_params.py --help Usage: loaddata_sde_params.py [Options] Options: --version show program's version number and exit -h, --help show this help message and exit --DBMS=DATABASE_TYPE Type of enterprise DBMS: SQLSERVER, ORACLE, or POSTGRESQL. -i INSTANCE DBMS instance name --auth=ACCOUNT_AUTHENTICATION Authentication type options (case-sensitive): DATABASE_AUTH, OPERATING_SYSTEM_AUTH. Default=DATABASE_AUTH -u USER user name -p PASSWORD password --dataloc=DATALOCATION Path to the data (either a geodatabase or a directory with shapefiles -D DATABASE Database name (Not required for Oracle)
'sdemon' certainly did require SDE user access rights, it just passed those accessrights to any connection on the port (which could be construed as a security hole).- V
A lot of improvements on the geodatabase side have been made in the lastest ArcGIS releases like renaming and modifying columns and making the sde command line tools available via tools. The possibility of creating proper spatial views however, still needs attention before deprecating the current tools.
Signed in members can post, follow updates, and more. New here? Register a free account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
