topic Re: Managing Layers and Indexes using PL/SQL in Data Management Questions

Managing Layers and Indexes using PL/SQL

PaulAustin — Wed, 30 May 2012 17:50:03 GMT

I am trying to setup new features classes using PL/SQL only (no manual steps or Python).

There are several reasons for this.

Run the install from the command line on unix, no windows requirement

Completely automate it so that someone can't screw it up

Allow users to perform specific functions that they wouldn't have permissions to do

What is the PL/SQL equivalent of the following?

sdelayer -o register -l

sdelayer -o delete -l

In ArcCatalog right clicking on a feature class and registering it with a geodatabase

In ArcCatalog layer properties Index tab, recalculating an index. At the moment this can only be done by the owner of the tables. For corporate reasons we don't have that password. With PL/SQL the procedures are run as the user that created them (the owner of the tables). Then I could grant permission to specific users to update them. Having everything done by the owner can be dangerous as that account should have restricted access as it can delete everything.

Cheers,
Paul

Re: Managing Layers and Indexes using PL/SQL

VinceAngelo — Wed, 30 May 2012 18:16:22 GMT

1) There is no SQL equivalent to 'sdelayer -o register'
2) You should not ever need to use 'sdelayer -o delete'
3) There is no SQL equivalent to "Register with geodatabase" (there isn't an ArcSDE
binary equivalent either).
4) Index creation/recreation is the only process which can be done with SQL (assuming
ST_GEOMETRY or native storage).

- V

Re: Managing Layers and Indexes using PL/SQL

PaulAustin — Wed, 30 May 2012 19:35:29 GMT

Delete is required for testing purposes to reset the database between tests.

I see there is a layers_util.update_layer_grids to set the grids but there doesn't seem to be an equivalent to CalculateDefaultGridIndex to calculate the grid sizes.

Re: Managing Layers and Indexes using PL/SQL

VinceAngelo — Thu, 31 May 2012 00:08:07 GMT

You should not 'delete' (sdelayer or sdetable) any layer that has been registered with
the geodatabase. Only an ArcGIS delete can clean up all the metadata tables.

An 'sdelayer -o delete' before a 'sdetable -o delete' is the most common command-line
scripting mistake -- the SE_table_delete() function [also used by ArcGIS] cleans up all
ArcSDE metadata, without the need to populate NULLs in all geometry columns (very slow
in large tables).

I haven't ever used anything other than the spatial index grid values I have calculated,
but I haven't ever seen a significant performance difference for grid sizes that weren't
completely wacked (ie all features in one cell or one cell per feature).

The best performance optimization step I've seen is to use centimeter or decimeter scale
coordinate references on global data (especially true with polygon and line data). Since
you're using SQL, you have the chance to override the submillimeter default that increases
table size and slows loading and query performance.

- V