Pedro's blog: db2, mysql, php, linux and performance


iconize and apache mod_alias
01/03/2011, 21:01
Filed under: Apache,General,GNU/Linux | Tags: , , , ,

Iconize is a cool CSS layout with a set of icons to iconize each kind of link inside your webpage. As I decided to use them in one of my personal projects I found out a little trouble with it because it didn’t work on the first try. Thinking on how http works I tried to find if the directory icons/ had some visibility from the internet and what i discovered? it worked but… wasn’t the icons folder of Iconize. WTF! was the icons folder of Apache!! so… working on Ubuntu:

grep -R icons /etc/apache2/*

And there it goes:

/etc/apache2/mods-enabled/alias.conf:#Alias /icons/ “/usr/share/apache2/icons/”

I added a comment on the line like:

#Alias /icons/ “/usr/share/apache2/icons/

And rebooted Apache:

/etc/init.d/apache2 restart

Problem solved, everything working.

Thank you pooliestudios.com people! Iconize is a great work!



db2 change LOGARCHMETH1

Long time ago since last update… Busy time.

To improve my default db2 udb installation I switched form circular logging to disk logging, the steps:

#1#mkdir /db2backup

#2#mkdir /db2archive

#3#mkdir /db2archive/db2inst1

#4#chown db2inst1:db2admin /db2archive/db2inst1

#5#chown db2inst1:db2admin /db2backup

#6#db2 update db cfg for MYDB using logarchmeth1 disk:/db2archive

#7#db2 backup db MYDB to /db2backup

#8#db2 archive log for database MYDB

1,2,3: for storing backups and archive logs

4,5: setting owners

6: switching to db2 disk logging in /db2archive

7: after enabling log archiving, DB2 sets the database into the backup pending state to ensure that a full offline backup is taken before starting with log archiving so I backup. If the backup gives back an error like:

SQL1035N  The database is currently in use.  SQLSTATE=57019

You should:

#db2 list applications

#db2 force application all

8: to verify that the database is ready for log archiving

Now you can check your backups or the archive history

#db2 list history backup all for MYDB

#db2 list history archive log all for db MYDB



SQL0668N Operation not allowed for reason code “1”
04/07/2010, 20:40
Filed under: DB2,General | Tags: , , ,

What a nice message code!

What does it mean? if we try the command:

db2 ? SQL0668N

It says:

QL0668N  Operation not allowed for reason code “<reason-code>” on table
“<table-name>”.

Explanation:

Access to table “<table-name>” is restricted. The cause is based on the
following reason codes “<reason-code>”:

1

The table is in the Set Integrity Pending No Access state. The
integrity of the table is not enforced and the content of the
table may be invalid. An operation on a parent table or an
underlying table that is not in the Set Integrity Pending No
Access state may also receive this error if a dependent table
is in the Set Integrity Pending No Access state.

Normally the same error message tell you what table has the problem, but, how can you know what is the table with that problem?

Try with the comand:

db2 “select TABSCHEMA, TABNAME from SYSCAT.TABLES where TYPE = ‘T’ and STATUS = ‘C'”

And you will get a list with all the tables with the SQL0668N rc=1 problem. How can you fix it? try with this comand:

db2 “SET INTEGRITY FOR TABSCHEMA.TABNAME IMMEDIATE CHECKED”

And everything is working again.



DB2, Zend, ajax, and the charset codification.
22/05/2010, 11:43
Filed under: DB2,General,PHP | Tags: , , , ,

Hi again, I have to apologize because I’ve been in holidays and I didn’t write so often in last weeks.

On last week I had a problem with the codification in DB2 and the codification of the html layout.

The database is defined with this line:

db2 “CREATE DATABASE D2MU AUTOMATIC STORAGE YES ON ‘/home/db2inst1’ ALIAS D2MU USING CODESET ISO-8859-15 TERRITORY ES”

And the html layout includes this meta tag:

<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-15” />

So, if I disable the layout (with zend):

$this->_helper->viewRenderer->setNoRender();
$this->_helper->layout()->disableLayout();

The special chars in the text obtained from the database were not show in the proper codification, what I did for fixing it? I included a line in the HTTP headers like:

header (‘Content-type: text/html; charset=ISO-8859-15‘);

And the problem was solved!



Protecting .svn folders with htaccess
09/04/2010, 21:01
Filed under: Apache,GNU/Linux,Security | Tags: , , ,

After configuring a subversion server for web projects you should take care about the .svn directories and files, ¿how may I secure them from the Internet savage? I found an easy way to do it with the .htaccess file:

RewriteEngine on
RewriteRule .*\.svn/.* – [F]

Don’t forget to enable mod_rewrite, and if you are already using some rule like

RewriteRule ^.*$

be sure of writing first the new rule:

RewriteRule .*\.svn/.* – [F]

RewriteRule ^.*$

Source: http://forum.webfaction.com/viewtopic.php?id=1069





phpMyAdmin and mod_security
15/03/2010, 21:08
Filed under: Apache,GNU/Linux,Security | Tags: , , , , ,

Sometimes a high level of security in a website can be painful. In my case, I lost some phpMyAdmin functionalities because the use of mod_security. The /var/log/httpd/modsec_debug.log said:

[15/Mar/2010:20:31:42 +0100] [thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][2] Warning. Pattern match “(?:\b(?:(?:s(?:elect\b(?:.{1,100}?\b(?:(?:length|count|top)\b.{1,100}?\bfrom|from\b.{1,100}?\bwhere)|.*?\b(?:d(?:ump\b.*\bfrom|ata_type)|(?:to_(?:numbe|cha)|inst)r))|p_(?:(?:addextendedpro|sqlexe)c|(?:oacreat|prepar)e|execute(?:sql)?|makewebtask)|ql_(? …” at ARGS:prev_sql_query. [file “/etc/httpd/modsecurity.d/modsecurity_crs_40_generic_attacks.conf”] [line “66”] [id “950001“] [msg “SQL Injection Attack“] [data “select * from `users`“] [severity “CRITICAL”] [tag “WEB_ATTACK/SQL_INJECTION”]


[15/Mar/2010:20:31:42 +0100] [
thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][2] Warning. Pattern match “(?:\b(?:(?:s(?:elect\b(?:.{1,100}?\b(?:(?:length|count|top)\b.{1,100}?\bfrom|from\b.{1,100}?\bwhere)|.*?\b(?:d(?:ump\b.*\bfrom|ata_type)|(?:to_(?:numbe|cha)|inst)r))|p_(?:(?:addextendedpro|sqlexe)c|(?:oacreat|prepar)e|execute(?:sql)?|makewebtask)|ql_(? …” at ARGS:sql_query. [file “/etc/httpd/modsecurity.d/modsecurity_crs_40_generic_attacks.conf”] [line “66”] [id “950001“] [msg “SQL Injection Attack“] [data “select * from `users` where“] [severity “CRITICAL”] [tag “WEB_ATTACK/SQL_INJECTION”]


[15/Mar/2010:20:31:42 +0100] [
thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][1] Access denied with code 400 (phase 2). Pattern match “\%(?!$|\W|[0-9a-fA-F]{2}|u[0-9a-fA-F]{4})” at ARGS:sql_query. [file “/etc/httpd/conf/modsecurity/modsecurity_crs_20_protocol_violations.conf”] [line “64”] [id “950107“] [msg “URL Encoding Abuse Attack Attempt”] [severity “WARNING”]

So, I had to add some local rules to the file /etc/httpd/modsecurity.d/modsecurity_localrules.conf

<LocationMatch “/phpMyAdmin/import.php”>
SecRuleRemoveById 950107
SecRuleRemoveById 950001
</LocationMatch>

I restarted the apache and everything is working!

Updated (16/03/2010):

I still had some troubles, so, I decided to disable mod_security totally on phpMyAdmin directory with the line:

SecRule REQUEST_URI ^/phpMyAdmin phase:1,allow,ctl:ruleEngine=off

In the file /etc/httpd/modsecurity.d/modsecurity_localrules.conf

Cheers!



DB2 export UTF8
25/02/2010, 18:54
Filed under: DB2,GNU/Linux,macosx | Tags: , , , , ,

Some tips and tricks.

Exporting data from DB2 to files in linux (I like EXPORT but you also can use UNLOAD or a simple SELECT and the redirection >):

$ db2 EXPORT TO “/tmp/unload.txt” OF del MODIFIED BY COLDEL, CHARDEL* SELECT * FROM MYTABLE

This command will export the table MYTABLE to the file /tmp/unload.txt formatting the fields like *COL1*,*COL    2*,*COL NUM 3*,…,*COL N*

In MacOSX formatting the files from UTF-8 format to ISO-8859-1 format (because the special spanish chars áéíóúñ…):

$ iconv -f UTF-8 -t ISO-8859-1 unload.txt > unload_8859.txt



DB2, PDO, BLOBs and XML with Zend
21/02/2010, 12:46
Filed under: DB2,PHP | Tags: , , ,

What’s the secret of using PHP with PDO over DB2? there are a lot of secrets! one of them is the use of blobs and xml files. I’m going to show how I made it work.

First of all I’m working with big files, so I need a way of handling big files from the the HTTP request (working with Zend), here it comes:

$upload         = new Zend_File_Transfer_Adapter_Http();

$registry         = Zend_Registry::getInstance(); //Getting the registry with the config values
$config            = $registry->configuration; //Getting the config
$destination     = $config->upload->directory; //Getting the upload directory

$upload->setDestination($destination);
//You can add validators here…
//$upload->addValidator(‘ExcludeMimeType’, false, ‘application’);

if (!$upload->receive()) {
//Do something because there is an error!
}

//Getting the full file name and the file name
$fileName          = $upload->getFileName(‘archivo’);
$filenameshort = $upload->getfileName(‘archivo’, false);

try{

//Opening the file and reading it…
$fileHandle = fopen($fileName, “r”);

if($fileHandle){
$fileContent = fread($fileHandle, filesize($fileName));

if (!is_string($fileContent)) {

//Do something with the error

}
}else {
//Do something with the error
}
//We got an error!
}catch(Exception $e){

//Do something with the error
//And removing the file..
unlink($fileName);
return $this->_redirect(‘main/add’);
}

Now we have the file source in $fileContent and we can play with it. Easy, right?

In the case I want to add some XML files after generating them or from the file system… how can I do it? IBM offers a set of UDF files called “XMLFromFile functions” (you can find them in http://www.ibm.com/developerworks/exchange…) because if the XML tree is getting super big, there are some troubles with the maximum SQL insert sentence size. Better to use this User defined functions:

$datos[‘ARCHIVO_CARACTERISTICAS’]   =

new Zend_Db_Expr(” clobFromFile(‘”.$xmlObject->getFileName().”‘) “);

//The function getFileName returns the full file name of the XML file on the system and the Zend_Db_Expr doesn’t add any extra quotes to the file name.

After we load the binary file in a PHP variable and we know how to get an XML file from the file system, the rest is to add everything to the database:

$datos[‘ARCHIVO_SOURCE’]  = $fileContent;

$datos[‘ARCHIVO_CARACTERISTICAS’]   =

new Zend_Db_Expr(” clobFromFile(‘”.$xmlObject->getFileName().”‘) “);

$tabla = new tablaArchivo();
$tabla->insert($datos);

The database table tablaArchivo looks like:

class tablaArchivo extends Zend_Db_Table_Abstract{
protected $_name     = ‘ARCHIVO’; //Table real name
protected $_primary = ‘ARCHIVO_ID’; //Table primary key
//References to other tables and other stuff here…
}

There you go! an easy way of loading binary files and xml files on DB2 with the help of PHP, PDO, the IBM UDF XMLFromFile functions and Zend.

Sources:

XMLFromFile functions

PDO_IBM

DB2 Express-C



Apache died after updating Ubuntu, pdo_ibm.so and php_pdo_declare_long_constant
06/02/2010, 12:51
Filed under: Apache,DB2,GNU/Linux,PHP | Tags: , , , , ,

After some months I installed my Ubuntu Server, yesterday I decided to update it, so:

$ apt-get update

$ apt-get upgrade

Between all the packages I found out php5, php5-dev, php5-common, php5-cli and libapache2-mod-php5, none of them should be dangerous… or maybe yes. I reboot and… crap, Apache doesn’t work! I tried to restart the daemon and got nothing, didn’t want to work. I took a look to the /var/log/apache2/error.log and I found:

/usr/sbin/apache2: symbol lookup error: /usr/lib/php5/20060613/pdo_ibm.so: undefined symbol: php_pdo_declare_long_constant

Something was wrong with the pdo_ibm module… I reinstalled again:

$ wget http://pecl.php.net/get/PDO_IBM-1.2.5.tgz

$ tar zxvf PDO_IBM-1.2.5.tgz

$ cd PDO_IBM-1.2.5

(Removed the line with the PDO dependency from the package.xml file: <dep type=”pkg” rel=”has” version=”1.0.0″>PDO</dep>)

$ pecl uninstall package.xml

$ pecl install package.xml

I tried again /etc/init.d/apache start and still didn’t work, so I decided to reinstall PDO:

$ pecl uninstall pdo

$ pecl install pdo

And after started apache /etc/init.d/apache start it worked!



ZDE on Snow Leopard makes code disappear!
05/02/2010, 18:22
Filed under: General,macosx,PHP | Tags: , , ,

What a surprise, just moved from MacOSX Leopard to Snow Leopard and what did I find?

Zend Development Studio doesn’t like the Java version already installed, the code is disappearing! the Java I had:

$ java -version
java version “1.6.0_17”
Java(TM) SE Runtime Environment (build 1.6.0_17-b04-248-10M3025)
Java HotSpot(TM) 64-Bit Server VM (build 14.3-b01-101, mixed mode)

I research a little and I found the fix: OS X 10.6 Snow Leopard Java problems (and fix)

At the end, everything working on 1.5:

$ java -version
java version “1.5.0_19”
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_19-b02-304)
Java HotSpot(TM) Client VM (build 1.5.0_19-137, mixed mode, sharing)

Enjoy it!

http://wiki.oneswarm.org/index.php/OS_X_10.6_Snow_Leopard