Alternatives to using SDE command line tools - Blog Discussion

[ChetDobbins]

With the release of 10.2 and plans to deprecate the ArcSDE command line tools, you may be wondering how current tasks that use these tools can be completed elsewhere. This blog provides some workflows that have alternate user interface tools in ArcCatalog/ArcMap that will make transitioning as seamless as possible.

http://blogs.esri.com/esri/supportcenter/2013/10/04/do-this-not-that-alternatives-to-using-sde-comma...

We are very interested in hearing feedback from everyone who uses the ArcSDE commands, including questions, concerns, and ideas for making this successful. You can also contact Esri Support Services for specific ArcSDE commands that do not have comparable replacements.

This document outlines the planned changes in platform and functionality in the ArcGIS 10.2 release and
includes a reference to ArcGIS 10.1 deprecation notes.
http://downloads2.esri.com/support/TechArticles/W26496_W25918_DEPRECATION_PLAN_FOR_ARCGIS_101_and_10...

[DavidGifford]

Shp2sde has (had?) the reject output option, where any shapes that weren't "right" would be written to an output shapefile. Really useful for large datasets when importing to SDE. If something similar could be done for Feature Class to Feature Class (maybe outputting the rejects into the same format as the input feature class) this would save users a lot of time. FC to FC currently just writes rejects to a text file listing the OID's which is almost useless in a large dataset.

I have this listed in ArcGIS Ideas (http://shar.es/U3csA).

Regards
-David

[MichelleBoivin]

Currently I am running through some issues creating spatial views, and according to ESRI the only way to create them is with the sdetable -o create_view command. Reason being is that all of our data currently has to be in SDEBINARY due to a GEOMETRY storage type incompatibility with geometric networks and network datasets (ESRI proven with open-ticket). We rely on numerous views for a great number of applications within the City and I cannot imagine not being able to create those. In the case of deprecation of the command line SDE tools, has this issue been addressed at all?

[JohnFletcher]

I have an interesting observation and wanted to share with you guys and see if you experience the same thing.

I am on ArcSDE 10.2, with Oracle 11.2.0.2.0, OS is RHEL6.1.  I was excited that using ArcGIS for Desktop there is an option to create a database view. So I followed the steps listed here
http://resources.arcgis.com/en/help/main/10.2/index.html#/Create_a_database_view_in_ArcGIS_for_Deskt...

and created a simple database view of one of my SDE feature layers.

I also created a similar view using SDE command line tool like I used to do in 10.0 and before.  Both methods created the views. However, I discovered that in the view created using the desktop tool, the objectID field data type is Long Integer, whereas the view created by the "traditional" SDE command line tool is ObjectID data type.

There are some advantages of having it as the "object ID" data type, especially using the view in ArcGIS services, etc.

I wonder if anybody else notice this?  Is this "long integer" thing a bug? or is it by design choice?  I hope to see some documentation about this, but haven't find any. I am very interested hearing how you guys think about this....
Thanks
Rong

I recently noticed something similar, with GUID type fields in Oracle. Using command-line tools results in a view with the same GUID types as the source tables. Using desktop results in a view with all GUID types converted to text. As GUIDs behave differently than text (importantly, what their respective string representation is), this is problematic.

My suspicion is that the columns in the view created using desktop either do not get inserted into COLUMN_REGISTRY, or they get inserted with the incorrect types. The actual database type is char(38), so without an entry in COLUMN_REGISTRY (or similar) there'll be no way for ArcGIS to know that this is meant to be a GUID. I'm just continuing to use command-line tools to create the views, for the time being.

John

[MarcoBoeringa]

I recently noticed something similar, with GUID type fields in Oracle. Using command-line tools results in a view with the same GUID types as the source tables. Using desktop results in a view with all GUID types converted to text. As GUIDs behave differently than text (importantly, what their respective string representation is), this is problematic.

My suspicion is that the columns in the view created using desktop either do not get inserted into COLUMN_REGISTRY, or they get inserted with the incorrect types. The actual database type is char(38), so without an entry in COLUMN_REGISTRY (or similar) there'll be no way for ArcGIS to know that this is meant to be a GUID. I'm just continuing to use command-line tools to create the views, for the time being.

John

They certainly should not be inserted into the COLUMN_REGISTRY, as that is an ArcSDE System Table. The tool you are using - Create Database View - is nothing but a "button" or "skin" to access RDBMS database functionality. It is not part of the ArcGIS Geodatabase realm, and hence it would be bad if it inserted data into the COLUMN_REGISTRY.

This may sound like a regression coming from the SDE Command Line tools, but this actually allows you to create (spatial) views in databases that are not "geodatabase enabled" and thus not "geodatabase aware", e.g. any "ordinary" non-GIS database you have on the shelves, like for customer data.

In addition, these tools were not really (or not only) designed to replace the ArcSDE Command Line tools, but to fulfil other ArcGIS users' needs.

Of course, you can do the same thing just as easy from your RDBMS management interface, like SQL Plus, SQL Server Management Studio etc, but this tool allows you to script it in Python using arcpy, or use it in ModelBuilder.

It is probably confusing because ArcGIS now contains both tools and menu options acting against geodatabases only, and tools working in mixed or non-geodatabase RDBMS systems, something many users still need to get accustomed too...

[JohnFletcher]

They certainly should not be inserted into the COLUMN_REGISTRY, as that is an ArcSDE System Table. The tool you are using - Create Database View - is nothing but a "button" or "skin" to access RDBMS database functionality. It is not part of the ArcGIS Geodatabase realm, and hence it would be bad if it inserted data into the COLUMN_REGISTRY.

This may sound like a regression coming from the SDE Command Line tools, but this actually allows you to create (spatial) views in databases that are not "geodatabase enabled" and thus not "geodatabase aware", e.g. any "ordinary" non-GIS database you have on the shelves, like for customer data.

In addition, these tools were not really (or not only) designed to replace the ArcSDE Command Line tools, but to fulfil other ArcGIS users' needs.

Of course, you can do the same thing just as easy from your RDBMS management interface, like SQL Plus, SQL Server Management Studio etc, but this tool allows you to script it in Python using arcpy, or use it in ModelBuilder.

It is probably confusing because ArcGIS now contains both tools and menu options acting against geodatabases only, and tools working in mixed or non-geodatabase RDBMS systems, something many users still need to get accustomed too...

Thanks Marco, good points. I can see the value in tools that operate on the DB without involving the GDB.

I still do not know how to create a spatial view that preserves the data types of the source feature classes (GUIDs in my case). Well, without using SDE command-line that is. I don't think that it would necessarily be a regression, but I do think that there needs to be some other tool which does allow for this use case.

John

[BlairShaman]

I'm using SQL to create a table in SQL Server with a geometry column. I need a way to automate registration of that table with the geodatabase. Using arcpy.RegisterWithGeodatabase_management doesn't work if the table is not populated; it complains that there is no geometry type specified.

I'm assuming that I would be able to do this using sdelayer -o register with the entity mask parameter, although I haven't tried. I would also be able to specify the spatial reference using the command line tool. The Register with Geodatabase tool, however, takes no parameters except for the input table.

Is there something I'm missing here, or is this another piece of functionality that isn't yet available in UI/GP/ArcPy?

Blair

[ShannonShields]

I'm using SQL to create a table in SQL Server with a geometry column. I need a way to automate registration of that table with the geodatabase. Using arcpy.RegisterWithGeodatabase_management doesn't work if the table is not populated; it complains that there is no geometry type specified.

I'm assuming that I would be able to do this using sdelayer -o register with the entity mask parameter, although I haven't tried. I would also be able to specify the spatial reference using the command line tool. The Register with Geodatabase tool, however, takes no parameters except for the input table.

Is there something I'm missing here, or is this another piece of functionality that isn't yet available in UI/GP/ArcPy?

Blair

Blair,

is there any reason why you are using SQL to create an empty table then register it with the geodatabase, rather than simply creating a feature class using python directly? RegisterWithGeodatabase is intended for registering existing (e.g. populated) tables, as opposed to brand-new empty ones that could simply be created using GP/UI/Python.

-Shannon

[BlairShaman]

Blair,

is there any reason why you are using SQL to create an empty table then register it with the geodatabase, rather than simply creating a feature class using python directly? RegisterWithGeodatabase is intended for registering existing (e.g. populated) tables, as opposed to brand-new empty ones that could simply be created using GP/UI/Python.

-Shannon

Shannon,
Unfortunately, that part is beyond my control. This lives within a larger database, the majority of which is non-spatial. There will only be a few tables that contain spatial data. The people that I'm working with are creating and updating the database within a Visual Studio database project, and need to build views and other database objects against the spatial tables. They would much prefer to be able to do all of this in Visual Studio, meaning that it would create tables with a geometry column, and then I would register them after the fact. Not ideal, I know.

Blair

[ScottBateman]

Surprised there has been no mention of .sdx files for moving data around.  Is the .sdx file format going to be supported any longer with the deprecation of the tools that create/consume them?

I agree with bixb0012 about GUI options being a step back for administrators.  As your environment grows, automation and anomaly identification become key for monitoring and finding problems.  If you want to check things one at a time, GUI is fine but if you want to setup alerts to let you know when a spatial index is missing or sub-optimal GUI is not all that helpful.

Along those lines, haven't heard any response to the si_stats question.  I've have problems with GDBT crashing on me a lot in the past so have favored si_stats for spatial index analysis and tuning.  let's face it, Windows GUI is slower and much less stable than a simple background thread and doesn't lock up my environment and prevent me from doing other work. 

Same point here about GUI vs. command line.  If you want to setup a job to check all of your SIs to make sure none have too many features per grid... I don't care what the tool is - SDE command line, python, LOGO, smoke signals... I just don't want to have to sit there and click it all day with my mouse.  Much more productive to review a report of anomalies each morning and do analysis and resolution instead of monitoring.

[Anonymous User]

I'm very interested to hear ESRI's guidance on .sdx as well. For large datasets, sdeexport/sdeimport is probably one of the most efficient means of transferring data. FGDB import/export is orders of magnitude slower. Many of our customers prefer this option because it is simple to implement and very fast.

-Joel