Results 1 to 12 of 12
  1. #1
    Join Date
    May 2009
    Posts
    25

    Unanswered: Database Creation

    The DB2 9 Certification Guide lists the steps by which a database is created and says they occur in exactly that order. The following two steps seem out of order to me.

    5. The system catalog tables and views are created
    After the table space SYSCATSPACE is created, a special set of tables, known as the system catalog tables, is constructed within that table space.
    8. Four schemas are created
    Once the system catalog tables and views are created, the following schemas are created: SYSIBM, SYSCAT, SYSSTAT, and SYSFUN.
    My understanding was that the catalog tables and views were in the SYSCAT schema so that it wouldn't be possible to create the tables first and the schema second. Is that correct?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 is a little different than some other databases in that you don't have to explicity create a schema, and that was not even allowed in initial releases years ago. A schema is implicitly created when you create an object with a new schema name.

    Also, the catalog tables are DB2 tables, and information about tables is stored in the catalog (including the catalog tables) so it is a chicken and egg thing as to which comes first.

    The catalog tables are in the SYSIBM schema and the views in the SYSCAT schema.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2009
    Posts
    25
    Quote Originally Posted by Marcus_A
    A schema is implicitly created when you create an object with a new schema name. ... The catalog tables are in the SYSIBM schema and the views in the SYSCAT schema.
    Hmm. That seems to confirm my suspicions. If the catalog tables are in the SYSIBM schema, and this schema is created implicitly at step 5 by the creation of the tables that belong to it, then the SYSIBM schema cannot be created again explicitly at step 8.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2farmer
    Hmm. That seems to confirm my suspicions. If the catalog tables are in the SYSIBM schema, and this schema is created implicitly at step 5 by the creation of the tables that belong to it, then the SYSIBM schema cannot be created again explicitly at step 8.
    It's a little more complicated than that. The catalog tables are not created with a "create table" statement, since there is not a SYSIBM.SYSTABLES table that already exists in which to insert the row indicating that a new table is being created (along with the other necessary catalog tables that would needed like SYSIBM.SYSCOLUMNS). So the way the initial catalog tables are created is something like the big bang theory of how the universe was created. It is beyond our comprehension to fully understand it (and will likely not appear as a test question, so you can move onto something else).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2009
    Posts
    25
    Quote Originally Posted by Marcus_A
    It is beyond our comprehension to fully understand it (and will likely not appear as a test question, so you can move onto something else).
    Thanks, I will. :-)

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The irony is the new 'modern' relational database (DB2) has to have it's starting point (the System catalog tables) created with the 'old' hierarchical database (IMS).

    At least on z/OS. I am not sure about LUW but it has to be something similar because you can't have a relational database without System catalog tables. And you can't create the System catalog tables as a relational database because no System catalog tables exist at the time. Sort of a 'chicken and egg' syndrome.

  7. #7
    Join Date
    Feb 2009
    Posts
    114
    Big bang creates the catalog and user tables subsequently evolve therefrom. A farmer could say that they are grown from the catalog

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Stealth_DBA
    ...you can't have a relational database without System catalog tables. And you can't create the System catalog tables as a relational database because no System catalog tables exist at the time. Sort of a 'chicken and egg' syndrome.
    I already said that above, including the exact phrase "chicken and egg" so you are being repetitive and guilty of plagiarism.

    Let's move on to another subject.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2009
    Posts
    114
    Hey Stealth! Next time you try to plagiarize from our Grandstander Superior, you will be taken out back and shot until dead!! ...

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2dummy1, sorry, I hadn't realized that 'chicken and egg' had been copyrighted.

    Besides, my main point was the irony of a relational database having as its foundation a hierarchical database.

    Sorry again for repeating previous post...

  11. #11
    Join Date
    Feb 2009
    Posts
    114
    But of course, IMS is also known as DB1. And both are in bldg C at 555 Bailey Ave - there has to be a connection

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Stealth_DBA
    db2dummy1, sorry, I hadn't realized that 'chicken and egg' had been copyrighted.

    Besides, my main point was the irony of a relational database having as its foundation a hierarchical database.

    Sorry again for repeating previous post...
    The DB2 for z/OS catalog tables do have hierarchical pointers (which we can't see) that resemble IMS but I don't really think it works that way in DB2 for LUW. I think DB2 for LUW just uses the big bang theory.

    This thread clearly deals with DB2 for LUW (although I know all the DB2 for z/OS DBA's are feeling neglected and like to chime-in even when off-topic).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •