RE: MULTISET gives -626 on secondary
Hi, everybody,
I have some useful information regarding
MULTISET behavior on HDR secondary.
It appeared, that the problem doesn't relate to MULTISET itself;
the problem is that MULTUSET is used for
the COMPLEX DATA TYPE: each line of the multiset-internal
select statement is considered by IDS as having the data type
'structure..', that is having EXTENDED DATA TYPE.
For each new EXTENDED DATA TYPE engine is trying to make
an insert into SYSXTDTYPES system table.
Obviously, this insert is impossible on Secondary.
The workaround is to run this particular SQL first on
Primary, and have the new 'extended type' permanently inserted into
the SYSXTDTYPES table. After that, SQL can be run successfully
on Secondary without the -626 error
Obviously, this is IDS design problem:
HDR in the Universal Server was introduced only in version 9.2x,
while MULTISET was available since 9.0
Programmers were not keeping in mind HDR in 1996-1997, when
Informix Universal Server was designed
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: Alexey Sonkin
>
> Hi, everybody,
>
> We've encountered very unpleasant problem:
> a lot of our reports (containing MULTISET in SELECT statement) give -626
> error
> when run on secondary server in HDR pair , while they run OK on primary
>
> Steps to reproduce (IDS 9.40uc4, logged database)
>
> ---- PRIMARY ----------
> set isolation to dirty read;
> set lock mode to wait 100;>
> create table client_request (req_id serial , descr varchar(30));
> alter table client_request add constraint primary key (req_id) constraint> cl_req_pk;
>
> create table transactions
> (tx_id serial not null,
> amount money(10,2),
> request_id int,
> tx_date date,> request_origin char(4));
> alter table transactions add constraint primary key (tx_id) constraint> tx_pk;
>
> insert into client_request values (1, 'aaaa');
> insert into client_request values (2, 'bbbb');>
> insert into transactions values(1, 1.0, 1, TODAY, 'wwww');
> insert into transactions values(2, 2.0, 1, TODAY, 'wwww');
> insert into transactions values(3, 3.0, 2, TODAY, 'wwww');>
> SELECT {+ FIRST_ROWS} FIRST 150
> SUBSTR(MULTISET(
> SELECT
> t.amount as amount,
> t.tx_date as date
> FROM transactions t
> WHERE req.req_id = t.request_id
> )::lvarchar,1,2048)
> as related_transactions
> FROM client_request req <------ that SQL runs OK on primary and gives
> -626 on secondary
> ---------------------
>
> ---------- SECONDARY ----
> --- SQL-1
> SELECT {+ FIRST_ROWS} FIRST 150
> SUBSTR(MULTISET(
> SELECT
> t.amount as amount,
> t.tx_date as date
> FROM transactions t
> WHERE req.req_id = t.request_id
> )::lvarchar,1,2048)
> as related_transactions
> FROM client_request req <---- ERROR 626
>
> --- SQL-2
> SELECT {+ FIRST_ROWS} FIRST 150
> SUBSTR(MULTISET(
> SELECT
> t.amount as amount
> FROM transactions t
> WHERE req.req_id = t.request_id
> )::lvarchar,1,2048)
> as related_transactions
> FROM client_request req <---- NO ERROR !!!!!!!!!!!!!!!!!!!!!
>
> --- SQL-3
> SELECT {+ FIRST_ROWS} FIRST 150
> SUBSTR(MULTISET(
> SELECT
> t.tx_date as date
> FROM transactions t
> WHERE req.req_id = t.request_id
> )::lvarchar,1,2048)
> as related_transactions
> FROM client_request req <---- ERROR 626 AGAIN
>
> --------------------------
>
> Please, note, that the difference between SQL-2 and SQL-3
> is only in the returned datatype
>
> best regards,
> Alexey Sonkin
>
>
>
> sending to informix-list
sending to informix-list