Configurando y usando Informix en Raspberry PI

Configurando y usando Informix en Raspberry PI

Una vez instalado todo, ahora tenemos que configurar el motor de base de datos y levantarlo.

Configuración y primeros pasos para levantar el motor de base de datos.
Como usuario Informix

Primero, exportar estas tres variables de ambiente:

export INFORMIXDIR=/opt/IBM/informix
export PATH=$PATH:$INFORMIXDIR/bin
export INFORMIXSERVER=ol_informix_raspi

Ponerlas en el .bashrc para dejarlas por defecto a nuestro usuario informix.

[email protected] ~ $ tail .bashrc
# this, if it's already enabled in /etc/bash.bashrc and /etc/profile
# sources /etc/bash.bashrc).

if [ -f /etc/bash_completion ] && ! shopt -oq posix; then
. /etc/bash_completion
fi

export INFORMIXDIR=/opt/IBM/informix
export PATH=$PATH:$INFORMIXDI/bin
export INFORMIXSERVER=ol_informix_raspi

Copiamos los dos archivos de configuración de ejemplo (.std) por sus homónimos sin extensión.

[email protected] ~ $ cp $INFORMIXDIR/etc/onconfig.std $INFORMIXDIR/etc/onconfig
[email protected] ~ $ cp $INFORMIXDIR/etc/sqlhosts.demo $INFORMIXDIR/etc/sqlhosts

Vamos al directorio de instalación del Informix,

[email protected] ~ $ cd /opt/IBM/informix

Generamos el directorio chunks (que va a tener los enlaces simbólicos a nuestros raw devices ya generados)

[email protected] /opt/IBM/informix $ mkdir chunks

Vamos al directorio chunks y generamos los enlaces simbólicos a los raw devices

[email protected] /opt/IBM/informix $ cd chunks/
[email protected] /opt/IBM/informix/chunks $ ln -s /dev/raw/raw1 rootdbs
[email protected] /opt/IBM/informix/chunks $ ln -s /dev/raw/raw2 datadbs00
[email protected] /opt/IBM/informix/chunks $ ln -s /dev/raw/raw3 datadbs01
[email protected] /opt/IBM/informix/chunks $ ln -s /dev/raw/raw4 tempdbs00
[email protected] /opt/IBM/informix/chunks $ ln -s /dev/raw/raw5 logsdbs

Verificamos que los enlaces hayan quedado bien.

[email protected] /opt/IBM/informix/chunks $ ls -la
total 8
drwxr-xr-x  2 informix informix 4096 Jun 20 12:47 .
drwxr-xr-x 19 informix informix 4096 Jun 20 11:02 ..
lrwxrwxrwx  1 informix informix   13 Jun 20 11:05 datadbs00 -> /dev/raw/raw2
lrwxrwxrwx  1 informix informix   13 Jun 20 11:06 datadbs01 -> /dev/raw/raw3
lrwxrwxrwx  1 informix informix   13 Jun 20 12:47 logsdbs -> /dev/raw/raw5
lrwxrwxrwx  1 informix informix   13 Jun 20 11:05 rootdbs -> /dev/raw/raw1
lrwxrwxrwx  1 informix informix   13 Jun 20 11:06 tempdbs00 -> /dev/raw/raw4

Editamos el archivo $INFORMIXDIR/etc/onconfig

vi $INFORMIXDIR/etc/onconfig

Buscar las siguientes variables y poner como valor:

ROOTPATH /opt/IBM/informix/chunks/rootdbs
DBSERVERNAME ol_informix_raspi
TAPEDEV /dev/null
LTAPEDEV /dev/null
LOGFILES 10

Editamos el archivo $INFORMIXDIR/etc/sqlhosts

vi $INFORMIXDIR/etc/sqlhosts

Borramos todo el contenido y agregamos la siguiente línea

ol_informix_raspi       onsoctcp        localhost       9125

Una vez hecho esto, inicializamos (algo asi como formatear el disco) la instancia de Informix.

[email protected] /opt/IBM/informix/etc $ oninit -iv

This action will initialize IBM Informix Dynamic Server;
any existing IBM Informix Dynamic Server databases will NOT be accessible -
Do you wish to continue (y/n)? y   <--- confirmamos

Reading configuration file '/opt/IBM/informix/etc/onconfig'...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded
Allocating and attaching to shared memory...succeeded
Creating resident pool 3238 kbytes...succeeded
Creating infos file "/opt/IBM/informix/etc/.infos.ol_informix_raspi"...succeeded
Linking conf file "/opt/IBM/informix/etc/.conf.ol_informix_raspi"...succeeded
Initializing rhead structure...rhlock_t 16384 (512K)... rlock_t (1875K)... Writing to infos file...succeeded
Initialization of Encryption...succeeded
Initializing ASF...succeeded
Initializing Dictionary Cache and SPL Routine Cache...succeeded
Bringing up ADM VP...succeeded
Creating VP classes...succeeded
Forking main_loop thread...succeeded
Initializing DR structures...succeeded
Forking 1 'soctcp' listener threads...succeeded
Starting tracing...succeeded
Initializing 8 flushers...succeeded
Initializing log/checkpoint information...succeeded
Initializing dbspaces...succeeded
Opening primary chunks...succeeded
Validating chunks...succeeded
Creating database partition...succeeded
Initialize Async Log Flusher...succeeded
Starting B-tree Scanner...succeeded
Init ReadAhead Daemon...succeeded
Init DB Util Daemon...succeeded
Initializing DBSPACETEMP list...succeeded
Init Auto Tuning Daemon...succeeded
Checking database partition index...succeeded
Initializing dataskip structure...succeeded
Checking for temporary tables to drop...succeeded
Updating Global Row Counter...succeeded
Forking onmode_mon thread...succeeded
Creating periodic thread...succeeded
Creating periodic thread...succeeded
Starting scheduling system...succeeded
Verbose output complete: mode = 5
[email protected] /opt/IBM/informix/etc $

Termina de levantar con la linea: Verbose output complete: mode = 5
Esto significa que el motor arrancó bien. Y, nos devuelve el shell.

Ejecutamosonstat -

[email protected] onstat - 
IBM Informix Dynamic Server Version 12.10.UC4DE -- On-Line -- Up 00:00:39 -- 52656 Kbytes

Lo que significa que, afirmativamente tenemos nuestro motor de base de datos andando

Ejecutamosonstat -m
Y tomamos nota del message log file (/opt/IBM/informix/tmp/online.log (lo vamos a necesitar))

[email protected] onstat -m
IBM Informix Dynamic Server Version 12.10.UC4DE -- On-Line -- Up 00:00:43 -- 52656 Kbytes

Message Log File: /opt/IBM/informix/tmp/online.log
11:46:07  Loading Module <BUILTINNULL>
11:46:12  DR: DRAUTO is 0 (Off)
11:46:12  DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
11:46:12  Event notification facility epoll enabled.
11:46:12  IBM Informix Dynamic Server Version 12.10.UC4DE Software Serial Number AAA#B000000
11:46:35  IBM Informix Dynamic Server Initialized -- Complete Disk Initialized.
11:46:35  Started 1 B-tree scanners.
11:46:35  B-tree scanner threshold set at 5000.
11:46:35  B-tree scanner range scan size set to -1.
11:46:35  B-tree scanner ALICE mode set to 6.
11:46:35  B-tree scanner index compression level set to med.
11:46:35  Dataskip is now OFF for all dbspaces
11:46:35  Building 'sysmaster' database ...
11:46:35  Checkpoint Completed:  duration was 0 seconds.
11:46:35  Sat Jun 20 - loguniq 1, logpos 0x3a8, timestamp: 0xae Interval: 2

11:46:35  Maximum server connections 0
11:46:35  Checkpoint Statistics - Avg. Txn Block Time 0.033, # Txns blocked 1, Plog used 4, Llog used 1

11:46:35  On-Line Mode

Después le hacemos un tail -f al archivo de mensajes (/opt/IBM/informix/tmp/online.log), y, esperamos a que nos aparezca la leyenda: 'sysadmin' database built successfully. Una vez aparecido ese mensaje, significa que, nuestra base esta 100% lista para usarse.

[email protected] /opt/IBM/informix/etc $ tail -f /opt/IBM/informix/tmp/online.log

11:46:35  B-tree scanner index compression level set to med.
11:46:35  Dataskip is now OFF for all dbspaces
11:46:35  Building 'sysmaster' database ...
11:46:35  Checkpoint Completed:  duration was 0 seconds.
11:46:35  Sat Jun 20 - loguniq 1, logpos 0x3a8, timestamp: 0xae Interval: 2
11:46:35  Maximum server connections 0
11:46:35  Checkpoint Statistics - Avg. Txn Block Time 0.033, # Txns blocked 1, Plog used 4, Llog used 1

11:46:35  On-Line Mode
11:46:58  Dynamically allocated new virtual shared memory segment (size 16384KB)
11:46:58  Memory sizes:resident:3504 KB, virtual:65536 KB, SHMTOTAL:1048576 KB
11:47:14  Booting Language <spl> from module <>
11:47:14  Loading Module <SPLNULL>
11:47:20  Unloading Module <SPLNULL>
11:47:26  Loading Module <SPLNULL>
11:47:28  Checkpoint Completed:  duration was 0 seconds.
11:47:29  Sat Jun 20 - loguniq 1, logpos 0x122e018, timestamp: 0x1396f Interval: 3

11:47:29  Maximum server connections 1
11:47:29  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 11, Llog used 4654
11:47:33  Logical Log 1 Complete, timestamp: 0x15bea.
11:47:33  Process exited with return code 127: /bin/sh /bin/sh -c /opt/IBM/informix/etc/alarmprogram.sh 2 23 "Logical Log 1 Complete, timestamp: 0x15bea." "Logical Log 1 Complete, timestamp: 0x15bea." "" 23001
11:47:43  Checkpoint Completed:  duration was 0 seconds.
11:47:43  Sat Jun 20 - loguniq 2, logpos 0x53c018, timestamp: 0x1a7df Interval: 4

11:47:43  Maximum server connections 1
11:47:43  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 54, Llog used 1686

11:47:59  Checkpoint Completed:  duration was 1 seconds.
11:48:03  Sat Jun 20 - loguniq 2, logpos 0xbd2018, timestamp: 0x215d4 Interval: 5

11:48:03  Maximum server connections 1
11:48:03  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 58, Llog used 1686

11:48:04  'sysmaster' database built successfully.
11:48:05  'sysutils' database built successfully.
11:48:05  'sysuser' database built successfully.
11:48:11  Building 'sysadmin' database ...
11:48:26  Logical Log 2 Complete, timestamp: 0x297d2.
11:48:26  Process exited with return code 127: /bin/sh /bin/sh -c /opt/IBM/informix/etc/alarmprogram.sh 2 23 "Logical Log 2 Complete, timestamp: 0x297d2." "Logical Log 2 Complete, timestamp: 0x297d2." "" 23001
11:48:43  'sysadmin' database built successfully.
11:48:43  SCHAPI: Started dbScheduler thread.
11:48:43  Auto Registration is synced
11:48:44  Updating Low Memory Manager to version 11
11:48:45  Installing patch to upgrade ph_task code. version(13.04)
11:48:45  SCHAPI: Started 2 dbWorker threads.
11:48:45  SCHAPI: Started Low Memory Manager thread.
11:48:45  Low Memory Manager is ON - Memory Limit 1048576 KB Thread ID 0x47594cd8
11:48:46  Low Memory manager Started
Total(Cap) 1048576 KB
Start Threshold 5120 KB
Stop Threshold 10240 KB
Idle Time 300 Sec
Call Internal Yes
Task Name Low Memory Manager
11:48:48  Checkpoint Completed:  duration was 6 seconds.
11:48:48  Sat Jun 20 - loguniq 3, logpos 0x823018, timestamp: 0x30154 Interval: 6

11:48:48  Maximum server connections 1
11:48:48  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 153, Llog used 4307

Bien tenemos nuestro motor andando. Ahora a darle el disco (raw devices) y hacer unos últimos ajustes. Falta poco. Vamos !

Creando los dbspaces (un dbspace es un agrupamiento lógico de disco (raw device o file (en este caso raw device)))

Creamos nuestro primer dbspace con uno de los raw devices que hicimos en la entrega previa.

[email protected] /opt/IBM/informix/chunks $ onspaces -c -d datadbs -p /opt/IBM/informix/chunks/datadbs00 -o 0 -s 2097152 

Verifying physical disk space, please wait ...
Space successfully added.

** WARNING **  A level 0 archive of Root DBSpace will need to be done.

Como creamos un nuevo dbspace nuevo nos pide un backup full de sistema, todavia no lo vamos a hacer

Agregamos un segundo raw device al dbspace

[email protected] /opt/IBM/informix/chunks $ onspaces -a datadbs -p /opt/IBM/informix/chunks/datadbs01 -o 0 -s 2097152

Verifying physical disk space, please wait ...
Chunk successfully added.

Creamos un dbspace llamado logsdbs de 500Mb

[email protected] /opt/IBM/informix/chunks $ onspaces -c -d logsdbs -p /opt/IBM/informix/chunks/logsdbs -o 0 -s 512000

Verifying physical disk space, please wait ...
Space successfully added.

** WARNING **  A level 0 archive of Root DBSpace will need to be done.

Generamos un dbspace temporal de 500 Mb tambien

[email protected] /opt/IBM/informix/chunks $ onspaces -c -d tempdbs -p /opt/IBM/informix/chunks/tempdbs00 -o 0 -s 512000

Verifying physical disk space, please wait ...
Space successfully added.

** WARNING **  A level 0 archive of Root DBSpace will need to be done.

Nos fijamos el archivo de mensajes:

12:50:13  Space 'datadbs' added.
12:50:29  Chunk '/opt/IBM/informix/chunks/datadbs01' added to space 'datadbs'.
12:51:40  Space 'logsdbs' added.
12:52:04  Space 'tempdbs' added.

Ejecutamos el backup completo del sistema

[email protected] /opt/IBM/informix/chunks $ ontape -s -L 0

Archive to tape device '/dev/null' is complete.
Program over.

Por último revisamos que nuestros chunks, estén en buen estado revisando los flags PO (que significan (Primary - Online) ) ejecutando onstat -d.

[email protected] /opt/IBM/informix/chunks $ onstat -d

IBM Informix Dynamic Server Version 12.10.UC4DE -- On-Line -- Up 01:08:01 -- 69040 Kbytes

Dbspaces
address  number   flags      fchunk   nchunks  pgsize   flags    owner    name
4451e8b0 1        0x60001    1        1        2048     N  BA    informix rootdbs
47999b80 2        0x60001    2        2        2048     N  BA    informix datadbs
45161978 3        0x60001    4        1        2048     N  BA    informix logsdbs
47445d98 4        0x60001    5        1        2048     N  BA    informix tempdbs

4 active, 2047 maximum

Chunks
address  chunk/dbs     offset     size       free       bpages     flags pathname
445db018 1      1      0          150000     65432                 PO-B-- /opt/IBM/informix/chunks/rootdbs
44de3018 2      2      0          1048576    1048523               PO-B-- /opt/IBM/informix/chunks/datadbs00
45161018 3      2      0          1048576    1048573               PO-B-- /opt/IBM/informix/chunks/datadbs01
44fc3018 4      3      0          256000     255947                PO-B-- /opt/IBM/informix/chunks/logsdbs
45088018 5      4      0          256000     255947                PO-B-- /opt/IBM/informix/chunks/tempdbs00

5 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always

[email protected] /opt/IBM/informix/chunks $

Ultimos ajustes: en el  onconfig - Cambiamos:

DBSPACETEMP tempdbs  <-- Agregamos tempdbs
DEF_TABLE_LOCKMODE row <-- Cambiamos page x row

Bajamos la base

[email protected] ~ $ onmode -yuck

Chequeamos que haya bajado

[email protected] ~ $ onstat -

shared memory not initialized for INFORMIXSERVER 'ol_informix_raspi'

Levantamos la base para que tome los cambios del default table lock mode y del dbspace temporal. Ejecutamos: oninit -v

[email protected] ~ $ oninit -v
Reading configuration file '/opt/IBM/informix/etc/onconfig'...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded
Allocating and attaching to shared memory...succeeded
Creating resident pool 3238 kbytes...succeeded
Creating infos file "/opt/IBM/informix/etc/.infos.ol_informix_raspi"...succeeded
Linking conf file "/opt/IBM/informix/etc/.conf.ol_informix_raspi"...succeeded
Initializing rhead structure...rhlock_t 16384 (512K)... rlock_t (1875K)... Writing to infos file...succeeded
Initialization of Encryption...succeeded
Initializing ASF...succeeded
Initializing Dictionary Cache and SPL Routine Cache...succeeded
Bringing up ADM VP...succeeded
Creating VP classes...succeeded
Forking main_loop thread...succeeded
Initializing DR structures...succeeded
Forking 1 'soctcp' listener threads...succeeded
Starting tracing...succeeded
Initializing 8 flushers...succeeded
Initializing SDS Server network connections...succeeded
Initializing log/checkpoint information...succeeded
Initializing dbspaces...succeeded
Opening primary chunks...succeeded
Validating chunks...succeeded
Initialize Async Log Flusher...succeeded
Starting B-tree Scanner...succeeded
Init ReadAhead Daemon...succeeded
Init DB Util Daemon...succeeded
Initializing DBSPACETEMP list...succeeded
Init Auto Tuning Daemon...succeeded
Checking database partition index...succeeded
Initializing dataskip structure...succeeded
Checking for temporary tables to drop...succeeded
Updating Global Row Counter...succeeded
Forking onmode_mon thread...succeeded
Creating periodic thread...succeeded
Creating periodic thread...succeeded
Starting scheduling system...succeeded
Verbose output complete: mode = 5

Chequeamos que haya levantado

[email protected] ~ $ onstat -

IBM Informix Dynamic Server Version 12.10.UC4DE -- On-Line -- Up 00:00:14 -- 69040 Kbytes

Y, por ultimo, generamos la base de datos de ejemplo (notar que instruimos que use el dbspace datadbs para la base)

[email protected] ~ $ dbaccessdemo -log -dbspace datadbs

DBACCESS  Demonstration Database Installation Script
Dropping existing stores_demo database ....
Creating stores_demo database ....
Lockmode set.
Database created.
Database closed.
Database selected.

BLA BLA BLA BLA

Database closed.
The creation of the demonstration database is now complete.  The remainder
of this script copies the examples into your current directory.
Press "Y" to continue, or "N" to abort. <-- Es conveniente apretar Y para después chusmear los scripts de creación.

Now copying SQL command files ....
End of DBACCESSDEMO script.

Y ahora nuestra primera consulta:

[email protected] ~ $ time dbaccess stores_demo << EOM
 > select count(*) from customer;
 > EOM

Database selected.
(count(*))
28

1 row(s) retrieved.

Database closed.
real 0m0.139s
user 0m0.060s
sys 0m0.020s

Mas adelante iré explicando, muy de a poco algunos aspectos internos de Informix, como asi tambien la interfaz de consultas dbaccess y onstat para monitorizarlo.

Un enlace para ir leyendo un poco

http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.welcome.doc/welcome.htm

Que es el sitio de documentacion oficial de IBM

Dudas? consultas? sugerencias ?

Hasta el próximo capítulo !!!