DB2 on the Mac arrives

19 December 2008 » DB2, IBM, Mac, PHP

Antonio Cangiano just posted that DB2 Express-C for 64-bit Intel Mac OS X 10.5 (Leopard) is now available for download from IBM.

Stay tuned to his site for more details.

Listing DB2 triggers, routines, indexes and keys

10 December 2008 » DB2, IBM

I recently compared two DB2 databases that should have been identical, but for some reason were not. The foreign keys were missing from one of the instances.

I suspect the keys were lost during a migration when the administrator commented out the constraints in the DDL as a means to avoid errors on import. Unfortunately, the admin never ran the ALTER statements to add them even after all tables were present.

To be sure nothing else was different, I checked for the existence of other database objects to make sure they were not lost as well. DB2 offers a simple way to query for these objects via the SYSIBM and SYSCAT schemas:

Viewing triggers
[code lang=”sql”]
SELECT NAME
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA = ‘[SCHEMA]’
ORDER BY NAME
[/code]

Viewing stored procedures and UDFs: P = Procedure, F = Function
[code lang=”sql”]
SELECT ROUTINENAME, ROUTINETYPE
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = ‘[SCHEMA]’
ORDER BY ROUTINENAME
[/code]

Viewing indexes
[code lang=”sql”]
SELECT NAME, CREATOR, USER_DEFINED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = ‘[SCHEMA]’
ORDER BY NAME
[/code]

Viewing foreign keys (by database)
[code lang=”sql”]
SELECT
SUBSTR(TABNAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTNAME, 1, 20) FK_NAME,
SUBSTR(REFTABNAME, 1, 12) PARENT_TABLE,
SUBSTR(REFKEYNAME, 1, 20) PK_ORIG_TABLE,
FK_COLNAMES
FROM SYSCAT.REFERENCES
ORDER BY TABLE_NAME
[/code]

Viewing foreign keys (by table)
[code lang=”sql”]
SELECT
SUBSTR(TABNAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTNAME, 1, 20) FK_NAME,
SUBSTR(REFTABNAME, 1, 12) PARENT_TABLE,
SUBSTR(REFKEYNAME, 1, 20) PK_ORIG_TABLE,
FK_COLNAMES
FROM SYSCAT.REFERENCES
WHERE TABNAME = ‘[TABLE]’
[/code]

To make sure I don’t have consistency problems in the future, my next step is to put together a short script to automate these queries post install.

Cut spam with Postgrey

28 October 2008 » Linux, System administration

I’ve regained control over my inbox (and my BlackBerry) thanks to a nice little utility called Postgrey. A hat tip to Thomas on the NYCBUG list for the pointer.

Postgrey is a policy server for Postfix that employs an RFC compliant technique for handling mail called greylisting. This is roughly equivalent to placing an incoming phone call on hold for a fixed amount of time.

If the caller has legitimate business, she’s more likely to wait around to chat with you. If it’s a telemarketer, he’s more likely to hang up and move on to the next prospect.

Employing the utility on a CentOS server using these simple steps (condensed from the CentOS HowTo) dramatically reduced the spam I receive:

[code lang=”bash”]
[root@192.168.1.1]# yum install postgrey
[root@192.168.1.1]# vi /etc/postfix/main.cf
smtpd_recipient_restrictions = permit_mynetworks,reject_unauth_destination,check_policy_service unix:postgrey/socket,permit
[root@192.168.1.1]# /sbin/service postgrey start
[root@192.168.1.1]# /sbin/service postfix reload
[root@192.168.1.1]# /sbin/chkconfig –levels 345 postgrey on
[/code]

The war against UCE rages on, but for now, this technique offers much needed respite, saving valuable time and wireless network bandwidth charges.

DHS: Preserving our Freedoms, Protecting America, Sharing Files

21 October 2008 » Music, Politics, Potpourri

I saw this interesting entry in my Web server logs today. It looks like the folks at the Department of Homeland Security may have some time on their hands to share their iTunes libraries among coworkers.

While it’s nice to see they have the spare hardware and bandwidth to set up an enjoyable working environment at the bureau, I worry about the threat posed by a malicious audio file introduced to their internal network.

Lets have a look at something United States Secretary of Homeland Security Michael B. Chertoff said last week, on the occasion National Cyber Security Awareness month:

Question: I just want to ask you what DHS has done to protect the information that the private sector is supplying to DHS through online systems. I am asking this because I am interested in the information that high-risk chemical facilities have submitted to you through your online system and how do we know that that information is secure.

Secretary Chertoff: Generally, we do pay a lot of attention to securing our own systems. I am happy to say a grade that government — I hate it when they grade you, I figured I was done with this in elementary school. It is worse in Washington because people that grade you are often — it is like the parent of your competitor. We do get graded on our security systems and I do think a few years ago we were getting a low grade, two years ago we got a D, last year we got a B+, this year our internal security systems are going to be better than last year’s.

I think we are getting our own house in order, but in a larger sense by reducing the number of entry points to the domains and by putting in a more robust set of protections for detections and prevention, that is going to protect our data.

Of course, this all assumes that the rogue Googler was intending to find instructions on how to set up a system at work, as opposed to doing some personal research for his network at home, but it raises questions nonetheless.

Oh well, at least according to my logs they have their own secure build of Internet Explorer 6…

IDUG India highlights

30 August 2008 » DB2, IBM, WebSphere

I just returned from two weeks in Bengaluru (Bangalore). The trip was quite productive, as I was able to work with my team on a complex Eclipse RCP/Apache Derby application in person, and solidify the two most important pillars of an extended team development effort; communication and collaboration.

Since I worked US Eastern time while in India – late afternoon to late evening weekdays – I also had a chance to attend the IDUG 2008 India Forum in the early part of the day Friday and all day Saturday.

Much like the on site face time with my development team, this conference provided an excellent opportunity to interact with DB2 experts and to put all the new Information Management technologies and products into a meaningful context.

Of particular relevance to my day job, the forum clarified where DB2 fits into the larger WebSphere, Web 2.0, and SOA picture. I was able to pick up some clever ideas for the IBM Press Room migration to DB2 9.5 next spring.

A few highlights and lessons learned from the conference:

  • Attending Curt Cotner’s keynote on the new IBM Data Studio tool and pureQuery technology. This Eclipse-based tool has a lot of potential as a standalone application development platform, or plug-in to my existing Rational Software Architect setup. Using pureQuery to improve performance and ease root cause analysis for WebSphere and DB2 apps was impressive. I also had a chance to meet Curt and chat with him briefly about PHP Web application frameworks and drivers.
  • Hearing from Leon Katsnelson about how DB2 fits into the larger SOA picture, with two very innovative case studies. One example in particular about managing volatile data in DB2 – such as currency rates and weather conditions – using a Java stored procedure to make a Web services call was very enlightening.
  • Finally getting my head around REST. The past weeks have featured a storm of misunderstanding about SOAP and RESTful Web services, sparked by a comment made by Damien Katz. Following the conversations there helped me understand the pros and cons of each philosophy, and attending a few sessions at the conference helped solidify the fundamental differences in approach to SOA.
  • Reminding myself to look into the Web 2.0 Starter Toolkit for IBM DB2. There seems to be some really cool sample applications and monitoring tools in there.
  • Getting a primer on Ruby as a language and seeing a live demo of Ruby on Rails as a framework. I’m not planning to jump ship anytime soon, but there are tons of ideas in there that can be fed back into the architectures I develop in my day job.
  • Thanks to Matthias Nicola, who was kind enough to copy his XQuery & SQL/XML cheatsheet onto a pen drive for me, I was able to play around with pureXML on my local copy of DB2.
  • Finally figuring out the difference between Data Studio and Data Studio Developer. The former is the free tool, the latter is the kit that can be licensed from IBM for a fee and includes all the cool pureQuery stuff.
  • Taking advantage of the opportunity for a free shot at the DB2 9 Application Developer exam (I passed!) and shoring up my cursor usage and trace analysis skills through some pre-test cramming.

So, all in all a great business trip and excellent conference. Kudos to the conference organizers, IDUG, and the presenters.

Trip to Bengaluru

16 July 2008 » DB2, Photos, Travel

I just confirmed my trip to southern India. I’ll be in Bengaluru (Bangalore) for about 2 weeks. It’s my first trip to Asia, so I’m really looking forward to it.

The preparations for this trip have been more complex than any other overseas trip I’ve taken. The good new is that once the visa paperwork and vaccinations are complete, the barriers to travel to the region will be much lower in the future.

My trip will finish up the second week of August, which coincides with India’s Independence Day. Apparently there’s a semi-annual botanical show at Lal Bagh that day and for some reason my camera is really good at taking pictures of flowers. :), so there’s always that.

But I’d love to know what else is recommended to do that Friday the 15th, or on the weekends in general. Side trips to Ooty and Kodaikanal have been recommended. Other hints, tips?

Update: Due to the Lufthansa strike, I pushed my trip back one week, and will now be able to attend the IDUG India Forum (Friday morning and all day Saturday) which is taking place right next to my hotel. That looks to be a great opportunity to hear from Curt Cotner and several other DB2 experts. Good stuff.

Up and Running with DB2 on Linux

16 July 2008 » DB2, IBM, PHP, Writing

The second edition of “Up and Running with DB2 on Linux” was published in June. This IBM Redbook gives readers the latest information they need to exploit DB2 9.5 for Linux.

I performed a technical review of this book, recommending updates here and there to the introductory section and the chapter on using the new Eclipse-based IBM Data Studio tool. I also provided the short section on using DB2 with PHP in chapter 8.

The editor took my suggestion to include a link to planetdb2.com in the online resources list, to give readers a way to keep on top of day-to-day news about DB2 from the experts who blog about it.

The entire review process was a great way to learn about the latest developments in the DB2 for Linux world. I hope the book provides you with the same insight.

Have a look and submit your feedback to help improve the next edition.

Helsinki and Tallinn

19 June 2008 » Photos, Thoughts

I spent some time with the family in Helsinki, Finland and Tallinn, Estonia earlier this week. Gorgeous places, both. Especially under twenty hours of daylight in June.

We’re still in Finland, looking forward to our second wedding party on Midsummer‘s eve this coming Saturday with the family who couldn’t make it to the US last November. :)

Next page »