English

How To: Populate an ArcSDE maintained row ID column when inserting a new ST_Geometry feature with SQL in DB2

Summary

When data is stored in ArcSDE for DB2, it is possible to insert new simple features using Structured Query Language (SQL). If the layer being updated has an ArcSDE maintained row ID (also called an object ID) and nonversioned data is being edited, use the DDL script provided here to create the NEXT_ROW_ID stored procedure. Use this stored procedure to generate a new, unique value for the row ID that can be used in SQL INSERT statements.

Procedure

Use the sample script provided here to create the NEXT_ROW_ID stored procedure. Use the stored procedure to generate a unique row ID value to be used in SQL INSERT statements.

The NEXT_ROW_ID procedure takes two input parameters: I_OWNER_NAME and I_REGID. The first input parameter is the name of the user who owns the table to be edited. The second input parameter is the registration ID of the table to be edited, as recorded in the ArcSDE system table, SDE.TABLE_REGISTRY.

The procedure returns three output parameters: O_ROWID, O_MSGCODE, and O_MESSAGE.

  1. Copy and paste the following script into a file on a computer that can access the database.

    Code:
    CONNECT TO sde93 USER sde USING sde
    @

    DROP PROCEDURE SDE.NEXT_ROW_ID
    @

    CREATE PROCEDURE SDE.NEXT_ROW_ID ( IN I_OWNER_NAME VARCHAR(128),
    IN I_REGID INTEGER,
    OUT O_ROWID INTEGER,
    OUT O_MSGCODE INTEGER,
    OUT O_MESSAGE VARCHAR(1024) )
    LANGUAGE SQL
    P1: BEGIN
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE L_SQLCODE INTEGER DEFAULT 0;
    DECLARE L_SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE L_TABLE_NAME VARCHAR(128) DEFAULT NULL;
    DECLARE C_ID_TYPE INTEGER DEFAULT 2;
    DECLARE C_NUM_REQUESTED_IDS INTEGER DEFAULT 1;
    DECLARE L_NUM_OBTAINED_IDS INTEGER DEFAULT NULL;

    DECLARE SE_SUCCESS INTEGER DEFAULT 0;
    DECLARE SQLZ_DISCONNECT_PROC INTEGER DEFAULT 1;

    P2: BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND, SQLWARNING
    BEGIN
    GET DIAGNOSTICS EXCEPTION 1 O_MESSAGE = MESSAGE_TEXT;
    SELECT SQLCODE, SQLSTATE
    INTO L_SQLCODE, L_SQLSTATE
    FROM SYSIBM.SYSDUMMY1;
    SET O_MSGCODE = L_SQLCODE;
    END;

    SET O_ROWID = NULL;
    SET O_MSGCODE = SE_SUCCESS;
    SET O_MESSAGE = NULL;

    IF I_OWNER_NAME IS NULL OR LENGTH(LTRIM(RTRIM(I_OWNER_NAME)))=0 THEN
    SET O_MSGCODE = -20066 /* SE_INVALID_PARAM_VALUE (-66) */;
    SET O_MESSAGE = 'Invalid input parameter (I_OWNER_NAME).';
    LEAVE P2;
    END IF;

    IF I_REGID IS NULL THEN
    SET O_MSGCODE = -20066 /* SE_INVALID_PARAM_VALUE (-66) */;
    SET O_MESSAGE = 'Invalid input parameter (I_REGID).';
    LEAVE P2;
    END IF;

    SET L_TABLE_NAME = I_OWNER_NAME || '.I' || CHAR(I_REGID);

    CALL SDE.SDE_GET_ROWIDS(L_TABLE_NAME,
    C_ID_TYPE,
    C_NUM_REQUESTED_IDS,
    O_ROWID,
    L_NUM_OBTAINED_IDS,
    O_MSGCODE,
    O_MESSAGE);

    IF O_MSGCODE = -20037 /* SE_TABLE_NOEXIST (-37) */ THEN
    SET O_ROWID = NULL;
    SET O_MSGCODE = -20169 /* SE_NO_SDE_ROWID_COLUMN ( -169 ) */;
    SET O_MESSAGE = 'No SDE ROWID column.';
    LEAVE P2;
    ELSEIF O_MSGCODE != SE_SUCCESS THEN
    SET O_ROWID = NULL;
    LEAVE P2;
    ELSE /* O_MSGCODE = SE_SUCCESS */
    SET O_MESSAGE = 'Procedure successfully completed.';
    END IF;

    END P2;
    EXIT:
    RETURN (SQLZ_DISCONNECT_PROC);
    END P1
    @

    GRANT EXECUTE ON PROCEDURE SDE.NEXT_ROW_ID( VARCHAR(128),
    INTEGER,
    INTEGER,
    INTEGER,
    VARCHAR(1024) ) TO PUBLIC
    @

  2. Edit the script, replacing the database name and sde user password with information appropriate to your site.
  3. Run the script by executing the following command in a DB2 command window.

    Code:
    db2 -td@ -vf next_row_id.db2


    In this example, 'next_row_id.db2' is the name of the script.
  4. Query the TABLE_REGISTRY system table to find the registration ID and owner of the table where a row needs to be inserted.

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

    Code:
    SELECT REGISTRATION_ID, OWNER, TABLE_NAME
    FROM SDE.TABLE_REGISTRY
    WHERE TABLE_NAME = 'FACTORIES'

    REGISTRATION_ID OWNER TABLE_NAME
    39 GIS FACTORIES

    1 record(s) selected.


  5. Use the registration ID and owner name returned from the previous SELECT statment (39 and 'GIS') when calling the NEXT_ROW_ID procedure 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, 39 is the registration ID of the table, and the three question marks indicate the three parameters that are returned: O_ROWID, O_MSGCODE, and O_MESSAGE.

    Code:
    CALL SDE.NEXT_ROW_ID('GIS',39,?,?,?)
    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

  6. Use the information returned by the procedure to insert records.

    In this example, a row is inserted into the factories table using the row ID 18.

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