Category: Database

This video demos an interesting prototype of a new UI for Java IDEs. I am not convinced, but it is worth watching for a new perspective:
[youtube https://www.youtube.com/watch?v=PsPX0nElJ0k&hl=en_US&fs=1&&w=640&h=505]

My friend and a great sf author Dr Peter Watts has just been beaten up by US border guards and charged with assaulting an officer. This kangaroo circus might end with a two year imprisonment. Please give what you can to his legal defense fund.

Story:
Boing Boing

Send money via Paypal to:
donate@rifters.com

I just donated $50. Cory Doctorow gave $1000. Contesting criminal charges in US court gets very expensive, so every little bit matters.

OLTP is a database use where there are lots and lots of simple transactions or queries. For example, a typical website uses a database in the OLTP way. This is in contrast to a data warehouse, where there is usually only a few complex queries for generating reports.

IBM just announced DB2 pureScale, a way of easily scaling up the number of transactions a database can handle. The scaling is horizontal, in the sense that it’s accomplished by adding more servers rather than making the servers bigger.

DB2 pureScale is:

• Transparent. Client applications don’t need to know how many database servers there are or which one they are accessing.
• Easy. Adding a new machine is a matter of running two commands.
• Resilient. If one node goes down, the other nodes take over and there is virtually no data loss.

There is an upcoming Chat with the Lab about it:

DB2 pureScale: Scaling Databases without Limits
Event Date: 10/21/2009
Event Time: 11:30 – 13:00 EDT (GMT-04:00)
Hosted By: Rav Ahuja (IBM)
Presented By: Sal Vella (IBM), Matt Huras (IBM), Aamer Sachedina (IBM)

Register now

I ran into the following error while converting a virtual machine on a VMWare ESX Server to OVF using the VMWare OVF Tool.

Disk Transfer Failed
Error: Unable to open NFC target disk

Googling suggested that a network problem or a permissions problem might be at fault. In fact, it turned out the problem was that someone had started the image on the ESX server mid-way through conversion. The OVF Tool cannot convert a running image — it requires a stopped machine. Stopping the vm made the problem go away.

In the future, we might all say “updation”.

SQL6031N  Error in the db2nodes.cfg file at line number "1".  Reason code "10".

DB2 caches your machine’s hostname in several places. If your machine is changing its hostname, or if you are somehow moving an existing installation to a machine with a different hostname, you will need to adjust the hostname stored by DB2. Hostname change is common in virtual and cloud environments, so even better than adjusting it would be writing a script that adjusts it for you.

Here’s my rudimentary stab at a such a script:

#!/bin/sh
echo -n "Discovering the new hostname "
UNAME_CACHE=$(uname -n)

echo -n "Adjusting hostname list for unconfigured database partitioning feature "
# this may need to be changed if DPF preconfigured
if [ -e /home/db2inst1/sqllib/db2nodes.cfg ]; then
    chmod 666 /home/db2inst1/sqllib/db2nodes.cfg
    su - db2inst1 -c "cp /home/db2inst1/sqllib/db2nodes.cfg /home/db2inst1/sqllib/db2nodes.cfg.old"
    su - db2inst1 -c "echo 0 $UNAME_CACHE 0 > /home/db2inst1/sqllib/db2nodes.cfg"
fi

echo -n "Making registry writable "
chmod 666 /var/db2/*

echo -n "Adjusting the DB2 hostname "
UNAME_CACHE=$(uname -n)
/opt/ibm/db2/V9.7/adm/db2set -g db2system=$UNAME_CACHE

echo -n "Updating DAS configuration "
# todo
# db2 uncatalog node 
db2 catalog admin tcpip node $UNAME_CACHE remote $UNAME_CACHE system $UNAME_CACHE
db2 update admin cfg using DB2SYSTEM $UNAME_CACHE
db2 update admin cfg using SMTP_SERVER $UNAME_CACHE

This doesn’t cover all cases. For example, if you use extended operating system security, you may also need to set the db2accountname and db2instowner parameters. A quick check with db2set -all should tell you if this applies in your case — if there is something that looks like a hostname in those parameters already, you need to change them.

I may also have missed other necessary changes. Please let me know if I did.:-)

References

• IBM Support | Changing hostname of DB2 server
• Fillmore Group | Changing the hostname of a DB2 server
• Diary of a Database Geek | The importance of db2nodes.cfg

Only 5 weeks from acquisition to death. No more licenses for existing customers, either. Harsh.

The DB2 data movement tool sounds like an excellent way to move stuff from, say, MySQL to DB2. I should see if I can use it to move a MediaWiki database.

Speaking of MediaWiki, I swear I’ll have a patched zip of 1.15 ready any day now.:-)

I now edit the Computers: Software: Databases: IBM DB2 category at dmoz.org open directory project.

Directories used to be important in the mid-1990s, as they tended to provide better search results than dumb link crawlers like Altavista. Yahoo started off as a human-edited hierarchical directory of links and adopted the crawler approach later.

However, since Google has come along with a better search algorithm, directories are in decline. dmoz is the only major one left, and this hasn’t escaped the notice of SEO folk, who chase the prestigious linkback and fill the submission queue with a lot of crud.

Feel free to suggest a URL for inclusion.:-)

DB2 v9.5 uses the Windows username as the default schema. Unfortunately, it does not support spaces in schema names. Accordingly, usernames – e.g. “Jane Smith” — that have a space can make DB2 unhappy.

I recently helped someone resolve an issue with this command:

db2sampl -force -name SAMPLE

Which brought up this error:

  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "JANE SMITH"...

--ERROR----
  SQLSTATE = 42601
  Native Error Code = -443

[IBM][CLI Driver][DB2/NT64] SQL0443N  Routine "*L_SAMPLE" (specific name "") has  returned an error SQLSTATE with diagnostic text "SQLSTATE 42601: A character,  token, or clause is invalid or". SQLSTATE=42601
--

  Creating tables with XML columns and XML data in schema "JANE SMITH"...

--ERROR----
  SQLSTATE = 42601
  Native Error Code = -443

[IBM][CLI Driver][DB2/NT64] SQL0443N  Routine "*L_SAMPLE" (specific name "") has  returned an error SQLSTATE with diagnostic text "SQLSTATE 42601: A character,  token, or clause is invalid or". SQLSTATE=42601
--

  'db2sampl' processing complete.

The full routine name is CREATE_SQL_SAMPLE. It tries to create tables for Jane Smith, fails, and blazes bravely on. Interestingly, an empty database results.

The workaround is to log in as a user without a space, such as “db2admin”, run db2sampl, and then log back in as yourself.

For running future queries, you can specify a different schema:

SET SCHEMA=db2admin;

And, of course, you can always explicitly specify a schema in your queries:

SELECT * FROM db2admin.tablename;