1

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?

1

It's an expected behavior. There is an internal Technote on this.

The COMPILATION_ENV table function helps with the current schema setting for each executable_id.

1

The more modern table function:

select EXECUTABLE_ID, STMT_TEXT, num_executions 
from TABLE(MON_GET_PKG_CACHE_STMT('D', NULL, NULL, -2)) as T 
where stmt_text like 'select *%'"

returns individual metrics for the different schemas.

It still takes some further examination to find out what executable_id that belongs to each schema, so I much prefer qualified table names in queries over the set schema pattern.

If tables are not qualified by schema, the schema can be detected by:

select EXECUTABLE_ID, VALUE as SCHEMA, STMT_TEXT, num_executions
from TABLE(MON_GET_PKG_CACHE_STMT('D', NULL, NULL, -2)) as T 
CROSS JOIN TABLE(COMPILATION_ENV(T.COMP_ENV_DESC)) S 
where stmt_text like 'select *%' 
  and name = 'SCHEMA'

More details on COMPILATION_ENV can be found in the answer provided by Mark Barinstein

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.