More of a finding, than a question, I'll add an answer in a minute.
The other day I stumbled upon an application that used several tables with the same name, but in different schemas. Example:
create table s1.parent (x int not null primary key); create table s1.child (y int not null primary key, x int not null references s1.parent(x)); create table s2.parent (x int not null primary key); create table s2.child (y int not null primary key, x int not null references s2.parent(x));
The application uses the pattern:
set schema s1; select * from parent x join child y on ...
Despite that the question have been executed against several schemas, there is only one row in:
select num_executions, rows_read, stmt_text from sysibmadm.snapdyn_sql where stmt_text like '....%'
and the numbers appear to be cumulative for all schemas. Why is that, and how do I figure out metrics for the different schemas?