Improving Retrieval Times on DB2 Indexes

These ideas are derived and quoted from “Best practices for tuning DB2 UDB v8.1 and its databases

CREATE INDEX has a number of options that allow for significant performance gains:

  • Optimizing for SELECT: use PCTFREE 0 if index is on a read-only table.
  • Optimizing for ascending or descending ordering: use ALLOW REVERSE SCANS to allow for an index to be scanned bi-directionally, which means quicker retrieval of ascending and descending result sets. This has no negative performance impact since the index structure does not change internally to support this feature.
  • Optimizing for direct retrieval from Indexed columns: INCLUDE can be used to include additional non-indexed columns in the index page to promote index-only access and avoid data page fetches.
  • TYPE-2 INDEXES drastically reduce next-key locking, allow for index columns greater than 255 bytes by default, allow for both online REORG and RUNSTATS, and support the new multidimensional clustering ability. All new indexes in v8 will be created as type-2 except when there was already a (pre-migration) type-1 index defined on the table. Use REORG INDEXES to convert type-1 to type-2 indexes.

XQuery and pureXML in DB2

I just came across the following two articles that have some good pointers for using the native XML capabilities of DB2:


Setting up Apache Authentication with htpasswd / htaccess

Authentication in Apache is done through htaccess, either from the configuration file, or from the .htaccess file in a given directory. Note that only full directories can be easily protected with this method.
Here is how: (first log in to the shell, as this method only works if
you have shell access)

$ cd .../html/protected_dir

$ cat > .htaccess

AuthType Basic

Authname "Protected KLC directory"

AuthUserFile ../../control/htpasswd

AuthGroupFile /dev/null

Require valid-user

+d

$ htpasswd -c ../../control/htpasswd user_name

[give passwd]

After the file is created for the first time, to
add more users:

$ htpasswd ../../control/htpasswd user_name

There are also more sophisticated authentication schemes available, that allow database driven authentication. Feel free to contact me for more information about those solutions.


How to change a link everywhere throughout one or multiple websites?

To do a recursive search and replace in a file system tree, the most effective way is to work directly on the file system. Using a tool like DreamWeaver is slow and cumbersome. Unix offers perl and find, which combined allow to do a search and replace recursively very easily. This implmentation is in perl, but sed could also be used to achieve the same result.  At the prompt (with ssh or putty) go to the root of the tree you would like to modify, and type the following commands:

for i in `find . -name .html`

do

perl -p -i~ -e 's/original text/replacement text/g' "$i"

done

The above shell script will cycle through all the subdirectories starting with the current directory and look for all .html files. “original text” can be any regular expression valid in perl.


How to add a secure cert to IIS on Windows

To add an SSL cert to IIS 5 on Windows, you need three separate steps:

  • Create a p12 (pkcs12) cert file:
cat server.key server.crt > server.pem
openssl pkcs12 -export -in server.pem -out server.p12 -name "server"

  • Import the p12 file into IIS:
Start->Run->mmc
Ctrl+M
Add...
Certificates
Computer Account
Finish
Close
OK
Open "Certificates (Local Computer)" tree
Right click Certificates
All Tasks->Import...
Browse to .p12 cert
Next
Next
Next
Finish

  • Select cert for site:
Open IIS Admin
Select properties of website
Select Directory Security Tab
Server Certificate...
Next
Assign existing cert
Next
Select Cert
Next
Next
Finish
Web Site tab
SSL Port 443
Apply
OK

Also if there isn’t separate IIS installed we can also attach the certificate from cmd:
Start / Run / cmd
List current certificates attached to the ports:
netsh http show sslcert
Add new certificate to a port:
netsh http add sslcert ipport=0.0.0.0:PORTNUMBER certhash=THUMBPRINT appid=GUID
PORTNUMBER: ipport=0.0.0.0: will remain untouched, just need to specify the port, for example:
ipport=0.0.0.0:443
THUMBPRINT: this is the thumbprint of the certificate. You can check this thumbprint by double click on the certificate in the certificate store,select Details, and “Thumbprint”. Use this without white spaces, for example: 42 b3 f1 c1 d1… will be 42b3f1c1d1…
GUID: this should be generated with Guidgen.exe .

Example command: netsh http add sslcert ipport=0.0.0.0:443 certhash=42b3f1c1d1c1fg8dd81sd1 appid={CJKC07D-8D1D-CCSa-CS1s-VSF1CS1dsX}


Connection pooling with mod_perl

I found this info in the PostgreSQL archives. Here are  2 methods:

Best method from Dan Lyke: Apache::DBI will pool across Perl programs, and you don’t have to change anything in your scripts.

Next best method from Gilles DAROLD: in your perl script use the following code

use vars qw($dbh);

$dbh ||= DBI::connect($datasrc, $dbuser, $dbpwd);

These can be use to create a database connection class in Perl that can handle transparently the connection / reconnection and disconnection for your code. The end use code doesn’t have to be OO, thus the connection object can be reused in any legacy code.


How to install Perl modules on a Unix / Linux server

From the command line, as the root user, run:

  # perl -MCPAN -e shell

And then at the cpan command line run:

  cpan> install Module::Name

Alternatively, using sudo on one line, as any user you can directly intall a module by running:

sudo perl -MCPAN -e 'install Module::Name'

How to find the full email headers (a.k.a. internet headers) in Outlook?

To be able to report SPAM, Viruses, or any other problem with email, the system administrators need the full email headers. These can be easily found in Netscape or Mozilla Thunderbird, just by viewing the raw message source, or by selecting View Full Headers in the View menu. However in Outlook 2003 it is a little bit harder. You need to follow these steps:

  • Click on the header of the email you need to work with
  • Right Click on the same header in question
  • Select Options...

You will then see the information you need under Internet Headers.


How to set up PPTP on Win2k

Setting up a folder for PPTP access:

  1. Right click and select properties
  2. Select Sharing tab
  3. Check “Share this folder” and give it a share name
  4. Click the permissions button, and make sure that only the right user(s) have permission

Setting up user for PPTP:

  1. Start->Settings->Network and Dial-up connections->Incoming connections
  2. Click users tab
  3. Check the check box next to user to authorize