A very small and interesting article on how to build and maintain
professional relationships which I thought would be worth sharing.

How to “Huff-and-Puff-Proof” Your Career

IBM is launching a first of its kind Linux based Business Intelligence solution. Called the BCU (Balanced Configuration Unit), it is a complete solution encompassing IBM hardware, Linux OS, and IBM software. This product joins the BCU for AIX, which is widely used today and available as well.

The IBM Data Warehousing Balanced Configuration Unit (BCU) for Linux is a package of:

• DB2 Data Warehouse Edition(DWE)
• eServer 326m (AMD Opteron)
• System Storage DS4800

The BCU integrates these hardware and software components and provides clients with:

• Hardware and database configuration
• Performance optimization
• Systems and Solution testing and client-side integration

Customers can complement the solution with industry-specific data models and BI software, or tools from ISVs such as those from Siebel, SAS, SAP and many others.

I was looking for certifications for Data Warehousing Professionals recognized the world over and instead what I found was this article. It talks about the use of SOA (Service Oriented Architecture) in BI (Business Intelligence).

It stirred my thoughts and brought back some memories as well. The bottom line, as the articles suggests is that we need to develop some shared metadata standards and do that fast. ERP or not, until and unless we use metadata correctly and pervasively, all investments (monetary, physical, human and intellectual) will be a waste. Must read for all DW and BI professionals. It sounds like a lot of junk, but will prove essential in the years to come.

A call to the open community to popularize shared meta data standards atleast in open data warehousing solutions.

I am down in the dumps again. With almost 75% of my work profile lost to SAP-BW guys, I have almost nothing to do these days. So I am back to blogging. Atleast it calms the nerves a little.

Anyone interested in a game of soccer? Or a heavy intellectual discussion on philosophy, message me.

I live in Bangalore right? Right. And what is Bangalore famous for? The IT Industry right? Of Course? And what is the IT Industry famous for? Coding right? Wrong!!!

The Indian IT Industry is famous not for its coding abilities. Instead it is infamous for it. So many coders writing millions and millions of lines of code. Who checks its quality and readability. The most distressing feature of coding is that its not just you who has to read it. Today your entire organisation wants to read it and tomorrow the whole world will.

Sure you can learn a language in 3 days. You can even become a master of any language in less than a month. But there is something about coding which no course or book or website teaches you. It must be readable just not by you but by anyone and everyone.

Coding is not just about theories and languages and concepts. It is an art in itself. And very few people are able to master it early on in their careers. Take my advice and follow these simple steps when you are coding. Be it C++ or Java or PHP or any language under the sun. These coding tips will help you understand what you are doing better and faster.

Click here to read this article on InformIT.

I know these things are “superficial” and “real coders never comment their code” and blah blah blah. But hey, its your code and you might need to read it just a year later. And instead of banging your head tomorrow over the logic, you just might spend an additional 10 minutes today to comment and format it. It makes everyone’s lives so much simpler.

Early this morning H contacted me to figure out an update statement for him. His problem was quite peculiar, although not rare. Consider a table in DB2 with the following structure and data:

As you can see, only some of the rows contains a non-null value for the field Field3. What H wanted me to do was to write an update statement which would update the values of all these rows (which contained a null value for Feild3) with the value in the row which contained a non-null value. So basically the end result had to be all the rows containing the value “546723″ in Field3. How would you accomplish this using a single update statement for the entire table? Remember, you have to do this for every unique value of Field1.

Lets take this step by step. First of course we know that we need to change the data in the table TABLEA. So we need to use the update SQL statement. Second, we also know that the Field3 column needs to be updated. And third, we are sure that Field 3 needs to be updated only when it is NULL. So, we have the following:

update TABLEA set Field3=#something# where Field3 is null

So what else do we need? The value to update the NULL value to. That is the tricky part. Since this value has to be retrieved from the table itself, we need to use a subquery (or a nested query as some people call it). We need to set it to the non-null value of Field3 where the value of Field1 matches the value of Field1 in the outer query. Thats where table aliases come in. Take a look:

update TABLEA t1 set Field3=(select Field3 from TABLEA t2 where Field3 is not null and t2.Field1=t1.Field1) where Field3 is null

But this creates another problem. The inner query will return more than one value, forcing DB2 to throw an error. So we need to make sure that the inner query returns just one value, regardless of the number of rows where the conditions are satisfied. To accomplish this, in Oracle we have the TOP N statement, which allows you to retrieve only the first, say 10, rows from it. Here’s how it looks in Oracle:

select top 10 * from table1

However, this is IBM’s DB2, and IBM obviously needs to do something different. So they came up with their own way to handle this. DB2 uses the FETCH FIRST N ROWS ONLY statement. And that too at the end of the SQL command. A simple SQL statement would look like this on DB2:

select * from table1 fetch first 10 rows only

For more on the Fetch First N Rows Only statement of DB2, read this.

Right, back to our problem. How does FETCH help us there? Here’s the new query, and as you can make out, the inner query returns only a single value now and hence, causes the overall statement to succeed. The SQL:

update TABLEA t1 set Field3=(select Field3 from TABLEA t2 where Field3 is not null and t2.Field1=t1.Field1 fetch first 1 rows only) where Field3 is null

So, just to reiterate the whole story, the inner query first of all retrieves the value of Field1 from the outer query for every row that the outer query encounters, and then retrieves ONLY ONE non-null value of Field3 from the table which has the same value for Field1. Then it passes that non-null value to the outer query, thereby allowing it to update all those rows where Field3 is NULL for that value of Field1 which was initially passed into the inner query.

Hope that cleared up some concepts. Or did it muddle up your existing ones? After all…that was the objective.

I managed to get past the original problems creating the function in DB2. Now, however, its a brand new story. For almost 2 full days, I have been looking to be granted access to execute some other function. Sent out quite a few mails over this. And it was finally granted yesterday night. So today I will try to execute this function and see if it succeeds. If it does, then I can start back on the DTS packages on SQL Server.

On the other end, quite a few Oracle jobs failed, the error as it turned out was induced by users upon entering incorrect data. J helped a lot with this, making me wonder for the first time how much Amar knew about this stuff.

Spoke to G over the phone in Pune. He is looking to move to Bombay soon. Him and Andy will be living in the commercial capital, on their own. Of course 2 of my sisters are already there and a third one will be there by year end. And who knows where Dad will be?? Life can be really complicated!!!

Most importantly, our recently discovered professional astrologer lady says everything should be settled by June. Oh Yes…Saummya wants to be in Mumbai in May. Remember the lock and key?? Lets hope for the best!!!