Installation MySQL v5.1.48 on CentOS release 5.5 (Final)

Before to start, install all the prerequisites for relative platform (to know the version use cat /etc/redhat-release) (you can find rpm on http://rpm.pbone.net/), namely (installation with rpm -ivh):

• cairomm-1.2.4-1.el5.kb.i386.rpm
• glibmm24-2.12.7-1.el5.kb.i386.rpm
• gtkmm24-2.10.8-1.el5.kb.i386.rpm
• libsigc++20-2.0.18-1.el5.kb.i386.rpm 

then (with rpm -ivh MySQL-*):

• MySQL-client-community-5.1.48-1.rhel5.i386.rpm
• MySQL-devel-community-5.1.48-1.rhel5.i386.rpm
• MySQL-embedded-community-5.1.48-1.rhel5.i386.rpm
• MySQL-server-community-5.1.48-1.rhel5.i386.rpm
• MySQL-shared-compat-5.1.48-1.rhel5.i386.rpm

Regarding the successive configuration, please follow steps below:

• cp /usr/share/mysql/my-small.cnf /etc/my.cnf
• vi /etc/my.cnf, and add:

[mysqld]
basedir         = /usr
datadir         = /var/lib/mysql

• uncomment bind-address ip
• give the grants:

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

• check firewall state (iptables on CentOS) executing follow instructions:

iptables -I RH-Firewall-1-INPUT -p tcp --dport 3306 -j ACCEPT

English abbreviations and acronyms

Other here.

@	at
a/c	account
AGM	annual general meeting
a.m.	ante meridiem (before noon)
a/o	account of (on behalf of)
AOB	any other business
ASAP	as soon as possible
ATM	automated teller machine (cash dispenser)
attn	for the attention of
approx.	approximately
cc	copy to
CEO	chief executive officer
c/o	care of (on letters : at the address of)
Co	company
cm	centimetre
COD	cash on delivery
dept	department
e.g.	exempli gratia (for example)
EGM	extraordinary general meeting
ETA	estimated time of arrival
etc	et caetera (and so on)
FYA	for your attention
FYI	for your information
GDP	gross domestic product
GNP	gross national product
GMT	Greenwich mean time (time in London)
i.e.	id est (meaning : 'that is')
Inc	incorporated
IOU	I owe you
IPO	initial public offer
Jr	junior
K	thousand
lb	pound (weight)
£	pound (money)
Ltd	limited company
mo.	month
N/A	not applicable
NB	Nota Bene (it is important to note)
no.	number
PA	personal assistant
p.a.	per annum (per year)
Plc	public limited company
pls	please
p.m.	post meridiem (after noon)
p.p.	per pro (used before signing in a person's absence)
PR	public relations
p.s.	post scriptum
pto	please turn over
p.w.	per week
qty	quantity
R & D	research and development
re	with reference to
ROI	return on investment
RSVP	repondez s'il vous plait (please reply)
s.a.e.	stamped addressed envelope
VAT	value added tax
VIP	very important person

DB2, delete data in a table

delete from table_name

or, if the table contains a large number of rows and you get an error of "full transaction log", you can run the truncate statement:

v8
alter table table_name activate not logged initially with empty table

from v9.1
truncate table table_name immediate
More information about this new statement on precius InfoCenter.

DB2, changing the datatype of a column (only v9.7)

ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE datatype;

Examples:
create table pippo(idpippo decimal(18) not null, cdesc char(20));
alter table pippo add primary key (idpippo);
insert into pippo values (2,’ciaooo’);

ALTER TABLE pippo ALTER COLUMN cdesc SET DATA TYPE varchar(50);
ALTER TABLE pippo ALTER COLUMN cdesc SET DATA TYPE varchar(60);

You can not go back to CHAR.
ALTER TABLE pippo ALTER COLUMN cdesc SET DATA TYPE char(40);
KO: Reason code=”23?.. SQLCODE=-20054, SQLSTATE=55019

ALTER TABLE pippo ALTER COLUMN idpippo SET DATA TYPE decimal(28);
ALTER TABLE pippo ALTER COLUMN idpippo SET DATA TYPE decimal(3);

In most cases, ALTER SET DATA TYPE requires table reorganization (reorg statement) because it changes the physical row format.

DB2, move to UTF8 from IBM-1252

The only way to move to UTF8 from IBM-1252 is to create a new database with UTF8 codeset. Export data from IBM-1252 database and load to the new UTF8 database.
There are a few things to consider during the ETL exercise:

• Length of CHAR columns – You might have to increase the width of CHARACTER columns in your UTF8 database. Depending on the character in your database, the column width can grow between 1-4x.
• Use of String manipulation functions – Because of the difference in character byte length, you will need to take care to make sure that your application will work properly if you are using function such as SUBSTR.
• Collation – On UTF8 databases, DB2 supports binary collation as well as 3 different Unicode Collation Algorithm (UCA) based collations. You may need to investigate a bit to port your applications properly.

DB2, conversion from DECIMAL value to VARCHAR (without zero)

You can perform this conversion using the following syntax:
REPLACE( REPLACE( rtrim( ltrim( REPLACE( char(  ), '0', ' '))), ' ', '0'), '.', '')

In fact, having the following sample table:
DESCRIBE TABLE pippo

 

COLUMN                         Type      Type

name                           schema    name               Length   Scale Nulls

------------------------------ --------- ------------------ -------- ----- ------

IDPIPPO                        SYSIBM    DECIMAL                  18     0 Yes

CNUMPIPPO                      SYSIBM    VARCHAR                 120     0 Yes

 

  2 record(s) selected.

with a single tuple:

INSERT INTO pippo VALUES (250,NULL);

we can see the difference between a simple cast and the syntax shown above:
UPDATE pippo SET CNUMPIPPO = char(idpippo);
 
IDPIPPO    CNUMPIPPO
250    000000000000000250.

and with some optimizations:
UPDATE pippo SET CNUMPIPPO=REPLACE( REPLACE( rtrim( ltrim( REPLACE( char(idpippo), '0', ' '))), ' ', '0'), '.', '');
 
IDPIPPO    CNUMPIPPO
250    250

NB: syntax is not used when there are negative values.

JDBC connection, syntax for DBMS

Oracle Thin
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@server[:1521]:sid
More information
download driver

DB2

DB2 App (tipo 2)
COM.ibm.db2.jdbc.app.DB2Driver
jdbc:db2:database name
Read the following articles for more information: Overview of Java Development on DB2 and Understand the DB2 UDB JDBC Universal Driver
download driver

Description of the connection type 2

DB2 Jcc (tipo 4)
com.ibm.db2.jcc.DB2Driver
jdbc:db2://hostname:port(50000)/database name
Read the following articles for more information: Overview of Java Development on DB2 and Understand the DB2 UDB JDBC Universal Driver
download driver and licence


Description of the connection type 4

MySQL
com.mysql.jdbc.Driver
jdbc:mysql://hostname[:3306]/database name
download driver

MSSQL Server 2005
com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc:microsoft:sqlserver://hostname:1433;
More information
download driver

ODBC Bridge
sun.jdbc.odbc.JdbcOdbcDriver
jdbc:odbc:odbc name

DB2 and jdbc, how solve slow reading of metadata

Connecting to the database as instance administrator and run the command:
BIND db2schema.bnd BLOCKING ALL GRANT PUBLIC

CAST function to convert string to numeric value

Through the SQL statements, we learn to handle strings as numeric values:

create table pippo (id varchar(50));
delete from pippo;
insert into pippo values ('1223430');
insert into pippo values ('300030');

DB2
select * from pippo order by integer(id);

MySQL
select * from pippo order by cast(id as decimal);

Oracle
select * from pippo order by to_number(id);

How to Revise an Email So That People Will Read It

I found a very interesting article, I hope you like it. The author is David Silverman, a famous business writing teacher.

How to Revise an Email So That People Will Read It

"People think that the first draft is the big event and that revision is cleaning up afterward. But the first draft is really setting up the chairs, tables, and cups, and revision isn't cleaning up after the party, it is the party."

"All first drafts are terrible. I don't care if you're Hemingway."

"What comes out unfiltered from anyone's mind is mud."

The first two quotations come from writing professors whose names I've since forgotten (and they were quoting other people whom they'd forgotten). The last one is one I just made up myself. But regardless of the source, the advice is sound: no email should be clicked-to-send without revision.

I've found that for your average email, the number of revisions largely depends on the number of recipients. Here's my experience:

1 to 5 recipients = 2 to 4 revisions
5 to 10 recipients = 8 to 12 revisions
Company-wide or to Executive Committee = 30 to 50 revisions

Even the simplest missive to one person benefits from a couple of extra passes, and if it's going to the management committee, expect everyone to have changes (and changes to those changes).

Here's a checklist to consider when revising:

1. Delete redundancies. Say it once. That's enough. If you're repetitive, the reader will stop reading and start skimming. (Like you probably just did.)
2. Use numbers and specifics instead of adverbs and adjectives. "The project is currently way behind schedule on major tasks," is not as clear as "The project is 3 weeks late delivering hamburger buns to Des Moines." (If you don't have numbers, still get rid of the adverbs and adjectives.)
3. Add missing context. Does your reader know that hamburger buns in Iowa are required for the company to collect $37 million? If you're not sure, remind them.
4. Focus on the strongest argument. Should those hamburger buns get shipped because the delay is embarrassing for the company, because it's costing children their lunch, or because it's costing the company tens of millions of dollars? Maybe all three, but one of those reasons (and it depends on your reader) will be enough to get buns on the road.
5. Delete off-topic material. The best emails say one thing and say it clearly. One-subject emails also make it easier for the recipient to file the message once they've taken action, something anyone who uses Outlook to manage tasks appreciates.
6. Seek out equivocation and remove it. "It was the best of times, it was the worst of times" works for Dickens, not status reports.
7. Kill your favorites. Is something in your text particularly pithy, amusing, or clever? Chances are, it's not. If it sticks out, it's probably a tap-dancing gorilla in boxer shorts — hilarious when you thought of it, embarrassing when it gets in your manager's inbox.
8. Delete anything written in the heat of emotion. Will this sentence show them who's been right about the hamburger buns since the beginning? Yes? Cut it.
9. Shorten. Remember the reader struggling to digest your message on the run — a BlackBerry or an iPhone gets about 40 words per screen. What looks short on your desktop monitor is an epic epistle on their mobile device.
10. Give it a day. With time, what seemed so urgent may no longer need to be said. And one less email is something everyone will thank you for.

Do you agree that even late-night emails sent from the bar should be revised before sending? (Have you ever seen one the next day?) Have you bravely sent something unrevised only to have it come flying back at you? What's your best advice for revising?

Here you will find the original version.

Improving LOAD performance

Example of a LOAD instruction (small table with 219146 rows) and related costs:

LOAD FROM table.ixf of ixf INSERT INTO table;

The utility is beginning the "LOAD" phase at time 11:28:37.789940
The utility has finished the "LOAD" phase at time 11:29:09.774459
Total: about 32 sec.

LOAD FROM table.ixf of ixf SAVECOUNT 200000 INSERT INTO table DATA BUFFER 5000
SORT BUFFER 768 CPU_PARALLELISM 4;

The utility is beginning the "LOAD" phase at time 11:50:08.747113
The utility has finished the "LOAD" phase at time 11:50:29.627021
Total: about 21 sec.

Now we study the parameters that have influenced results:

CPU_PARALLELISM
Use this parameter to exploit intra-partition parallelism (if this is part of your machine’s capability), and significantly improve load performance. The parameter specifies the number of processes or threads used by the load utility to parse, convert, and format data records. The maximum number allowed is 30. If there is insufficient memory to support the specified value, the utility adjusts the value. If this parameter is not specified, the load utility selects a default value that is based on the number of CPUs on the system.
Record order in the source data is preserved regardless of the value of this parameter.
If tables include either LOB or LONG VARCHAR data, CPU_PARALLELISM is set to one. Parallelism is not supported in this case.
Although use of this parameter is not restricted to symmetric multiprocessor (SMP) hardware, you may not obtain any discernible performance benefit from using it in non-SMP environments.

DATA BUFFER
The DATA BUFFER parameter specifies the total amount of memory allocated to the load utility as a buffer. It is recommended that this buffer be several extents in size. An extent is the unit of movement for data within DB2, and the extent size can be one or more 4KB pages. The DATA BUFFER parameter is useful when working with large objects (LOBs); it reduces I/O waiting time. The data buffer is allocated from the utility heap. Depending on the amount of storage available on your system, you should consider allocating more memory for use by the DB2(R) utilities. The database configuration parameter util_heap_sz can be modified accordingly. The default value for the Utility Heap Size configuration parameter is 5 000 4KB pages. Because load is only one of several utilities that use memory from the utility heap, it is recommended that no more than fifty percent of the pages defined by this parameter be available for the load utility, and that the utility heap be defined large enough.

SORT BUFFER
This option specifies a value that overrides the sortheap database configuration parameter during a load operation. It is relevant only when loading tables with indexes and only when the INDEXING MODE parameter is not specified as DEFERRED. The value that is specified cannot exceed the value of sortheap. This parameter is useful for throttling the sort memory that is used when loading tables with many indexes without changing the value of sortheap, which would also affect general query processing.

Install DB2 Express-C in silent mode on Linux

This article teaches you the basics of how install, in silent mode and without X Server, DB2 Express-C with the support of the official guide and other authoritative documents.

1. Copy the installation tar file (my favorite mode to do this is through WinScp and Putty) and login as root on server.
2. Before start check if there are all prerequisites (they can have different names in other Linux distribution): compat-libstdc++, nfs-utils, libaio1, ksh, libstdc++5.
3. Now, after you have unpacked the archive containing the installation files ("tar -xvf" and/or "gunzip"), run the following statement:

./db2_install

4. As DB2 server has machine level authentication, now it's time to create every groups and users that we need:

Groups:
/usr/sbin/groupadd -g 999 db2iadm1
/usr/sbin/groupadd -g 998 db2fadm1
/usr/sbin/groupadd -g 997 dasadm1
/usr/sbin/groupadd -g 1000 db2dev

Users:
/usr/sbin/useradd -g db2grp1 -m -d /home/db2inst1 db2inst1 -p db2inst1
/usr/sbin/useradd -g db2fgrp1 -m -d /home/db2fenc1 db2fenc1 -p db2fenc1
/usr/sbin/useradd -g dasadm1 -m -d /home/dasusr1 dasusr1 -p dasusr1
/usr/sbin/useradd -g db2dev -m -d /home/db2user db2user –p db2user

It's necessary to verified the password:
passwd db2inst1
passwd db2fenc1
passwd dasusr1
passwd db2user

5. Instance creation: db2icrt -a SERVER -p 50000 -s wse -u db2fenc1 db2inst1
6. Das user creation: dascrt -u dasusr1
7. Verify default shell used by DB2 users:

vi /etc/passwd

From:

db2inst1:x:1001:1001::/home/db2inst1:/bin/sh

db2fenc1:x:1002:1002::/home/db2fenc1:/bin/sh

dasusr1:x:1003:1003::/home/dasusr1:/bin/sh

to:

db2inst1:x:1001:1001::/home/db2inst1:/bin/bash

db2fenc1:x:1002:1002::/home/db2fenc1:/bin/bash

dasusr1:x:1003:1003::/home/dasusr1:/bin/bash

8. Enabling remote administration as root user:

vi /etc/services

and add following lines:
ibm-db2 523/tcp # IBM DB2 DAS
ibm-db2 523/udp # IBM DB2 DAS
db2c_db2inst1 50000/tcp # IBM DB2 instance - db2inst1

as db2inst1 (instance administrator):

db2 update dbm cfg using SVCENAME db2c_db2inst1
db2set DB2COMM=tcpip
db2stop
db2start

and as dasusr1 (administration user):

db2admin stop
db2admin start

9. Instance automatically startup, as db2inst1:

 db2set DB2AUTOSTART=YES

as root:

vi /etc/init.d/db2

and put following lines:

#!/bin/bash
#
# Script to start DB2 instances on bootup.
#
set -e
. /lib/lsb/init-functions
case "$1" in
start)
/opt/ibm/db2/version/instance/db2istrt
;;
stop|restart|reload)
;;
esac
exit 0

10. Admin manager automatically startup:

as root:
vi /etc/rc.local

and put following lines:
su dasusr1 -c /home/dasusr1/script/startadmin.sh
exit 0

as dasusr1:
vi /home/dasusr1/script/startadmin.sh

and put following lines:
#!/bin/sh
# The following three lines have been added by UDB DB2.
if [ -f /home/dasusr1/das/dasprofile ]; then
. /home/dasusr1/das/dasprofile
fi
db2admin start

after:
chmod 777 /home/dasusr1/script/startadmin.sh

11. Optimizing the Linux kernel by editing the configuration (/etc/sysctl.conf) through the rules of document "The DB2 UDB memory model" in DeveloperWorks, beautiful web site:

vi /etc/sysctl.conf

Example 64 bit achitecture with 2G of RAM (please look "Tuning and Monitoring Database System Performance" redbook):
kernel.sem=250 256000 32 1024
kernel.shmmni=4096
kernel.shmmax=1610612736
kernel.shmall=1932735283
kernel.msgmni=1024
kernel.msgmax=65536
kernel.msgmnb=65536

12. Instance environment configuration, as db2inst1:

vi .bashrc

and put following lines:
# some more ls aliases
alias ll='ls -l'
alias la='ls -A'
alias l='ls -CF' 
export PATH=/home/db2inst1/scripts:$PATH
export DB2CODEPAGE=1252

after:

vi /home/db2inst1/sqllib/db2profile

and put following lines:

export DB2CODEPAGE=1252 (or your code page)
and as root:
vi /etc/profile
and put following lines:
DB2INSTANCE=db2inst1
export DB2INSTANCE
INSTHOME=/home/db2inst1
export DB2CODEPAGE=1252

DB2 CLP, How to turn autocommit off

By default DB2 uses autocommit, but you can disable it executing the "update command options".

Disable autocommit

update command options using c off
.
.
commit

Enable autocommit

update command options using c on

If executed from command line put all statements in a file and execute with:
db2 -tv +c -f filename

Install DB2 Client in silent mode on Linux

Please follow the steps below to install the DB2 Client v9.7 in silent mode:

1. Copy the installer into the directory you want on the server used. You can visit the IBM's website and find Data Server Client and Data Server Driver Package (optional, this package contains drivers and libraries for various programming language environments).
2. Follow carefully the paragraphs concerning the prerequisites to install. Read the document on IBM InfoCenter.
3.  Create the necessary users:

/usr/sbin/groupadd db2iadm1
/usr/sbin/useradd -g db2iadm1 -m -d /home/db2inst1 db2inst1 -p db2inst1

change the environment depending on the Linux distribution used. For example, Ubuntu uses bash so:

db2inst1:x:1003:1005::/home/db2inst1:/bin/sh
to:
db2inst1:x:1003:1005::/home/db2inst1:/bin/bash

4. You can find the samples of response file necessary to start-up the installer with -r option enabled on /path/client/db2/linux/samples. After you can execute:

./db2setup -r path/db2client.rsp

before to execute it you should edit the file and change follow parameters:

LIC_AGREEMENT             = ACCEPT         ** ACCEPT or DECLINE
db2inst1.NAME             = db2inst1
db2inst1.GROUP_NAME       = db2iadm1
db2inst1.HOME_DIRECTORY   = /home/db2inst1
db2inst1.PASSWORD         = password