Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.
So I did the following:
- Opened the DB2 GUI (was easiest to do this way).
- Connected to the desired database as db2admin.
- Added my logon to the database as a user.
- GRANT all authorities to that id (my id).
- Disconnect.
- Connect to the desired database as my id.
- GRANT all authorities to db2admin.
- Disconnect.
Voila! The db2admin logon now as all authorities.
EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the
DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the
instance owner (in my case the db2admin id) have all the authority
needed to interact with the database. I could have actually just
commented out those grant lines above in the script. Those were left
over from a script which ran against an older version of DB2. I have
also found if I need to have the instance owner DBADM after doing a
restore to database A from database B, it is easiest just to set the
registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available
in Fix Pack 2 and above). Then I don't have to try to grant instance
owner DBADM. It automatically is granted that to any database restored
into the instance. If you are not at Fix Pack 5, you have to bounce
the instance for this to take affect.
DB2 shouldn't have issues at all with storage of large files. Some of how you store them will depend on what exactly you are storing. Are you storing XML files? Large text files? Word documents or PDFs? Video? Audio?
You have several options available to you. Graphic strings, binary strings, your large objects (CLOB, BLOB, and DBCLOB), and just plain XML.
If you are using just character data I would side with a CLOB
. But check your sizes, if you are consistently lower than 32K it might just be better to use a VARCHAR
. (Reason being is that large objects in DB2 are stored outside the table by default (unless you use the INLINE
option, but even then it only works to certain sizes of LOBs), and they are also not cached in bufferpools. DB2 always retreives LOB's straight from disk (unless they are INLINE
'd as mentioned above). If you are storing XML documents, you might want to look into the XML
type as DB2 has a more optimal way to store and retrieve XML. You can set up special XML indexes on XML types too. And you can query the XML using SQL/XML or XQuery using XPath.
If you are storing binary objects, then I'd go with BLOB
.
I think you may be right about the compression piece. (Although it wouldn't hurt to test it. There are about four or five different compression levels in DB2 and I know I can do at least backup compression with 9.7 Express-C).
Here is a 10.1 Edition comparison chart. And here is a article on the different editions and what they all offer.
Also take a look at space requirements for tables to help you better pick what you need.
Best Answer
Information Center is pretty reliable, and I don't see any indication that it will divide memory for non-existent databases. Do you have another link that indicates that?
Otherwise, it is meant to be the maximum number of allowable activated/connected to databases. So there is no need to set it high (I believe by default it is set to something like 12).
Based on the page you have, it is recommended that you set it to the number of databases you have + 10% for room for growth. So if you have 1 database, you would want 1 database + 10% (which in this case would round up) so you would set it to 2.
The reason I believe IBM recommends this, is that you can sometimes get caught with
NUMDB
less than your number of databases. We have had this happen to us on several occasions in our development and QAT environments. We set the number of databases to something like 9 or 10. And then the developers decided they needed another database. We DBA's ran theCREATE DATABASE
script like usual and it didn't fail. However, when the developers tried to access the new database, they got errors (I don't remember the exactSQLCODE
off the top of my head). And it turned out we needed to up the value ofNUMDB
as we had reached the maximum number of activated/connected databases.So I would follow IBM's recommendation, which in your case would be to set
NUMDB
to 2. This gives you what you need with some room for growth.I think this also might not be a "hot" setting, thus requiring you to run
db2stop
anddb2start
for it to take affect.