Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Comparison of SQL Server and UDB

    Or Compare z/OS and UDB

    I need to understand the differences in Terminology

    For example, on out z/OS installation, you have a subsystem. In that sub-system, you create Databases, under that you have all the collections of "things" unique to that database (and owner)

    Is a UDB Schema a z/OS Owner?

    If anyone has a link for a SQL Server to UDB Comparison/explanation, that would be great
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jul 2004
    Posts
    306

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A DB2 z/OS subsystem is roughly equivalent to a database in DB2 LUW (no longer called UDB). This is the level of the DB2 System Catalog for both of them.

    However, in LUW one would typically not mix multiple applications in the same database (unless very small), whereas you might have just one (or few) Production DB2 z/OS subsytems for all your applications.

    A DB2 z/OS database is just a collection of tablespaces (and tables) and has no real equivalent in DB2 LUW. But if you did have two sets of tables that were for completely different applications in the same DB2 subsystem, you probably would want to make sure they were in different DB2 z/OS databases There are some physical things at z/OS database level such as DBD's, and if you have multiple unrelated tables for different applications in the same subsystem, you want them in different databases to keep the DBD a reasonable size. Some temporary objects (like temp tables) automatically are created in a specific DB2 controlled database, and if you don't specify the database, the object will go to the default database.

    In DB2 LUW, schema has little meaning other than a prefix to a table name. It is not necessarily related to a userid in the database or in the OS (DB2 LUW uses OS authentication). It is not necessary to explicitly create a schema in LUW since a schema will automatically get created in the schema catalog table the first time an object is created with a new schema name. DB2 LUW does allow explicit schema creation beforehand because DBA's from other DBMS's were having anxiety attacks about not being to create a 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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where does owner fall into all of this?

    And what's the security for LUW?

    in z/OS we use RACF...and we create databases using a secondary auth ID, then assign RACF ID's to groups.

    This ALL is coming out of the fact of how and installation of connectiuons that IBM seems to have many different Schemas for all their different tables

    I have a developer who asked me whyu he can do SELECT * FROM scheama1.Table1, but can't do SELECT * FROM schema2.Tablex

    Is it all based on the connections? Can you gain access to tables in other schemas? Can these schemas be in different "instances" or subsystems?
    Last edited by Brett Kaiser; 04-23-12 at 12:18.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    • In DB2 LUW, OWNER is the Authorization ID of the owner of the table, etc.
    • In DB2 z/OS CREATEDBY is the Primary authorization ID of the user who created the table, etc.


    • In DB2 LUW, TABSCHEMA is the schema name of the table, etc.
    • In DB2 z/OS CREATOR is The schema of the table, etc.

    You can find this information in the the column definitions for SYSCAT.TABLES (LUW) or SYSIBM.SYSTABLES (z/OS). They are contained in the Appendix of the SQL Reference Vol 1 (LUW) or SQL Reference (z/OS).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Brett Kaiser View Post
    And what's the security for LUW?
    I answered that question above. DB2 LUW uses OS authentication (for control of accounts/groups and passwords) and then permissions are granted via SQL to those users (or groups). There is no schema level security in LUW.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And just another aside...does IBM have a free developers edition of LUW that I can download and start playing with?

    Also, how do you work with LUW

    Are there tools like QMF, SPUFI or Platinum?

    And What about JCL?

    Oh, and is the a Procedural Component to SQL Yet (besides COBOL) that can be compiled directly to the Database like a SQL Stored Procedure?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 Express-C is free. It does lack some features, but not many.
    IBM - Download DB2 Express-C - Free to develop, deploy, distribute
    You should probably hurry and download 9.7 if you want it, becasue it will be replaced by 10.1 soon (previous versions of Express-C are not available for download).

    You can also get the full version (ESE) with a 90 trial license.

    IBM Data Studio (free version) is the GUI interface. Most DBA's work from the Linux/Unix/Windows Command Line more much of their activity. Third party GUI tools for DBA's are available from Ebarcadero, Quest, and maybe a few others.

    The equivilent of JCL is shell scripting (ksh or bash for Linux or UNIX) and Windows shell scripting for Windows.

    You can download free Linux OS distributions from many sites, which is what I would recommend on any cheap or old PC, and then install DB2 there.
    Last edited by Marcus_A; 04-23-12 at 13:56.
    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
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Brett Kaiser View Post
    ... is there a Procedural Component to SQL Yet (besides COBOL) that can be compiled directly to the Database like a SQL Stored Procedure?
    In this respect, DB2 for z/OS and DB2 for LUW are almost identical: both implement SQL PL to be used in the body of a stored procedure.
    A LUW stored procedure body could be used as z/OS procedure body without modifications, and vice versa.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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