%DB2PATH%\SQLLIB\samples\c directory on windows contains many examples of C routines to extend DB2 or to work with DB2 from C programs. This article describes how to compile the sample code for 64 bit.
- Set the environment (VC includes the batch file that allows to set the environment variable):
You have one or more NetApp storage systems (F960 or later series), running Data ONTAP® 7G (or later). You would like to take advantage of the snapshot capabilities, to facilitate the database backup process. However, you don’t want to use the default root login for the automated logins, nor do you want to use the unsecure rsh, as these options would violate corporate security policies (especially if you have a compliance commitment to ISO 27002, PCI or HIPAA).
Create a restricted users that has only login access and the ability to manage snapshots:
ssh on the filer:
secureadmin setup ssh (it is recommended that you select long keys when you are asked 1024 and 768 for ssh v1 – ssh1 shouldn’t be enabled anyway – 2048 for ssh2).
ssh on the filer:
secureadmin enable ssh2 (at this point you should be able to log in to the filer with ssh as root with your admin password)
- Create group / role / user:
useradmin user add snapuser -g Users
useradmin role add snaps -c "Snapshot Manager" -a cli-snap*,login-ssh,login-telnet
useradmin group add cli-snapshot-group -r snaps
useradmin user modify snapuser -f -g cli-snapshot-group
useradmin user list snapuser
The last command allows you to check your work, and the output should like:
Allowed Capabilities: cli-snap*,login-ssh,login-telnet
Password min/max age in days: 0/4294967295
- Put your public keys in the authorized keys file on the filer:
/etc/sshd/snapuser/.ssh/authorized_keys (typically you do that by mounting the filer root volume on one of your AIX boxes – any OS that can mount the root volume should work).
- At this point you are ready to test by logging in via ssh to the
snapuser account. Keep in mind that before you can successfully log in, you have to log out from the NetApp.
Writing efficient SQL
SELECTqueries for DB2 can be tricky. Some general SQL guidelines apply, and there are also specific practices that apply only to DB2.
Here is a list of tips you can use to write good
SELECT * in your queries. Instead, state the columns you want to select.
- Use the consistent acronyms to alias your table names (makes it easier to read joins)
FETCH ONLY x ROWS to limit the results set
MERGE instead of
You have a directory that deserves it’s own file system for some reason. This could be because you need to increase throughput, manage backups separately, manage quotas separately or just to have a cleaner data architecture.
- Create a new filesystem using
- Mount the new filesystem temporarily to
- Stop all processes that access the directory to move
- Move all contents to the new filesystem using
mount /new/filesystem /path/to/directory
This principle is pretty much the same of any Unix operating system.
One of our IBM Domino servers all of a sudden decided that it couldn’t get an exclusive lock on its database files any longer. The databases happened to be on a NetApp head, and even after rebooting the server, the locking problem would persist. I other words, Domino’s nfslock was failing. As a result Domino wouldn’t start, and would send out errors to the domino startup log file similar to:
“Directory Assistance failed opening Primary Domino Directory names.nsf, error: This database is currently in use by another process”
Turns out that the NetApp lock table is sensitive to the server name and not the IP address. As a result, a lock from domino12.domain.com isn’t the same as a lock from domino12. To make matters worse, a Red Hat Linux machine might present itself either way depending on the config file details (even the order of the short name vs long name in the hosts file matters).
How to confirm the problem? On the NetApp, list the locks with
lock status -f
Now that you know the client name under witch the lock shows up, you can clear the lock with
priv set advanced
sm_mon -l clientname
Finally, you can check the the lock is gone with:
lock status -f