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 object ID (row ID) column. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the object ID.

Tip:

If the data is versioned, you must use a multiversioned view to edit it. See Editing versioned data in DB2 using multiversioned views and SQL for information on how to do this.

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

Steps:

1. Log in to the database from a 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. Query the TABLE_REGISTRY table to find the registration ID and owner of the table into which you want to insert a row.

   In this example, the registration ID and owner name for the factories table is returned.

   SELECT REGISTRATION_ID,OWNER,TABLE_NAME
   FROM SDE.TABLE_REGISTRY
   WHERE TABLE_NAME = 'FACTORIES';
   
   REGISTRATION_ID   OWNER     TABLE_NAME
        5             gis       factories

3. Log in to a DB2 client application or execute a SQL PL routine to call the next_row_id procedure.
4. Use the registration ID and owner name returned from the previous SELECT statement (gis and 5) when you call the next_row_id routine to obtain the next available row ID. This routine is stored in the schema of the sde user.

   In this example, GIS is the table owner, 5 is the registration ID of the table, and the three question marks indicate the three parameters that are returned: ROWID, MSGCODE, and MESSAGE.

   CALL SDE.next_row_id('GIS',5,?,?,?)
   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

5. Go back to the SQL interface to insert a record into the table.

   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))
   );