ZON ISP (Bad) as Usual

I'm considering changing my Internet Service Provider (ISP).

I currently have a optical fiber connection, fiber to the home (FTTH), from TVTel at 30Mbit/s.
But TVTel was acquired by ZON and so I'm considered migrating to it, since the value for money seems better, at approximately the same cost I can get a 50Mbit/s optical fiber connection.

But ZON has three major problems:

1. Extremely bad reputation when it comes to ISP;
2. Lousy customer service;
3. the ZON optical fiber solution is not FTTH.
   

They are fighting the first couple of problems and the fact is that the number of complaints against them has seen a serious decrease.
But all this made me delay the decision for some months, to see if there were other ISPs that would provide me FTTH.
Unfortunately no one else provides FTTH on the area, and since ZON seems to be discontinuing the TVTel infrastructure that they bought, that leaves the optical fiber to the local node and coaxial cable solution.

So, I decided to give it a try, specially because there is a one month trial period were one can cancel the service if one is not satisfied.
And currently, I'm not a satisfied customer. The 50Mbit/s connection on the signed contract is actually working at less than 10% of the speed.
Here is this morning speed measure from the organization responsible for the internet in Portugal, Fundação para a Computação Científica Nacional
(FCCN), where it says that this morning I almost got an "astonishing" 5Mbit/s connection:

ZON internet connection speed meter.

Look at the red graph speed on the right of the picture. Yes, it says that if we were in 2002 and using an ADSL 4Mbit/s connect that would be great. Unfortunately this we're on the end of the first decade of the 21st Century and I have a signed contract for 50Mbit/s.
For details, check the numbers on the left, the first number is the maximum download speed, the second is the average download speed, the third is the maximum upload speed and the fourth is the average upload speed.
There's no doubt what so ever, ZON optical fiber connection is easily beaten by a 8Mbit/s ADSL connection.

Here's the download information detail

ZON internet download speed.

Check the graphs on the bottom. It not even gets close to 5Mbit/s and the average speed is just above 1.2Mbit/s and the speed is just not constant, it jumps up and down.
Remember that this is an optical fiber 50Mbits/s connection.

And here is my current TV Tel 30Mbit/s download speed data, taken immediately after I got the lousy speed from ZON.

TVTel internet download speed.

The average speed is just below 22MBit/s, the first value on the left. The maximum speed is just above 22MBit/s and the minimum speed is just above 20Mbit/s. The connection is stable, regular, linear, smooth and, above all, fast!

Let me correct my first sentence on this post: "I was considering changing my ISP", because with a service like this one, ZON will definitely not get a contract with me.
But the first ISP that provides me FTTH will probably.

./M6

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