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


SQL16061N The value cannot be constructed as, or cast (using an implicit or explicit cast) to the data type SQLSTATE=10608
28/05/2011, 13:30
Filed under: DB2,PHP,xQuery | Tags: , , , , ,

Everything was working properly until one of the users add a summary artwork greater than the cast size in the xquery select.
Here the select i am using:

select A.ARCHIVO_ID,
F.ARCHIVO_FORMATO_DESCRIPCION_ES,
X.NOMBRE#,
X.RESUMEN#,
X.AUTOR#
from ARCHIVO A,
ARCHIVO_FORMATO F,
ARCHIVO_LOCALIZACION L,
ARCHIVO_TECNICA T,
ARCHIVO_CATEGORIA C,
ASIGNATURA_ARCHIVO AA,
ARCHIVO_BIBLIOGRAFIA B ,
xmltable(
‘$c/obra’ passing A.ARCHIVO_CARACTERISTICAS as “c” columns
NOMBRE# VARCHAR(100) path ‘nombre’,AUTOR# VARCHAR(100) path ‘autor’,
RESUMEN# VARCHAR(1000) path ‘resumen’
) as X
where A.ARCHIVO_ESTADO BETWEEN 1 AND 2 AND
A.ARCHIVO_PRIVADO = ‘N’ AND

The describe table:

db2 => describe table archivo

Data type Column
Column name schema Data type name Length Scale Nulls
——————————- ——— ——————- ———- —– ——
ARCHIVO_ID SYSIBM INTEGER 4 0 No
ARCHIVO_FORMATO SYSIBM INTEGER 4 0 No
ARCHIVO_TIPOLOGIA SYSIBM INTEGER 4 0 No
ARCHIVO_LOCALIZACION SYSIBM INTEGER 4 0 No
ARCHIVO_ESTADO SYSIBM INTEGER 4 0 No
ARCHIVO_VISITAS SYSIBM INTEGER 4 0 No
ARCHIVO_USUARIO_CREACION SYSIBM INTEGER 4 0 No
ARCHIVO_IP_CREACION SYSIBM VARCHAR 16 0 No
ARCHIVO_FECHA_CREACION SYSIBM TIMESTAMP 10 6 No
ARCHIVO_USUARIO_MODIFICACION SYSIBM INTEGER 4 0 Yes
ARCHIVO_IP_MODIFICACION SYSIBM VARCHAR 15 0 Yes
ARCHIVO_FECHA_MODIFICACION SYSIBM TIMESTAMP 10 6 Yes
ARCHIVO_PRIVADO SYSIBM VARCHAR 1 0 No
ARCHIVO_NOTA SYSIBM DOUBLE 8 0 Yes
ARCHIVO_CARACTERISTICAS SYSIBM XML 0 0 No
ARCHIVO_SOURCE SYSIBM BLOB 262144000 0 Yes

A XML file example:

<?xml version=”1.0″ encoding=”iso-8859-15″?>
<obra tipo=”Imagen”>
<nombre>Sin t&amp;iacute;tulo</nombre>
<autor>Vicente Ort&amp;iacute; Mateu</autor>
<periodo>Siglo XXI</periodo>
<archivo>42.jpg</archivo>
<resumen>escultura en m&amp;amp;aacute;rmol crema Valencia y madera, colecci&amp;amp;oacute;n particular</resumen>
<mime_type></mime_type>
<alto>2352</alto>
<ancho>2208</ancho>
<descriptores>
<descriptor></descriptor>
</descriptores>
</obra>

And here the error:

SQL16061N The value “El presente artículo constituye un inv…” cannot
be constructed as, or cast (using an implicit or explicit cast) to the data
type “VARCHAR_1000”. Error QName=err:FORG0001. SQLSTATE=10608

I had to change the cast from RESUMEN# VARCHAR(1000) to RESUMEN# VARCHAR(2000) because the select was failing and I was getting 0 results…



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!



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!



DB2, the date and PHP
04/02/2010, 18:58
Filed under: General | Tags: , , , ,

Here comes the trick:

The common format when I get a date row from the db2 database is:

echo $date; //gets 2010-02-04 18:21:42

How did I make the magic with php:

$date        = strftime(“%d/%m/%Y”, strtotime($date));

echo $date; //gets 04/02/2010

Source: http://php.net/manual/en/function.strftime.php



DB2 Ignoring Generated always restriction on loading
05/12/2009, 22:07
Filed under: DB2 | Tags:

A little trick:

CREATE TABLE “THEPROFILE”.”ROL”  (
“ROL_ID” INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +4
INCREMENT BY +1
MINVALUE +0
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
“ROL_NOMBRE” VARCHAR(50) NOT NULL ,
“ROL_DESCRIPCION” VARCHAR(255) NOT NULL )
IN “USERSPACE1” ;

If you want to load a new row and avoid the GENERATED ALWAYS AS IDENTITY then:

db2 “load from ‘rol.sql’ of del modified by identityoverride insert into db2admin.rol”

We know there are 4 rows on rol.sql.

Be careful with the state of the table space after running the load, maybe you’ll need this command:

db2 “SET INTEGRITY FOR THEPROFILE.ROL IMMEDIATE CHECKED”

Enjoy!



A Course History
28/11/2009, 12:00
Filed under: DB2,Mainframe | Tags: , , , ,

Hey buddies,

since some months I’m looking for an specialized IBM course on DB2, I already got the diploma of this one, but the thing I want is to get some more knowledge around zOS area, some one like this:

DB2 9 for z/OS Database Administration Workshop Part 1 (ES) (I couldn’t because we were 2 persons subscribed on 4 minimum )

Or this one:

DB2 9 for z/OS System Administration (ES) (I couldn’t because I was the only one subscribed…)

So, as my two database possibilities were gone, I decide to move forward and try over the System Performance line:

Basic z/OS Tuning Using the Workload Manager (WLM) (ES)

And yes! I got it on the third round.
Skills taught:
(more…)