Recently I got a mail from a reader, who had problems creating a DB2 table because of lacking authorisation. In many shops it is DBA work to create tables and indexes, but sometimes it is nice just to create a table for your own good. And then you may run into all sorts of authorisation issues.

If you write a simple CREATE TABLE statement where you just specify the table name and column definitions and nothing else, DB2 will create the table in the database DSNDB04 per default. If you are already getting authorisation issues here then you need to carry out the following SQL statement (hopefully you are allowed to do so):

select char(name,8) dbname, char(stgroup,8) stogroup, bpool
from sysibm.sysdatabase
where name in
( select name from sysibm.sysdbauth
  where createtabauth <> ' ' and grantee = 'PUBLIC'
);

Which will return the names of all the databases everybody has access to create tables in. Choose one of the names on the list and append IN <dbname> to your CREATE TABLE statement and try again. If you continue to have authorisation problems you either do not have access to use the stogroup (storage group) or the bpool (bufferpool) returned by the above SQL statement in connection to the database name. The easiest thing to do is to try a new database name in the IN you added. If all database names fails or the above SQL statement did not return any databases then you have to find an existing tablespace to create the table in or even create a tablespace to create your table in. Getting this far you need to carry out the following SQL statement:

select char(qualifier,8) dbname, char(name,8) name, obtype
from sysibm.sysresauth
where grantee = 'PUBLIC' and obtype in ('B','S','R')
;
Example result:
QUALIFIER NAME     OBTYPE
---------+--------+------
          BP0      B
          BP1      B
          SYSDEFLT S
DSNDB04   SYSDEFLT R
DSQDBDEF  DSQTSDEF R

In connection with the SQL statement I have shown an example of what the output might look like. The column OBTYPE tells you what the contens of columns NAME and QUALIFIER is. If OBTYPE is B then NAME contains a bufferpool name everybody can use. If OBTYPE is S then NAME contains a storage group name everybody can use. If OBTYPE is R then QUALIFIER and NAME together specifies a tablespace everybody can create tables in.

The easiest choice is to create your table in a tablespace returned by the last specified SQL statement. Just edit your IN <dbname> to IN <dbname>.<name> and try again. It should work, but maybe the tablespace allows for only one table and then you need to try one of the other tablespaces on the list, if any.

You may also choose to create your own tablespace for your table. You do so by using a CREATE TABLESPACE <tsname> IN <dbname> USING STOGROUP <stogroup> SEGSIZE 64 BUFFERPOOL <bpool>;. Choose your own favourite value for <tsname>. The <dbname> value needs to be one of the database names returned by the first SELECT SQL statement in this tip. <stogroup> must be a storage group you have accesss to according to the second SELECT SQL statement in this tip and the same rule applies to <bpool>. If either a storage group or a buffer pool (or both) is missing in the result, you may not have access to create your own tablespace. Please contact the most friendly DBA you know on your installation and ask for help. The parameter SEGSIZE 64 specifies that your tablespace can contain many tables. If you omit this parameter your tablespace can only contain one table. If you get lucky and succeed in creating your own tablespace then you just alter IN <dbname> on your CREATE TABLE to IN <dbname>.<tsname> used in your CREATE TABLESPACE statement.