retrieving identity column from federated table

There appear to be two possible ways to retrieve the generated value for an
identity columns.

Function IDENTITY_VAL_LOCAL(); eg:

INSERT INTO VSEUSER.ID_TEST (TEXT)
VALUES ('FJS2');
SELECT IDENTITY_VAL_LOCAL() AS MY_ID
FROM SYSIBM.SYSDUMMY1;

And selecting from a data-chage-table-reference, eg:

SELECT ID
FROM FINAL TABLE (
INSERT INTO VSEUSER.ID_TEST (TEXT)
VALUES ('FJS')
);

Neither of these appear to work when the object being inserted in to is a
"federated object".

In the first case the insert succeeds but the select from
IDENTITY_VAL_LOCAL() returns NULL.

The second case fails altogether:

SQL30090N Operation invalid for application execution environment.
Reason code = "22".

22 A federated insert, update, or delete operation, or a call to
federated procedure with an SQL data access indication of
MODIFIES SQL DATA is invalid in a function, a
data-change-table-reference, a dynamic compound statement, a
trigger, and an application execution environment where a
* SAVEPOINT is in effect
* scrollable cursor is used
* target view contains multiple tables or nicknames

For reason 22,
Either remove the federated insert, update or delete operation,
or the call to the federated procedure with an SQL data access
indication of MODIFIES SQL DATA or:
* execute the federated insert, update, or delete operation
outside of the function, the data-chagne-table-reference,
they dynamic compound statement or trigger
* execute the call to the federated procedure with an SQL data
access indication of MODIFIES SQL DATA outside of the
function, the dynamic compound statement or trigger
* release the savepoint that is in effect
* remove the use of the scrollable cursor
* redefine the target view to refer to only one table or
nickname
* execute the federated insert, update, or delete operation
outside of the trigger.

Are there any alternatives? If I used a SEQUENCE instead of an IDENTITY
column would this help? (I haven't tried it. Can you even "federate" a
sequence?)

Thanks,
Frank

--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403

Frank,

You cannot federate a sequence.
I have passed your question to the federated team.
You may need to do a "pass through", not sure.

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Feedback from backstage:

Create a function template for the remote identity value and map that to
the remote IDENTITY_VAL_LOCAL function. The SQL would be similar to the
following:
CREATE FUNCTION remote_identity_val() RETURNS DECIMAL(31,0)
AS TEMPLATE NOT DETERMINISTIC NO EXTERNAL ACTION;
CREATE FUNCTION MAPPING my_mapping FOR remote_identity_val()
SERVER drda_server
OPTIONS(REMOTE_NAME 'IDENTITY_VAL_LOCAL');
You can then use the function template REMOTE_IDENTITY_VAL in your SQL,
as you would use the IDENTITY_VAL_LOCAL function. Please note that this
function template cannot be used unless you map it to a remote function
as shown above. Also, it can be used only in SQL statements that
reference a nickname under DRDA_SERVER (or whichever server name you
use) and only if the optimizer decides that the function can be pushed
down to the data source. If you try to use the function template
somewhere else (or the optimizer cannot push it down), you will get the
following error:
SQL0142N The SQL statement is not supported.


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab