IBM Support

CREATE TABLE with remote SUBSELECT

News


Abstract

CREATE TABLE AS and DECLARE GLOBAL TEMPORARY TABLE are enhanced to allow the select to reference a single remote database which is different than the current server connection

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > CREATE TABLE with remote SUBSELECT

CREATE TABLE AS and DECLARE GLOBAL TEMPORARY TABLE are enhanced to allow the select to reference a single remote database which is different than the current server connection

  • An implicit remote connection is established and used by Db2 for i.
  • The remote query can reference a single remote homogeneous or heterogeneous table.

This enhancement is the third installment in extending Db2 for i  to utilize implicit or explicit remote 3 part names within SQL.

  1. Base 3-part name support
  2. INSERT WITH remote SUBSELECT
  3. CREATE TABLE WITH remote SUBSELECT
Example 1. Create a table in the local database, referencing a remote database via the AS clause
CREATE TABLE DATALIB.MY_TEMP_TABLE AS (SELECT CURRENT_SERVER CONCAT ' is the Server Name', IBMREQD
FROM X1423P2.SYSIBM.SYSDUMMY1) WITH DATA
SELECT * FROM DATALIB.MY_TEMP_TABLE

Use of an ALIAS is the best practice for remote 3-part names because it shields the application. (database transparency)

Notice how the text of the query does not change

Example 2. Remote access using an ALIAS
CREATE OR REPLACE ALIAS DATALIB.TARGET_TABLE FOR X1423P2.SYSIBM.SYSDUMMY1
CREATE TABLE DATALIB.MY_TEMP_TABLE(Server_Name) AS
(SELECT CURRENT_SERVER CONCAT ' is the Server Name' FROM DATALIB.TARGET_TABLE)
WITH DATA
CREATE OR REPLACE ALIAS DATALIB.TARGET_TABLE FOR LP01UT18.SYSIBM.SYSDUMMY1
INSERT INTO DATALIB.MY_TEMP_TABLE (
SELECT CURRENT_SERVER CONCAT ' is the Server Name' FROM DATALIB.TARGET_TABLE)
SELECT * FROM DATALIB.MY_TEMP_TABLE
 

Example 3

Create table localrdb.myschema.tbl1 as (select * from rmtrdb.myschema.tbl1) with data

-- The above statement creates tbl1 on the localrdb based on tbl1 on rmtrdb and copies all the data. Note that the user can also explicitly select a few columns of the remote table in the select. User can also specify ‘with no data’ instead of ‘with data’ to just create the table without data.

Example 4

Declare global temporary table session.mygtt as (select * from rmtrdb.myschema.tbl1) with data

-- The above statement is similar to the first statement, but it creates the global temporary table in qtemp library.

Example 5

create table tbl2 as (select * from rmtrdb.myschema.tbl2) with data including defaults

-- This statement will create the table and copy the default value attribute of the columns that were defined on the remote table. The Display File Field Description (DSPFFD) command can be used to confirm the existence of the default value.

Field Level Information

Data Field Buffer Buffer Field Column
Field Type Length Length Position Usage Heading

COL1 CHAR 10 10 1 Both COL1

Allows the null value

Default value . . . . . . . . . . . . . . :

'abc'

Example 6

create table tbl3 as (select * from rmtrdb.myschema.tbl3) with data including identity

-- This statement will create the table and copy the identity attributes of the columns that were defined on the remote table. The Display File Field Description (DSPFFD) command can be used to confirm that the identity value is intact.

Field Level Information

Data Field Buffer Buffer Field Column
Field Type Length Length Position Usage Heading

COL1 BINARY 9 0 4 1 Both COL1

Identity column information:

GENERATED . . . . . . . . . . . . . . . : ALWAYS

Original START WITH . . . . . . . . . . : 1

Current START WITH . . . . . . . . . . : 1

INCREMENT BY . . . . . . . . . . . . . : 1

MINVALUE . . . . . . . . . . . . . . . : 1

MAXVALUE . . . . . . . . . . . . . . . : 2147483

CYCLE (Yes or No) . . . . . . . . . . . : No

Number of values to CACHE . . . . . . . : 20

Generate in order of request (ORDER). . : No

Limitations of this new support:

There are some restrictions with the support based on the remote server.

For Db2 for i , Db2 for z/OS, and Db2 for LUW remote servers

The materialized query table clauses are not allowed.
A column with a FIELDPROC cannot be listed in the select list.

Db2 for z/OS and Db2 for LUW

Copy-options cannot be specified on the create table statement.

[{"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

ibm11167352