ArcSDE Administration Command Reference

ArcSDE Administration Command Reference

sdetable

This command administers business tables and their data.

Usage syntax

sdetable -o create -t <table> -d <column_definition>
       [-k <config_keyword>] [-i <service>]
       [-s <server_name>] [-D <database>]
        -u <DB_User_name> [-p <DB_User_password>] [-q]

sdetable -o delete -t <table>
       [-i <service>] [-s <server_name>] [-D <database>]
        -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o truncate -t <table>
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o list -t <table> -c <column> -v <column_value>
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o describe -t <table> [-i <service>] [-s <server_name>]
        [-D <database>] -u <DB_User_name> [-p <DB_User_password>] [-q]

sdetable -o describe_long -t <table> [-i <service>] [-s <server_name>]
       [-D <database>] -u <DB_User_name> [-p <DB_User_password>] [-q]

sdetable -o create_index -t <table> -n <index>
        -c <column> [-k <config_keyword>]
        [-Q] [-i <service>] [-s <server_name>] [-D <database>]
        -u <DB_User_name> [-p <DB_User_password>] [-q]

sdetable -o delete_index -n <index> [-t <table>]
       [-i <service>] [-s <server_name>] [-D <database>]
        -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o rename -t <table> -T <new_name>
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o {grant | revoke} -t <table> -U <user>
       -A <SELECT,UPDATE,INSERT,DELETE>
       [-i <service>] [-s <server_name>] [-D <database>]
       -u <DB_User_name> [-p <DB_User_password>] [-I] [-q]

sdetable -o register -t <table>
        [{-c <row_id_column> -C {SDE|USER} } | {-C NONE} ]
        [-L {OFF | ON}] [-M <minimum_id>] [-S <table_description>]
        [-V {SINGLE | MULTI}] [-k <config_keyword>]
        [-H {VISIBLE | HIDDEN}] [-R {MANY | SINGLE}]
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-q]

sdetable -o unregister -t <table> [-F]
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o alter_reg -t <table>
        [{-c <row_id_column> -C {SDE|USER} } | {-C NONE} ]
        [-L {OFF | ON}] [-M <minimum_id>] [-S <table_description>]
        [-V {SINGLE | MULTI}] [-F] [-k <config_keyword>]
        [-H {VISIBLE | HIDDEN}] [-R {MANY | SINGLE}]
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o describe_reg [{-U <user> | -t <table>}] [-i <service>]
       [-s <server_name>] [-D <database>]
        -u <DB_User_name> [-p <DB_User_password>] [-q]

sdetable -o create_view -T <view_name> -t <table1,table2...tablen>
        -c <table_col1,table_col2...table_coln>
        [-a <view_col1,view_col2...view_coln>] [-w <"where_clause">]
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o create_mv_view -T <view_name> -t <table_name>
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o delete_mv_view -t <table_name>
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o alter_column -t <table> -c <column>
       [-x {<ALLOW_NULLS | DISALLOW_NULLS}]
       [-z <sde_column_type>] [-S <column_description>]
       [-i <service>] [-s <server_name>] [-D <database>]
        -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -o add_uuid_column -t <table> -c <column> -u <DB_User_name>
        [-D <database>] [-p <DB_User_password>]
        [-i <service>] [-s <server_name>] [-q]

sdetable -o populate_uuid_column -t <table> -c <column> -u <DB_User_name>
        [-D <database>] [-p <DB_User_password>]
        [-i <service>] [-s <server_name>] [-q]

sdetable -o rebuild_index -t <table>
        [-x {RASTER | SPATIAL | VERSIONED | XML | <ALL>}]
        [-i <service>] [-s <server_name>] [-D <database>]
         -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

sdetable -h

sdetable -?

sdetable -o update_dbms_stats has database specific usage:

Oracle:

sdetable -o update_dbms_stats -t <table_name> [ -K <keyword>]
-m <{estimate | compute}>
[-n <{ALL | <index_name>}>] [-i <service>] [-s <server_name>][-D <database>]
[-u <DB_User_name>] [-p <DB_User_password>] [-N] [-q]

SQL Server:

sdetable -o update_dbms_stats -t <table_name> [ -K <keyword>]
-m <"{with fullscan | with sample %% percent} [index]"> [-i <service>]
[-s <server_name>] [-D <database>] [-u <DB_User_name>]
[-p <DB_User_password>] [-N] [-q]

Informix:

sdetable -o update_dbms_stats -t <table_name> [ -K <keyword>]
-m <{low | medium | high}>
[-i <service>] [-s <server_name>][-D <database>] [-u <DB_User_name>]
[-p <DB_User_password] [-N] [-q]

DB2:

sdetable -o update_dbms_stats -t <table_name> [-K <keyword>]
[-n {ALL | <index_name>}] [-m <{dbms options}>]
[-i <service>] [-s <server_name>] [-D <database>]
-u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

Operations

add_uuid_column	Adds a universal unique column to the table.
alter_column	Alters the definition of a column.
alter_reg	Modifies a registration entry such as converting the table from single to multi versioned and vice versa
create	Creates a new table
create_mv_view	Creates an intelligent multiversioned view.
create_index	Creates an index on a table
create_view	Creates a DBMS view
delete	Deletes a table. Can be used to completely delete a layer
delete_mv_view	Deletes an intelligent multiversioned view
delete_index	Deletes an index on a table
describe	Displays a table definition
describe_long	Displays the table definition in detail
describe_reg	Displays table registration entries
grant	Grants access to a table for a user
list	Lists column data for a given field value
populate_uuid_column	Populates the universal unique ID column
rebuild_index	Rebuilds the indexes of the specified objects.
register	Registers a table in the ArcSDE table registry
rename	Renames a table
revoke	Removes access to a table for a user
truncate	Deletes all records from a table
unregister	Removes a table from the ArcSDE table registry
update_dbms_stats	Updates RDBMS table and/or index statistics

Options

-a	A comma-separated list of a view column names. Use if you want a view to have different column names
-A	Type of access: SELECT, UPDATE, INSERT, or DELETE
-c	For the alter_reg or the registration operation specifies the row ID column name. This option requires using "-C SDE" or "-C USER". If using the create_view operation, use a comma-separated list of column names. For the add_uuid_column and the populate_uuid_column operations, specifies the uuid column to be added or populated.
-C	Row ID column type: SDE SDE-maintained row_id column. Tables that are registered with the geodatabase must be SDE maintained. Your application must not alter the values of the row_id column. USER User-maintained row_id column. Your application will maintain the row-id. NONE No row_id column. The table_registry does not have a reference to a row_id column. You can't use the "-c" option with this choice.
-d	Column definition
-D	Database or data source name. Not supported by all DBMSs.
-F	Force a multi-versioned table with edits to be converted to a non-versioned table, with loss of edits. The records of stored in the delta tables are lost when they are dropped
-h	Prints usage and options
-H	Registers the table as either visible or hidden
-i	ArcSDE service name
-I	Inherit grant privilege. The "grant option" is included with the granted privilege. For example, if user A grants user B update privileges on a table, the -I option indicates that user A also wants to grant user B the ability to grant other users update privileges on a particular table.
-k	Configuration keyword
-K	Schema object keyword: a or A<registration_ID> table ("adds" ) b or B<business table d or D<Registration_ID> table ("deletes" ) f or F<Layer_id> table ("feature")
-L	Enable (on) or disable (off) a ROW_LOCK on a registered table
-m	DBMS update statistics mode.
-M	Minimum row ID
-n	Index name
-N	No verification is performedthe operation begins immediately after being invoked
-o	Operation
-p	ArcSDE user RDBMS password
-q	Quiet; all titles and warnings are suppressed
-Q	Index is unique
-R	[{SINGLE | MANY}] - Sets the row id allocation method for an sde-maintained row id column. Set the method to SINGLE if records are infrequently inserted into the table. Metadata tables are examples of these types of tables. Setting the method to SINGLE causes the session to get a single ID value each time a record is inserted into the table. Setting the method to MANY, the default, causes the session to fetch a block of ID values each time an insert occurs. The next block is not fetched until enough inserts have occurred to exhaust the current block of IDs. Use MANY for regular business tables which have many rows inserted during a session. This option is currently only implemented for Oracle, to avoid gaps in the metadata tables sde-maintained row id columns.
-s	ArcSDE server hostname (default: localhost)
-S	For the registration or alter_reg operations specifies the registration description string. For the alter_column operation specifies the column description.
-t	Table name. If you do not own the table, qualify the table name as "owner.table". If using the create_view operation, use a comma- separated list of table names.
-T	New table name
-u	ArcSDE user RDBMS username
-U	ArcSDE user RDBMS usernamethe user to grant or revoke table access to OR who owns a table
-v	Column value
-V	Specify if the table is single or multiversioned
-w	SQL where clause
-x	Allow or disallow NULLS on a column
-z	Specifies the column data type.
-?	Prints usage and options (use "-\?" on C shell)

Discussion

To create a business table, supply the table name and field specifications. The field specification uses standard SQL syntax to specify a field list, so you cannot use any SQL reserved words as field names. The field specification may use any data type defined by ANSI SQL89. The field specification of sdetable accepts ArcSDE standard data types which it maps to DBMS native data types. The standard ArcSDE data types are:

smallint(n) where: 0 < n <= 4
int16
int32
int64
float32
float64
integer(n) where: 4 < n <= 10
float(n,m) where: 0 < n <= 6, 0 < m <= DBMS limit
double(n,m) where: 6 < n <= DBMS limit, 0 < m < DBMS limit
string(n) where: 0 < n <= DBMS limit
blob
clob
uuid
nstring
nclob
date

In the case of ArcSDE for Informix and ArcSDE for DB2 the spatial data types are also accepted. Creating business table with a spatial type does not create a feature class. To create a feature class from an empty business table containing a spatial type, use the sdelayer -o add command. If the table contains records, use the sdelayer -o register command to register the spatial table as a feature class.

st_point
st_curve
st_linestring
st_polygon
st_multipoint
st_multicurve
st_multilinestring
st_multipolygon
st_multisurface
st_surface
st_geometry
st_geometrycollection

This command creates a business table:

$ sdetable -o create -t victoria -d "name string(20), tot_pop integer(9)" -k vict -u av -p mo -s ultra -i esri_80

The resulting table contains two columns: a string column called "name" and an integer column called "tot_pop".

Tables are created as single versioned. Use the alter_reg operation to reregister them as multiversioned if you wish to edit the table with the ArcGIS desktop. A rowid column that will store the unique object ID, to the edited rows must be either added to the table or an existing one must be named. The rowid column must be managed by the sde user. The table victoria is reregistered as multiversioned. The column objectid is added as the rowid column and is populated by ArcSDE with unique id values.

$ sdetable -o alter_reg -t victoria -c objectid -C sde -V multi -u av -p mo -N

The rename operation can be used to change the name of a table. Not all DBMSs support table renaming.

$ sdetable -o rename -t world -T world2000 -u av -p mo

To delete a business table, use the sdetable "delete" operation. The associated feature class (layer), if one exists, is deleted as well.

$ sdetable -o delete -t victoria -u av -p mo -s ultra -i esri_40

Truncating deletes all the records of the business table and the associated feature class (layer), leaving the definition of the table and feature class (layer) intact.

$ sdetable -o truncate -t victoria -u av -p mo -s ultra -i esri_40

To change a business table's specification, use the ALTER statement from the DBMS's SQL utility.

To list the business table definition, use the "describe" operation. It lists the column name and the related data type and length in bytes for each column in the business table.

$ sdetable -o describe -t victoria -u av -p mo -i esri_40

To list the business table definition in column detail, use the "describe_long" operation.
It lists following properties of each column:

Column Owner
Column Table
Column Name
SDE Column Type
Column Size
Decimal Digits
Null Allowed?
Layer ID

$ sdetable -o describe_long -t victoria -u av -p mo -i esri_40

Use the "alter_column" operation to alter a columns definition. You can change the NULL constraint or even the data type if the DBMS will allow it. You cannot alter the definition of a column  that has been registered as SDE maintained.

$ sdetable -o alter_column -t victoria -c parcels -x ALLOW_NULLS -u av -p mo -N -q

To list rows of a business table, you must specify the table, a column name, and a value of the specified column. An implicit query is performed on the table to fetch and display the rows that have a column equal to the value. The command below lists the rows of the business table whose parcels column value equals 10:

$ sdetable -o list -t victoria -c parcels -v 10 -u av p mo -i esri_40

The "create_index" operation creates an index on a business table column. This example creates an index on column "parcel_no".

$ sdetable -o create_index -t victoria -c parcel_no -n index1 -u av -p mo -i esri_40

You can create aggregate indexes by specifying a comma-separated list of columns. The example creates an aggregate index on the parcel_no and zone_no columns.

$ sdetable -o create_index -t victoria -c parcel_no,zone_no -n index2 -u av -p mo

To remove an index from an attribute table, specify the index name.

The following command deletes the index "index1" from the business table:

$ sdetable -o delete_index -n index1 -u av -p mo

Not all DBMSs maintain unique index names, in which case you must qualify the index name with the table name (for example, victoria.index1).

To add a universally unique ID column to a business table use the add_uuid_column operation.

$ sdetable -o add_uuid_column -t victoria -c parceluuid -u av -p mo

Populate a universally unique ID column with the populate_uuid_column operation.

$ sdetable -o populate_uuid_column -t victoria -c parceluuid -u av -p mo

You can also use sdetable to manage registered tables. Tables created with the sdetable create operation are automatically registered as single versioned. However tables created from the SQL interface must be registered to be accessable from geodatabase. To register a feature class (layer) called "world":

$ sdetable -o register -t world -u av -p mo

Just as tables are registered they can also be unregistered using the unregister operation.

$ sdetable -o unregister -t world -u av -p mo

However, tables cannot be unregistered if they have been registered as multiversioned, they are part of a geodatabase object, or they have a spatial column. In all cases the dependencies must be removed first or you must use the -F option..

To manually remove the dependencies, unregister the table from geodatabase using ArcCatalog if it has been registered with the geodatabase and remove the spatial column using the sdelayer -o delete operation, if it has one.

Alternatively, you can use the -F option to force the unregistering of the table. However all edits stored in the delta tables are lost. The table will not be unregistered with the geodatabase.

$ sdelayer -o delete -l world,feature -u av -p mo

You must alter the registration with the alter_reg operation to reregister the table as single-version, if it was registered as multiversion, before you can unregister the table.

$ sdetable -o alter_reg -t world -V single -u av -p mo

You can specify a particular column to use for the unique row ID column with "-c". If you do this, you also need to use "-C" to specify whether the row ID column is ArcSDE or user managed. Tables that are registered with the geodatabase must be registered with an ArcSDE managed ROWID column. By default the tables are registered with the -C NONE option, if the -c option is not specified. If the specified row id column does not exist when the table is registered ArcSDE will add it.

$ sdetable -o register -t world -c row_id -C SDE -V multi -u av -p mo

The registration information can be described by the ArcSDE administrator. The administrator can describe the registration of one or all of the tables owned by a particular user. In this example the sde administrator described the registration of the world table.

$ sdetable -o describe_reg -t world -u sde -p sde

ArcSDE 8.1 Tue Oct 20 17:00:36 2000
Layer Administration Utility
------------------------------------------------------
Table Owner : AV
Table Name : WORLD
Registration Id : 109
Row ID Column : OBJECTID
Row ID Column Type : SDE Maintained
Row Lock : Not Enabled
Minimum Row ID : 1
Dependent Objects : Layer, Multiversioned Table
Dependent Object Names : F52, S52, A93, D93
Registration Date : Thu Sep 17 11:32:19 2000
Config. Keyword : DEFAULTS
User Privileges : SELECT, UPDATE, INSERT, DELETE
Visibility : Visible

To describe the registrations of all of the tables owned by the av user the sde administrator substitutes the -U av for the -t world option.

You can unregister or alter the registration entry of a table with the "unregister" and "alter_reg" operations, respectively.

The "alter_reg" operation is used to turn row locking on, reset the minimum ID to 1,000 , and change the table to hidden. Hidden tables do not appear in lists unless they are specifically requested.

$ sdetable -o alter_reg -t world -L on -M 1000 -H hidden -u av -p mo

Views can be created with the "create_view" operation. The views created are stored in your DBMS just like any other view you would create with an SQL editor. You may create views that join several tables together up to the limit imposed by your DBMS. However, just as ArcSDE imposes the limit of one spatial column per table, that limit also applies to views created by the "create_view" operation.

Note when a view is created from multiple base tables, and as a result more than one rowid column exists, ArcSDE uses the first rowid column in the attribute list. It is important that this row id column does not contain duplicate values of NULL values as a result of the query used to create the view. If it does, querying the view will become unpredictable.

$ sdetable -o create_view -T world_view -t "world,countries" -c "world.population, world.gnp, countries.name, countries.feature" -a "population, gross_national_product, name,feature" -w "world.cname = countries.name" -u av -p mo

Use the delete operation to delete a view created by the "create_view" operation. In this case the delete operation merely removes the view. The delete operation does not drop the underlying business tables which the view was accessing.

To create views that can be used through ArcGIS desktop applications, you should do the following:

- use sdetable -o create_view to create the view, being sure to include the OBJECTID field from the feature class in the view's fields

- use sdetable -o alter_reg to set the OBJECTID column as a user maintained row_id column

$ sdetable -o delete -t world_view -u av -p mo

The "update_dbms_stats" operation can be used to update the DBMS statistics of a business table, the index of a business table, or all the indexes of a business table. All DBMSs supported by ArcSDE maintain statistics about the business tables and indexes. Following a lot of changes to a table or index, the statistics for that data object become stale. As a rule of thumb, if more than 20 percent of the table has changed, you should use the "update_dbms_stats" operation on that table.

The statistics of all a table's indexes are automatically updated whenever you update the statistics of the table.

The mode and index options are specific to each DBMS. Each of the DBMS implementations are listed below.

Oracle:

To update the statistics of a table and all its indexes, do not specify the "-n" option.

$ sdetable -o update_dbms_stats -t world -m compute -u av -p mo

To update the statistics of all the indexes but not the table and its indexes, specify the "-n all" option.

$ sdetable -o update_dbms_stats -t world -m estimate -n all -u av -p mo

To update the statistics of an individual index, specify the -n option with the name of an index.

$ sdetable -o update_dbms_stats -t world -m estimate -n world_ix -u av -p mo

The mode can be set to either compute of estimate. When the statistics are computed all of the rows of the table are analyzed. When the estimate mode is used Oracle analyzes a sample of the rows of the table.

SqlServer:

The mode can be set to either either a full scan which would analyze the entire table or a partial scan. If an index is specified only the statistics for the index are updated.

In this example, all of the rows of the world table are analyzed.

$ sdetable -o update_dbms_stats -t world -m "with fullscan" -u av -p mo

In the following example a partial scan is performed on an index of the world table.

$ sdetable -o update_dbms_stats -t world -m "world_ix with sample 30 percent" -u av -p mo

Informix:

The mode can be set to either low medium or high. By default the mode is set to low.

In this example the statistics of the world table are updated with a mode of high.

$ sdetable -o update_dbms_stats -t world -m high -u av -p mo

All RDBMS:

The "-K" option allows you to specify which ArcSDE schema object to analyze. One or any combination of the keyword values may be used. For example:

$ sdetable -o update_dbms_stats -K ad -u av -p mo

This command updates the DBMS statistics for the A<Registration_id> ("adds") and the D<Registration_ID> ("deletes") tables.

If no keyword value is given, the statistics for the business table and associated support tables will be updated.

The optional multiversioned view is created with the "create_mv_view" operation. This views can also be created by calling the ArcSDE C API function SE_table_create_mv_view.

Once you have created the view you can access the multiversioned business table from within the DBMS SQL interface. These views provide direct SQL access to a multiversioned business table. If created on a feature class, a multiversion view will appear as a table in the geodatabase.

$ sdetable -o create_mv_view -T world_imv_view -t world -u av -p mo

Using the DBMS SQL interface set the current version by executing the stored procedure sde.version_util.set_current_version.

For example, if the current version of the database that you wished to access were called 'working' set the database to that version during the SQL session as follows:

SQL> execute sde.version_util.set_current_version('working');

After the desired version is set, access the multiversioned view as you would any other business table.

For example, to select records

SQL> select * from world_mv_view;

or insert records

SQL> insert into world_mv_view (county_name, population) values ('Canada', 30000000);

The delete_mv_view operation drops a multiversioned view.

$ sdetable -o delete_mv_view -t world -u av -p mo

The view can also be dropped using the ArcSDE C API function SE_table_drop_mv_view.

For more information regarding the creation and access of multversioned views consult Native SQL Access.