Inserting a value into a global ID or GUID column in DB2 using SQL

Global ID and GUID data types store registry-style strings consisting of 36 characters enclosed in curly brackets. These strings uniquely identify a feature or table row within a geodatabase and across geodatabases. Global IDs are used to maintain uniqueness of records in one-way and two-way geodatabase replicas and nonversioned data used in offline maps. GUIDs can be added to any table.

If the table you want to edit participates in replication or offline maps or contains a GUID column, you must insert a unique value to the global ID or GUID column when you insert a new record to the table using SQL. To do this, you can use the sde.next_globalid function.

When editing a nonversioned table that is not enabled for archiving, use the function to insert the ID directly to the table. When editing a nonversioned table that is enabled for archiving, use the function to insert the ID to the archive view of the table. When editing a versioned table, use the function to insert a value to the versioned view of the table.

Steps:
  1. Log in to the database from an SQL prompt as a user with privileges to edit the table.

    If you are editing through a versioned or archive view, the user must also have privileges to edit the view.

  2. When inserting a record, use the sde.next_globalid function to insert the next global ID value. Since this is a geodatabase table, it also contains an ObjectID. Use the sde.next_rowid function to insert a value to the ObjectID field as well.
    --Get next ObjectID
    CALL sde.next_rowid('JASON', 'ASSETS', ?, ?, ?);
    
    Value of output parameters
    
    Parameter Name :   O_ROWID
    Parameter Value :  49999
    
    Parameter Name :   O_MSGCODE
    Parameter Value :  0
    
    Parameter Name :   O_MESSAGE
    Parameter Value :  Procedure successfully completed.
    
    Return Status = 1
    
    --Insert record
    INSERT INTO jason.assets (objectid, asset_id, globalid) 
     VALUES (49999, 57, sde.next_globalid);
    
  3. When you have completed your edits, commit them to the database.

Related Topics

6/19/2015