25 Useful IPtable Firewall Rules Every Linux Administrator Should Know

Just came across 25 Useful IPtable Firewall Rules Every Linux Administrator Should Know.

./M6

How to Recover SQL Server 2008 Password

Here's how to recover the SA password on a Microsoft SQL Server 2008 database: How to recover SA password on Microsoft SQL Server 2008 R2

./M6

58 Cool Linux Hacks

Here's a set of useful linux commands: 58 Cool Linux Hacks.

./M6

MySQL Root Password Recovery

Having trouble with a lost MySQL root password or root login not working?
Here's a step-by-step MySQL Root Password Recovery tutorial.
./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

Reset Windows and Linux Passwords

I've just came across a fantastic tool for system administration.
Ever used a system that no one recalls the administration password?
Or have you ever inherited a system that the only person who administrated it retired?

This tool will certainly ease the trouble of resetting the administration password: Kon-Boot.

./M6