\pset null '[NULL]' Null display is "[NULL]". \i ./test/sql/tccdb.sql SELECT 'CREATE DATABASE regtest' WHERE NOT EXISTS ( SELECT FROM pg_database WHERE datname = 'regtest' )\gexec CREATE DATABASE regtest CREATE DATABASE GRANT ALL PRIVILEGES ON DATABASE regtest to postgres; GRANT \c regtest You are now connected to database "regtest" as user "postgres". \i ./test/sql/tcfdw.sql -- Install extension CREATE EXTENSION IF NOT EXISTS db2_fdw; CREATE EXTENSION select db2_diag(); db2_diag ------------------------------------------------------------------------------------------------------------------------- db2_fdw 18.2.0, PostgreSQL 18.1, DB2INSTANCE=postgres, DB2_HOME=/var/lib/pgsql/sqllib, DB2LIB=/var/lib/pgsql/sqllib/lib (1 row) -- Install FDW Server CREATE SERVER IF NOT EXISTS sample FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'SAMPLE'); CREATE SERVER -- Map a user CREATE USER MAPPING FOR PUBLIC SERVER sample OPTIONS (user 'db2inst1', password 'db2inst1'); CREATE USER MAPPING \i ./test/sql/tcstart.sql -- Prepare a local schema CREATE SCHEMA IF NOT EXISTS sample; CREATE SCHEMA -- Import the complete sample db into the local schema IMPORT FOREIGN SCHEMA "DB2INST1" FROM SERVER sample INTO sample; IMPORT FOREIGN SCHEMA -- list imported tables \detr+ sample.* List of foreign tables Schema | Table | Server | FDW options | Description --------+---------------------+--------+----------------------------------------------------+------------- sample | act | sample | (schema 'DB2INST1', "table" 'ACT') | [NULL] sample | bintypes | sample | (schema 'DB2INST1', "table" 'BINTYPES') | [NULL] sample | catalog | sample | (schema 'DB2INST1', "table" 'CATALOG') | [NULL] sample | cl_sched | sample | (schema 'DB2INST1', "table" 'CL_SCHED') | [NULL] sample | clobtest | sample | (schema 'DB2INST1', "table" 'CLOBTEST') | [NULL] sample | customer | sample | (schema 'DB2INST1', "table" 'CUSTOMER') | [NULL] sample | department | sample | (schema 'DB2INST1', "table" 'DEPARTMENT') | [NULL] sample | emp_photo | sample | (schema 'DB2INST1', "table" 'EMP_PHOTO') | [NULL] sample | emp_resume | sample | (schema 'DB2INST1', "table" 'EMP_RESUME') | [NULL] sample | employee | sample | (schema 'DB2INST1', "table" 'EMPLOYEE') | [NULL] sample | empmdc | sample | (schema 'DB2INST1', "table" 'EMPMDC') | [NULL] sample | empprojact | sample | (schema 'DB2INST1', "table" 'EMPPROJACT') | [NULL] sample | in_tray | sample | (schema 'DB2INST1', "table" 'IN_TRAY') | [NULL] sample | inventory | sample | (schema 'DB2INST1', "table" 'INVENTORY') | [NULL] sample | iot_gre_space | sample | (schema 'DB2INST1', "table" 'IOT_GRE_SPACE') | [NULL] sample | numerictypes | sample | (schema 'DB2INST1', "table" 'NUMERICTYPES') | [NULL] sample | org | sample | (schema 'DB2INST1', "table" 'ORG') | [NULL] sample | product | sample | (schema 'DB2INST1', "table" 'PRODUCT') | [NULL] sample | productsupplier | sample | (schema 'DB2INST1', "table" 'PRODUCTSUPPLIER') | [NULL] sample | projact | sample | (schema 'DB2INST1', "table" 'PROJACT') | [NULL] sample | project | sample | (schema 'DB2INST1', "table" 'PROJECT') | [NULL] sample | purchaseorder | sample | (schema 'DB2INST1', "table" 'PURCHASEORDER') | [NULL] sample | remotetable | sample | (schema 'DB2INST1', "table" 'REMOTETABLE') | [NULL] sample | sales | sample | (schema 'DB2INST1', "table" 'SALES') | [NULL] sample | staff | sample | (schema 'DB2INST1', "table" 'STAFF') | [NULL] sample | staffg | sample | (schema 'DB2INST1', "table" 'STAFFG') | [NULL] sample | suppliers | sample | (schema 'DB2INST1', "table" 'SUPPLIERS') | [NULL] sample | tbcfileentry | sample | (schema 'DB2INST1', "table" 'TBCFILEENTRY') | [NULL] sample | tbcfileentry_sample | sample | (schema 'DB2INST1', "table" 'TBCFILEENTRY_SAMPLE') | [NULL] sample | test_hidden | sample | (schema 'DB2INST1', "table" 'TEST_HIDDEN') | [NULL] sample | testbigint | sample | (schema 'DB2INST1', "table" 'TESTBIGINT') | [NULL] sample | timetypes | sample | (schema 'DB2INST1', "table" 'TIMETYPES') | [NULL] sample | tm2acct | sample | (schema 'DB2INST1', "table" 'TM2ACCT') | [NULL] sample | uaci_rtdeployment | sample | (schema 'DB2INST1', "table" 'UACI_RTDEPLOYMENT') | [NULL] sample | vact | sample | (schema 'DB2INST1', "table" 'VACT') | [NULL] sample | vastrde1 | sample | (schema 'DB2INST1', "table" 'VASTRDE1') | [NULL] sample | vastrde2 | sample | (schema 'DB2INST1', "table" 'VASTRDE2') | [NULL] sample | vdepmg1 | sample | (schema 'DB2INST1', "table" 'VDEPMG1') | [NULL] sample | vdept | sample | (schema 'DB2INST1', "table" 'VDEPT') | [NULL] sample | vemp | sample | (schema 'DB2INST1', "table" 'VEMP') | [NULL] sample | vempdpt1 | sample | (schema 'DB2INST1', "table" 'VEMPDPT1') | [NULL] sample | vemplp | sample | (schema 'DB2INST1', "table" 'VEMPLP') | [NULL] sample | vempprojact | sample | (schema 'DB2INST1', "table" 'VEMPPROJACT') | [NULL] sample | vforpla | sample | (schema 'DB2INST1', "table" 'VFORPLA') | [NULL] sample | vhdept | sample | (schema 'DB2INST1', "table" 'VHDEPT') | [NULL] sample | vm2acct | sample | (schema 'DB2INST1', "table" 'VM2ACCT') | [NULL] sample | vphone | sample | (schema 'DB2INST1', "table" 'VPHONE') | [NULL] sample | vproj | sample | (schema 'DB2INST1', "table" 'VPROJ') | [NULL] sample | vprojact | sample | (schema 'DB2INST1', "table" 'VPROJACT') | [NULL] sample | vprojre1 | sample | (schema 'DB2INST1', "table" 'VPROJRE1') | [NULL] sample | vpstrde1 | sample | (schema 'DB2INST1', "table" 'VPSTRDE1') | [NULL] sample | vpstrde2 | sample | (schema 'DB2INST1', "table" 'VPSTRDE2') | [NULL] sample | vstafac1 | sample | (schema 'DB2INST1', "table" 'VSTAFAC1') | [NULL] sample | vstafac2 | sample | (schema 'DB2INST1', "table" 'VSTAFAC2') | [NULL] (54 rows) set log_min_messages=debug5; SET -- starting testcases -- running tc001.sql \i ./test/sql/tc001.sql -- -- TC001: Dropping and re-creating a foreign table "manually" -- -- drop an imported table \d+ sample.org; Foreign table "sample.org" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ----------+-----------------------+-----------+----------+---------+-------------------------------------------------------------------------------------------------------+----------+--------------+------------- deptnumb | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | deptname | character varying(14) | | | | (db2type '12', db2size '14', db2bytes '14', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | manager | smallint | | | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '1', db2ccsid '0') | plain | | division | character varying(10) | | | | (db2type '12', db2size '10', db2bytes '10', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | location | character varying(13) | | | | (db2type '12', db2size '13', db2bytes '13', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | Not-null constraints: "org_deptnumb_not_null" NOT NULL "deptnumb" Server: sample FDW options: (schema 'DB2INST1', "table" 'ORG') DROP FOREIGN TABLE IF EXISTS sample.org; DROP FOREIGN TABLE -- recreate it manually \d+ sample.org; psql:test/sql/tc001.sql:8: error: Did not find any relation named "sample.org". CREATE FOREIGN TABLE sample.org ( DEPTNUMB SMALLINT OPTIONS (key 'yes') NOT NULL , DEPTNAME VARCHAR(14) , MANAGER SMALLINT , DIVISION VARCHAR(10) , LOCATION VARCHAR(13) ) SERVER sample OPTIONS (schema 'DB2INST1',table 'ORG'); CREATE FOREIGN TABLE \d+ sample.org; Foreign table "sample.org" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ----------+-----------------------+-----------+----------+---------+-------------+----------+--------------+------------- deptnumb | smallint | | not null | | (key 'yes') | plain | | deptname | character varying(14) | | | | | extended | | manager | smallint | | | | | plain | | division | character varying(10) | | | | | extended | | location | character varying(13) | | | | | extended | | Not-null constraints: "org_deptnumb_not_null" NOT NULL "deptnumb" Server: sample FDW options: (schema 'DB2INST1', "table" 'ORG') -- -- TC001a: on a freshly created foreign table remove the content and manually re-create it again. -- -- remove its content delete from sample.org; DELETE 8 SELECT * FROM sample.org; deptnumb | deptname | manager | division | location ----------+----------+---------+----------+---------- (0 rows) -- repopulate the content insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(10,'Head Office',160,'Corporate','New York'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(15,'New England',50,'Eastern','Boston'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(20,'Mid Atlantic',10,'Eastern','Washington'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(38,'South Atlantic',30,'Eastern','Atlanta'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(42,'Great Lakes',100,'Midwest','Chicago'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(51,'Plains',140,'Midwest','Dallas'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(66,'Pacific',270,'Western','San Francisco'); INSERT 0 1 insert into sample.org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(84,'Mountain',290,'Western','Denver'); INSERT 0 1 -- inquire the content SELECT * FROM sample.org; deptnumb | deptname | manager | division | location ----------+----------------+---------+-----------+--------------- 10 | Head Office | 160 | Corporate | New York 15 | New England | 50 | Eastern | Boston 20 | Mid Atlantic | 10 | Eastern | Washington 38 | South Atlantic | 30 | Eastern | Atlanta 42 | Great Lakes | 100 | Midwest | Chicago 51 | Plains | 140 | Midwest | Dallas 66 | Pacific | 270 | Western | San Francisco 84 | Mountain | 290 | Western | Denver (8 rows) -- -- END of TC001 -- -- running tc002.sql \i ./test/sql/tc002.sql -- -- TC002: Importing a single foreign table from a remote schema -- \d+ sample.act; Foreign table "sample.act" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ---------+-----------------------+-----------+----------+---------+-------------------------------------------------------------------------------------------------------------+----------+--------------+------------- actno | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0', key 'true') | plain | | actkwd | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | actdesc | character varying(20) | | not null | | (db2type '12', db2size '20', db2bytes '20', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | Not-null constraints: "act_actno_not_null" NOT NULL "actno" "act_actkwd_not_null" NOT NULL "actkwd" "act_actdesc_not_null" NOT NULL "actdesc" Server: sample FDW options: (schema 'DB2INST1', "table" 'ACT') SELECT * FROM sample.act; actno | actkwd | actdesc -------+--------+--------------------- 10 | MANAGE | MANAGE/ADVISE 20 | ECOST | ESTIMATE COST 30 | DEFINE | DEFINE SPECS 40 | LEADPR | LEAD PROGRAM/DESIGN 50 | SPECS | WRITE SPECS 60 | LOGIC | DESCRIBE LOGIC 70 | CODE | CODE PROGRAMS 80 | TEST | TEST PROGRAMS 90 | ADMQS | ADM QUERY SYSTEM 100 | TEACH | TEACH CLASSES 110 | COURSE | DEVELOP COURSES 120 | STAFF | PERS AND STAFFING 130 | OPERAT | OPER COMPUTER SYS 140 | MAINT | MAINT SOFTWARE SYS 150 | ADMSYS | ADM OPERATING SYS 160 | ADMDB | ADM DATA BASES 170 | ADMDC | ADM DATA COMM 180 | DOC | DOCUMENT 190 | XML | XML Document 999 | ABCDE | uhbviurw (20 rows) -- drop foreign table act DROP FOREIGN TABLE IF EXISTS sample.act; DROP FOREIGN TABLE -- import it using limit to IMPORT FOREIGN SCHEMA "DB2INST1" LIMIT TO ("ACT") FROM SERVER sample INTO sample; IMPORT FOREIGN SCHEMA -- test its working again \d+ sample.act; Foreign table "sample.act" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ---------+-----------------------+-----------+----------+---------+-------------------------------------------------------------------------------------------------------------+----------+--------------+------------- actno | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0', key 'true') | plain | | actkwd | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | actdesc | character varying(20) | | not null | | (db2type '12', db2size '20', db2bytes '20', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | Not-null constraints: "act_actno_not_null" NOT NULL "actno" "act_actkwd_not_null" NOT NULL "actkwd" "act_actdesc_not_null" NOT NULL "actdesc" Server: sample FDW options: (schema 'DB2INST1', "table" 'ACT') SELECT * FROM sample.act; actno | actkwd | actdesc -------+--------+--------------------- 10 | MANAGE | MANAGE/ADVISE 20 | ECOST | ESTIMATE COST 30 | DEFINE | DEFINE SPECS 40 | LEADPR | LEAD PROGRAM/DESIGN 50 | SPECS | WRITE SPECS 60 | LOGIC | DESCRIBE LOGIC 70 | CODE | CODE PROGRAMS 80 | TEST | TEST PROGRAMS 90 | ADMQS | ADM QUERY SYSTEM 100 | TEACH | TEACH CLASSES 110 | COURSE | DEVELOP COURSES 120 | STAFF | PERS AND STAFFING 130 | OPERAT | OPER COMPUTER SYS 140 | MAINT | MAINT SOFTWARE SYS 150 | ADMSYS | ADM OPERATING SYS 160 | ADMDB | ADM DATA BASES 170 | ADMDC | ADM DATA COMM 180 | DOC | DOCUMENT 190 | XML | XML Document 999 | ABCDE | uhbviurw (20 rows) -- -- END of TC002 -- -- running tc003.sql \i ./test/sql/tc003.sql -- -- TC003: Run a simple join -- \d+ sample.employee; Foreign table "sample.employee" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -----------+-----------------------+-----------+----------+---------+----------------------------------------------------------------------------------------------------------------+----------+--------------+------------- empno | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208', key 'true') | extended | | firstnme | character varying(12) | | not null | | (db2type '12', db2size '12', db2bytes '12', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | midinit | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | lastname | character varying(15) | | not null | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | workdept | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | phoneno | character(4) | | | | (db2type '1', db2size '4', db2bytes '4', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | hiredate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | job | character(8) | | | | (db2type '1', db2size '8', db2bytes '8', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | edlevel | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | sex | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | birthdate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | salary | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | bonus | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | comm | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | Not-null constraints: "employee_empno_not_null" NOT NULL "empno" "employee_firstnme_not_null" NOT NULL "firstnme" "employee_lastname_not_null" NOT NULL "lastname" "employee_edlevel_not_null" NOT NULL "edlevel" Server: sample FDW options: (schema 'DB2INST1', "table" 'EMPLOYEE') \d+ sample.sales; Foreign table "sample.sales" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------------+-----------------------+-----------+----------+---------+-------------------------------------------------------------------------------------------------------+----------+--------------+------------- sales_date | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | sales_person | character varying(15) | | | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | region | character varying(15) | | | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | sales | integer | | | | (db2type '4', db2size '10', db2bytes '4', db2chars '10', db2scale '0', db2null '1', db2ccsid '0') | plain | | Server: sample FDW options: (schema 'DB2INST1', "table" 'SALES') -- test a simple join explain (analyze,verbose) select * from sample.employee a, sample.sales b where a.lastname = b.sales_person; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan (cost=200.00..424.60 rows=928 width=362) (actual time=4.085..33.655 rows=41.00 loops=1) Output: a.empno, a.firstnme, a.midinit, a.lastname, a.workdept, a.phoneno, a.hiredate, a.job, a.edlevel, a.sex, a.birthdate, a.salary, a.bonus, a.comm, b.sales_date, b.sales_person, b.region, b.sales DB2 query: SELECT r1."EMPNO", r1."FIRSTNME", r1."MIDINIT", r1."LASTNAME", r1."WORKDEPT", r1."PHONENO", r1."HIREDATE", r1."JOB", r1."EDLEVEL", r1."SEX", r1."BIRTHDATE", r1."SALARY", r1."BONUS", r1."COMM", r2."SALES_DATE", r2."SALES_PERSON", r2."REGION", r2."SALES" FROM ("DB2INST1"."EMPLOYEE" r1 INNER JOIN "DB2INST1"."SALES" r2 ON (((r1."LASTNAME" = r2."SALES_PERSON")))) DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT r1."EMPNO" , r1."FIRSTNME" , r1."MIDINIT" , r1."LASTNAME" , DB2 plan: r1."WORKDEPT" , r1."PHONENO" , r1."HIREDATE" , r1."JOB" , DB2 plan: r1."EDLEVEL" , r1."SEX" , r1."BIRTHDATE" , r1."SALARY" , r1. DB2 plan: "BONUS" , r1."COMM" , r2."SALES_DATE" , r2."SALES_PERSON" , DB2 plan: r2."REGION" , r2."SALES" DB2 plan: FROM ("DB2INST1" ."EMPLOYEE" r1 INNER JOIN "DB2INST1" ."SALES" r2 DB2 plan: ON (((r1."LASTNAME" =r2."SALES_PERSON" )))) DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 13,629282 DB2 plan: Estimated Cardinality = 41,999996 DB2 plan: DB2 plan: Access Table Name = DB2INST1.SALES ID = 2,16 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 4 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | Process Build Table for Hash Join DB2 plan: Hash Join DB2 plan: | Estimated Build Size: 4000 DB2 plan: | Estimated Probe Size: 8000 DB2 plan: | Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | | tenantID = 0 DB2 plan: | | #Columns = 14 DB2 plan: | | Skip Inserted Rows DB2 plan: | | Avoid Locking Committed Data DB2 plan: | | Currently Committed for Cursor Stability DB2 plan: | | May participate in Scan Sharing structures DB2 plan: | | Scan may start anywhere and wrap, for completion DB2 plan: | | Fast scan, for purposes of scan sharing management DB2 plan: | | Scan can be throttled in scan sharing management DB2 plan: | | Relation Scan DB2 plan: | | | Prefetch: Eligible DB2 plan: | | Lock Intents DB2 plan: | | | Table: Intent Share DB2 plan: | | | Row : Next Key Share DB2 plan: | | Sargable Predicate(s) DB2 plan: | | | Process Probe Table for Hash Join DB2 plan: Return Data to Application DB2 plan: | #Columns = 18 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=106 Planning Time: 4.942 ms Execution Time: 35.726 ms (92 rows) select * from sample.employee a, sample.sales b where a.lastname = b.sales_person; empno | firstnme | midinit | lastname | workdept | phoneno | hiredate | job | edlevel | sex | birthdate | salary | bonus | comm | sales_date | sales_person | region | sales --------+----------+---------+-----------+----------+---------+------------+----------+---------+-----+------------+----------+--------+---------+------------+--------------+---------------+-------- 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2005-12-31 | LUCCHESSI | Ontario-South | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-04-01 | LUCCHESSI | Manitoba | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-04-01 | LUCCHESSI | Ontario-South | 3 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-03-31 | LUCCHESSI | Manitoba | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-03-30 | LUCCHESSI | Manitoba | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-03-30 | LUCCHESSI | Quebec | 2 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-03-30 | LUCCHESSI | Ontario-South | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-03-29 | LUCCHESSI | Quebec | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 | 2006-03-29 | LUCCHESSI | Ontario-South | 3 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2005-12-31 | LEE | Ontario-South | 3 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-04-01 | LEE | Manitoba | 9 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-04-01 | LEE | Quebec | 8 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-04-01 | LEE | Ontario-North | [NULL] 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-04-01 | LEE | Ontario-South | 8 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-31 | LEE | Manitoba | 3 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-31 | LEE | Quebec | 7 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-31 | LEE | Ontario-North | 3 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-31 | LEE | Ontario-South | 14 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-30 | LEE | Manitoba | 4 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-30 | LEE | Quebec | 7 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-30 | LEE | Ontario-North | 3 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-30 | LEE | Ontario-South | 7 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-29 | LEE | Manitoba | 5 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-29 | LEE | Quebec | 3 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 1996-03-29 | LEE | Ontario-North | 2 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2006-03-29 | LEE | Ontario-South | 2 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2005-12-31 | LEE | Manitoba | 2 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 | 2005-12-31 | LEE | Quebec | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2005-12-31 | GOUNOT | Quebec | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-04-01 | GOUNOT | Manitoba | 7 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-04-01 | GOUNOT | Quebec | 3 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-04-01 | GOUNOT | Ontario-North | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-04-01 | GOUNOT | Ontario-South | 3 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-31 | GOUNOT | Quebec | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-31 | GOUNOT | Ontario-South | 2 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-31 | GOUNOT | Manitoba | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-30 | GOUNOT | Quebec | 18 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-30 | GOUNOT | Ontario-South | 2 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-29 | GOUNOT | Manitoba | 7 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-29 | GOUNOT | Quebec | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 | 2006-03-29 | GOUNOT | Ontario-South | 3 (41 rows) -- -- End of TC003 -- -- running tc004.sql \i ./test/sql/tc004.sql -- -- TC004: clone a foreign table into a local table including content -- create table sample.orgcopy as select * from sample.org; SELECT 8 \d+ sample.org* Foreign table "sample.org" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ----------+-----------------------+-----------+----------+---------+-------------+----------+--------------+------------- deptnumb | smallint | | not null | | (key 'yes') | plain | | deptname | character varying(14) | | | | | extended | | manager | smallint | | | | | plain | | division | character varying(10) | | | | | extended | | location | character varying(13) | | | | | extended | | Not-null constraints: "org_deptnumb_not_null" NOT NULL "deptnumb" Server: sample FDW options: (schema 'DB2INST1', "table" 'ORG') Table "sample.orgcopy" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+------------- deptnumb | smallint | | | | plain | | | deptname | character varying(14) | | | | extended | | | manager | smallint | | | | plain | | | division | character varying(10) | | | | extended | | | location | character varying(13) | | | | extended | | | Access method: heap select * from sample.orgcopy; deptnumb | deptname | manager | division | location ----------+----------------+---------+-----------+--------------- 10 | Head Office | 160 | Corporate | New York 15 | New England | 50 | Eastern | Boston 20 | Mid Atlantic | 10 | Eastern | Washington 38 | South Atlantic | 30 | Eastern | Atlanta 42 | Great Lakes | 100 | Midwest | Chicago 51 | Plains | 140 | Midwest | Dallas 66 | Pacific | 270 | Western | San Francisco 84 | Mountain | 290 | Western | Denver (8 rows) drop table sample.orgcopy; DROP TABLE -- -- END of TC004 -- -- running tc005.sql \i ./test/sql/tc005.sql -- -- TC005: run a pushdown query for aggregate functions -- \d+ sample.employee; Foreign table "sample.employee" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -----------+-----------------------+-----------+----------+---------+----------------------------------------------------------------------------------------------------------------+----------+--------------+------------- empno | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208', key 'true') | extended | | firstnme | character varying(12) | | not null | | (db2type '12', db2size '12', db2bytes '12', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | midinit | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | lastname | character varying(15) | | not null | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | workdept | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | phoneno | character(4) | | | | (db2type '1', db2size '4', db2bytes '4', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | hiredate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | job | character(8) | | | | (db2type '1', db2size '8', db2bytes '8', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | edlevel | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | sex | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | birthdate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | salary | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | bonus | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | comm | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | Not-null constraints: "employee_empno_not_null" NOT NULL "empno" "employee_firstnme_not_null" NOT NULL "firstnme" "employee_lastname_not_null" NOT NULL "lastname" "employee_edlevel_not_null" NOT NULL "edlevel" Server: sample FDW options: (schema 'DB2INST1', "table" 'EMPLOYEE') explain (analyze,verbose) select min(salary),max(salary),avg(salary),sum(salary +comm + bonus),count(*) from sample.employee; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan (cost=121.72..144.35 rows=1 width=136) (actual time=2.536..2.613 rows=1.00 loops=1) Output: (min(salary)), (max(salary)), (avg(salary)), (sum(((salary + comm) + bonus))), (count(*)) DB2 query: SELECT MIN("SALARY"), MAX("SALARY"), AVG("SALARY"), SUM((("SALARY" + "COMM") + "BONUS")), COUNT(*) FROM "DB2INST1"."EMPLOYEE" DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT MIN("SALARY" ), MAX("SALARY" ), AVG("SALARY" ), SUM((( DB2 plan: "SALARY" + "COMM" )+ "BONUS" )), COUNT(*) DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,817206 DB2 plan: Estimated Cardinality = 1,000000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 3 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | Predicate Aggregation DB2 plan: | | | Column Function(s) DB2 plan: Aggregation Completion DB2 plan: | Column Function(s) DB2 plan: Return Data to Application DB2 plan: | #Columns = 5 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=20 Planning Time: 3.423 ms Execution Time: 3.763 ms (71 rows) select min(salary),max(salary),avg(salary),sum(salary +comm + bonus),count(*) from sample.employee; min | max | avg | sum | count ---------+-----------+----------+------------+------- 8000.00 | 152750.00 | 56988.95 | 2567223.00 | 43 (1 row) -- explain (analyze,verbose) select empno, firstnme,lastname, salary + bonus + comm from sample.employee where salary > 43840.01 and lastname like 'L%'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..116.89 rows=1 width=150) (actual time=2.461..3.119 rows=3.00 loops=1) Output: empno, firstnme, lastname, ((salary + bonus) + comm) DB2 query: SELECT "EMPNO", "FIRSTNME", "LASTNAME", "SALARY", "BONUS", "COMM" FROM "DB2INST1"."EMPLOYEE" WHERE (("SALARY" > 43840.01)) AND (("LASTNAME" LIKE 'L%' ESCAPE '\')) DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "FIRSTNME" , "LASTNAME" , "SALARY" , "BONUS" , DB2 plan: "COMM" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE (("SALARY" > 43840.01))AND (("LASTNAME" LIKE 'L%' ESCAPE '\' ) DB2 plan: ) DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816642 DB2 plan: Estimated Cardinality = 2,627403 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 6 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 2 DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 6 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=3 Planning Time: 3.191 ms Execution Time: 4.304 ms (71 rows) select empno, firstnme,lastname, salary + bonus + comm from sample.employee where salary > 43840.01 and lastname like 'L%'; empno | firstnme | lastname | ?column? --------+----------+-----------+---------- 000110 | VINCENZO | LUCCHESSI | 71120.00 000220 | JENNIFER | LUTZ | 52827.00 000330 | WING | LEE | 47900.00 (3 rows) -- -- END of TC005 -- -- running tc006.sql \i ./test/sql/tc006.sql -- -- TC006: UPDATE/DELETE against a key column and revert to baseline -- -- For UPDATE/DELETE, db2_fdw requires at least one primary key column marked -- with the column option "key". -- The DB2 SAMPLE.ORG table uses DEPTNUMB as its primary key. ALTER FOREIGN TABLE sample.org ALTER COLUMN deptnumb OPTIONS (ADD key 'true'); psql:test/sql/tc006.sql:8: FEHLER: Option »key« mehrmals angegeben \d+ sample.org; Foreign table "sample.org" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ----------+-----------------------+-----------+----------+---------+-------------+----------+--------------+------------- deptnumb | smallint | | not null | | (key 'yes') | plain | | deptname | character varying(14) | | | | | extended | | manager | smallint | | | | | plain | | division | character varying(10) | | | | | extended | | location | character varying(13) | | | | | extended | | Not-null constraints: "org_deptnumb_not_null" NOT NULL "deptnumb" Server: sample FDW options: (schema 'DB2INST1', "table" 'ORG') -- Baseline row SELECT deptnumb, deptname, location FROM sample.org WHERE deptnumb = 10; deptnumb | deptname | location ----------+-------------+---------- 10 | Head Office | New York (1 row) -- UPDATE + verify UPDATE sample.org SET location = 'New York City' WHERE deptnumb = 10; UPDATE 1 SELECT deptnumb, deptname, location FROM sample.org WHERE deptnumb = 10; deptnumb | deptname | location ----------+-------------+--------------- 10 | Head Office | New York City (1 row) -- Revert + verify UPDATE sample.org SET location = 'New York' WHERE deptnumb = 10; UPDATE 1 SELECT deptnumb, deptname, location FROM sample.org WHERE deptnumb = 10; deptnumb | deptname | location ----------+-------------+---------- 10 | Head Office | New York (1 row) -- DELETE + INSERT to test DELETE/INSERT pair (choose a row we can reconstruct deterministically) SELECT * FROM sample.org WHERE deptnumb = 84; deptnumb | deptname | manager | division | location ----------+----------+---------+----------+---------- 84 | Mountain | 290 | Western | Denver (1 row) DELETE FROM sample.org WHERE deptnumb = 84; DELETE 1 SELECT * FROM sample.org WHERE deptnumb = 84; deptnumb | deptname | manager | division | location ----------+----------+---------+----------+---------- (0 rows) INSERT INTO sample.org (deptnumb, deptname, manager, division, location) VALUES (84, 'Mountain', 290, 'Western', 'Denver'); INSERT 0 1 SELECT * FROM sample.org WHERE deptnumb = 84; deptnumb | deptname | manager | division | location ----------+----------+---------+----------+---------- 84 | Mountain | 290 | Western | Denver (1 row) -- -- END of TC006 -- -- running tc007.sql \i ./test/sql/tc007.sql -- -- TC007: Transaction / rollback semantics for foreign table modifications -- SELECT deptnumb, location FROM sample.org WHERE deptnumb = 15; deptnumb | location ----------+---------- 15 | Boston (1 row) BEGIN; BEGIN UPDATE sample.org SET location = 'Boston (temp)' WHERE deptnumb = 15; UPDATE 1 SELECT deptnumb, location FROM sample.org WHERE deptnumb = 15; deptnumb | location ----------+--------------- 15 | Boston (temp) (1 row) ROLLBACK; ROLLBACK -- Should be back to original SELECT deptnumb, location FROM sample.org WHERE deptnumb = 15; deptnumb | location ----------+---------- 15 | Boston (1 row) -- -- END of TC007 -- -- running tc008.sql \i ./test/sql/tc008.sql -- -- TC008: Query-based foreign table -- DROP FOREIGN TABLE IF EXISTS sample.emp_l; psql:test/sql/tc008.sql:4: HINWEIS: Fremdtabelle »emp_l« existiert nicht, wird übersprungen DROP FOREIGN TABLE CREATE FOREIGN TABLE sample.emp_l ( empno char(6), firstnme varchar(12), lastname varchar(15) ) SERVER sample OPTIONS (schema 'DB2INST1',table 'EMPLOYEE', readonly 'true'); CREATE FOREIGN TABLE \d+ sample.emp_l; Foreign table "sample.emp_l" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ----------+-----------------------+-----------+----------+---------+-------------+----------+--------------+------------- empno | character(6) | | | | | extended | | firstnme | character varying(12) | | | | | extended | | lastname | character varying(15) | | | | | extended | | Server: sample FDW options: (schema 'DB2INST1', "table" 'EMPLOYEE', readonly 'true') SELECT * FROM sample.emp_l ORDER BY empno; empno | firstnme | lastname --------+-----------+---------- 000010 | CHRISTINE | H 000020 | MICHAEL | T 000030 | SALLY | K 000050 | JOHN | G 000060 | IRVING | S 000070 | EVA | P 000090 | EILEEN | H 000100 | THEODORE | S 000110 | VINCENZO | L 000120 | SEAN | O 000130 | DELORES | Q 000140 | HEATHER | N 000150 | BRUCE | A 000160 | ELIZABETH | P 000170 | MASATOSHI | Y 000180 | MARILYN | S 000190 | JAMES | W 000200 | DAVID | B 000210 | WILLIAM | J 000220 | JENNIFER | L 000230 | JAMES | J 000240 | SALVATORE | M 000250 | DANIEL | S 000260 | SYBIL | J 000270 | MARIA | P 000280 | ETHEL | S 000290 | JOHN | P 000300 | PHILIP | S 000310 | MAUDE | S 000320 | RAMLAL | M 000330 | WING | L 000340 | JASON | G 200010 | DIAN | H 200120 | GREG | O 200140 | KIM | N 200170 | KIYOSHI | Y 200220 | REBA | J 200240 | ROBERT | M 200280 | EILEEN | S 200310 | MICHELLE | S 200330 | HELENA | W 200340 | ROY | A 300001 | Thomas | M (43 rows) -- Optional: show it is read-only by intent (uncomment if you want a hard failure case) INSERT INTO sample.emp_l(empno, firstnme, lastname) VALUES ('999999', 'X', 'Y'); psql:test/sql/tc008.sql:18: FEHLER: Fremdtabelle »emp_l« erlaubt kein Einfügen DROP FOREIGN TABLE sample.emp_l; DROP FOREIGN TABLE -- -- END of TC008 -- -- running tc009.sql \i ./test/sql/tc009.sql -- -- TC009: Close cached DB2 connections and verify reconnect works -- SELECT count(*) FROM sample.employee; count ------- 43 (1 row) SELECT db2_close_connections(); db2_close_connections ----------------------- (1 row) SELECT count(*) FROM sample.employee; count ------- 43 (1 row) -- -- END of TC009 -- -- running tc010.sql \i ./test/sql/tc010.sql -- -- TC010: Pushdown predicates (IN/BETWEEN/OR/IS NULL) -- EXPLAIN (analyze,verbose) SELECT empno, lastname, salary FROM sample.employee WHERE workdept IN ('A00','B01','E21') AND salary BETWEEN 40000 AND 70000 AND (lastname LIKE 'L%' OR lastname LIKE 'G%'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..127.26 rows=1 width=90) (actual time=3.397..3.889 rows=3.00 loops=1) Output: empno, lastname, salary, workdept DB2 query: SELECT "EMPNO", "LASTNAME", "SALARY", "WORKDEPT" FROM "DB2INST1"."EMPLOYEE" WHERE (("SALARY" >= 40000)) AND (("SALARY" <= 70000)) AND (("WORKDEPT" IN ('A00', 'B01', 'E21'))) AND ((("LASTNAME" LIKE 'L%' ESCAPE '\') OR ("LASTNAME" LIKE 'G%' ESCAPE '\'))) DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" , "SALARY" , "WORKDEPT" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE (("SALARY" >=40000))AND (("SALARY" <=70000))AND (("WORKDEPT" DB2 plan: IN ('A00' , 'B01' , 'E21' )))AND ((("LASTNAME" LIKE 'L%' DB2 plan: ESCAPE '\' )OR ("LASTNAME" LIKE 'G%' ESCAPE '\' ))) DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816905 DB2 plan: Estimated Cardinality = 1,019460 DB2 plan: DB2 plan: Table Constructor DB2 plan: | 3-Row(s) DB2 plan: Nested Loop Join DB2 plan: | Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | | tenantID = 0 DB2 plan: | | Index Scan: Name = DB2INST1.XEMP2 ID = 2 DB2 plan: | | | Regular Index (Not Clustered) DB2 plan: | | | Index Columns: DB2 plan: | | | | 1: WORKDEPT (Ascending) DB2 plan: | | #Columns = 4 DB2 plan: | | Skip Inserted Rows DB2 plan: | | Avoid Locking Committed Data DB2 plan: | | Currently Committed for Cursor Stability DB2 plan: | | Evaluate Predicates Before Locking for Key DB2 plan: | | #Key Columns = 1 DB2 plan: | | | Start Key: Inclusive Value DB2 plan: | | | | 1: ? DB2 plan: | | | Stop Key: Inclusive Value DB2 plan: | | | | 1: ? DB2 plan: | | Data Prefetch: Sequential(0), Readahead DB2 plan: | | Index Prefetch: None DB2 plan: | | Lock Intents DB2 plan: | | | Table: Intent Share DB2 plan: | | | Row : Next Key Share DB2 plan: | | Sargable Predicate(s) DB2 plan: | | | #Predicates = 4 DB2 plan: | | | Return Data to Application DB2 plan: | | | | #Columns = 4 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 4.897 ms Execution Time: 5.137 ms (78 rows) SELECT empno, lastname, salary FROM sample.employee WHERE workdept IN ('A00','B01','E21') AND salary BETWEEN 40000 AND 70000 AND (lastname LIKE 'L%' OR lastname LIKE 'G%') ORDER BY empno; empno | lastname | salary --------+-----------+---------- 000110 | LUCCHESSI | 66500.00 000330 | LEE | 45370.00 000340 | GOUNOT | 43840.00 (3 rows) -- NULL handling sanity check SELECT count(*) AS total, count(*) FILTER (WHERE comm IS NULL) AS comm_is_null, count(*) FILTER (WHERE comm IS NOT NULL) AS comm_is_not_null FROM sample.employee; total | comm_is_null | comm_is_not_null -------+--------------+------------------ 43 | 43 | 43 (1 row) -- -- END of TC010 -- --running tc011.sql \i ./test/sql/tc011.sql -- -- TC011: ORDER BY with LIMIT/OFFSET -- EXPLAIN (analyze,verbose) SELECT empno, lastname, salary FROM sample.employee LIMIT 5; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..101.11 rows=5 width=90) (actual time=1.892..2.597 rows=5.00 loops=1) Output: empno, lastname, salary DB2 query: SELECT "EMPNO", "LASTNAME", "SALARY" FROM "DB2INST1"."EMPLOYEE" FETCH FIRST 5 ROWS ONLY DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" , "SALARY" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: FETCH FIRST 5 ROWS ONLY DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,809195 DB2 plan: Estimated Cardinality = 5,000000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 3 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: Return Data to Application DB2 plan: | #Columns = 3 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 2.343 ms Execution Time: 3.654 ms (64 rows) SELECT empno, lastname, salary FROM sample.employee LIMIT 5; empno | lastname | salary --------+----------+----------- 000010 | HAAS | 152750.00 000020 | THOMPSON | 94250.00 000030 | KWAN | 98250.00 000050 | GEYER | 80175.00 000060 | STERN | 72250.00 (5 rows) EXPLAIN (analyze,verbose) SELECT empno, lastname, salary FROM sample.employee LIMIT 5 OFFSET 2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.05..101.16 rows=5 width=90) (actual time=2.222..2.917 rows=5.00 loops=1) Output: empno, lastname, salary DB2 query: SELECT "EMPNO", "LASTNAME", "SALARY" FROM "DB2INST1"."EMPLOYEE" OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" , "SALARY" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" OFFSET 2 ROWS DB2 plan: FETCH NEXT 5 ROWS ONLY DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,810677 DB2 plan: Estimated Cardinality = 2,333333 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 3 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: Residual Predicate(s) DB2 plan: | #Predicates = 1 DB2 plan: Return Data to Application DB2 plan: | #Columns = 3 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 2.287 ms Execution Time: 3.951 ms (66 rows) SELECT empno, lastname, salary FROM sample.employee LIMIT 5 OFFSET 2; empno | lastname | salary --------+-----------+---------- 000030 | KWAN | 98250.00 000050 | GEYER | 80175.00 000060 | STERN | 72250.00 000070 | PULASKI | 96170.00 000090 | HENDERSON | 89750.00 (5 rows) EXPLAIN (analyze,verbose) SELECT empno, lastname, salary FROM sample.employee ORDER BY salary DESC, empno LIMIT 5 OFFSET 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on sample.employee (cost=100.06..101.19 rows=5 width=90) (actual time=2.671..3.370 rows=5.00 loops=1) Output: empno, lastname, salary DB2 query: SELECT "EMPNO", "LASTNAME", "SALARY" FROM "DB2INST1"."EMPLOYEE" ORDER BY "SALARY" DESC NULLS FIRST, "EMPNO" ASC NULLS LAST OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" , "SALARY" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: ORDER BY "SALARY" DESC NULLS FIRST, "EMPNO" ASC NULLS LAST OFFSET 2 DB2 plan: ROWS DB2 plan: FETCH NEXT 5 ROWS ONLY DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816353 DB2 plan: Estimated Cardinality = 2,333333 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 3 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 3 DB2 plan: | | | #Sort Key Columns = 2 DB2 plan: | | | | Key 1: SALARY (Descending) DB2 plan: | | | | Key 2: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 7,000000 DB2 plan: | | | | Row Width = 28 DB2 plan: | | | | Sort Limited To Estimated Row Count DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 3 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: Residual Predicate(s) DB2 plan: | #Predicates = 1 DB2 plan: Return Data to Application DB2 plan: | #Columns = 3 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=12 Planning Time: 2.792 ms Execution Time: 4.424 ms (88 rows) SELECT empno, lastname, salary FROM sample.employee ORDER BY salary DESC, empno LIMIT 5 OFFSET 2; empno | lastname | salary --------+-----------+---------- 000070 | PULASKI | 96170.00 000020 | THOMPSON | 94250.00 000090 | HENDERSON | 89750.00 000100 | SPENSER | 86150.00 000050 | GEYER | 80175.00 (5 rows) -- -- END of TC011 -- -- running tc012.sql \i ./test/sql/tc012.sql -- -- TC012: PREPARE/EXECUTE parameter binding -- PREPARE act_by_no(int) AS SELECT actno, actkwd, actdesc FROM sample.act WHERE actno = $1; PREPARE EXPLAIN (analyze,verbose) EXECUTE act_by_no(10); QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.act (cost=100.00..119.98 rows=4 width=88) (actual time=4.066..4.139 rows=1.00 loops=1) Output: actno, actkwd, actdesc DB2 query: SELECT "ACTNO", "ACTKWD", "ACTDESC" FROM "DB2INST1"."ACT" WHERE (("ACTNO" = 10)) DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "ACTNO" , "ACTKWD" , "ACTDESC" DB2 plan: FROM "DB2INST1" ."ACT" DB2 plan: WHERE (("ACTNO" =10)) DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,809176 DB2 plan: Estimated Cardinality = 1,000000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.ACT ID = 2,12 DB2 plan: | tenantID = 0 DB2 plan: | Index Scan: Name = DB2INST1.XACT2 ID = 2 DB2 plan: | | Regular Index (Not Clustered) DB2 plan: | | Index Columns: DB2 plan: | | | 1: ACTNO (Ascending) DB2 plan: | | | 2: ACTKWD (Ascending) DB2 plan: | #Columns = 3 DB2 plan: | Single Record DB2 plan: | Fully Qualified Unique Key DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | Evaluate Predicates Before Locking for Key DB2 plan: | #Key Columns = 1 DB2 plan: | | Start Key: Inclusive Value DB2 plan: | | | 1: 10 DB2 plan: | | Stop Key: Inclusive Value DB2 plan: | | | 1: 10 DB2 plan: | Data Prefetch: None DB2 plan: | Index Prefetch: None DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 3 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 1.442 ms Execution Time: 4.651 ms (75 rows) EXECUTE act_by_no(10); actno | actkwd | actdesc -------+--------+--------------- 10 | MANAGE | MANAGE/ADVISE (1 row) DEALLOCATE act_by_no; DEALLOCATE -- -- END of TC012 -- -- running tc013.sql \i ./test/sql/tc013.sql -- -- TC013: Obtain LOB and NULLs -- select deploymentdata from sample.uaci_rtdeployment; deploymentdata ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- \x5c78333134363338343233303338333033303330333033303330333033303330333033303330333033303435343433373434333034323339333433313433343333353337333533363338343534343334343634363432343433373330343133303331343634313436333033313434333833343336333533303333333034353333343234343431343433353331343633383434333933363339343433393434343433393339333533343336343334333337343534343338333133393436333133393331333833323339331c2b04 (1 row) select rtdeploymentid,icid,deploymentdata from sample.uaci_rtdeployment; rtdeploymentid | icid | deploymentdata ----------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 | 1 | \x5c78333134363338343233303338333033303330333033303330333033303330333033303330333033303435343433373434333034323339333433313433343333353337333533363338343534343334343634363432343433373330343133303331343634313436333033313434333833343336333533303333333034353333343234343431343433353331343633383434333933363339343433393434343433393339333533343336343334333337343534343338333133393436333133393331333833323339331c2b04 (1 row) select * from sample.uaci_rtdeployment; rtdeploymentid | icid | deploymentdata | rtdepstatusid | deploymentversion | createdate | createby | updatedate | updateby | version | isactive ----------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+------------+----------+------------+----------+---------+---------- 1 | 1 | \x5c78333134363338343233303338333033303330333033303330333033303330333033303330333033303435343433373434333034323339333433313433343333353337333533363338343534343334343634363432343433373330343133303331343634313436333033313434333833343336333533303333333034353333343234343431343433353331343633383434333933363339343433393434343433393339333533343336343334333337343534343338333133393436333133393331333833323339331c2b04 | 1 | 1 | [NULL] | [NULL] | [NULL] | [NULL] | 1 | 1 (1 row) -- -- END of TC013 -- -- running tc014.sql \i ./test/sql/tc014.sql -- -- TC014: INSERT -- \d+ sample.remotetable Foreign table "sample.remotetable" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------------+-----------+----------+---------+---------------------------------------------------------------------------------------------------------------+----------+--------------+------------- col1 | integer | | not null | | (db2type '4', db2size '10', db2bytes '4', db2chars '10', db2scale '0', db2null '0', db2ccsid '0', key 'true') | plain | | col2 | character varying(99) | | not null | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | col3 | character varying(99) | | not null | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | col4 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col5 | character varying(1000) | | | | (db2type '12', db2size '1000', db2bytes '1000', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col6 | character varying(255) | | | | (db2type '12', db2size '255', db2bytes '255', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col7 | character varying(255) | | | | (db2type '12', db2size '255', db2bytes '255', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col8 | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | col9 | time(0) without time zone | | | | (db2type '92', db2size '8', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | col10 | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | col11 | time(0) without time zone | | | | (db2type '92', db2size '8', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | col12 | integer | | | | (db2type '4', db2size '10', db2bytes '4', db2chars '10', db2scale '0', db2null '1', db2ccsid '0') | plain | | col13 | character(2) | | not null | | (db2type '1', db2size '2', db2bytes '2', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | col14 | character(2) | | | | (db2type '1', db2size '2', db2bytes '2', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col15 | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col16 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col17 | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col18 | character(2) | | | | (db2type '1', db2size '2', db2bytes '2', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col19 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col20 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col21 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col22 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col23 | character varying(99) | | | | (db2type '12', db2size '99', db2bytes '99', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col24 | timestamp(6) without time zone | | not null | | (db2type '93', db2size '26', db2bytes '16', db2chars '26', db2scale '6', db2null '0', db2ccsid '0') | plain | | col25 | character varying(256) | | not null | | (db2type '12', db2size '256', db2bytes '256', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | col26 | character varying(255) | | | | (db2type '12', db2size '255', db2bytes '255', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col27 | character varying(999) | | | | (db2type '12', db2size '999', db2bytes '999', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | col28 | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | Not-null constraints: "remotetable_col1_not_null" NOT NULL "col1" "remotetable_col2_not_null" NOT NULL "col2" "remotetable_col3_not_null" NOT NULL "col3" "remotetable_col13_not_null" NOT NULL "col13" "remotetable_col24_not_null" NOT NULL "col24" "remotetable_col25_not_null" NOT NULL "col25" Server: sample FDW options: (schema 'DB2INST1', "table" 'REMOTETABLE') SELECT * FROM sample.remotetable; col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 | col13 | col14 | col15 | col16 | col17 | col18 | col19 | col20 | col21 | col22 | col23 | col24 | col25 | col26 | col27 | col28 --------+----------+----------+----------+--------+--------+----------+------------+----------+------------+----------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------------------------+----------+--------+--------+-------- 581928 | sometext | sometext | sometext | [NULL] | [NULL] | sometext | 2025-12-31 | 12:24:00 | 2025-12-31 | 12:24:00 | 999 | AA | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | 2025-12-31 12:24:00.988 | sometext | [NULL] | [NULL] | [NULL] 681928 | sometext | sometext | sometext | [NULL] | [NULL] | sometext | 2025-12-31 | 12:24:00 | 2025-12-31 | 12:24:00 | 998 | AA | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | 2025-12-31 12:24:00.988 | sometext | [NULL] | [NULL] | [NULL] (2 rows) INSERT INTO sample.remotetable (col1, col2, col3, col4, col7, col8, col9, col10, col11, col12, col13, col24, col25) VALUES ( 581927, 'sometext', 'sometext', 'sometext', 'sometext', current_date, current_time, current_date, current_time, 999, 'AA', current_timestamp, 'sometext' ); INSERT 0 1 SELECT * FROM sample.remotetable; col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 | col13 | col14 | col15 | col16 | col17 | col18 | col19 | col20 | col21 | col22 | col23 | col24 | col25 | col26 | col27 | col28 --------+----------+----------+----------+--------+--------+----------+------------+----------+------------+----------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+----------------------------+----------+--------+--------+-------- 581927 | sometext | sometext | sometext | [NULL] | [NULL] | sometext | 2026-07-26 | 14:50:50 | 2026-07-26 | 14:50:50 | 999 | AA | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | 2026-07-26 14:50:50.412549 | sometext | [NULL] | [NULL] | [NULL] 581928 | sometext | sometext | sometext | [NULL] | [NULL] | sometext | 2025-12-31 | 12:24:00 | 2025-12-31 | 12:24:00 | 999 | AA | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | 2025-12-31 12:24:00.988 | sometext | [NULL] | [NULL] | [NULL] 681928 | sometext | sometext | sometext | [NULL] | [NULL] | sometext | 2025-12-31 | 12:24:00 | 2025-12-31 | 12:24:00 | 998 | AA | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] | 2025-12-31 12:24:00.988 | sometext | [NULL] | [NULL] | [NULL] (3 rows) DELETE FROM sample.remotetable where col1 = 581927; DELETE 1 -- -- END of TC014 -- -- running tc015.sql \i ./test/sql/tc015.sql -- -- TC015: TABLESAMPLE and PERCENTILE -- \d+ sample.tbcfileentry Foreign table "sample.tbcfileentry" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -------------+--------------------------------+-----------+----------+---------+------------------------------------------------------------------------------------------------------------------+----------+--------------+------------- tbcfientid | bigint | | not null | | (db2type '-5', db2size '19', db2bytes '8', db2chars '19', db2scale '0', db2null '0', db2ccsid '0', key 'true') | plain | | filename | character varying(255) | | not null | | (db2type '12', db2size '255', db2bytes '255', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | filepath | text | | not null | | (db2type '-99', db2size '1048576', db2bytes '1048576', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | filesize | bigint | | | | (db2type '-5', db2size '19', db2bytes '8', db2chars '19', db2scale '0', db2null '1', db2ccsid '0') | plain | | filetype | character varying(100) | | | | (db2type '12', db2size '100', db2bytes '100', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | checksum | character varying(64) | | | | (db2type '12', db2size '64', db2bytes '64', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | owner | character varying(100) | | | | (db2type '12', db2size '100', db2bytes '100', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | created_at | timestamp(6) without time zone | | not null | | (db2type '93', db2size '26', db2bytes '16', db2chars '26', db2scale '6', db2null '0', db2ccsid '0') | plain | | modified_at | timestamp(6) without time zone | | | | (db2type '93', db2size '26', db2bytes '16', db2chars '26', db2scale '6', db2null '1', db2ccsid '0') | plain | | status | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | description | text | | | | (db2type '-99', db2size '1048576', db2bytes '1048576', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | Not-null constraints: "tbcfileentry_tbcfientid_not_null" NOT NULL "tbcfientid" "tbcfileentry_filename_not_null" NOT NULL "filename" "tbcfileentry_filepath_not_null" NOT NULL "filepath" "tbcfileentry_created_at_not_null" NOT NULL "created_at" "tbcfileentry_status_not_null" NOT NULL "status" Server: sample FDW options: (schema 'DB2INST1', "table" 'TBCFILEENTRY') \d+ sample.tbcfileentry_sample Foreign table "sample.tbcfileentry_sample" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -------------+--------------------------------+-----------+----------+---------+------------------------------------------------------------------------------------------------------------------+----------+--------------+------------- tbcfientid | bigint | | not null | | (db2type '-5', db2size '19', db2bytes '8', db2chars '19', db2scale '0', db2null '0', db2ccsid '0') | plain | | filename | character varying(255) | | not null | | (db2type '12', db2size '255', db2bytes '255', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | filepath | text | | not null | | (db2type '-99', db2size '1048576', db2bytes '1048576', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | filesize | bigint | | | | (db2type '-5', db2size '19', db2bytes '8', db2chars '19', db2scale '0', db2null '1', db2ccsid '0') | plain | | filetype | character varying(100) | | | | (db2type '12', db2size '100', db2bytes '100', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | checksum | character varying(64) | | | | (db2type '12', db2size '64', db2bytes '64', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | owner | character varying(100) | | | | (db2type '12', db2size '100', db2bytes '100', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | created_at | timestamp(6) without time zone | | not null | | (db2type '93', db2size '26', db2bytes '16', db2chars '26', db2scale '6', db2null '0', db2ccsid '0') | plain | | modified_at | timestamp(6) without time zone | | | | (db2type '93', db2size '26', db2bytes '16', db2chars '26', db2scale '6', db2null '1', db2ccsid '0') | plain | | status | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | description | text | | | | (db2type '-99', db2size '1048576', db2bytes '1048576', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | Not-null constraints: "tbcfileentry_sample_tbcfientid_not_null" NOT NULL "tbcfientid" "tbcfileentry_sample_filename_not_null" NOT NULL "filename" "tbcfileentry_sample_filepath_not_null" NOT NULL "filepath" "tbcfileentry_sample_created_at_not_null" NOT NULL "created_at" "tbcfileentry_sample_status_not_null" NOT NULL "status" Server: sample FDW options: (schema 'DB2INST1', "table" 'TBCFILEENTRY_SAMPLE') EXPLAIN (analyze,verbose) SELECT * from sample.tbcfileentry_sample; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.tbcfileentry_sample (cost=100.00..124.74 rows=67 width=1196) (actual time=8.914..981.595 rows=1191.00 loops=1) Output: tbcfientid, filename, filepath, filesize, filetype, checksum, owner, created_at, modified_at, status, description DB2 query: SELECT "TBCFIENTID", "FILENAME", "FILEPATH", "FILESIZE", "FILETYPE", "CHECKSUM", "OWNER", "CREATED_AT", "MODIFIED_AT", "STATUS", "DESCRIPTION" FROM "DB2INST1"."TBCFILEENTRY_SAMPLE" DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "TBCFIENTID" , "FILENAME" , "FILEPATH" , "FILESIZE" , DB2 plan: "FILETYPE" , "CHECKSUM" , "OWNER" , "CREATED_AT" , DB2 plan: "MODIFIED_AT" , "STATUS" , "DESCRIPTION" DB2 plan: FROM "DB2INST1" ."TBCFILEENTRY_SAMPLE" DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 29,834015 DB2 plan: Estimated Cardinality = 1000,000000 DB2 plan: DB2 plan: Return Data to Application DB2 plan: | #Columns = 11 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=59 Planning Time: 2.701 ms Execution Time: 983.743 ms (52 rows) EXPLAIN (verbose) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tbcfientid) AS Perc_Cont FROM sample.tbcfileentry; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Foreign Scan (cost=112.80..148.62 rows=1 width=8) Output: (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((tbcfientid)::double precision))) DB2 query: SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "TBCFIENTID" ASC) FROM "DB2INST1"."TBCFILEENTRY" DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT PERCENTILE_CONT(0.5)WITHIN DB2 plan: GROUP (ORDER BY "TBCFIENTID" ASC) DB2 plan: FROM "DB2INST1" ."TBCFILEENTRY" DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 178,364655 DB2 plan: Estimated Cardinality = 1,000000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.TBCFILEENTRY ID = 3,16 DB2 plan: | tenantID = 0 DB2 plan: | Index Scan: Name = DB2INST1.PK_TBCFILEENTRY ID = 1 DB2 plan: | | Regular Index (Not Clustered) DB2 plan: | | Index Columns: DB2 plan: | | | 1: TBCFIENTID (Ascending) DB2 plan: | #Columns = 1 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | #Key Columns = 0 DB2 plan: | | Start Key: Beginning of Index DB2 plan: | | Stop Key: End of Index DB2 plan: | Index-Only Access DB2 plan: | Index Prefetch: Sequential(26), Readahead DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Index Predicate(s) DB2 plan: | | Predicate Aggregation DB2 plan: | | | Column Function(s) DB2 plan: Aggregation Completion DB2 plan: | Column Function(s) DB2 plan: Return Data to Application DB2 plan: | #Columns = 1 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: (70 rows) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tbcfientid) AS Perc_Cont FROM sample.tbcfileentry; perc_cont ----------- 5000.5 (1 row) SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbcfientid) AS Perc_Disc FROM sample.tbcfileentry; perc_disc ----------- 5000 (1 row) -- -- END of TC015 -- -- running tc016.sql \i ./test/sql/tc016.sql -- -- TC016: test between -- \d+ sample.employee; Foreign table "sample.employee" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -----------+-----------------------+-----------+----------+---------+----------------------------------------------------------------------------------------------------------------+----------+--------------+------------- empno | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208', key 'true') | extended | | firstnme | character varying(12) | | not null | | (db2type '12', db2size '12', db2bytes '12', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | midinit | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | lastname | character varying(15) | | not null | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | workdept | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | phoneno | character(4) | | | | (db2type '1', db2size '4', db2bytes '4', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | hiredate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | job | character(8) | | | | (db2type '1', db2size '8', db2bytes '8', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | edlevel | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | sex | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | birthdate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | salary | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | bonus | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | comm | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | Not-null constraints: "employee_empno_not_null" NOT NULL "empno" "employee_firstnme_not_null" NOT NULL "firstnme" "employee_lastname_not_null" NOT NULL "lastname" "employee_edlevel_not_null" NOT NULL "edlevel" Server: sample FDW options: (schema 'DB2INST1', "table" 'EMPLOYEE') explain (analyze,verbose) select * from sample.employee where lastname between 'LUTZ' and 'LEE' order by lastname; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..114.56 rows=1 width=258) (actual time=2.758..2.759 rows=0.00 loops=1) Output: empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm DB2 query: SELECT "EMPNO", "FIRSTNME", "MIDINIT", "LASTNAME", "WORKDEPT", "PHONENO", "HIREDATE", "JOB", "EDLEVEL", "SEX", "BIRTHDATE", "SALARY", "BONUS", "COMM" FROM "DB2INST1"."EMPLOYEE" WHERE (("LASTNAME" >= 'LUTZ')) AND (("LASTNAME" <= 'LEE')) ORDER BY "LASTNAME" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "FIRSTNME" , "MIDINIT" , "LASTNAME" , "WORKDEPT" , DB2 plan: "PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" , "SEX" , DB2 plan: "BIRTHDATE" , "SALARY" , "BONUS" , "COMM" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE (("LASTNAME" >='LUTZ' ))AND (("LASTNAME" <='LEE' )) DB2 plan: ORDER BY "LASTNAME" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 0,000038 DB2 plan: Estimated Cardinality = 0,000000 DB2 plan: DB2 plan: Table Constructor DB2 plan: | N-Rows DB2 plan: Residual Predicate(s) DB2 plan: | #Predicates = 1 DB2 plan: Return Data to Application DB2 plan: | #Columns = 14 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 5.113 ms Execution Time: 4.377 ms (56 rows) select * from sample.employee where lastname between 'LEE' and 'LUTZ' order by lastname; empno | firstnme | midinit | lastname | workdept | phoneno | hiredate | job | edlevel | sex | birthdate | salary | bonus | comm --------+----------+---------+-----------+----------+---------+------------+----------+---------+-----+------------+----------+--------+--------- 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1988-05-16 | SALESREP | 19 | M | 1959-11-05 | 66500.00 | 900.00 | 3720.00 000220 | JENNIFER | K | LUTZ | D11 | 0672 | 1998-08-29 | DESIGNER | 18 | F | 1978-03-19 | 49840.00 | 600.00 | 2387.00 (3 rows) -- explain (analyze,verbose) select * from sample.employee where salary between 10000.00 and 60000.00 order by salary; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..114.56 rows=1 width=258) (actual time=3.548..17.921 rows=26.00 loops=1) Output: empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm DB2 query: SELECT "EMPNO", "FIRSTNME", "MIDINIT", "LASTNAME", "WORKDEPT", "PHONENO", "HIREDATE", "JOB", "EDLEVEL", "SEX", "BIRTHDATE", "SALARY", "BONUS", "COMM" FROM "DB2INST1"."EMPLOYEE" WHERE (("SALARY" >= 10000.00)) AND (("SALARY" <= 60000.00)) ORDER BY "SALARY" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "FIRSTNME" , "MIDINIT" , "LASTNAME" , "WORKDEPT" , DB2 plan: "PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" , "SEX" , DB2 plan: "BIRTHDATE" , "SALARY" , "BONUS" , "COMM" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE (("SALARY" >=10000.00))AND (("SALARY" <=60000.00)) DB2 plan: ORDER BY "SALARY" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,819019 DB2 plan: Estimated Cardinality = 26,381359 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 14 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 2 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 14 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: SALARY (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 27,000000 DB2 plan: | | | | Row Width = 84 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 14 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 14 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=4 Planning Time: 4.269 ms Execution Time: 19.574 ms (88 rows) select * from sample.employee where salary between 10000.00 and 60000.00 order by salary; empno | firstnme | midinit | lastname | workdept | phoneno | hiredate | job | edlevel | sex | birthdate | salary | bonus | comm --------+-----------+---------+------------+----------+---------+------------+----------+---------+-----+------------+----------+---------+--------- 200340 | ROY | R | ALONZO | E21 | 5698 | 1997-07-05 | FIELDREP | 16 | M | 1956-05-17 | 31840.00 | 500.00 | 1907.00 000290 | JOHN | R | PARKER | E11 | 4502 | 2006-05-30 | OPERATOR | 12 | M | 1985-07-09 | 35340.00 | 300.00 | 1227.00 200330 | HELENA | | WONG | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | F | 1971-07-18 | 35370.00 | 500.00 | 2030.00 000310 | MAUDE | F | SETRIGHT | E11 | 3332 | 1994-09-12 | OPERATOR | 12 | F | 1961-04-21 | 35900.00 | 300.00 | 1272.00 200310 | MICHELLE | F | SPRINGER | E11 | 3332 | 1994-09-12 | OPERATOR | 12 | F | 1961-04-21 | 35900.00 | 300.00 | 1272.00 000280 | ETHEL | R | SCHNEIDER | E11 | 8997 | 1997-03-24 | OPERATOR | 17 | F | 1976-03-28 | 36250.00 | 500.00 | 2100.00 000270 | MARIA | L | PEREZ | D21 | 9001 | 2006-09-30 | CLERK | 15 | F | 2003-05-26 | 37380.00 | 500.00 | 2190.00 000300 | PHILIP | X | SMITH | E11 | 2095 | 2002-06-19 | OPERATOR | 14 | M | 1976-10-27 | 37750.00 | 400.00 | 1420.00 200240 | ROBERT | M | MONTEVERDE | D21 | 3780 | 2004-12-05 | CLERK | 17 | M | 1984-03-31 | 37760.00 | 600.00 | 2301.00 200120 | GREG | | ORLANDO | A00 | 2167 | 2002-05-05 | CLERK | 14 | M | 1972-10-18 | 39250.00 | 600.00 | 2340.00 000320 | RAMLAL | V | MEHTA | E21 | 9990 | 1995-07-07 | FIELDREP | 16 | M | 1962-08-11 | 39950.00 | 400.00 | 1596.00 000230 | JAMES | J | JEFFERSON | D21 | 2094 | 1996-11-21 | CLERK | 14 | M | 1980-05-30 | 42180.00 | 400.00 | 1774.00 000340 | JASON | R | GOUNOT | E21 | 5698 | 1977-05-05 | FIELDREP | 16 | M | 1956-05-17 | 43840.00 | 500.00 | 1907.00 000170 | MASATOSHI | J | YOSHIMURA | D11 | 2890 | 1999-09-15 | DESIGNER | 16 | M | 1981-01-05 | 44680.00 | 500.00 | 1974.00 000330 | WING | | LEE | E21 | 2103 | 2006-02-23 | FIELDREP | 14 | M | 1971-07-18 | 45370.00 | 500.00 | 2030.00 200280 | EILEEN | R | SCHWARTZ | E11 | 8997 | 1997-03-24 | OPERATOR | 17 | F | 1966-03-28 | 46250.00 | 500.00 | 2100.00 200010 | DIAN | J | HEMMINGER | A00 | 3978 | 1995-01-01 | SALESREP | 18 | F | 1973-08-14 | 46500.00 | 1000.00 | 4220.00 000260 | SYBIL | P | JOHNSON | D21 | 8953 | 2005-09-11 | CLERK | 16 | F | 1976-10-05 | 47250.00 | 300.00 | 1380.00 000240 | SALVATORE | M | MARINO | D21 | 3780 | 2004-12-05 | CLERK | 17 | M | 2002-03-31 | 48760.00 | 600.00 | 2301.00 000250 | DANIEL | S | SMITH | D21 | 0961 | 1999-10-30 | CLERK | 15 | M | 1969-11-12 | 49180.00 | 400.00 | 1534.00 000120 | SEAN | | O'CONNELL | A00 | 2167 | 1993-12-05 | CLERK | 14 | M | 1972-10-18 | 49250.00 | 600.00 | 2340.00 000220 | JENNIFER | K | LUTZ | D11 | 0672 | 1998-08-29 | DESIGNER | 18 | F | 1978-03-19 | 49840.00 | 600.00 | 2387.00 000190 | JAMES | H | WALKER | D11 | 2986 | 2004-07-26 | DESIGNER | 16 | M | 1982-06-25 | 50450.00 | 400.00 | 1636.00 000180 | MARILYN | S | SCOUTTEN | D11 | 1682 | 2003-07-07 | DESIGNER | 17 | F | 1979-02-21 | 51340.00 | 500.00 | 1707.00 000150 | BRUCE | | ADAMSON | D11 | 4510 | 2002-02-12 | DESIGNER | 16 | M | 1977-05-17 | 55280.00 | 500.00 | 2022.00 000200 | DAVID | | BROWN | D11 | 4501 | 2002-03-03 | DESIGNER | 16 | M | 1971-05-29 | 57740.00 | 600.00 | 2217.00 (26 rows) -- -- END of TC016 -- -- running tc017.sql \i ./test/sql/tc017.sql -- -- TC017: test multi-argument function pushdown (substr, replace, lpad, rpad, strpos, mod) -- \d+ sample.employee; Foreign table "sample.employee" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -----------+-----------------------+-----------+----------+---------+----------------------------------------------------------------------------------------------------------------+----------+--------------+------------- empno | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208', key 'true') | extended | | firstnme | character varying(12) | | not null | | (db2type '12', db2size '12', db2bytes '12', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | midinit | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | lastname | character varying(15) | | not null | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | workdept | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | phoneno | character(4) | | | | (db2type '1', db2size '4', db2bytes '4', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | hiredate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | job | character(8) | | | | (db2type '1', db2size '8', db2bytes '8', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | edlevel | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | sex | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | birthdate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | salary | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | bonus | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | comm | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | Not-null constraints: "employee_empno_not_null" NOT NULL "empno" "employee_firstnme_not_null" NOT NULL "firstnme" "employee_lastname_not_null" NOT NULL "lastname" "employee_edlevel_not_null" NOT NULL "edlevel" Server: sample FDW options: (schema 'DB2INST1', "table" 'EMPLOYEE') explain (analyze,verbose) select empno, lastname from sample.employee where substr(lastname,1,1) = 'L' order by empno; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.222..2.473 rows=3.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((substr("LASTNAME", 1, 1) = 'L')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((substr("LASTNAME" , 1, 1)='L' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,818185 DB2 plan: Estimated Cardinality = 3,805378 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 4,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 3.254 ms Execution Time: 3.443 ms (84 rows) select empno, lastname from sample.employee where substr(lastname,1,1) = 'L' order by empno; empno | lastname --------+----------- 000110 | LUCCHESSI 000220 | LUTZ 000330 | LEE (3 rows) -- explain (analyze,verbose) select empno, lastname from sample.employee where substr(lastname,2,3) = 'UTZ' order by empno; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.117..2.184 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((substr("LASTNAME", 2, 3) = 'UTZ')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((substr("LASTNAME" , 2, 3)='UTZ' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 4.767 ms Execution Time: 3.152 ms (84 rows) select empno, lastname from sample.employee where substr(lastname,2,3) = 'UTZ' order by empno; empno | lastname --------+---------- 000220 | LUTZ (1 row) -- explain (analyze,verbose) select empno, lastname from sample.employee where replace(lastname,'E','X') = 'LXX' order by empno; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.706..2.916 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((replace("LASTNAME", 'E', 'X') = 'LXX')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((replace("LASTNAME" , 'E' , 'X' )='LXX' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 7.133 ms Execution Time: 4.787 ms (84 rows) select empno, lastname from sample.employee where replace(lastname,'E','X') = 'LXX' order by empno; empno | lastname --------+---------- 000330 | LEE (1 row) -- explain (analyze,verbose) select empno, lastname from sample.employee where lpad(lastname,10,'*') = '******LUTZ' order by empno; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.039..2.103 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((lpad("LASTNAME", 10, '*') = '******LUTZ')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((lpad("LASTNAME" , 10, '*' )='******LUTZ' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 3.161 ms Execution Time: 3.068 ms (84 rows) select empno, lastname from sample.employee where lpad(lastname,10,'*') = '******LUTZ' order by empno; empno | lastname --------+---------- 000220 | LUTZ (1 row) -- explain (analyze,verbose) select empno, lastname from sample.employee where rpad(lastname,10,'*') = 'LUTZ******' order by empno; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.020..2.091 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((rpad("LASTNAME", 10, '*') = 'LUTZ******')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((rpad("LASTNAME" , 10, '*' )='LUTZ******' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 3.077 ms Execution Time: 3.057 ms (84 rows) select empno, lastname from sample.employee where rpad(lastname,10,'*') = 'LUTZ******' order by empno; empno | lastname --------+---------- 000220 | LUTZ (1 row) -- explain (analyze,verbose) select empno, lastname from sample.employee where strpos(lastname,'EE') = 2 order by empno; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.054..2.125 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((INSTR("LASTNAME", 'EE') = 2)) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((INSTR("LASTNAME" , 'EE' )=2)) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 2.980 ms Execution Time: 3.093 ms (84 rows) select empno, lastname from sample.employee where strpos(lastname,'EE') = 2 order by empno; empno | lastname --------+---------- 000330 | LEE (1 row) -- explain (analyze,verbose) select empno, edlevel from sample.employee where mod(edlevel,2) = 0 order by empno; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..138.23 rows=8 width=30) (actual time=2.213..5.263 rows=33.00 loops=1) Output: empno, edlevel DB2 query: SELECT "EMPNO", "EDLEVEL" FROM "DB2INST1"."EMPLOYEE" WHERE ((mod("EDLEVEL", 2) = 0)) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "EDLEVEL" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((mod("EDLEVEL" , 2)=0)) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 12 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 3.045 ms Execution Time: 6.231 ms (84 rows) select empno, edlevel from sample.employee where mod(edlevel,2) = 0 order by empno; empno | edlevel --------+--------- 000010 | 18 000020 | 18 000030 | 20 000050 | 16 000060 | 16 000070 | 16 000090 | 16 000100 | 14 000120 | 14 000130 | 16 000140 | 18 000150 | 16 000170 | 16 000190 | 16 000200 | 16 000220 | 18 000230 | 14 000260 | 16 000290 | 12 000300 | 14 000310 | 12 000320 | 16 000330 | 14 000340 | 16 200010 | 18 200120 | 14 200140 | 18 200170 | 16 200220 | 18 200310 | 12 200330 | 14 200340 | 16 300001 | 18 (33 rows) -- -- END of TC017 -- -- running tc018.sql \i ./test/sql/tc018.sql -- -- TC018: test translate() pushdown, including argument reordering -- -- PostgreSQL's translate(string, from, to) and DB2's TRANSLATE(expression, to-string, -- from-string) take the "from"/"to" arguments in opposite order; deparseFuncExpr() must -- swap them so the pushed-down predicate keeps PostgreSQL semantics. \d+ sample.employee; Foreign table "sample.employee" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description -----------+-----------------------+-----------+----------+---------+----------------------------------------------------------------------------------------------------------------+----------+--------------+------------- empno | character(6) | | not null | | (db2type '1', db2size '6', db2bytes '6', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208', key 'true') | extended | | firstnme | character varying(12) | | not null | | (db2type '12', db2size '12', db2bytes '12', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | midinit | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | lastname | character varying(15) | | not null | | (db2type '12', db2size '15', db2bytes '15', db2chars '0', db2scale '0', db2null '0', db2ccsid '1208') | extended | | workdept | character(3) | | | | (db2type '1', db2size '3', db2bytes '3', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | phoneno | character(4) | | | | (db2type '1', db2size '4', db2bytes '4', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | hiredate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | job | character(8) | | | | (db2type '1', db2size '8', db2bytes '8', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | edlevel | smallint | | not null | | (db2type '5', db2size '5', db2bytes '2', db2chars '5', db2scale '0', db2null '0', db2ccsid '0') | plain | | sex | character(1) | | | | (db2type '1', db2size '1', db2bytes '1', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | birthdate | date | | | | (db2type '91', db2size '10', db2bytes '6', db2chars '0', db2scale '0', db2null '1', db2ccsid '0') | plain | | salary | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | bonus | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | comm | numeric(9,2) | | | | (db2type '3', db2size '9', db2bytes '11', db2chars '9', db2scale '2', db2null '1', db2ccsid '0') | main | | Not-null constraints: "employee_empno_not_null" NOT NULL "empno" "employee_firstnme_not_null" NOT NULL "firstnme" "employee_lastname_not_null" NOT NULL "lastname" "employee_edlevel_not_null" NOT NULL "edlevel" Server: sample FDW options: (schema 'DB2INST1', "table" 'EMPLOYEE') explain (analyze,verbose) select empno, lastname from sample.employee where translate(lastname,'AEIOU','12345') = 'L5TZ' order by empno; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=2.085..2.156 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((TRANSLATE("LASTNAME", '12345', 'AEIOU') = 'L5TZ')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((TRANSLATE("LASTNAME" , '12345' , 'AEIOU' )='L5TZ' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 3.061 ms Execution Time: 3.132 ms (84 rows) select empno, lastname from sample.employee where translate(lastname,'AEIOU','12345') = 'L5TZ' order by empno; empno | lastname --------+---------- 000220 | LUTZ (1 row) -- explain (analyze,verbose) select empno, lastname from sample.employee where translate(lastname,'AEIOU','12345') = translate('LUTZ','AEIOU','12345') order by empno; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.12 rows=4 width=76) (actual time=1.107..1.273 rows=1.00 loops=1) Output: empno, lastname DB2 query: SELECT "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((TRANSLATE("LASTNAME", '12345', 'AEIOU') = 'L5TZ')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "EMPNO" , "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((TRANSLATE("LASTNAME" , '12345' , 'AEIOU' )='L5TZ' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816761 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Sargable Predicate(s) DB2 plan: | | Return Data to Application DB2 plan: | | | #Columns = 2 DB2 plan: Return Data Completion DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 7.559 ms Execution Time: 3.648 ms (84 rows) select empno, lastname from sample.employee where translate(lastname,'AEIOU','12345') = translate('LUTZ','AEIOU','12345') order by empno; empno | lastname --------+---------- 000220 | LUTZ (1 row) -- explain (analyze,verbose) select empno, translate(lastname,'AEIOU','12345') from sample.employee where translate(lastname,'AEIOU','12345') = translate('LUTZ','AEIOU','12345') order by empno; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.employee (cost=100.00..123.14 rows=4 width=60) (actual time=2.097..2.168 rows=1.00 loops=1) Output: empno, (translate((lastname)::text, 'AEIOU'::text, '12345'::text)) DB2 query: SELECT TRANSLATE("LASTNAME", '12345', 'AEIOU'), "EMPNO", "LASTNAME" FROM "DB2INST1"."EMPLOYEE" WHERE ((TRANSLATE("LASTNAME", '12345', 'AEIOU') = 'L5TZ')) ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT TRANSLATE("LASTNAME" , '12345' , 'AEIOU' ), "EMPNO" , DB2 plan: "LASTNAME" DB2 plan: FROM "DB2INST1" ."EMPLOYEE" DB2 plan: WHERE ((TRANSLATE("LASTNAME" , '12345' , 'AEIOU' )='L5TZ' )) DB2 plan: ORDER BY "EMPNO" ASC NULLS LAST DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 6,816846 DB2 plan: Estimated Cardinality = 1,680000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.EMPLOYEE ID = 2,6 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | May participate in Scan Sharing structures DB2 plan: | Scan may start anywhere and wrap, for completion DB2 plan: | Fast scan, for purposes of scan sharing management DB2 plan: | Scan can be throttled in scan sharing management DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Predicate(s) DB2 plan: | | #Predicates = 1 DB2 plan: | | Insert Into Sorted Temp Table ID = t1 DB2 plan: | | | tenantID = 0 DB2 plan: | | | #Columns = 2 DB2 plan: | | | #Sort Key Columns = 1 DB2 plan: | | | | Key 1: EMPNO (Ascending) DB2 plan: | | | Sortheap Allocation Parameters: DB2 plan: | | | | #Rows = 2,000000 DB2 plan: | | | | Row Width = 24 DB2 plan: | | | Piped DB2 plan: Sorted Temp Table Completion ID = t1 DB2 plan: Access Temp Table ID = t1 DB2 plan: | tenantID = 0 DB2 plan: | #Columns = 2 DB2 plan: | Relation Scan DB2 plan: | | Prefetch: Eligible DB2 plan: Return Data to Application DB2 plan: | #Columns = 3 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning Time: 3.420 ms Execution Time: 3.186 ms (83 rows) select empno, translate(lastname,'AEIOU','12345') from sample.employee where translate(lastname,'AEIOU','12345') = translate('LUTZ','AEIOU','12345') order by empno; empno | translate --------+----------- 000220 | L5TZ (1 row) -- -- END of TC018 -- -- running tc019.sql \i ./test/sql/tc019.sql -- -- TC019: test using a query as table for a fdw table -- CREATE FOREIGN TABLE sample.percent ( percent NUMERIC ) SERVER sample OPTIONS ( table '(SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY TBCFIENTID) AS percent FROM DB2INST1.TBCFILEENTRY)'); CREATE FOREIGN TABLE -- \d+ sample.percent; Foreign table "sample.percent" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ---------+---------+-----------+----------+---------+-------------+---------+--------------+------------- percent | numeric | | | | | main | | Server: sample FDW options: ("table" '(SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY TBCFIENTID) AS percent FROM DB2INST1.TBCFILEENTRY)') -- explain (analyze,verbose) select * from sample.percent; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on sample.percent (cost=100.00..431.64 rows=1462 width=32) (actual time=1.717..1.780 rows=1.00 loops=1) Output: percent DB2 query: SELECT "PERCENT" FROM (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY TBCFIENTID) AS percent FROM DB2INST1.TBCFILEENTRY) DB2 plan: DB2 plan: DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: DB2 Universal Database Version 12.1, 5622-044 (c) Copyright IBM Corp. 1991, 2022 DB2 plan: Licensed Material - Program Property of IBM DB2 plan: IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 plan: DB2 plan: ******************** DYNAMIC *************************************** DB2 plan: DB2 plan: ==================== STATEMENT ========================================== DB2 plan: DB2 plan: Isolation Level = Cursor Stability DB2 plan: Blocking = Block Unambiguous Cursors DB2 plan: Query Optimization Class = 5 DB2 plan: DB2 plan: Partition Parallel = No DB2 plan: Intra-Partition Parallel = No DB2 plan: DB2 plan: SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", DB2 plan: "SYSHADOOP", "DB2INST1" DB2 plan: DB2 plan: DB2 plan: Statement: DB2 plan: DB2 plan: SELECT "PERCENT" DB2 plan: FROM DB2 plan: (SELECT PERCENTILE_CONT(0.9)WITHIN DB2 plan: GROUP (ORDER BY TBCFIENTID)AS percent DB2 plan: FROM DB2INST1.TBCFILEENTRY) DB2 plan: DB2 plan: DB2 plan: compiledInTenantID = 0 DB2 plan: DB2 plan: Section Code Page = 1208 DB2 plan: DB2 plan: Estimated Cost = 178,364655 DB2 plan: Estimated Cardinality = 1,000000 DB2 plan: DB2 plan: Access Table Name = DB2INST1.TBCFILEENTRY ID = 3,16 DB2 plan: | tenantID = 0 DB2 plan: | Index Scan: Name = DB2INST1.PK_TBCFILEENTRY ID = 1 DB2 plan: | | Regular Index (Not Clustered) DB2 plan: | | Index Columns: DB2 plan: | | | 1: TBCFIENTID (Ascending) DB2 plan: | #Columns = 1 DB2 plan: | Skip Inserted Rows DB2 plan: | Avoid Locking Committed Data DB2 plan: | Currently Committed for Cursor Stability DB2 plan: | #Key Columns = 0 DB2 plan: | | Start Key: Beginning of Index DB2 plan: | | Stop Key: End of Index DB2 plan: | Index-Only Access DB2 plan: | Index Prefetch: Sequential(26), Readahead DB2 plan: | Lock Intents DB2 plan: | | Table: Intent Share DB2 plan: | | Row : Next Key Share DB2 plan: | Sargable Index Predicate(s) DB2 plan: | | Predicate Aggregation DB2 plan: | | | Column Function(s) DB2 plan: Aggregation Completion DB2 plan: | Column Function(s) DB2 plan: Return Data to Application DB2 plan: | #Columns = 1 DB2 plan: DB2 plan: End of section DB2 plan: DB2 plan: Planning: Buffers: shared hit=9 Planning Time: 2.452 ms Execution Time: 2.023 ms (76 rows) select * from sample.percent; percent ------------------ 9000.10000000000 (1 row) -- -- END of TC019 -- -- running tc020.sql \i ./test/sql/tc020.sql -- -- TC020: CLOB round-trip correctness -- -- Regression test for bugs in db2GetLob.c's chunked LOB fetch that -- corrupted multi-byte CLOB content: -- 1. column->colType (the raw DB2 CLI SQL_CLOB constant, -99) was -- compared against the FDW's internal nonSQLType enum value -- DB2_CLOB (24) instead, so CLOB columns were always fetched via -- SQL_C_BINARY (no CCSID conversion) rather than SQL_C_CHAR. -- 2. db2CheckErr() maps SQL_SUCCESS_WITH_INFO to SQL_SUCCESS, so the -- chunked-fetch loop's "while (rc == SQL_SUCCESS_WITH_INFO)" never -- iterated past the first LOB_CHUNK_SIZE (8192 byte) chunk, -- silently truncating any larger CLOB/BLOB value. -- 3. The realloc() call for subsequent chunks did not reserve a byte -- for the eventual string terminator: a 1-byte heap overflow for -- any LOB spanning more than one chunk. -- -- Requires DB2INST1.CLOBTEST(ID INTEGER NOT NULL PRIMARY KEY, TXT CLOB(10K)) -- to pre-exist in the SAMPLE database, populated with: -- INSERT INTO DB2INST1.CLOBTEST (ID, TXT) VALUES -- (1, 'Gruesse aus Muenchen: Grüße, 日本語テスト 😀'), -- (2, REPEAT('x', 8191) || 'ü' || REPEAT('y', 100)); -- Row 2 places a 2-byte UTF-8 character exactly across the FDW's -- 8192-byte LOB fetch chunk boundary. -- (Already picked up as sample.clobtest by tcstart.sql's IMPORT FOREIGN -- SCHEMA, same as any other pre-existing DB2INST1 table.) -- \d+ sample.clobtest; Foreign table "sample.clobtest" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------------------------------------------------------------------------------------------------------------+----------+--------------+------------- id | integer | | not null | | (db2type '4', db2size '10', db2bytes '4', db2chars '10', db2scale '0', db2null '0', db2ccsid '0', key 'true') | plain | | txt | text | | | | (db2type '-99', db2size '10240', db2bytes '10240', db2chars '0', db2scale '0', db2null '1', db2ccsid '1208') | extended | | Not-null constraints: "clobtest_id_not_null" NOT NULL "id" Server: sample FDW options: (schema 'DB2INST1', "table" 'CLOBTEST') -- -- short, single-chunk multi-byte content (Latin-1 accents, CJK, emoji) SELECT id, length(txt) AS charlen, octet_length(txt) AS bytelen, txt = 'Gruesse aus Muenchen: Grüße, 日本語テスト 😀' AS content_matches FROM sample.clobtest WHERE id = 1; id | charlen | bytelen | content_matches ----+---------+---------+----------------- 1 | 37 | 54 | t (1 row) -- -- multi-chunk content with a multi-byte character straddling the -- 8192-byte LOB_CHUNK_SIZE boundary SELECT id, length(txt) AS charlen, octet_length(txt) AS bytelen, txt = (repeat('x', 8191) || 'ü' || repeat('y', 100)) AS content_matches FROM sample.clobtest WHERE id = 2; id | charlen | bytelen | content_matches ----+---------+---------+----------------- 2 | 8292 | 8293 | t (1 row) -- -- END of TC020 -- -- testcases ended -- starting cleanup \i ./test/sql/tcend.sql \c postgres You are now connected to database "postgres" as user "postgres". DROP DATABASE regtest; DROP DATABASE -- test finished