Editing nonversioned geodatabase data in DB2 using SQL

You can use SQL to update, insert data into, and delete data from nonversioned tables in the geodatabase if they do not participate in geodatabase behavior. See What type of data can be edited using SQL? for information on the types of data and geodatabase behavior you cannot edit with SQL.

All data that is registered with the geodatabase has a system-maintained, unique, not-null ObjectID (Row ID) field. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the ObjectID.

This set of instructions describes updating one row at a time. You would most likely write a routine or client program to retrieve ObjectIDs and update your data.

Steps:

1. Log in to the database from an SQL editor such as the DB2 Call Level Interface.

   Be sure to log in to the database as a user who has permission to edit the data.

2. Execute an SQL statement similar to the following to call the Next_RowID stored procedure.

   The Next_RowID stored procedure takes the table owner and table name as input to return the next ObjectID for the table.

   In this example, GIS is the table owner, FACTORIES is the table name, and the three question marks indicate the three parameters that are returned: ROWID, MSGCODE, and MESSAGE.

   CALL SDE.next_rowid('GIS','FACTORIES',?,?,?)
   Value of output parameters
   -----------------------------
   
   Parameter Name : O_ROWID
   Parameter Value : 18
   
   Parameter Name : O_MSGCODE
   Parameter Value : 0
   
   Parameter Name : O_MESSAGE
   Parameter Value : Procedure successfully
   completed.
   
   Return Status = 1
   

3. Insert a record into the table using the O_ROWID value returned from the previous call statement.

   INSERT INTO FACTORIES
   (OBJECTID,NAME,SHAPE)
   VALUES(
   18,
   'megafactory',
   db2gse.ST_PolyFromText('POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )',
   db2gse.coordref()..srid(101))
   );
   

4. You can continue editing or, if you are done editing, commit your edits to the database.