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