RE: HDR: MULTISET gives -626 on secondary
Running the query first on primary doesn't help on secondary
I've filed a bug, waiting for the results
As for extensibility features on secondary...
MULTISET is such a wonderful way to convert small
subquery result set to a single string to display
it in a single cell of the huge web-based report
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: ajaykg68@yahoo.com [mailto:ajaykg68@yahoo.com]
>
> This is a limitation on using extensibility on HDR secondary. I think
> workaround mentioned in release notes will work (provided you wait
> for DRTIMEOUT after running the query on primary).
>
> From 9.40 release notes:
>
> Limitation on Using Complex Types in HDR Environments
> Queries involving complex data types (like LIST, SET, or MULTISET, or
> named or unnamed ROW types), might fail against the secondary server
> in an HDR environment. A workaround is to run the same query on the
> primary server first, and then on the secondary server.
>
> -Ajay Gupta
>
> "Obnoxio The Clown" <obnoxio@serendipita.com> wrote in message
> news:<cf9u2q$eu0$1@news.xmission.com>...
> > Alexey Sonkin said:
> > > 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
> >
> > That is *seriously* weird. It looks like it doesn't like the date. Have
> > you logged a case?
> >
> > --
> > Bye now,
> > Obnoxio
> >
> > "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule"
> > - Coluche
> >
> > "I'm trying to see things your way, but I can't get my head up my ass"
> > - JCH
> >
> > "Ogni uomo mi guarda come se fossi una testa di cazzo"
> > - Marco
> >
> > http://www.catb.org/~esr/faqs/smart-questions.html
> > sending to informix-list
sending to informix-list