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.
Best Answer
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!