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
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.
- Base 3-part name support
- INSERT WITH remote SUBSELECT
- CREATE TABLE WITH remote SUBSELECT
FROM X1423P2.SYSIBM.SYSDUMMY1) WITH DATA
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
(SELECT CURRENT_SERVER CONCAT ' is the Server Name' FROM DATALIB.TARGET_TABLE)
WITH DATA
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
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.
Was this topic helpful?
Document Information
Modified date:
15 January 2020
UID
ibm11167352