Category: Database

Simplify the DB2 9.7 security model
Starting in DB2 9.7, the DB2 security model was changed. Security administration (SECADM) was split off from database administration (DBADM), and users with SECADM permissions could no longer peek at data, at least not by default.

Secure by default is a great practice. Itâ€™s fantastic when deploying a production database at a financial institution. However, itâ€™s not so good when you are a new user trying to learn a tool.

Control Center seems to run as SYSTEM by default on WinXP, which leads to errors like this:
```
SQL0551N  "SYSTEM" does NOT have the required authorization
OR privilege TO perform operation "SELECT" ON object "LEONSP.ACT". 
SQLSTATE=42501
```
Letâ€™s remedy this by granting lots of privileges all around.

Open up the DB2 Command Window.

Connect to the database as a user that already has permissions (leonsp for me):
```
db2 connect TO samptest user leonsp USING password
```
Grant all privileges to SYSTEM:
```
db2 GRANT  DBADM, SECADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE  TO USER system 
```
Did you create DB2ADMIN or some other id when installing DB2 on Windows? Letâ€™s grant all privileges to that user as well:
```
db2 GRANT  DBADM, SECADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE  TO USER db2admin
```
You should now no longer see the SELECT privilege error. Note that you should not do this in a production environment, as this is less secure than the default configuration.
June 26, 2010
Cataloging databases after reinstalling DB2
On Windows, uninstalling DB2 will remove the instance but keep the databases in place. After you reinstall, youâ€™ll want to recatalog these databases. This is fairly straightforward.

Find out their names:
```
db2 list db directory on C:
```
Map each of the databases that you want:
```
db2 catalog db SAMPLE on C:
```
You can also catalog remote databases the same way. In my experience, this is much easier than going through Control Center:
```
db2 catalog tcpip node MYNODE remote MYHOSTNAME server 50000
db2 catalog db SAMPLE as MYSAMPLE at node MYNODE
db2 connect to MYSAMPLE user MYUSER using MYPASSWORD
```
The database will now show up in your Control Center catalog. You will also be able to connect to the local alias from Data Studio and other tools.
June 26, 2010
Going from DB2 Enterprise to DB2 Express-C
Downgrading from DB2 Enterprise to DB2 Express-C is a fairly straightforward process.

Stop DB2.
```
db2stop force
```
Uninstall DB2 Enterprise.
```
sudo su - db2inst1
/opt/ibm/db2/V9.7/install/db2_deinstall -a
exit
```
Install DB2 Express-C without creating any users, etc.
```
sudo ./db2_install
```
Update the instance with the new edition information.
```
sudo /opt/ibm/db2/V9.7/instance/db2iupdt db2inst1
```
You might encounter this error.
```
db2inst1@vhost0074:~> db2start
SQL5043N  Support for one or more communications protocols
failed to start successfully. However, core database manager
functionality started successfully.
```
Look up your DB2 TCP/IP service name. It should be similar to db2c_db2inst1 where db2inst1 is the instance owner.
```
cat /etc/services | grep db2
```
Make sure itâ€™s set correctly in the DB2 configuration.
```
db2 update dbm cfg using SVCENAME db2c_db2inst1
```
Start DB2.
```
db2start
```
You should now have a working DB2 Express-C installation with all the same databases.
June 26, 2010
Enable NFS services for HADR
You might need to enable network file system (NFS) services when configuring the High Availability Disaster Recovery (HADR) or Database Partioning (DPF) DB2 features. For example, with HADR, you might want to set up a slave database that shares a backup with the master database through NFS.

These commands should enable NFS services on most distributions of Linux:
```
sudo /sbin/chkconfig nfs on
sudo /sbin/chkconfig nfslock on
sudo /sbin/chkconfig rpcgssd on
sudo /sbin/chkconfig rpcidmapd on
sudo /sbin/chkconfig portmap on
sudo /etc/init.d/iptables restart
sudo /etc/init.d/nfslock restart
sudo /etc/init.d/portmap restart
sudo /etc/init.d/nfs restart
```
Some of the services might not exist on your distribution. Additional steps may be needed.

IBM Smart Business Cloud for Test & Development might be one of the environments where youâ€™d have to do this.
June 26, 2010
Simplify DB2 Express-C security
Starting in DB2 9.7, the DB2 security model was changed. Security administration (SECADM) was split off from database administration (DBADM), and users with SECADM permissions could no longer peek at data, at least not by default.

Secure by default is a great practice. It’s fantastic when deploying a production database at a financial institution. However, it’s not so good when you are a new user trying to learn a tool.

Control Center seems to run as SYSTEM by default on WinXP, which leads to errors like this:
```
SQL0551N  "SYSTEM" does not have the required authorization or privilege to 
perform operation "SELECT" on object "LEONSP.ACT".  SQLSTATE=42501
```
Let’s remedy this by granting lots of privileges all around.

Open up the DB2 Command Window.

Connect to the database as a user that already has permissions (leonsp for me):
```
db2 connect to samptest user leonsp using password
```
Grant all privileges to SYSTEM:
```
db2 GRANT  DBADM, SECADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE  TO USER system
```
Did you create DB2ADMIN or some other id when installing DB2 on Windows? Let’s grant all privileges to that user as well:
```
db2 GRANT  DBADM, SECADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE  TO USER db2admin
```
You should now no longer see the SELECT privilege error. Note that you should not do this in a production environment, as this is much less secure than the default configuration.
June 16, 2010
Cataloging local DB2 databases
On Windows, uninstalling DB2 will remove the instance but keep the databases in place. After you reinstall, you’ll want to recatalog these databases. This is fairly straightforward.

Find out their names:
```
db2 list db directory on C:
```
Map each of the databases that you want:
```
db2 catalog db SAMPLE on C:
```
You can also catalog remote databases the same way. In my experience, this is much easier than going through Control Center:
```
db2 catalog tcpip node MYNODE remote MYHOSTNAME server 50000
db2 catalog db SAMPLE as MYSAMPLE at node MYNODE
db2 connect to MYSAMPLE user MYUSER using MYPASSWORD
```
The database will now show up in your Control Center catalog. You will also be able to connect to the local alias from Data Studio and other tools.

Reference
- Mapping existing databases after reinstalling DB2
June 15, 2010
Going from DB2 Enterprise to Express-C
Downgrading from DB2 Enterprise to DB2 Express-C is a fairly straightforward process.

Stop DB2.
```
db2stop force
```
Uninstall DB2 Enterprise.
```
sudo su - db2inst1
/opt/ibm/db2/V9.7/install/db2_deinstall -a
exit
```
Install DB2 Express-C without creating any users, etc.
```
sudo ./db2_install
```
Update the instance with the new edition information.
```
sudo /opt/ibm/db2/V9.7/instance/db2iupdt db2inst1
```
You might encounter this error.
```
db2inst1@vhost0074:~> db2start
SQL5043N  Support for one or more communications protocols
failed to start successfully. However, core database manager
functionality started successfully.
```
Look up your DB2 TCP/IP service name. It should be similar to db2c_db2inst1 where db2inst1 is the instance owner.
```
cat /etc/services | grep db2
```
Make sure it’s set correctly in the DB2 configuration.
```
db2 update dbm cfg using SVCENAME db2c_db2inst1
```
Start DB2.
```
db2start
```
You should now have a working DB2 Express-C installation with all the same databases.
June 9, 2010
Get your hands off me, you damn dirty bloggers

“I don’t want to see us descend into a nation of bloggers.”
– Steve Jobs

Steve Jobs went on to scream “You maniacs! You blogged it up! Ah, damn you! God damn you all to the blogosphere!”

June 2, 2010
Enable NFS for DB2 DPF or HADR
You might need to enable network file system (NFS) services when configuring the High Availability Disaster Recovery (HADR) or Database Partioning (DPF) DB2 features. For example, with HADR, you might want to set up a slave database that shares a backup with the master database through NFS.

These commands should enable NFS services on most distributions of Linux:
```
sudo /sbin/chkconfig nfs on
sudo /sbin/chkconfig nfslock on
sudo /sbin/chkconfig rpcgssd on
sudo /sbin/chkconfig rpcidmapd on
sudo /sbin/chkconfig portmap on
sudo /etc/init.d/iptables restart
sudo /etc/init.d/nfslock restart
sudo /etc/init.d/portmap restart
sudo /etc/init.d/nfs restart 
```
Some of the services might not exist on your distribution. Additional steps may be needed.

IBM Smart Business Cloud for Test & Development might be one of the environments where you’d have to do this.
June 2, 2010
At Impact 2010

I’m in Las Vegas Saturday through Thursday for Impact 2010. Drop me a word if you are there too.

Having built the DB2 images for WebSphere CloudBurst, I’ll be helping Dustin Amrhein run a lab on Tuesday:

Hands-on Lab with WebSphere CloudBurst Appliance
Tuesday at 1:30 PM
TDC-1369A
Venetian, San Polo 3403

I’ll also spend a lot of time demoing and discussing DB2 and WAS cloud images at the RightScale pedestal:

Run DB2 and WebSphere in the Cloud with RightScale
Demo at ped A3

Oh, and if you are looking for a hard copy of the legendary Getting Started with DB2 Express-C book, be sure to drop by.

April 30, 2010