Skip to Content

Schema-Concept issues

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Feb 27, 2007 at 01:11 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 02, 2007 at 02:07 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.