Recovering from a bad Hard Disk Crash

Last time I've used my computer I burned a DVD Video with K3B and shutdown openSUSE as usual.

Yesterday, when I turned the computer on, it went directly into GRUB shell which allowed me to execute some commands with no real use to me.

Obviously something was wrong, and I though that it was the Master Boot Record (MBR) that got corrupted, as I've already have experienced before.
So I got the openSUSE installation disk and boot with it in order to fix the problem. I've requested the fix option from the menu but then something weird happened.
When scanning the hard disk, it displayed a message that there was something wrong and I would not be able to edit the disk partitions. I checked the partitions and the information seems to show that everything was OK.
I needed something else to see what was wrong with the hard disk and to fix it.

I grabbed an old Knoppix CD, version 3.3, the only one that I know that loads correctly on my Acer laptop (by the way, don't by an Accer if you wish to have Linux on a laptop), and checked the disks. This is not the first time I use Knoppix to save a system, it's actually one of my companion disks.
Knoppix automatically mounted both windows partitions correctly but the Linux EXT4 and Swap partitions were missing.
Now I start to get worried...

I got into a shell and checked the partitions with fdisk.
I detected the problem immediately: I had two partitions starting at the same cylinder! I even recall its number, 2086.
The second partition had an Id of f, which is awkward since the Id is a numeric value, and was marked as the boot partition. Using fdisk to see what my chances were of correcting this I realized that a drastic solution would probably be the only solution. When I deleted that second weird partition, the following partitions were gone to.

So, before I do something that I could regret latter, I checked the content of the Windows partitions to find out that if anything would go wrong I already had a backup of everything.
The data on the Linux partition seemed already lost, so I would have to settle for the backups.
On the other hand, I was still able to save everything from the Windows partitions, but that would be unecessary.

I finally decided what to do and how to do it.
I've deleted that second weird partition, defined the first partition as boot and saved the partition table.
I could have tried to recreate the partition table with fdisk, but since I had backups of everything, I just went for the full disaster scenario.

I then rebooted with the openSUSE installation disk, created a second partition, that I reserved to replace the lost second partition for Windows, and created a third one for Linux, where I installed openSUSE.

When openSUSE installation finished I've reboot and started Windows, which loaded just fine from the saved first partition. When Windows loaded, it was time to format that previous reserved second partition as NTFS.

The whole thing took me a couple of hours but I got both Windows and openSUSE up and running again. Today I'll restore the backups, which will probably take a couple hours more.

Moral of the story:

1. Always have a Knoppix disk at hand.
2. Backups are a tech best friend.
   

./M6

Distribute Python Applications on Windows

How to distribute Python applications on Windows with py2exe and InnoSetup.



./M6

Source Code Format in Blogger

Here's a simple tutorial to post formatted source code on Blogger:
Getting code formatting with syntax highlighting to work on blogger.

./M6

openSUSE

I've just moved from Kubuntu to openSUSE.
The reason is simple, Kububtu 9.10 revealed too unstable for me. In just a couple of months I had to reinstall once and when troubles reappear for the second time, I talked with a friend and decided to give a try to openSUSE.

So far, so good. It's a lot more stable and my hardware seems to be better supported, even my ATI Mobility Radeon X700 that always gave me headaches in Linux works fine. Unfortunately the 3D support seems not be be fully working, but that is not a issue, at least for now.

I kept KDE as my window manager, it suits better my needs. The first look and feel at it was very pleasant. Even the desktop wallpaper is very good.
One of the first things I've learn was to add the Packman repository, since the default official repositories does not have some software for "some legal uncertainties". For instance, it does not have the mpeg2enc from mjpegtools, one has to remove the mjpegtools, add the Packman repository and add the mjpegtools again in order to have the Packman mjpegtools package installed, which does have the mpeg2enc.
Other great software, like 'Q' DVD-Author and DeVeDe, also comes from the Packman repository, so it is critical to include it.

Some things are different, specially when it comes to the administration part. Maybe it is because openSUSE has so much affinity with SUSE servers, it has a lot of easy administration tools right out-of-the-box.
But some things seem the same. The Network Manager still sucks. Why is it so hard to use, specially when its flag is "Pain-Free Networking"? Since Network Manager appeared, all it gave me was problems. In Kubuntu it simply did not connect via wifi, and now I can't enable it, even after I defined it to be my network manager and enabled it!...

Anyway, everything seems to work just fine and I'll keep openSUSE for a while, specially because it seems very stable.

./M6

DB2 How To

While I was searching for the loading method that accepted a dynamic query from inside a stored procedure, I found a simple and very useful DB2 How To.

Here it is: DB2 How Tos.

./M6

DB2 Copy Schema With Tables

Recently I needed to automate a DB2 schema backup.
My requirements were quite easy, just create a new schema and copy the tables and their content into the new schema.

What initially started as a simple task reveled to be a hard task and resulted in a quite simple script.

The first problem was to execute several commands in the DB2 Command Editor. I was getting syntax errors because the statement termination character was set to be the same as the SQL end statement character ;.
Setting it to # solved my problem, but I did waste some time with this so keep in mind to change the statement termination character to avoid such problem.

I've created a specific schema for this automation task COPY_DATABASE_SCHEMA, so all the procedures will be created there.

Here is the procedure that will copy a table content:

/**
* Copies, with replacement, the content from the source table
* into the target table.
* It will not check for success, i.e. no loading validation
* will be performed.
*
* @sourceTable: the full qualified source table name
* @targetTable: the full qualified target table name
*/
create procedure COPY_DATABASE_SCHEMA.COPY_TABLE(
sourceTable VARCHAR(128),
targetTable VARCHAR(128))
LANGUAGE SQL
begin
declare v_version_number INTEGER default 1;
declare v_cursor_statement VARCHAR(32672);
declare v_load_command VARCHAR(32672);
declare v_sqlcode INTEGER default -1;
declare v_sqlmessage VARCHAR(2048) default '';
declare v_rows_read BIGINT default -1 ;
declare v_rows_skipped BIGINT default -1;
declare v_rows_loaded BIGINT default -1;
declare v_rows_rejected BIGINT default -1;
declare v_rows_deleted BIGINT default -1;
declare v_rows_committed BIGINT default -1;
declare v_rows_part_read BIGINT default -1;
declare v_rows_part_rejected BIGINT default -1;
declare v_rows_part_partitioned BIGINT default -1;
declare v_mpp_load_summary VARCHAR(32672) default NULL;

set v_cursor_statement =
    'DECLARE C1 CURSOR FOR SELECT * from ' || sourceTable;
set v_load_command =
    'load from C1 of cursor insert into ' || targetTable;

call db2load(1, v_cursor_statement, v_load_command, v_sqlcode,
          v_sqlmessage, v_rows_read, v_rows_skipped,
          v_rows_loaded, v_rows_rejected, v_rows_deleted,
          v_rows_committed, v_rows_part_read,
          v_rows_part_rejected, v_rows_part_partitioned,
          v_mpp_load_summary) ;
end#

Please note that the target table must already be created in order for this procedure to work.
This procedure was the hard part. Copying the contents of any table requires a dynamic prepared query statement to use as a cursor, and the load command is not available inside a procedure, so the db2load procedure from SYSPROC must be used.

Here is the procedure that will create a schema, create the tables on it and copy the contents:

/**
* Copies an entire schema into a new schema named with the current date.
* @sourceSchema: the source schema name
* @targetSchema: the target schema name
* @tableNameSelection: the tables name to include ('%' for all tables)
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.COPY_DATABASE(
sourceSchema VARCHAR(50),
targetSchema VARCHAR(50),
tableNameSelection VARCHAR(150)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtSchema VARCHAR(250);
DECLARE stmtTableStructure VARCHAR(200);
DECLARE stmtTableContents VARCHAR(250);
DECLARE stmtAlias VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE numPages BIGINT;
DECLARE nError INTEGER DEFAULT 0;
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE V_LOAD_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH HOLD WITH RETURN FOR V_STMT;
DECLARE LOAD_CUR CURSOR FOR V_LOAD_STMT;
DECLARE CONTINUE HANDLER for not_found SET at_end = 1;


-- Create schema
SET stmtSchema = char('CREATE SCHEMA ' concat char(targetSchema));
EXECUTE IMMEDIATE stmtSchema;
SET CURRENT SCHEMA targetSchema;


-- Copy tables and views
SET V_SQL = 'SELECT name, npages FROM SYSIBM.SYSTABLES
        WHERE CREATOR = ''' || trim(sourceSchema) || '''
        AND NAME LIKE ''' || trim(tableNameSelection) || '''
        order by name';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;

fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName, numPages;
IF at_end <> 0 THEN
   LEAVE fetch_loop;
ELSE   
   SET stmtTableStructure = 'CREATE TABLE ' ||
       targetSchema || '.' || tableName || ' LIKE ' ||
       sourceSchema || '.' || tableName ;
   EXECUTE IMMEDIATE stmtTableStructure;

   IF numPages > 0 THEN
      call COPY_DATABASE_SCHEMA.copy_table(
              sourceSchema || '.' || tableName,
              targetSchema || '.' || tableName);
   END IF;
END IF;
END LOOP fetch_loop;

CLOSE TGT_TABLE_CUR;
END#

It dynamically issues DB2 SQL commands to create the schema and the tables structure and makes use of the previously created COPY_TABLE to copy the tables content. See the code comments for more information.

To use this just

call COPY_DATABASE_SCHEMA.COPY_DATABASE('originalSchemaName',
          'newBackupSchemaName', '%');

Since I also need to discard old copies, I have created a procedure to drop a schema, including all the tables on it.

/**
*  Drops an entire schema even if it has tables.
*  Views are not supported.
*  @schemaName: the name of the schema to drop
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.DROP_SCHEMA(
schemaName VARCHAR(50)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtDropSchema VARCHAR(250);
DECLARE stmtDropTable VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH RETURN FOR V_STMT;
DECLARE CONTINUE HANDLER for not_found SET at_end = 1;

-- Copy tables and views
SET V_SQL = 'SELECT name FROM SYSIBM.SYSTABLES
   WHERE CREATOR = ''' || trim(schemaName) || '''
   AND CREATOR NOT LIKE ''SYS%''';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;

fetch_loop:
LOOP
    FETCH TGT_TABLE_CUR INTO tableName;
    IF at_end <> 0 THEN LEAVE fetch_loop;
    ELSE
       SET stmtDropTable = 'DROP TABLE ' ||
                   schemaName || '.' || tableName;
       EXECUTE IMMEDIATE stmtDropTable;
    END IF;
END LOOP fetch_loop;

CLOSE TGT_TABLE_CUR;

-- Drop schema
SET stmtDropSchema = char('DROP SCHEMA ' concat
             char(schemaName) concat ' RESTRICT');
EXECUTE IMMEDIATE stmtDropSchema;
END#

This procedures have some limitations, for instance views are not supported.
Nevertheless, it is a good start to all that need to clone a schema or a table.

./M6

Layer Intromission

I'm using JTOpen JDBC driver to access AS/400 via DB2.
It works pretty well, but unfortunately it tries to be more than it should be, a JDBC driver that allows a Java application to access a DB2 database.

It interacts with the AS/400 system in a way it should not. For instance, when it's time to change the password, the driver pops-up a notification window stating that it's time to change the password and asks the user if he wishes to do it now.
If this feature seems nice, let me tell you that it is totally wrong!
First, it is a JDBC driver, not an AS/400 system administration application.
Second, this kind of promiscuity among layers has disastrous results.

When one uses a JDBC driver one expects that it operates with the database and nothing else.
When JTOpen starts getting "smart" by interacting with the system, awkward things happen.
For instance, an entire critical system can fail because the driver hang on a "password will expire soon, do you wish to change it now" message and waits for user action instead of doing what it is suppose to do: database work.
Please not that this is not a "password expired" message but a "you must change your password within x days, do you wish to do it now" question.

Here is a real example of such problem: I executed a command line batch which was hold by the JTOpen driver "would you like to change your password now" question.

JTOpen "Would you like to change your password now" User Dialog Box Question.

I got lucky because the command was issued by me, but what if it was issued by a scheduler on a Saturday morning? It would only be noticed Monday morning and a critical job would not have been performed because the JDBC driver messed up.

./M6