[A DB2 database contains the tables and is running on an AS400 minicomputer. The application ODBC connects to a local network Adaptive Server Anywhere database. The ASA database ODBC connects to the DB2 over the company network. The ASA configuration uses 3 sql statements:
1. CREATE SERVER remotedb2 CLASS 'db2odbc' USING 'remote_db2'; (remote_db2 = ODBC DSN)
2. CREATE EXTERNLOGIN appuser TO remotedb2 REMOTE LOGIN user... IDENTIFIED BY pw...;
3. CREATE EXISTING TABLE p_itmst AT 'remotedb2..APLUSHBFPL.itmst'; (for each table accessed, "p_" prefix = proxy table) Edit 5/3/2021]
DW painter: retrieval hangs when using 6 retrieval arguments (sql syntax not graphic).
DB painter: retrieves ok (argument values hardcoded).
If the dw retrieval arguments are unused (values hardcoded into syntax), retrieves ok. If 1 retrieval argument is used the dw painter still hangs (5 hardcoded). See the sql select below (nothing special).
Small selects with few retrieval arguments retreive ok (syntax or graphic).
If DB profile ODBC Disable Bind is checked, 1 retrieval argument, retrieves ok. If all 6 retrieval arguments used, only some rows are retrieved.
In PB 10.5 this dw retrieves ok. When PB 19 hangs above, have to close/crash PB to start over. No changes have been made after migration. What to do?
Development details...
PB: 2019 R3 build 2703
PC: win10 Pro, ver 20H2
DB: Adaptive Server Anywhere 9.0.2.3951 64 bit, network server
DB profile: default settings (ODBC DSN, user, password).
Tables in query: proxy tables on server [Actual tables in DB2 database. Edit 5/3/2021]
The sql (nothing special):
//retrieval arguments (Edit 4/30/2021: removed abbreviations for argument type).
string as_part_num
string as_business_unit
string as_warehouse
string array as_orderstatus
number al_begindate
number al_enddate
//DW painter: data source (with retrieval arguments) [Added comments. Edit 5/3/2021]
SELECT "p_itmst"."imitno", p_ordet.odorno, p_ordet.odwhid, sum(p_ordet.odqtor) AS c_sumqtyordered
FROM "p_itmst", //part
"p_ordet", //order detail
"p_orhed" //order header
WHERE ( "p_itmst"."imitno" = "p_ordet"."oditno" ) and //joins
( "p_ordet"."odcono" = "p_orhed"."ohcono" ) and
( "p_ordet"."odorno" = "p_orhed"."ohorno" ) and
( "p_ordet"."odorgn" = "p_orhed"."ohorgn" ) and
( ( rtrim("p_itmst"."imitno") like :as_part_num ) AND //part criteria
( "p_itmst"."imsusp" <> 'S' ) AND
( "p_itmst"."imvnno" = :as_business_unit ) AND
p_ordet.odcsno <> 6844 AND //order detail criteria
( "p_ordet"."odwhid" like :as_warehouse ) AND
"p_ordet"."odwhid" <> 'AP' AND
( "p_ordet"."odorst" not in (:as_orderstatus) ) AND
substr("p_orhed"."ohorno", 2, 1) in ('0','1','2','3','4','5','6','7','8','9') and //>= '0' //order header criteria
( "p_orhed"."ohortp" <> 'Q' ) AND
((p_orhed.ohcsno > 7000 and p_orhed.ohcsno < 8000) or p_ordet.odohld <> 'CR') AND
( "p_orhed"."ohrsdt" between :al_begindate and :al_enddate ) )
GROUP BY "p_itmst"."imitno", p_ordet.odorno, p_ordet.odwhid
//DB painter (without retrieval arguments)
SELECT p_itmst.imitno, p_ordet.odorno, p_ordet.odwhid, sum(p_ordet.odqtor) AS c_sumqtyordered
FROM p_itmst, //part
p_ordet, //order detail
p_orhed //order header
WHERE ( p_itmst.imitno = p_ordet.oditno ) and //joins
( p_ordet.odcono = p_orhed.ohcono ) and
( p_ordet.odorno = p_orhed.ohorno ) and
( p_ordet.odorgn = p_orhed.ohorgn ) and
( ( rtrim(p_itmst.imitno) like '%' ) AND //part criteria
( p_itmst.imsusp <> 'S' ) AND
( p_itmst.imvnno = 'MXBU' ) AND
p_ordet.odcsno <> 6844 AND //order detail criteria
( p_ordet.odwhid like '%' ) AND
p_ordet.odwhid <> 'AP' AND
( p_ordet.odorst not in ('3','4') ) AND //[quote each list item. Edit 5/6/2021]
substr(p_orhed.ohorno, 2, 1) in ('0','1','2','3','4','5','6','7','8','9') and //order header criteria
( p_orhed.ohortp <> 'Q' ) AND
((p_orhed.ohcsno > 7000 and p_orhed.ohcsno < 8000) or p_ordet.odohld <> 'CR') AND
( p_orhed.ohrsdt between 210420 and 210425 ) )
GROUP BY p_itmst.imitno, p_ordet.odorno, p_ordet.odwhid
;
