topic Re: View number of edits made in SDE.Default in Data Management Questions

View number of edits made in SDE.Default

Lake_Worth_BeachAdmin — Wed, 12 Apr 2017 13:03:33 GMT

I am looking to see the number of edits(changes) made in the Default.SDE (users editing directly to the default) to catalog these numbers before I run my reconcile/post/compress script.

I know this can be done with versions with the "version Changes Tool" See edits made in a specific SDE Version before Reconcile/POST however this does not let me see anything with the SDE.Default.

I would prefer using arcpy (not mandatory) so I can add a few statements to my reconcile/post/compress script so it will print out the number of edits made (I need to catalog these numbers every week).

Re: View number of edits made in SDE.Default

Lake_Worth_BeachAdmin — Fri, 14 Apr 2017 12:05:03 GMT

Does anyone have any ideas?

Re: View number of edits made in SDE.Default

RexRobichaux2 — Fri, 14 Apr 2017 23:33:28 GMT

This might be possible through python- but it should certainly be achievable through a DBMS client (for example SQL Server Management Studio or SQL Developer for Oracle) of a feature class by feature class level. Try the following workflow and query (this was done within a SQL Server geodatabase for testing).

In ArcCatalog create a new point, line or polygon feature class in your enterprise gdb. I created a test polygon FC named "Defaulttest"
Register the new FC as versioned (without the option to move edits to base)
Open SQL Server Management Studio and navigate to the SDE_table_registry table < Open the table and order by table_name column
Find the newly created Defaulttest feature class and its corresponding registration_id value in the column. This will correspond with the delta (adds and deletes) tables for that feature class For me it was (182- update the below query with your values)
Then run the following query (ensuring it's against the geodatabase containing the edits):

SELECT 'Updated Features' as delta, COUNT(*) as cnt
FROM sde.A182
WHERE SDE_STATE_ID IN
(SELECT STATE_ID
FROM SDE.sde_STATES
WHERE LINEAGE_NAME IN
(SELECT LINEAGE_NAME
FROM SDE.sde_STATES
WHERE STATE_ID =
(SELECT STATE_ID FROM SDE.sde_VERSIONS WHERE NAME ='DEFAULT'
)
)
)

Likewise, the following query should return how many deletes are currently stored in the associated delete table:

SELECT 'Updated Features' as delta, COUNT(*) as cnt
FROM sde.d182
WHERE SDE_STATE_ID IN
(SELECT STATE_ID
FROM SDE.sde_STATES
WHERE LINEAGE_NAME IN
(SELECT LINEAGE_NAME
FROM SDE.sde_STATES
WHERE STATE_ID =
(SELECT STATE_ID FROM SDE.sde_VERSIONS WHERE NAME ='DEFAULT'
)
)
)

There might be a way to obtain total amounts of edits (cumulatively)- but this is the only easy method I know in SQL to determine updated feature numbers on a feature class by feature class basis. I hope this is helpful!

Best,

Rex R

Re: View number of edits made in SDE.Default

Lake_Worth_BeachAdmin — Mon, 17 Apr 2017 12:28:17 GMT

thank you,

I am hoping I can figure this out with ArcPy 😕

Re: View number of edits made in SDE.Default

VinceAngelo — Mon, 17 Apr 2017 13:56:02 GMT

Any SQL query solution can be implemented in ArcPy through an arcpy.ArcSDESQLExecute cursor.

Re: View number of edits made in SDE.Default

Lake_Worth_BeachAdmin — Wed, 19 Apr 2017 15:47:17 GMT

Thank you Vince, this is what I am looking for. However my knowledge of SQL is somewhat dim. Basically I would just use the ArcSDESQLEXECUTE cursor, point to my SDE path connection and then supply a SQL query for all table edits (changes, adds, deletes).

The SQL part is going to be my issue. Thanks for info!

Re: View number of edits made in SDE.Default

Lake_Worth_BeachAdmin — Wed, 19 Apr 2017 17:44:50 GMT

Thanks Rex, I see this only queires a specific feature class (table) is there an option for the entire Databse itself (SDE) or do I need to create a list of the tables and loop through them all?

Re: View number of edits made in SDE.Default

RexRobichaux2 — Wed, 19 Apr 2017 17:52:56 GMT

Sure thing Joe. Unfortunately, I believe there is no way to quickly see all edits currently contained within or referenced by a version because when versioned edits are made in ArcGIS- they are made in and stored to corresponding feature class delta tables until the (database is compressed) and the queried through a series of versioning-related tables.

I believe you will have to create queries such as the two provided above for every feature class that undergoes versioned editing and therefore could potentially have versioned edits residing within their corresponding Adds and Deletes tables that are referenced by the default version. This can be found from SDE_table_registry table by finding the registration_id value for each versioned, edited feature class. Perhaps Vince could confirm this but that is the only way I could foresee you obtaining all edits currently referenced by the Default version. Hope this helps!

Re: View number of edits made in SDE.Default

VinceAngelo — Thu, 20 Apr 2017 13:53:41 GMT

Correct. Individual edits associated with a state only exist in the An and Dn tables associated with each table. To find all edits associated with all tables participating in that state would require iterating all versioned tables.

- V

Re: View number of edits made in SDE.Default

VinceAngelo — Thu, 20 Apr 2017 13:56:05 GMT

[This got detached from the thread stream by auto-save; pasted back in where it belongs]

Rex gave you two SQL statements, though the second one has a typo ('Updated features' instead of 'Deleted features'). Working with the two of them you should be able to synthesize the distinctions between inserts and updates, and deletes and updates.

- V