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.
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.
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.
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
- Log in to a DB2 client application or execute a SQL PL routine to call the next_row_id procedure.
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
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)) );