How To: Register DBMS Tables to an ArcStorm Database

Procedure

External DBMS (ex. Oracle, Informix, and Sybase) tables must be registered with an ArcStorm database if the tables are going to be updated within the context of an ArcStorm transaction. Nonregistered tables can still be used for display and query purposes with ArcStorm spatial features, but they can't be used in an ArcStorm transaction. How do I register a DBMS table?

Answer:

Use the REGISTERDBMS command from SchemaEdit to accomplish this. Below is an outline of the procedure needed to register Oracle tables to ArcStorm databases in various scenarios:

1. First, create the necessary ArcStorm Oracle system tables:

se databasename
setconnectinfo oracle system/xxxx@T:sanawar:oracle7 /*system is the oracle
dba,
or whomever is granted enough
permissions to do this.
createsystables oracle

2. Then grant the ArcStorm/Oracle users privileges for the ArcStorm/Oracle system tables. In the example, the Oracle user is 'snoopy':

grant select, update, insert, delete on as__trans to snoopy;
grant select, update, insert, delete on as__scrtab to snoopy;
grant select, insert, update on as__tablesxdb to snoopy;
grant select, insert, update on as__tables to snoopy;
grant select, insert, update, delete on as__archives to snoopy;
grant select, update, insert, delete on as__hstseg to snoopy;
grant select on as__tables_seq to snoopy;
grant select on as__archives_seq to snoopy;
grant select, update,insert, delete on as__worktables to snoopy;

3. Then create public synonyms for all the ArcStorm/Oracle system tables through
SQLPLUS:

create public synonym as__tables for system.as__tables;
create public synonym as__tablesxdb for system.as__tablesxdb;
create public synonym as__trans for system.as__trans;
create public synonym as__scrtab for system.as__scrtab;
create public synonym as__tables_seq for system.as__tables_seq;
create public synonym as__archives_seq for system.as__archives_seq;
create public synonym as__archives for system.as__archives;
create public synonym as__hstseg for system.as__hstseg;
create public synonym as__worktables for system.as__worktables;

4. Using SQLPLUS, create a public synonym for the Oracle table you want to register to ArcStorm. In the example below, 'snoopy' is going to be the Oracle user registering the Oracle table called 'yasstates'.

create public synonym ysyn for snoopy.yasstates

5. Before we register the DBMS tables with ArcStorm database, the transactional columns must be added to the DBMS tables. To add the transactional tables, either the DBMS's command tool can be used (such as Oracle's SQL*PLUS) or ARC: DBMSEXECUTE. Here's an ORACLE example:

Arc: DBMSEXECUTE ORACLE
Enter DBMS specific command. (Enter "END" or a blank line when finished.)
>: ALTER TABLE YASSTATES ADD (
>: create__id VARCHAR2(15) DEFAULT 0 NOT NULL,
>: lock__id VARCHAR2(15) DEFAULT 0 NOT NULL,
>: phase__id INTEGER DEFAULT 0 NOT NULL
>: )
>: ;
Are you done entering the statement (Y/N)? y
Do you wish to use this statement (Y/N)? y
DBMSEXECUTE successful

6. Now that all the ArcStorm/Oracle system tables and public synonyms are created, go into SchemaEdit and register the Oracle tables:

SchemaEdit: setconnectinfo oracle snoopy/snoopy@T:sanawar:oracle7
SchemaEdit: registerdbms oracle yasstates.
Started DBA user connection.
Table YASSTATES registered with no history.

At this point the ArcStorm/Oracle system tables are owned by Oracle user 'system', and Oracle table 'yasstates' is owned by Oracle user 'snoopy'. The REGISTERDBMS was successful.

7. Now lets say that you have Oracle user that wants to register tables owned
by another Oracle user. You will have to do use the public synonym that you created for that table. In our example, 'ysyn':

SchemaEdit: registerdbms oracle ysyn
Table YASSTATES registered with no history.

8. Now you can use the LISTDBMSTABLES to see the tables that are registered with the ArcStorm database.

SchemaEdit: listdbmstables
Usage: LISTDBMSTABLES
SchemaEdit: listdbmstables oracle
Executing SQL Function/Command...

ASDBI Server Started...
asdbi: RPC Program Number 1073741824
Table
-----
YASSTATES
SchemaEdit:

Related Documents:

See the following on-line ArcDoc references for more information:

ArcStorm Database management->ArcStorm->Creating an ArcStorm database->Incorporating attribute tables.

ArcStorm Database management->ArcStorm->Informix, Ingres, Oracle, and Sybase specific notes.

CREATEYSTABLES, REGISTERDBMS, SETCONNECTINFO command references.

Article ID:000001366

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Register DBMS tables to an ArcStorm database

Procedure

Discover more on this topic