IBM Support

INSERT with remote SUBSELECT

News


Abstract

INSERT with remote SUBSELECT

Content

The INSERT SQL statement is enhanced to allow the sub-select to reference a single remote database which is different than the current server connection. Other SQL statements remain restricted to a single database reference per statement.

An implicit remote connection is established and used by DB2 for i for the duration of the subselect. The subselect must avoid referencing more than one target database. Either explicit three part names or implicit remote names using an SQL alias can be used within the sub-select.

Example 1: (explicit remote database reference)
CREATE TABLE DATALIB.MY_TEMP_TABLE
(SERVER_NAME VARCHAR(40), DATA_VALUE CHAR(1))
INSERT INTO DATALIB .MY_TEMP_TABLE
(SELECT CURRENT_SERVER CONCAT ' is the Server Name', IBMREQD
FROM X1423P2.SYSIBM.SYSDUMMY1)
SELECT * FROM DATALIB .MY_TEMP_TABLE
Figure 1. Insert with remote sub-select results
image-20200115124449-1
Example 2: (implicit remote database reference using an SQL alias)

CREATE ALIAS DATALIB.REMOTE_TABLE FOR x1423p2.SYSIBM.SYSDUMMY1


INSERT INTO DATALIB.MY_TEMP_TABLE
(SELECT CURRENT_SERVER CONCAT ' is the Server Name', IBMREQD
FROM DATALIB.REMOTE_TABLE)

SELECT * FROM DATALIB.MY_TEMP_TABLE
(same result as shown in Example 1)

To discover more information on this topic, refer to the "Inserting data from a remote database" section within the Database SQL Programming book.

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
15 January 2020

UID

ibm11167430

Page Feedback