DB2 INSERT INTO vs SELECT INTO

In DB2 if you need to populate a table, you need to use INSERT INTO, like in this example:

INSERT INTO new_table
SELECT col1,col2 FROM source_table
WHERE col1='something';

and if you need to populate query variable, you need to use SELECT INTO, like in this example:

SELECT * INTO :var1, :var2, :var3
FROM table_name WHERE col1= 'something';

Source: DB2 Documentation


Adding a second disk to an LVM

In a world of storage on demand and virtualized environments, adding more storage on the fly has become simple even on Linux machines without expensive software like Veritas storage manager.  Here is how to proceed:

  • need to use LVM or LVM 2
  • add the new disk as an LVM capable physical volume: pvcreate /dev/sdb (or hdX)
  • add the new disk to the volume group, e.g. vgextend VolGroup00 /dev/sdb
  • extend the logical volume, e.g. lvextend -L +55G /dev/VolGroup00/LogVol00
  • unmount the volume to resize (this may require stopping services and/or boot into rescue mode)
  • check the partition: e2fsck -f /dev/VolGroup00/LogVol00
  • resizet the partition: resize2fs /dev/VolGroup00/LogVol00

If booting from rescue, before you can act on the partition, you need to enable it with:

  • vgchange -a y VolGroup00
  • lvchange -a y /dev/VolGroup00/LogVol00

libstdc++.so.5 is needed by xxx

Trying to install the IBM JDK 64bit I came across the following error:

libstdc++.so.5 is needed by ibm-java2-x86_64-sdk-5.0-8a.0.x86_64

It turns out that yum has a neat feature to show what package provides a given component:

yum whatprovides libstdc++.so.5

gave the answer, and

yum install compat-libstdc++-33.i386

took care of installingthe missing package.


Public key for xxx.rpm is not installed

After an upgrade to CentOS 5 / RHEL 5 we got the following error:

Downloading Packages:
warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID e8562897
Public key for openldap-2.3.27-8.el5_2.4.x86_64.rpm is not installed

It turns out that the key needs to be loaded and yum has not tried to load the key properly. The following manual key loading solved the issue:

rpm --import http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-5

Error: kernel conflicts with e2fsprogs < 1.37-4

During an audit, we noticed that one of our Red Hat 5 / CentOS 5 servers wasn’t getting the security patches.  Upon running the yum update command manually, we discovered that it fails with:


Error: kernel conflicts with e2fsprogs < 1.37-4

The problem turned out to be the presence of a number if 32bit packages. To solve it, we removed the 32bit architecture packages with:

yum remove *.{i386,i586,i686}

(only do that if you are on x86_64 and you verified that the packages you need are present in 64bit version)


Increase maximum table space in MySQL

For those of us that are still forced to used 32bit MySQL, there is a table size limit of 4GB by default (even though the file size limit on those systems is 4TB on ext3 – 2TB on NTFS).  Here is what to do to lift that limit:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL can’t optimize the space required based only on the number of rows.

If you want to make the default larger, then you can set the mysqld engine parameter myisam_data_pointer_size = 7 in /etc/my.cnf (or whereever your MySQL server config file is). The setting of 7 will allow 256TB table size.

References:

  1. MySQL 5.0 Reference Manual
  2. Overcoming MySQL’s 4GB Limit
  3. Vbulletin Community Forum

Drop Index in MySQL takes forever

Even though for our core systems we have migrated most databases to DB2, we still have to deal with MySQL for some side systems, such as CRM, Blog, etc.   One of the MySQL features that I recently noticed is that dropping indexes on sizable tables takes forever.  That is apparently due to a convoluted indexing process in MySQL.  The workaround was found here: MySQL General Discussion List

It goes as follows:

1) create table T1 like T;
This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
2) alter table T1 drop index ndx3;
This drops index ndx3 on the empty T1, which should be instantaneous.
3) insert into T1 select * from T;
This will populate table T and load all three(3) indexes for T1 in one pass.
4) drop table table T;
5) alter table T1 rename to T;

An alternative ugly hack is to start the drop index, kill the database server.  That will crash the table on which the drop index was.  The crashed table can be recovered by running mysiamchk.