Compiling C code on Windows 7 for DB2 9.7 64bit

Problem

The %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.

Solution

  • Set the environment (VC includes the batch file that allows to set the environment variable): C:\progra~2\micros~2.0\vc\vcvarsall.bat x64

Netapp Automation for DB2 9.7 (or Oracle)

Problem

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).

Solution

Create a restricted users that has only login access and the ability to manage snapshots:

  • Setup 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).
  • Start 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:
    Name: snapuser
    Info:
    Rid: 131075
    Groups: cli-snapshot-group
    Full Name:
    Allowed Capabilities: cli-snap*,login-ssh,login-telnet
    Password min/max age in days: 0/4294967295
    Status: enabled
  • 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.

References


DB2 Query Writing Best Practices

Problem

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.

Solution

Here is a list of tips you can use to write good SELECT queries:

  • Avoid 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)
  • Use FETCH ONLY x ROWS to limit the results set
  • Use CASE or MERGE instead of UNION ALL

References


Migrating a directory to a separate filesystem on AIX

Problem

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.

Solution

  • Create a new filesystem using mkfs
  • Mount the new filesystem temporarily to /mnt
  • Stop all processes that access the directory to move
  • Move all contents to the new filesystem using mv
  • umount /mnt
  • mount /new/filesystem /path/to/directory

This principle is pretty much the same of any Unix operating system.


NFS Locking Problem on Net App connected IBM Domino Server 8.5

Problem

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”

Solution

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
priv set

Finally, you can check the the lock is gone with:

lock status -f

References