Schema-Concept issues

[lbreddemann]

Dear all,

playing around with the implmentation of the schema concept in MaxDB 7.6.01 Build 10 I came acrosse some oddities.

1. There are still tables without a schemaname assigned in the catalog while many of the catalog-tables do have a schemaname.

This looks inconsistent to me.

select owner, schemaname, tablename from tables where owner='SUPERDBA'

OWNER;SCHEMANAME;TABLENAME
[...]
SUPERDBA;SUPERDBA;DBAN_NUM_HISTORY_PAGES
SUPERDBA;SUPERDBA;DBAN_NUM_REGION_WAITERS
SUPERDBA;SUPERDBA;DBAN_NUM_RUNNABLE_USERTASKS
SUPERDBA;SUPERDBA;DBAN_NUM_RUNNABLE_US_P_UKT
SUPERDBA;SUPERDBA;DBAN_NUM_RUNNING_USERTASKS
SUPERDBA;SUPERDBA;DBAN_NUM_TABS_FEW_SAMPLE_ROWS
SUPERDBA;SUPERDBA;DBAN_NUM_TRACES_ACTIVATED
SUPERDBA;SUPERDBA;DBAN_NUM_VBEGEXCL_USERTASKS
SUPERDBA;SUPERDBA;DBAN_NUM_VWAIT_USERTASKS
SUPERDBA;SUPERDBA;DBAN_REGION_BLOCKERS_AR
[...]
SUPERDBA;;ACTIVECONFIGURATION
SUPERDBA;;ALLOCATORSTATISTIC
SUPERDBA;;ALL_CATALOG
SUPERDBA;;ALL_COL_COMMENTS
SUPERDBA;;ALL_COL_PRIVS
SUPERDBA;;ALL_COL_PRIVS_MADE
[...]

2. It does not seem to be clear to the MaxDB who is the owner of tables that belong to a schema which is NOT names after the user who owns that schema:

select owner, schemaname, tablename from tables where owner='MONA'
  - No result

select owner, schemaname, tablename from tables where schemaname='HOTEL'

OWNER;SCHEMANAME;TABLENAME
MONA;HOTEL;CITY
MONA;HOTEL;CUSTOMER
MONA;HOTEL;CUSTOMER_ADDR
MONA;HOTEL;CUSTOM_HOTEL
MONA;HOTEL;EMPLOYEE
MONA;HOTEL;HOTEL
MONA;HOTEL;HOTEL_ADDR
MONA;HOTEL;RESERVATION
MONA;HOTEL;ROOM

but:

select owner, schemaname from schemas where owner='MONA'
OWNER                            | SCHEMANAME                       |
-------------------------------- | -------------------------------- |
MONA                             | MONA                             |
MONA                             | HOTEL                            |

Please clarify what's wrong here.

Thanks and best regards,

Lars

[martin_koerner · Accepted solution]

Hello Lars,

1. 1:

the database objects you have found without a schemaname are

of type "public synonym". Public synonyms don't belong to a

schema.

1. 2:

The result of your first SELECT is caused by setting of parameter

SERVERDBFOR_SAP. The default value is "YES". With this setting

the query

select owner, schemaname, tablename from tables where owner='MONA'

is changed internally to

select owner, schemaname, tablename from tables where schemaname='MONA'

Schema 'MONA' does exist but there are no tables within this schema. That's why there is no result. If you change the mentioned parameter to "NO" you will get a result because there are tables for owner 'MONA' in another schema ('HOTEL'). The internal change of owner to schemaname has been done with regards to compatibility with older R/3 releases. Per default for every owner an identically named schema does exist.

Your second SELECT shows the tables in schema 'HOTEL' for every user who owns tables within this schema.

Hope this explanation helps.

Best regards,

Martin

Message was edited by:

Martin Koerner

Message was edited by:

Martin Koerner

[lbreddemann · Accepted solution]

HI Martin,

thanks for your quick response.

Anyhow, there are still some points I'd like to make about this:

1. OK, it's clear that PUBLIC objects don't belong to a specific DB User. But that does not mean, that they do not belong to anyone. The opposite is true: these objects are owned by everybody.

So it would be semantically correct to have this information in this view. Either by using the OWNER field with some special value (Oracle uses "PUBLIC" for this) or by a additional field that indicates public ownership.

2. That's both interesting and disappointing, as this is nowhere documented. The documentation for this parameter just states: "If it's a db instance for an SAP product, than this parameter is set to TRUE".

This should be improved.

Perhabs you can enter two low prio PTS for this.

Thanks and best regards,

Lars

[martin_koerner · Accepted solution]

Hi Lars,

1. 1

Public synonyms are not owned by everybody. Owner is the one who created this synonym. But public synonyms can be used by everybody. This is represented by value 'YES' in column 'PUBLIC' within table domain.synonyms.

Example: Please check the output of

select * from domain.synonyms where synonymname = 'ACTIVECONFIGURATION'

1. 2

Our point of view is that the documentation of parameter SERVERDBFOR_SAP is sufficient. If more information is required (like my explanation above) we can create an internal note. Do you mean this is really necessary? So far the users set this parameter according to the use of the database within or without SAP and do not question the internals behind.

Best regards,

Martin

[lbreddemann · Accepted solution]

HI Martin,

Ok.

ad 1)

You're right, the owner is the creator of the object.

Still the SCHEMANAME should not be NULL for ojects that are PUBLIC.

As SCHEMAS are a mechanism for namespace handling, leaving this the way it is would mean to have a inconstistency here.

It is possible to do a select on a public synonym via PUBLIC.<name> so it should be correct to see PUBLIC as the schemaname then.

ad 2)

Well, this is of course a matter of taste. In my opinion, the parameter is not enough documented as there was no way to conclude from my question that it has anything to do with it.

I'm not asking for showing internals to public audience but to enable database users to get to such non-bug solutions themselves.

Thanks and best regards

Lars

[martin_koerner · Accepted solution]

Hi Lars,

1. 1

you are right concerning the 'PUBLIC.SYNONYMS' construct. We don't want to enter value 'PUBLIC' in column 'SCHEMANAME' because there could be already a user or schema with this name. Command 'GRANT TO PUBLIC' would cause confusion in this case.

1. 2

I will create an internal note containing my explanation above. Send you an e-mail when done.

Best regards,

Martin

[lbreddemann · Accepted solution]

Hi Martin,

thanks for the update.

KR Lars

[martin_koerner]

Hi Lars,

I have created note 1033494. This note has been released internally. As it is quite new it's only available in German so far. Translation into English will follow.

Best regards,

Martin