5

As a developer new to DB2, I was surprised when I discovered that I could not create my own tables in the development database environment. So, I posed a question to the db2 DBA. I don’t want to be too much of a nuisance by questioning policy and was hoping to get a better understanding of whether our policy of preventing developers from creating tables is justified for DB2.

Question: I understand that you must follow policy, but do you know why our company might create a DB2-only policy to prevent DB2 developers from creating tables in their own schema while simultaneously allowing SQL Server and Oracle developers to do so?

Answer: The mainframe is amore controlled environment than Oracle and SQL. All applications within our company share the DB2 subsystems. In Oracle or SQL, each application has its own instance and their testing would not impact another instance.
-- DB2 mainframe does not create multiple tables in the same file like Oracle. So, on the mainframe if you were able to create tables in test, you could potentially use up all the dasd if you created a huge table and if you were to test against that huge table, it could blow out the subsystem sort space, edm pool,etc. that is sized for a test environment. --DB2 Mainframe applications share subsystems and all system resources. Each test db2 subsystem is sized according to the function of that DB2 subsystem… DB2T – testing, DB2F – System testing, DB2B – Production Support. They all have different sizing allowance.

Question to SO: Re: “you could potentially use up all the dasd if you created a huge table” Doesn’t DB2 have the ability to limit space usage by schema to prevent a developer from using up all of the DASD (which I presume is Disk Access Storage device/aka disk space)? Looking up the acronym EDM and finding “Environmental Descriptor Manager” I presume that this is what contains all of the table definitions, like a Master database. Is this a valid concern that is specific to DB2?

From the DBA’s answer, I generally understand that our architecture is such that each environment, eg, TEST, UAT, etc, does not run in a separate instance and one instance can affect the other, but it sound like if this is the case, then we already have this issue given that developers have the ability to insert millions of rows into tables that were created for them. Isn’t the real threat the size of the tables that I create and rather than the ability to create a table? I would think that a DB2 DBA should be able to limit developers to a safe level.

  • These are not a technical questions, these are policy questions that are specific to your organization. – Ian Bjorhovde Sep 9 '14 at 20:39
  • I'm asking if there is a fundamental techical limitation in DB2 vs. Oracle and SQL that would impact the policy. For example, "yes, one might want to restrict DB2 more than the otehr dbs because of DB2's inferior capability to isolate processes/environments or limit space used at the schema level." (Just an example of a response, I'm not making that claim.) – ChadD Sep 9 '14 at 20:54
  • You are also talking DB2 on either i5/OS or z/OS rather than DB2 Linux, Unix, Windows. Mainframe DB2 functions a little bit differently. While I work with DB2 LUW, I can understand the answer given by your DBA as we also have DB2 for i at our shop. Perhaps Walter G can better address if this is a "limitation" of mainframe DB2 or is purely policy like Ian mentions? Speaking of which, is the "mainframe" DB2 you are discussing i or z? – Chris Aldrich Sep 10 '14 at 12:40
  • 3
    DB2 for z/OS is very different from DB2 for LUW, Oracle, or SQL Server in the way tables and tablespaces are physically implemented, so you cannot translate your non-mainframe experiences there. In DB2 generally schemas are just namespaces, they don't have any intrinsic quota metrics to allow resource management by schema name. – mustaccio Sep 10 '14 at 14:45
  • 3
    One man's limitation is another man's opportunity. If you are into cross-platform development you should be open to conceptual differences between platforms. – mustaccio Sep 10 '14 at 16:02
2

They probably do this because storage allocation on the mainframe is much more complex than for unix or windows platforms.

zOS is basically a very modern sophisticated piece of harware and operating system that is emulating a 1960s mainframe. In much the same ways as the latest Intel chips are emulating a 1980s 386 chip.

The problem is that the very low level close to the wire disk I/O which was necessary to get high bandwidth and and quick access times on the original hardware is "baked in" to the OS and many applications. So all disk storage for legacy apps deals with channels and volumes. A volume is a "pretend" 3330 disk with 8GB or 32GB of storage usually allocated as a piece of a real nTerabyte raid system.

To define tables properly in zOS DB2 you need to create tables spaces which in turn requires you to allocate storage on specific volumes this is fairly messy as four or five entities need to be linked together TABLE -> TABLESPACE -> Volume Group -> Disk Volumes + Index -> IndexTablespace -> Volume Group -> Disk Volumes.

Basically your DBAs are saving you a lot of grief by not letting you play with the matches.

P.S. If you use the modern half of zOS (Unix Systems Services) this all becomes irrelevant but it's going to be a long slow migration!

0

The culture of not permitting developers to have a sandbox they can make changes in is changing. There are many shops now allowing developers to create and alter schemas developer sandbox schemas, particularly for prototyping. There are some allowing certain types of changes (alter add column for example).

Storage administrators can define policies to control how much disk is available for sandbox environments.

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.