topic Re: Alternatives to using SDE command line tools - Blog Discussion in Data Management Questions

Alternatives to using SDE command line tools - Blog Discussion

ChetDobbins — Mon, 21 Oct 2013 14:40:41 GMT

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-command-line-tools

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_102_FINAL_050713.pdf

Re: Alternatives to using SDE command line tools - Blog Discussion

PF1 — Tue, 05 Nov 2013 18:10:12 GMT

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-command-line-tools

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_102_FINAL_050713.pdf

The only limitation I have run into so far: Fine grained DBMS permissions at the table/layer level.

In arcsde you could grant or revoke 'edit' permissions with either insert, update, or delete:

sdetable -o {grant | revoke} -t <table> -U <user>
-A <SELECT,UPDATE,INSERT,DELETE> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}]
[-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-I] [-q]

The closest python script tool available appears to be the Change Privileges tool which only allows grant/revoke at the 'edit' or 'select' level:

The RDBMS equivalent commands for the Edit parameter are Update, Insert, and Delete. All three are granted or revoked simultaneously by the Edit parameter.

I run into this frequently as often times we would only like to 'update' features and restrict the 'insert' or 'delete' capabilities.

Re: Alternatives to using SDE command line tools - Blog Discussion

VinceAngelo — Tue, 05 Nov 2013 19:05:45 GMT

The versioning model doesn't support access resolution beyond {NO_ACCESS, READ_ONLY, READ_WRITE}. This is a design feature. You could not circumvent it with 'sdetable -o grant'
previously either (if all the required permissions weren't present for all tables, then the entire
feature class [or feature dataset] had the next lowest access, if any).

At 10.2, if you have simple feature classes (non-versioned tables, not registered with the
geodatabase), you can use Catalog to right-click Manage... Privileges..., and it will offer the
traditional four checkboxes (SELECT, INSERT, UPDATE, DELETE) by user.

So it seems as if your request is either impossible or already available. Do you have access to
10.2 to see if this meets your requirements?

- V

Re: Alternatives to using SDE command line tools - Blog Discussion

PF1 — Tue, 05 Nov 2013 19:14:40 GMT

The versioning model doesn't support access resolution beyond {NO_ACCESS, READ_ONLY, READ_WRITE}. This is a design feature. You could not circumvent it with 'sdetable -o grant'
previously either (if all the required permissions weren't present for all tables, then the entire
feature class [or feature dataset] had the next lowest access, if any).

At 10.2, if you have simple feature classes (non-versioned tables, not registered with the
geodatabase), you can use Catalog to right-click Manage... Privileges..., and it will offer the
traditional four checkboxes (SELECT, INSERT, UPDATE, DELETE) by user.

So it seems as if your request is either impossible or already available. Do you have access to
10.2 to see if this meets your requirements?

- V

Hi Vince,

We have a lot of backend processing and scripts which update data. Much of the data is not registered as versioned, but is registered with the GDB. So we frequently grant just 'update' to many accounts to necessary feature classes or tables.

I have seen those check boxes you refer to but have not tried them yet. Unfortunatly though those would not be script-able with python so thier use is limited for ad hoc purposes that are hard to track and baseline changes.

That was new news to me though regarding the 'design feature' with versioned datasets. It seams many of the traditional database capabilities are truly limited when using SDE versioning and I try to stay away from it as much as possible. Something as simple as a unique constraint on a primary key field is a prime example...

Appreciate the feedback! Thanks!

Regards,
Patrick

Re: Alternatives to using SDE command line tools - Blog Discussion

JoshuaBixby — Mon, 18 Nov 2013 20:29:04 GMT

I wasn't sure whether to post the following comments here or over in the Blogs, so I erred on doing both.

I don't mean to be contrary, but I just can't get excited about replacing one suite of simple, script-able tools with an assortment of scattered and mostly GUI-driven tools. For example, how is replacing several of the sdemon commands (-o info -I users/locks, -o pause/resume, -o kill) with tabs in ArcCatalog a step forward? I realize the tabs in ArcCatalog are going to be convenient for the casual SDE administrator, and I think providing them brings some value, but providing them as the exclusive means to manage geodatabases is a step backwards.

I am not necessarily advocating for keeping the command line binaries around, I just think nearly all of the functionality should exist through non-GUI means in one form or another. If Esri wants to migrate away from command line binaries to ArcPy/Python; great, I will work with that. I just can't work with managing thousands of users scattered across hundreds of geodatabases using tabs in ArcCatalog or some other GUI-only option.

Re: Alternatives to using SDE command line tools - Blog Discussion

RL — Mon, 18 Nov 2013 21:48:27 GMT

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_Desktop/019v0000000n000000/

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

Re: Alternatives to using SDE command line tools - Blog Discussion

CarmenDurham — Tue, 19 Nov 2013 18:51:49 GMT

The blog here was very helpful:
http://blogs.esri.com/esri/supportcenter/2013/10/04/do-this-not-that-alternatives-to-using-sde-command-line-tools/
but I found what I really needed way down in the comments/questions from others.

Please consider compiling an all-inclusive document that provides all the alternatives to using the sde command line tools as a reference. There are a lot of SDE admins who are very much "part time database admins" who probably don't get to read all about the changes until we are forced to (like upgrading to 10.1 "SDE", SQL 2012, and trying not to install SDE executables). Trying to search through the Online Help seems to make me bounce around to different locations and therefore more confused. I don't think I am alone.

I really, really miss the "ArcSDE Configuration and Tuning Guide for SQL Server" .pdfs that used to be created like this:
http://downloads2.esri.com/support/documentation/sde_/1009ArcSDE_Config_GD_SQLServer.pdf

I do like the GUI options for basic admin tasks - I will be able to hand over some of these tasks to others.

I found the following UC videos helpful even though I had seen the equivalent of these in older versions!:

Geodatabase Admin - Intro (helpful for seeing where new tools are located even if topic isn't new):
http://video.esri.com/watch/2735/geodatabase-administration-an-introduction

ADministering SQL SErver Geodatabase (notice it says nothing SDE - but is) - Shannon S. seems to always answer my questions.
http://video.esri.com/watch/2616/administering-your-microsoft-sql-server-geodatabase

Thanks,
Carmen

Re: Alternatives to using SDE command line tools - Blog Discussion

JoshuaBixby — Thu, 21 Nov 2013 14:39:48 GMT

For example, how is replacing several of the sdemon commands (-o info -I users/locks, -o pause/resume, -o kill) with tabs in ArcCatalog a step forward?

I have to eat one of my earlier statements. I have since learned that ArcPy now supports AcceptConnections, DisconnectUser, and ListUsers as of ArcGIS 10.1. The sdemon functionality for listing locks and stats still appears to be missing.

As an FYI, there is another related enhancement request someone referenced on the ArcGIS Ideas site:
NIM095119 - SDEMON:Create geoprocessing tools or arcpy functions for operations performed when using the 'sdemon' command.

Re: Alternatives to using SDE command line tools - Blog Discussion

JoshuaBixby — Mon, 25 Nov 2013 16:01:10 GMT

I have since learned that ArcPy now supports AcceptConnections, DisconnectUser, and ListUsers as of ArcGIS 10.1.

Sigh.... ListUsers requires an administrative SDE workspace/connection, SDEMON didn't, so there is still some progress to be had.

Re: Alternatives to using SDE command line tools - Blog Discussion

VinceAngelo — Mon, 25 Nov 2013 16:17:50 GMT

'sdemon' certainly did require SDE user access rights, it just passed those access
rights to any connection on the port (which could be construed as a security hole).

- V

Re: Alternatives to using SDE command line tools - Blog Discussion

FranciscoAcosta_Bazán — Tue, 26 Nov 2013 17:50:14 GMT

That will replace geoprocess for "dbtune" command ?
for loads in custom mode, change keywords etc.
I do not understand that there are no courses 10.1 / 2 where they could study the performance depending on the DBMS:
Oracle (ACTO), SQLServer ACTS ...

Regards
Francisco

Re: Alternatives to using SDE command line tools - Blog Discussion

ModyBuchbinder — Sun, 01 Dec 2013 08:19:54 GMT

Some more options that are missing:
1)            Sdegdbrepair - ??
2)            Sdeexport / sdeimport: should we continue to use this as a way to transfer data?
3)            Sdegroup - ??
4)            Sdelayer -o si_stats : is GDBT going to be part of core? It does not exists for 10.1
5)            Sdelayer -o load_only / normal_io : helps to load a lot of data.
6)            Sdetable -o add_uuid_column / populate_uuid_column - ??
7)            Sdetable -o unregister : should we use that at all?

We tried to get a NIM for all of them but failed�?�
Ofcourse not all of them are the same importance

Thanks
Mody

Re: Alternatives to using SDE command line tools - Blog Discussion

VytautasGipiskis — Wed, 04 Dec 2013 04:28:16 GMT

1. sdelayer -o stats (useful when working on sdebinary/sdelob)
2. sdelayer -o describe_long (to get a spatial index sizes for sdebinary, UI no longer shows it)
3. sdelayer -o alter -g (alter spatial index for sdebinary, as tools not always provide best estimate for spatial index)
4. sdegroup �?? while caches provides best per performance, sometimes users just need to snap to a vector features. Grouped layer provides best performance for such scenarios
5. sdelayer -o si_stats �?? there is no equivalent for this info in GUI
6. sdedbtune �?? what is a proper way to alter dbtune table ? Use SQL ?
7. sdeversion -o close_state for closing unclosed states ?

Re: Alternatives to using SDE command line tools - Blog Discussion

DavidKlein1 — Tue, 14 Jan 2014 00:18:10 GMT

Is it indeed the case that SDEGDBREPAIR will no longer exist at 10.2??

We have just had our 2nd unplanned outage on our editing geodatabase in 6 weekswhich was fixable by SDEGDBREPAIR. We are now planning our 10.2 upgrade...but the prospect of not having that SDEGDBREPAIR safety net is more than a little unnerving!

Can someone from Esri please chime in on availability of these other SDE command line tools?

Re: Alternatives to using SDE command line tools - Blog Discussion

DavidGifford — Thu, 16 Jan 2014 00:21:01 GMT

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

Re: Alternatives to using SDE command line tools - Blog Discussion

MichelleBoivin — Thu, 16 Jan 2014 17:26:16 GMT

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?

Re: Alternatives to using SDE command line tools - Blog Discussion

JohnFletcher — Tue, 04 Feb 2014 17:32:24 GMT

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_Desktop/019v0000000n000000/

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

Re: Alternatives to using SDE command line tools - Blog Discussion

MarcoBoeringa — Tue, 04 Feb 2014 18:53:08 GMT

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...

Re: Alternatives to using SDE command line tools - Blog Discussion

JohnFletcher — Thu, 06 Feb 2014 03:01:43 GMT

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

Re: Alternatives to using SDE command line tools - Blog Discussion

BlairShaman — Fri, 07 Feb 2014 11:41:04 GMT

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