Subselect or union operations impossible to execute in HADR standby

Question

Trying to execute some queries in a HADR database with RoS (Read only Standby) with a union operator or with a subselect, I got the error SQL1773N reason code 5.

What is the reason? they are operations that do not generate writes.

Union

with hist(start_time, operationtype) as (
 select start_time, operationtype
 from sysibmadm.db_history
 where operation = 'B' )
select 'delta', timestampdiff(8, current timestamp - char(timestamp(max(start_time))))
from hist
where operationtype = 'D' or operationtype = 'E'
union all
select 'delta', timestampdiff(8, current timestamp - char(timestamp(max(start_time))))
from hist
where operationtype = 'I' or operationtype = 'O'

Subselect

with hist(start_time, operationtype) as (
 select start_time, operationtype
 from sysibmadm.db_history
 where operation = 'B' )
select 'delta', operationtype, start_time, timestampdiff(8, current timestamp - char(timestamp(start_time)))
from hist
where start_time = (
 select max(start_time)
 from hist
 where operationtype = 'D' or operationtype = 'E')

It could be (just a guess here) that it has to materialize those sub-queries into a work file, which still counts as a "write" on the standby. — bhamby, Apr 13 '13 at 15:44
I agree with you, however this is not defined in the documentation. — AngocA, Apr 16 '13 at 12:21
The docs say "Queries on the standby database can use only SMS system temporary table spaces." which may be relevant here, would be good to check that. — mao, Oct 19 '17 at 07:54

score 2 · Answer 1 · edited Oct 17 '17 at 21:47

This seems to be a problem specific to sysibmadm.db_history.
Tried the following union all and subSelect on Standby with ROS enabled, and both worked fine

CREATE TABLE TAB101  (
id bigint NOT NULL,
createTimestamp TIMESTAMP NOT NULL,
primary key (id))

insert into tab101 (id, CREATETIMESTAMP) values 
(1, current timestamp - 35 minutes), 
(2, current timestamp - 30 minutes),
(3, current timestamp - 25 minutes), 
(4, current timestamp - 20 minutes),
(5, current timestamp - 15 minutes), 
(6, current timestamp - 10 minutes),
(7, current timestamp - 5 minutes), 
(8, current timestamp)

with tempTab101 (id, CREATETIMESTAMP) as (
  select id, CREATETIMESTAMP from tab101
)
select id, CREATETIMESTAMP from tempTab101 
where id > 1
union all
select id, CREATETIMESTAMP from tempTab101
where id <= 10

with tempTab101 (id, CREATETIMESTAMP) as (
  select id, CREATETIMESTAMP from tab101
)
select * from tempTab101
where id = (
  select id from tempTab101 where id=2
)

Subselect or union operations impossible to execute in HADR standby

1 Answers1