Truncate Water Junction Object Table fails

[MarcoMattioli]

Hi everyone,

I need to clear the Water Junction Object table from time to time. As far as I know there are two GP Tools to accomplish this task: the Truncate Table and the Delete Row. The Delete Row is way too slow: in my case it takes about one hour to complete.

I used the Truncate Table and I saw that it takes few seconds to clear other standalone tables, so I would like to use it even for the Water Junction Object one.

I am aware of the limitations of this tool, so I stopped the services involving the table, unregistered the table as versioned, disabled the attachments and even excluded it from editing, but when I try to run the tool, it fails with the error: ERROR 160592: Truncation not allowed while editing.

I tried, without success, to run the tool both in ArcGIS Pro and in a Python script.

Is there something else that I need to check to make it work?

In another thread I read that sometimes, if the table is involved in an attribute rule, the tool may fail with this same error. I don't think that this is the case, because the only rules in my UN are the standard ones preloaded in the Asset Package. Anyway, just to be sure, Is there a way to check which rules involve a given table?

Thanks,

Marco

[RobertKrisher]

1) Can you briefly explain why you want to do this? Since there may be a better approach or concerns with this approach, depending on your situation.

2) Do any of these feature classes have geodatabase relationships?

[MarcoMattioli]

Hi Robert,

thanks for the prompt answer.

1)  I only use the Water Junction Object table to store clients data. Every row of this table is in a Junction-Junction Connectivity Association with a Water Device geometry. Every night the Water Junction Object table is aligned with an Oracle table. To reduce execution time, only the differences between the two tables are processed, so that only about 100 rows are inserted/modified/removed from the Water Junction Object table. In case of errors during the alignment, to avoid data loss, we clear the table and reprocess the whole amount of data. I would like to use the Truncate Table to minimize the offline time because, ideally, it should be always available. 

2) No, the only relationship class was the one created for the attachments.

[gis_KIWI4]

@MarcoMattioli - How does your process handle the junction Junction relationships?
The relationships are stored in the associations table so if you are adding/removing/changing the Junction Object the associations table needs to be updated as well.

I agree with @RobertKrisher comment that this customer data is better managed outside the UN.
If you have a chance in the future to move it out then a separate table.

Also wondering, if you want to keep the existing process then is there a way to achieve the same result without Truncating the whole table? Compare the two tables (all the attributes). Determine the updates/deletes/inserts and process them in blocks. 

PS - if you want to see if attribute rules against a certain table, click on the table and right click into the Attribute Rule View.

Example - 

[MarcoMattioli]

Thanks for the suggestion: I am now implementing the solution you proposed.

[RobertKrisher · Accepted solution]

1 - I assume you're synchronizing customer data to this table? We recommend that you avoid any kind of operation that would require you to completely truncate and reload network data. We usually see customers putting the link to the customer data in the network, but storing the customer details (which shouldn't be editable in the GIS) in separate, un-versioned tables that can be freely modified/truncated by an external process.

2 - The system only lets you run truncate table on simple features. Because this table has a controller dataset (the utility network) it is not considered simple.

3  - I was surprised to see we let you unregister the table as versioned, this puts the system in an inconsistent state. That is to say that the system tables may still contain historical or versioned data for this table, even though the table itself has removed that information.

[MarcoMattioli]

Hi Robert,

1) I will see if we are able to move them outside the network, thanks for the suggestion

2) Thanks for the explanation: I didn't know that it is considered as controlled

3) I suspected that this approach could mess up something...