Data Bob Jr.

THE DAY THE DATA DIED

2012-02-04T16:12:00.000-08:00

"THE DAY THE DATA DIED"-Loose Bruce Kerr Parody - For more amazing video clips, click here

What Data Tells Us

2012-01-31T20:18:00.000-08:00

This year's Strata ConferenceMaking Data Work,, will be hell in Santa Clara, California, USA from February 28 to March 1, 2012

Here is a video from last year's event.

Reference:

City -- Jackie Chan

2012-01-28T04:09:00.000-08:00

City by Jackie Chan. Also features NBA star Yao Ming and pianist Lang Lang

Friends -- Richard Clayderman

2012-01-28T03:56:00.001-08:00

Friends by Richard Clayderman

Set up ODBC Connection For IIS DataStage (v.8.5)

2012-01-26T05:59:00.000-08:00

Configure Access to ODBC Data Source For IIS DataStage (v8.5, Window Server)

Thie following information can also be found here.

From the Engine Tier;
· Launch the dos Prompt as Adminstratior
· Change to cd c:\windows\syswow64
· Lounch odbcad32.exe

C:\Windows\SysWOW64>odbcad32.exe

· Select a ODBC driver that you want to use, for example, SQL server. Make sure to only use drivers that are provided by IBM

· Define the DSN Connection by select the System DSN tab, then select ADD. Give the DSN a name, and provide the server, user id and password. You can also specify a default database.

When you are done, make sure to test the connection. It all is fine, you should be able to see the DSN from the IIS components such as DataStage or Information Analyzer.

Related Note:

Find Oracle Table Size, Number Of Columns and Rows

2012-01-26T05:46:00.000-08:00

Query to Find Table Size, Number of Rows and Columns in a Oracle Database

Here is a query that gives the table size which include number of columns, number of rows and table data size in MB. I found these information are helpful during the data warehouse design phase.

select col.table_name,

col.col_cnt as column_count,
rc.row_cnt as row_count,
s.size_in_MB as table_size_in_MB
from
(
/* number of columns */
SELECT upper(table_name), COUNT(*) col_cnt,
FROM dba_tab_columns
WHERE owner = 'V500'
group by upper(table_name)
) col

join

(
/* number of rows */
select
table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) as row_cnt
from dba_tables
where (iot_type != 'IOT_OVERFLOW'or iot_type is null)
and owner = 'SCOTT'
) rc
on upper(col.table_name) = upper(rc.table_name)

join

(
/* table size in MB */
SELECT
owner, table_name, (sum(bytes)/1024/1024) size_in_MB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
and owner = 'SCOTT'
)

group by owner, table_name

) s
on upper(col.table_name) = upper(s.table_name);

Related Notes:

Retrieve Oracle Table Column Name And Data Type

2012-01-26T05:37:00.000-08:00

Query to Get Oolumn Names and Data Type of an Oracle Table

Here is at query that retrieves column names and their data type of Oracle tables in a database. I found it handy to have a searchable copy of the source tables and their column name and data types when doing data profiling, modeling and mapping.

select owner,

table_name,
column_name,
data_type || decode(data_type,'DATE', ' ', '(' || data_length || decode(data_scale,null,'', ',' || data_scale) || ')' ) as Data_Type,
nullable,
data_default
from dba_tab_columns
where owner in('SCOTT','MARY') ;

If dba_tab_columns is not available, table user_tab_colimns will also work.

Related Notes:

Find Current Running SQL Statements In Oracle

2012-01-26T05:27:00.000-08:00

List Curent Running Queries From Oracle Database Serv er

Here was a query I used to check the queries which were submitted by the module “ASBAgent.exe” (the datastage ASB Agent, if you use DataStage). You can remove the module clause and the username clause to see all running queries .

SELECT first_load_time,
module,
sql_text,
username,
disk_reads_per_exec,
buffer_gets,
rows_processed,
hit_ratio
FROM
(SELECT module,
sql_text ,
u.username ,
round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec,
s.buffer_gets ,
100 - round(100 * s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio,
s.first_load_time
FROM sys.v_$sql s, sys.all_users u
WHERE s.parsing_user_id=u.user_id
and UPPER(u.username) ='SCOTT'
and module = 'ASBAgent.exe'
) s
order by first_load_time desc;

Related Notes:

Configuring DataStage v8.5 Parallel Engine (Windows)

2011-12-22T05:01:00.000-08:00

Configuring the DataStage v8.5 Parallel Engine On Windows

The information can be found in this document. I added a few points for Windows Server related steps since I am not familiar with Windows Servers.

If the parallel engine is installed on a computer that runs Microsoft Windows Server, Standard or Enterprise edition, you can configure the parallel engine for Microsoft Windows Server, 32-bit or 64 bit editions, by editing the Windows Registry and by changing system settings that control available memory. The Windows Registry includes keys that control desktop heap size, swap area size, the number of available TCP connections, and memory management.

1. Before Starting Configuration

Log in to Windows Server as the Administrator.
Start the Registry Editor, In Window Server 2008, open the DOS prompt as Administrator (right click on the dos prompt and select “Run as administrator”. From the DOS prompt, enter regedit

C:\Windows\system32>regedit

Back up the Windows Registry. If you plan to make several changes (for example, to configure the parallel engine), create a single backup before you make all the changes. To backup the registry, From the Registry Editor, select File -> Export and save the registry to a file.

2. Configuring the Windows Registry: Enabling auto-tuning for paged pool size

Enable auto-tuning for the paged pool size. Enabling auto-tuning ensures that sufficient memory is available for paged pools.

The Windows Server kernel allocates memory in pools. These pools are known as the paged pool and the non-paged pool. Performance degradation and server instability might result if the memory for these pools is exhausted. To avoid this situation, you can enable auto-tuning at server startup by editing the PagedPoolSize registry value in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management registry subkey.

2.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

(2.) in the right pane of the Registry Editor, right-click PagedPoolSize, and click Modify.
(3.) in the Base area, select Hexadecimal.
(4.) In the Value data field, specify 0 (zero). If 0 is already specified, auto-tuning is enabled, and you do not need to change this value. If you must specify a value other than 0, the value data for SystemPages must be 0.
(5.) Click OK.

3. Configuring the Windows Registry: Enabling auto-tuning for system page size

Enable auto-tuning for the system page size. Enabling auto-tuning ensures that sufficient memory is available for page tables and page table entries.

To support virtual to physical address translation, the Windows operating system uses page tables and page table entries (PTEs). These tables reside in kernel memory. If not enough memory is allocated for the page tables, Windows might fail to create processes, threads, and I/O buffers. Because the parallel engine creates many processes to run a job, jobs will fail at startup or during run time if Windows does not have enough resources to create processes.

You can enable auto-tuning at server startup by editing the SystemPages registry value in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management registry subkey.

3.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

(2.) In the right pane of the Registry Editor, right-click SystemPages, and click Modify.
(3.) In the Base area, select Hexadecimal.
(4.) In the Value data field, specify 0 (zero). Typically, specifying 0 for this value provides sufficient memory. If the available PTEs are exhausted, however, you can specify the maximum value, 0XFFFFFFFF. If you must specify a value other than 0, the value data for PagedPoolSize must be 0.
(5.) Click OK.

4 Configuring the Windows registry: Setting the threshold for de-committing memory

Set the threshold for de-committing memory. On computers that have 1 GB or more of RAM, setting the threshold to the suggested value improves memory management.

When Windows frees memory at a specific address, the memory can remain committed, or it can be de-committed and marked as reserved. You can improve memory management on computers that have 1 GB or more of RAM by editing the HeapDeCommitFreeBlockThreshold registry value in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager registry subkey. This registry value specifies the number of contiguous bytes of memory (around the address of the freed memory) above which the block of memory is de-committed.

4.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

(2.) Specify the value data for HeapDeCommitFreeBlockThreshold registry value:
(3.) If the registry value does not exist, click Edit > New > DWORD Value, and type the name of the registry value.
(4.) In the right pane of the Registry Editor, right-click the registry value, and click Modify.
(5.) In the Base area, select Hexadecimal.
(6.) In the Value data field, specify 0x000400.
(7.) Click OK.

5 Configuring the Windows registry: Increasing the noninteractive desktop heap size

Increase the noninteractive desktop heap size to ensure that a sufficient number of processes can be created and run concurrently.

DataStage® processes are created in a specific desktop heap. Each process consumes a small amount of memory from the desktop heap. If you expect to run many InfoSphere DataStage jobs concurrently, increase the size of the noninteractive desktop heap to allow more processes to be created and run concurrently.

You increase the size of the noninteractive desktop heap by editing the SharedSection parameter string in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems registry subkey. The SharedSection parameter string is located in the Windows registry value and uses the following format to specify heap size:

SharedSection=xxxx,yyyy,zzzz

Where:

· xxxx specifies the maximum size of the system-wide heap (in kilobytes)
· yyyy specifies the size of each desktop heap
· zzzz specifies the size of the desktop heap that is associated with a noninteractive Windows instance

5.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems

(2.) In the right pane of the Registry Editor, right-click the Windows registry value, and click Modify.

(3.) In the Value data field, locate the SharedSection parameter string, and change the last SharedSection parameter from 512 to 2048. For example, specify the following values for this parameter:

SharedSection=1024,3072,2048

If you cannot change the size of the desktop heap to 2048, try setting the value to 1024. For example, specify the following values for this parameter:

SharedSection=1024,3072,1024
(4.) Click OK.

6. Configuring the Windows registry: Specifying TCP/IP settings

Specify settings for TCP/IP in the Windows Registry to ensure that the required number of parallel processes can run concurrently.

You specify TCP/IP settings by editing the following registry values in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters registry subkey:

MaxUserPort

Specifies the maximum port number for ephemeral TCP ports.

TcpNumConnections

Specifies the maximum number of concurrent connections that TCP can open. This value significantly affects the number of concurrent osh.exe processes that are allowed. If the value for TcpNumConnections is too low, Windows cannot assign TCP ports to stages in parallel jobs, and the parallel jobs cannot run.

These keys are not added to the registry by default.

6.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

(2.) Specify the following registry values: MaxUserPort and TcpNumConnections.

a. If the registry value does not exist, click Edit > New > DWORD Value, and type the name of the registry value.
b. In the right pane of the Registry Editor, right-click the registry value, and click Modify.
c. In the Base area, select Hexadecimal.
d. In the Value data field, specify the following values:
    Registry value name                         Value data                          Valid range
     MaxUserPort                                      65534                                5000 - 65534 (decimal)
    TcpNumConnections                        65534                                 0 - 0xfffffe (65534 decimal)
e. Click OK.

7 Configuring Windows: Changing swap area size

Change the size of the swap area to improve performance.

7.1 Procedure

(1.) Click Start > Control Panel > System.
(2.) In the System Properties window, click the Advanced tab.
(3.) In the Performance area, click Settings.
(4.) In the Performance Options window, select the Advanced tab.
(5.) In the Virtual memory area, click Change.
(6.) In the Virtual Memory window, specify a value for Maximum size. Set this value to be one to one- and-a-half times larger than the value for physical memory.
(7.) Click Set.
(8.) Click OK three times, and then close the Control Panel window.

8. Configuring Windows: Enable Physical Address Extension (PAE)

Enable /PAE switch. Edit the boot.ini file to remove the /3GB switch and, if necessary, add the /PAE switch.

By default, Windows reserves 2 GB of memory for the kernel and 2 GB of memory for user mode programs. When the /3GB switch is specified in the boot.ini file, 3 GB of memory is reserved for user mode programs, and the amount of kernel memory is reduced by 1 GB. This setting results in an insufficient amount of memory for the kernel. To resolve this problem, remove the /3GB switch, if present from boot.ini file(for Windows Server 2000 or 2003).

In addition, on servers that have more than 4 GB of physical memory installed, specify the /PAE switch in the boot.ini file. For Windows 2008 server, use the BCDEDIT.exe tool (see section 11.8.1 below). The /PAE switch specifies physical address extensions that allow a 32-bit processor to access memory that is above the 4 GB limit.

8.1 Procedure

(1.) Click Start > Control Panel > System.
(2.) In the System Properties window, click the Advanced tab.
(3.) In the Startup and Recovery area, click Settings.
(4.) Click Edit to edit the boot.ini file.
(5.) Remove the /3GB switch, if present.
(6.) On servers that have more than 4 GB of physical memory installed, add the /PAE switch to the boot.ini file.
(7.) Save and close the boot.ini file.
(8.) Click OK twice, and then close the Control Panel window.

Note: There is no boot.ini in Windows Server 2008. To enable PAE in Windows 2008 server, use the Boot Configuration Data Editor, BCDEDIT.EXE, which is located in c:\windows\system32\ and follow the following steps:

(1.) Open Command Prompt with elevated privileges (Run as Administrator)
(2.) Execute the following command

BCDEDIT /SET PAE ForceEnabl

(3.) To verify, run BCDEDIT without any switch:

BCDEDIT

You should see “PAE ForceEnabl” at the end of the screen.

9 Restart Windows Server

After you make changes to the Windows Registry, restart Windows to apply your changes.

Reference:

Music of the Night - Richard Clayderman

2011-12-13T00:30:00.000-08:00

Misic of the Night by Richard Clayderman

LOAD SLOWLY CHANGING DIMENSION TYPE 2 USING MYSQL STORED PROCEDURE

2011-11-21T03:12:00.001-08:00

LOAD SLOWLY CHANGING DIMENSION TYPE 2 USING MYSQL STORED PROCEDURE

Unlike Oracle (merge statement) or other databases, MySQL does not support “upsert” (perform update or insert in one sql statement). Therefore, it requires some coding to achieve both insert and update when loading a type 2 slowly changing dimension.

Here was my lab exercise which I used a common dimension, product.

1. Tables.

I created the following tables during my lab exercise.

1.1 Dimension table: dim_product.

This was the product dimension table that we want to keep history of changes of production name, product description, product category and price.
Create table dim_product(
PRODUCT_SID int(12) NOT NULL auto_increment ,
PRODUCT_ID int(12) NOT NULL,
PRODUCT_CATEGORY_ID int(10) not null,
PRODUCT_NAME VARCHAR(50),
PRODUCT_DESCRIPTION VARCHAR(200),
PRICE DECIMAL(10,2),
PRICE_EFFECTIVE_DATE DATEtime,
LAST_MODIFIED_DATE DATEtime,
EFFECTIVE_START_DATE DATEtime,
EFFECTIVE_END_DATE DATEtime,
IS_CURRENT_ROW char(1),
CREATED_DATE DATEtime,
UPDATED_DATE DATEtime,
primary key (product_sid)
);

1.2 Staging table: stg_product.

This was the staging table that we will use to store new product records. Assuming production records will transfer to this table first before load into the product dimension.

create table pcdb.stg_product (
product_id int(12) not null,
product_category_id int(10) not null,
product_name varchar(50),
product_description varchar(200),
price decimal(8,2),
price_effective_date datetime,
last_modified_date datetime,
created_date datetime);

1.3 Tempary table: tmp_product.

This is a temporary table that will be used in the store procedure to process data.

create table tmp_product (
PRODUCT_SID int(12) ,
PRODUCT_ID int(12) NOT NULL,
PRODUCT_CATEGORY_ID int(10) not null,
PRODUCT_NAME VARCHAR(50),
PRODUCT_DESCRIPTION VARCHAR(200),
PRICE DECIMAL(10,2),
PRICE_EFFECTIVE_DATE DATEtime,
LAST_MODIFIED_DATE DATEtime,
created_date datetime,
scd_row_type_id tinyint(2)
);

2. The Stored Procedure

I created a stored procedure to accomplish the SCD type 2 dimension load in MySQL. Here was the simple store procedure.

use labdb
drop procedure if exists sp_dim_product;
delimiter //

CREATE PROCEDURE sp_dim_product()

BEGIN

/* Procedure name: sp_dim_prodcut
Description: user subscriptions 
SCD: Type 2.
Dependence: dim_user_product
Table size: large. keep track changes in product.
*/

Declare v_start_date datetime;
declare v_load_id int;
declare v_error_msg varchar(200);
declare v_proc_name varchar(36) default 'sp_dim_product'; 
declare v_validation tinyint;
declare v_max_data_date datetime;

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN 
set v_error_msg =concat('Procedure ',v_proc_name,' Encounter SQLEXCEPTION Error: Error code unknown');
SELECT v_error_msg; 
END;

truncate table tmp_product;

insert into tmp_product(
PRODUCT_SID ,
PRODUCT_ID ,
PRODUCT_CATEGORY_ID ,
PRODUCT_NAME ,
PRODUCT_DESCRIPTION ,
PRICE ,
PRICE_EFFECTIVE_DATE ,
LAST_MODIFIED_DATE ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
IS_CURRENT_ROW ,
scd_row_type_id ,
)
select 
ifnull(d.product_sid,0),
p.product_id,
p.product_category_id,
p.product_name,
p.product_description,
p.price,
p.price_effective_date,
p.last_modified_date,
p.last_modified_date as effective_start_date,
'2050/12/31' as effective_end_date,
'T',
( case when d.product_sid is null then 1 
when (d.product_sid is not null and
(p.product_category_id <> d.product_category_id or
p.product_name <> d.product_name or
p.product_description <> d.PRODUCT_DESCRIPTION or
p.price <> d.price) 
) then 2
else 0 
end) as scd_row_type_id ,
now()
from stg_product p
left join dim_product d 
on (p.product_id = d.product_id and d.is_current_row = 'T')
;

update dim_product d, tmp_product t
set d.is_current_row = 'F', 
d.EFFECTIVE_END_DATE = t.LAST_MODIFIED_DATE,
updated_date = now()
where d.product_sid = t.product_sid
and t.scd_row_type_id = 2;

insert into dim_product(
PRODUCT_ID ,
PRODUCT_CATEGORY_ID ,
PRODUCT_NAME ,
PRODUCT_DESCRIPTION ,
PRICE ,
PRICE_EFFECTIVE_DATE ,
LAST_MODIFIED_DATE ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
IS_CURRENT_ROW ,
CREATED_DATE ,
UPDATED_DATE 
)
select PRODUCT_ID ,
PRODUCT_CATEGORY_ID ,
PRODUCT_NAME ,
PRODUCT_DESCRIPTION ,
PRICE ,
PRICE_EFFECTIVE_DATE ,
LAST_MODIFIED_DATE ,
LAST_MODIFIED_DATE ,
EFFECTIVE_END_DATE ,
IS_CURRENT_ROW ,
now() ,
now()
from tmp_product
where scd_row_type_id in (1,2)
;

commit;

END;
//

3 How it works

A slowly changing dimension type 2 (SCD2) is defined to keep track changes of attributes in the underlying source table. While there are many attributes in a table, the business rules decide what changes to track. Here, for demonstration purpose, I choose to keep track of changes of each of the following attributes: production name, product description, and product category and price.

In the procedure, I used left outer join on product id, between the dimension table (existing records) and the staging table (new and modified records). For the new records, the product_sid will be null because they were not in the dimention table and I used a scd_row_type_id=1 flag to indicate that they were new records and assigned each of them with a ‘0’ poduct_sid. For the existing records, there would be a product_sid for each record and I used a scd_row_type_id=2 flag to indicate that they were modified exiting records.

select 
ifnull(d.product_sid,0),
p.product_id,
p.product_category_id,
p.product_name,
p.product_description,
p.price,
p.price_effective_date,
p.last_modified_date,
p.last_modified_date as effective_start_date,
'2050/12/31' as effective_end_date,
'T',
( case when d.product_sid is null then 1 
when (d.product_sid is not null and
(p.product_category_id <> d.product_category_id or
p.product_name <> d.product_name or
p.product_description <> d.PRODUCT_DESCRIPTION or
p.price <> d.price) 
) then 2
else 0 
end) as scd_row_type_id ,
now()
from stg_product p
left join dim_product d 
on (p.product_id = d.product_id and d.is_current_row = 'T')
;

update dim_product d, tmp_product t
set d.is_current_row = 'F', 
d.EFFECTIVE_END_DATE = t.LAST_MODIFIED_DATE,
updated_date = now()
where d.product_sid = t.product_sid
and t.scd_row_type_id = 2;

To identify the modified records, I used a case statement. By following the business rules, I compared each of the attributes to see if any of the attributes were modified. If it was, assigned a ‘2’ to the scd_row_type_id flag.

( case when d.product_sid is null then 1 
when (d.product_sid is not null and
(p.product_category_id <> d.product_category_id or
p.product_name <> d.product_name or
p.product_description <> d.PRODUCT_DESCRIPTION or
p.price <> d.price) )
 then 2

To indicate the current record, I created a column called IS_CURRENT_ROW and set it to ‘T’ it was a current row. For existing (history) records, I set it to the IS_CURRENT_ROW to ‘F’.

update dim_product d, tmp_product t
set d.is_current_row = 'F', 
d.EFFECTIVE_END_DATE = t.LAST_MODIFIED_DATE,
updated_date = now()
where d.product_sid = t.product_sid
and t.scd_row_type_id = 2;

At the end of the procedure, I inserted both the new and modified records which the t.scd_row_type_id was ‘1’ or ‘2’. Please note that records had not been modified, would had scd_row_type_id = 0 and would not be inserted into the dimention table.

insert into dim_product(
PRODUCT_ID ,
PRODUCT_CATEGORY_ID ,
PRODUCT_NAME ,
PRODUCT_DESCRIPTION ,
PRICE ,
PRICE_EFFECTIVE_DATE ,
LAST_MODIFIED_DATE ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
IS_CURRENT_ROW ,
CREATED_DATE ,
UPDATED_DATE 
)
select PRODUCT_ID ,
PRODUCT_CATEGORY_ID ,
PRODUCT_NAME ,
PRODUCT_DESCRIPTION ,
PRICE ,
PRICE_EFFECTIVE_DATE ,
LAST_MODIFIED_DATE ,
LAST_MODIFIED_DATE ,
EFFECTIVE_END_DATE ,
IS_CURRENT_ROW ,
now() ,
now()
from tmp_product
where scd_row_type_id in (1,2)
;

To test the procedure, first run the following query to insert two records into the staging table:

truncate table stg_product;

insert into stg_product ( PRODUCT_ID, PRODUCT_CATEGORY_ID, PRODUCT_NAME,PRODUCT_DESCRIPTION,PRICE,PRICE_EFFECTIVE_DATE, LAST_MODIFIED_DATE, CREATED_DATE)
values (1,8,'Apple iPad 36 GB','Apple iPad 36 GB with case', 800,now(),now(),now());

insert into stg_product ( PRODUCT_ID, PRODUCT_CATEGORY_ID, PRODUCT_NAME,PRODUCT_DESCRIPTION,PRICE,PRICE_EFFECTIVE_DATE, LAST_MODIFIED_DATE,CREATED_DATE)
values (2,7,'Canon 12 MPIX Digital Camera','Canon 12 MPIX Digital Camera, Zoon Len, Case', 150,now(),now(),now());

commit;

Check the stage tables to make sure the records are there.

MySQL>select * from stg_product;

Now run the store procedure which was created in section 2. After running the procedure, run the following query to check the records in the dim_product table.

MySQL>select * from dim_product;

There should be two rows in the dimension table with the column effective_start_date, effective_end_date and is_current_row populated. Now, let’s find out how the merge statement handles SCD Type 2 records. To begin, run the following queries against the stage table:

update stg_product set price = 700 , price_effective_date = now(), last_modified_date = now() where product_id = 1;
insert into stg_product (
PRODUCT_ID, 
PRODUCT_CATEGORY_ID,
PRODUCT_NAME,
PRODUCT_DESCRIPTION,
PRICE,
PRICE_EFFECTIVE_DATE,
LAST_MODIFIED_DATE,
CREATED_DATE)
values (3,6,'EPSon Color Printer',
'EPSon color print with high definition photo prints', 200,now(),now(),now());
commit;

The above statements updated a record where product_id equal to 1 to change it price to 700, and inserted one more new record, product_id 3, into the stg_product table. To help understand what the records set looks like from the select statement that use the left outer join, let’s copy the query and execute it to get the record set.

select 
ifnull(d.product_sid,0),
p.product_id,
p.product_category_id,
p.product_name,
p.product_description,
p.price,
p.price_effective_date,
p.last_modified_date,
p.last_modified_date as effective_start_date,
'2050/12/31' as effective_end_date,
'T',
( case when d.product_sid is null then 1 
when (d.product_sid is not null and
(p.product_category_id <> d.product_category_id or
p.product_name <> d.product_name or
p.product_description <> d.PRODUCT_DESCRIPTION or
.price <> d.price) 
) then 2
else 0 
end) as scd_row_type_id ,
now()
from stg_product p
left join dim_product d 
on (p.product_id = d.product_id and d.is_current_row = 'T')
;

As you can see, we have one rows for product_id 1, and one row for the new record, product_id 3. Also, at the end of the row, you can also see that the scd_record_type_id column has two ids for product id 1. One is 2 and the other one is 1.

Now, run the stored procedure in section 2 to load the data into the dimension table.

4. Summary

This purpose of this procedure is to show how to use outer join to maintain a Type 2 slow changing dimention in MySQL. This procedure dose not include all the necessary error checking routines. Also, there are other ‘real world’ situations need to be considered when implementing this procedure. For example, if there are large amount of records have been modified for any given time, there may be a performance issue when performing an update. Also, as the dimension table growth bigger, outer join will get slower. Therefore, other techniques need to be used to make the codes more efficient.

Reference:

Related Note:

iPhone Emergency

2011-11-16T06:18:00.001-08:00

Help! My iPhone dosen't work!

Start WebSphere InfoSphere Server Using Administrator User in Window 2008 Server

2011-10-04T05:48:00.000-07:00

Start WebSphere InfoSphere Server Using Administrator User in Window 2008 Server

To start the WebSphere InfoSphere server, you must start it with administrator privileges (as an administrator). Otherwise, the services will not start.
To start the services using administrator, from the start menu, click on All Program and then click on IBM WebSphere ->Profile ->InfoSphere

Highlight Start the server and right mouse click. Select “Run as Administrator”. In Window server 2008, even though the user is belong to the local administrator group, if you do not select Run as Administrator, the services will not start.

From Control Panel, check the services to make sure they are started.

Passing Parameters To A MYSQL Query Inside A Shell Script

2011-07-22T01:28:00.000-07:00

Passing Parameter To A MySQL query Inside A Shell Script.

From time to time, I found myself need to run some adhoc queries to pull data. I found that it was more convenience to create a shell script that could accept the query files as variable and can pass variables to the query.

Here is an example. I have a query to pull customer data from a customer table. It looks like this:

/* script:get_customer_record.sql */

Select c_id, c_first_name,c_last_name, c_address,
……
,last_modified_date
from customer
where last_modified_date >=@start_date and last_modified_date <= @end_date;

@start_date and @end_date are variables to be passed at run time.

Here is the wrapper shell script:

#!/bin/bash

## script name; mysql_script_runner.sh
## wrapper script to execute mysql script with variables

ARGS=4

if [ $# -ne "$ARGS" ]
then 

 echo "you passed $# parameters"
 echo "Usage: `basename $0` sql_script_file start_date end_date output_file"

exit 
fi 
sql_script=$1 
start_date=$2 
end_date=$3 
output_file=$4 

#run mysql query with paramenters 

/usr/bin/mysql –uuser_id -ppassword –h mysql-host -A -e "set @start_date=${start_date}; set @end_date=${end_date}; source ${sql_script};" >${data_file};

exit

# end of script.

To execute the script from command line, I can do something like this:

# mysql_script_runner.sh get_customer_record.sql ‘2011-06-01’ ‘2011-06-12 23:59:59’ cust_rec.dat

I can also set it up to run as a cron job with some small changes.

Related Note:

GET FIRST AND LAST DAY OF A MONTH IN BASH SHELL

Bad Data You'll Cry

2011-07-05T23:59:00.000-07:00

A song about bad data, BI or both?

Cognos Framework Manager

2011-07-03T22:40:00.000-07:00

Tutorial on Cognos Framework Manager

I am new to Cognos 10. The last time I used Cognos was more than 10 years ago. I found the following videos tutorials on Cognos Framework Manager and they were very helpfull. Many thanks to the video owners for sharing.

This video tutorial convers the following topics:

Create data sources
Create project in Framword Manager
Create data modle - DMR
Create Calculaticve fields
Create packages

The following 5 videoes are tutorials on Cognos Frameword Manager.

1. This video tutorial covers the following topics:

Creat data sources
Creat data models

2. This video tutorial covers the following topics:

Creat Projects
Creat data sources
Import data structure (meta data)
Creat Cognos objects
Also covers base concepts of dimension and facts

3.This video tutorial covers the following topics.

Object Type
- Namespace
- Query Object
Also covers base concepts of dimension and facts

4.This video tutorial covers the following topics.

Relational data model (DMR)
Measures
Also covers base concepts of dimension and facts

5.This video tutorial covers the following topics.

Drill down
Define hierarchivies

6.This video tutorial covers the following topics.

How Cognos stores data
Cognos object type
Create namespace
XML for Cognos model
.cpf file

GET FIRST AND LAST DAY OF A MONTH IN BASH SHELL

2011-06-29T22:59:00.000-07:00

GET FIRST AND LAST DAY OF A MONTH IN BASH SHELL

Linux system comes with a date commend. The man page has all the details. The following shows how to get the first date and last day of a month using the date commends in bash shell.

First Day, current month:
# date -d "-0 month -$(($(date +%d)-1)) days"

First Day, last month:
# date -d "-1 month -$(($(date +%d)-1)) days"

Last Day, current month:
# date -d "-$(date +%d) days +1 month"

Last Day, last month:
# date -d "-$(date +%d) days -0 month"

Last Day, month before last month:
# date -d "-$(date +%d) days -1 month"

%d = day of month.

Spinach Eggs Recipe

2011-06-28T23:13:00.000-07:00

Spinach Egg Recipe (for kids)

Ingredients

3 bunches of Spinaches (about 2 lb)
3 eggs
Salt
Oliver Oil
Sugar
A small slice of butter.
Oyster Sauce (optional)
Mrs. Dash Seasoning Blend(optional)

Instructions

1. Clean the Spinaches.

2. Prepare the eggs. In a small bowl, Put a bit of salt and a 1/3 of coffee spoon of Oliver oil. Beat eggs and mix well until color and texture are uniform throughout.

3. Heat the pan, add one tea spoon of Oliver Oil in to the pan and let it heat up. Pour the egg mixture into the pan. Set the burner on low. Flip the egg to let both sides of the eggs to developing a leathery brown skin. Make sure not to over cook.

4. Chap eggs into small pieces and set aside.

5. Boil hot water in a pot. When the water is boil, put Spinaches into the hot water for 30 seconds.

6. Put Spinaches into a basket and wait for the water to drain out.

7. Chap the Spinaches into small pieces and mix them with the eggs in saucepan. Add one coffer spoon of sugar and a slice of butter. Optionally, add one spoon of Oyster Source (optional) and a coffee spoon of Mrs. Mrs. Dash Seasoning Blend. Please make sure that Oyster Sauce will be fine with the people who you want to serve. Some kids, may not like Oyster Sauce. Heat and stir the mix for about 30 seconds.

8. Put the eggs and spinaches mix into an egg molds. And it ready to server

After Dinner Books:

Scrambeled Eggs Supper! By Dr. Seuss

SQL Jam

2011-06-21T21:48:00.000-07:00

A funny SQL song ...

Load Slowly Changing Dimension Type 2 using Oracle Merge Statement

2011-04-20T23:07:00.000-07:00

Load Slowly Changing Dimension Type 2 using Oracle Merge Statement

By using Oracle Merge statement, we are able to perform an insert and update statements (sometime referred to as “upsert”) in one query. The merge statement primarily useful in data warehousing situations, especially when maintaining Type 2 Slowly changing dimension (SCD2), where large amounts of data are commonly inserted and updated

1. Tables

To explore how the merge statement works, let’s use the following set of tables

Product dimension table: dim_product.

CREATE TABLE DIM_PRODUCT
(
PRODUCT_SID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
PRODUCT_CATEGORY_ID NUMBER,
PRODUCT_NAME VARCHAR2(50 CHAR),
PRODUCT_DESCRIPTION VARCHAR2(200 CHAR),
PRICE NUMBER(8,2),
PRICE_EFFECTIVE_DATE DATE,
LAST_MODIFIED_DATE DATE,
EFFECTIVE_START_DATE DATE,
EFFECTIVE_END_DATE DATE,
IS_CURRENT_ROW VARCHAR2(1 CHAR)
CREATED_DATE DATE,
UPDATED_DATE DATE
);

Source stage table: stg_product.
This is a stage table use to store the newly modified product records from the product table.

CREATE TABLE STG_PRODUCT
(
PRODUCT_ID NUMBER NOT NULL,
PRODUCT_CATEGORY_ID NUMBER,
PRODUCT_NAME VARCHAR2(50 CHAR),
PRODUCT_DESCRIPTION VARCHAR2(200 CHAR),
PRICE NUMBER(8,2),
PRICE_EFFECTIVE_DATE DATE,
LAST_MODIFIED_DATE DATE,
CREATED_DATE DATE
);

Let’s also created a sequence to use for the dim_product dimension table ‘s surrogate keys.

CREATE SEQUENCE s_dim_product
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

2. SCD Logics and the Merge Query

A slowly changing dimension type 2 (SCD2) is defined to keep track changes of attributes in the underlying source table. While there are many attributes in a table, the business rules decide what changes to track. Here, for demonstration purpose, let’s choose to keep track of changes of each attribute in the underlying product source table, which is to keep history of production name, product description, and product category and price changes.

The merger statement provides the abilities to perform insert and update with only one select statement based upon a join condition. Rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery. The details of the merge statement can be found from the oracle documentation online.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm

An SCD2 normally has three additional columns. The data effective start and end date of the record, and a flag to indicate the current record. To maintain histories, a new record is created to store the changing attributes while we keep the exiting records as histories. To help achieve that, a small utility table with only tow records in it is created. In this utility table, one record indicates the insert flag and the other indicates the update flag. Here the table structure:

Utility table: scd_row_type.

CREATE TABLE SCD_ROW_TYPE
(
SCD_ROW_TYPE_ID NUMBER NOT NULL,
SCD_ROW_TYPE_DESCRIPTION VARCHAR2(20 CHAR)
);

After creating the table, insert the following records into the table:

Insert into scd_row_type(scd_row_type_id,scd_row_description) values(1,’For Insert’);
Insert into scd_row_type(scd_row_type_id,scd_row_description) values(2,’For Update’);

Now we are ready to construct the merge query

MERGE INTO dim_product p
USING ( SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid,
                   PRODUCT_ID,
                   PRODUCT_CATEGORY_ID,
                   PRODUCT_NAME,
                   PRODUCT_DESCRIPTION,
                   PRICE,
                   PRICE_EFFECTIVE_DATE,
                   LAST_MODIFIED_DATE,
                   CREATED_DATE,
                   m.scd_row_type_id
              FROM    (SELECT dp.product_sid,
                              sp.PRODUCT_ID,
                              sp.PRODUCT_CATEGORY_ID,
                              sp.PRODUCT_NAME,
                              sp.PRODUCT_DESCRIPTION,
                              sp.PRICE,
                              sp.PRICE_EFFECTIVE_DATE,
                              sp.LAST_MODIFIED_DATE,
                              sp.CREATED_DATE,
                              CASE
                                 WHEN dp.product_id IS NULL
                                 THEN
                                    1
                                 WHEN (dp.product_category_id !=
                                          sp.product_category_id
                                       OR dp.product_name != sp.product_name
                                       OR DP.PRODUCT_DESCRIPTION !=
                                             sp.product_description
                                       OR dp.price != sp.price
                                       OR dp.price_effective_date !=
                                             sp.price_effective_date)
                                 THEN
                                    2
                                 ELSE
                                    0
                              END
                                 AS scd_row_type_id
                         FROM    stg_product sp
                              LEFT JOIN
                                 Dim_product dp
                              ON (sp.product_id = dp.product_id and  dp.is_current_row = 'Y')
                     ) m
                   JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)
                   ) mp
        ON  (p.product_sid = mp.product_sid)
when matched then
   update set P.EFFECTIVE_END_DATE = mp.LAST_MODIFIED_DATE, is_current_row = 'N', updated_date = sysdate
when NOT matched then
insert (P.PRODUCT_SID,P.PRODUCT_ID,P.PRODUCT_CATEGORY_ID, P.PRODUCT_NAME, P.PRODUCT_DESCRIPTION, P.PRICE, p.PRICE_EFFECTIVE_DATE,P.LAST_MODIFIED_DATE,
 p.effective_start_date,P.EFFECTIVE_END_DATE,is_current_row, created_date,
 updated_date )
 values (s_dim_product.nextval,mp.PRODUCT_ID,mp.PRODUCT_CATEGORY_ID,mp.PRODUCT_NAME,
 mp.PRODUCT_DESCRIPTION, mp.PRICE,mp.price_effective_date,
mp.LAST_MODIFIED_DATE,mp.last_modified_date,
 to_date('2099-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'),’Y', sysdate,sysdate);
  commit;

3. How it works

To make it easier to understand how the merge statement work when we actually perform some tests with data. First, let’s issue the following insert statements to create two records in the stage table.

truncate table stg_product;

insert into stg_product ( PRODUCT_ID, PRODUCT_CATEGORY_ID,  PRODUCT_NAME,PRODUCT_DESCRIPTION,PRICE,PRICE_EFFECTIVE_DATE,  LAST_MODIFIED_DATE,  CREATED_DATE)
        values (1,8,'Apple iPad 36 GB','Apple iPad 36 GB with case', 800,sysdate,sysdate,sysdate);

insert into stg_product ( PRODUCT_ID, PRODUCT_CATEGORY_ID,  PRODUCT_NAME,PRODUCT_DESCRIPTION,PRICE,PRICE_EFFECTIVE_DATE,  LAST_MODIFIED_DATE,CREATED_DATE)
         values (2,7,'Canon 12 MPIX Digital Camera','Canon 12 MPIX Digital Camera, Zoon Len, Case', 150,sysdate,sysdate,sysdate);

commit;

Check the stage table to make sure the records are there.

Sqlplus>select * from stg_product;

Now run the merge statement which was created in section 2. Once the merge query statement was executed, run the following query to checked the records in the dim_product table.

Sqlplus>select * from dim_product;

There should be two rows in the dimension table with the column effective_start_date, effective_end_date and is_current_row populated. Now, let’s find out how the merge statement handles SCD Type 2 records. To begin, run the following queries against the stage table:

update stg_product set price = 700 , price_effective_date = sysdate, last_modified_date = sysdate where product_id = 1;

insert into stg_product (
PRODUCT_ID, 
PRODUCT_CATEGORY_ID,
PRODUCT_NAME,
PRODUCT_DESCRIPTION,
PRICE,
PRICE_EFFECTIVE_DATE,
LAST_MODIFIED_DATE,
CREATED_DATE)
values (3,6,'EPSon Color Printer',
'EPSon color print with high definition photo prints', 200,sysdate,sysdate,sysdate);
 commit;

The above statements updated a record where product_id equal to 1 to change it price to 700, and inserted one more new record, product_id 3, into the stg_product table. To help understand what the records set looks like from the using clause of the merge statement, let’s copy the query and execute it to get the record set.

SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid,
                   PRODUCT_ID,
                   PRODUCT_CATEGORY_ID,
                   PRODUCT_NAME,
                   PRODUCT_DESCRIPTION,
                   PRICE,
                   PRICE_EFFECTIVE_DATE,
                   LAST_MODIFIED_DATE,
                   CREATED_DATE,
                   m.scd_row_type_id,
         s.scd_row_type_id
              FROM    (SELECT dp.product_sid,
                              sp.PRODUCT_ID,
                              sp.PRODUCT_CATEGORY_ID,
                              sp.PRODUCT_NAME,
                              sp.PRODUCT_DESCRIPTION,
                              sp.PRICE,
                              sp.PRICE_EFFECTIVE_DATE,
                              sp.LAST_MODIFIED_DATE,
                              sp.CREATED_DATE,
                              CASE
                                 WHEN dp.product_id IS NULL
                                 THEN
                                    1
                                 WHEN (dp.product_category_id !=
                                          sp.product_category_id
                                       OR dp.product_name != sp.product_name
                                       OR DP.PRODUCT_DESCRIPTION !=
                                             sp.product_description
                                       OR dp.price != sp.price
                                       OR dp.price_effective_date !=
                                             sp.price_effective_date)
                                 THEN
                                    2
                                 ELSE
                                    0
                              END
                                 AS scd_row_type_id
                         FROM    stg_product sp
                              LEFT JOIN
                                 Dim_product dp
                              ON (sp.product_id = dp.product_id and  dp.is_current_row = 'Y')
                     ) m
                   JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)

Examine the result set, it should look something like:

PRODUCT_SID PRODUCT_ID PRODUCT_CATEGORY_ID PRODUCT_NAME         PRODUCT_DESCRIPTION                                 PRICE   PRICE_EFFECTIVE_DATE LAST_MODIFIED_DATE  CREATED_DATE   SCD_ROW_TYPE_ID
-6789            1            8            Apple iPad 36 GB     Apple iPad 36 GB with the latest createst           700    4/19/2011 2:11        4/19/2011 2:11     4/19/2011 2:10   2
62               1            8            Apple iPad 36 GB     Apple iPad 36 GB with the latest createst           700    4/19/2011 2:11        4/19/2011 2:11     4/19/2011 2:10   2
                 3            6            EPSon Color Printer  EPSon color print with high definition photo prints 200    4/19/2011 2:11        4/19/2011 2:11     4/19/2011 2:11   1

As you can see, we have two rows for product_id 1, and one row for the new record, product_id 3. Also, at the end of the row, you can also see that the scd_record_type_id column has two ids for product id 1. One is 2 and the other one is 1. for the one the that has a scd_record_type_id equql to 1, the product_sid is changed to –6789. Therefore, this record will be used for insert. The logic that define which record to update or insert was defined in the case statement:

(CASE
   WHEN dp.product_id IS NULL
     THEN  1
   WHEN (dp.product_category_id != sp.product_category_id
          OR dp.product_name != sp.product_name
          OR DP.PRODUCT_DESCRIPTION != sp.product_description
          OR dp.price != sp.price
          OR dp.price_effective_date != sp.price_effective_date)
     THEN  2
   ELSE    0
 END) AS scd_row_type_id

If the product_id is not existing in the dim_product table, it is a new record. Therefore, it is assigned a 1 and it will be inserted. If any of the changes occurred to an existing record, it is an update and is assigned a 2. Otherwise, any identical record will be assign a 0. The join statement:

JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)

takes only record with a scd_record_type_id that is 1 or greater since the scd_record_type table only have two records, scd_record_type_id 1 and 2. Therefore, any record that with a scd_record_type = 0 (identically records) will not be included in the final result set. To create a type 2 record, which is a existing record that has one or more new attributes, we change the existing product_sid to a dummy number, -6789, by using the decode statement:

DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid

We choose this number because we had define the surrogate key of the dim_product table to only use positive value for keys. Hence, it will be inserted into the dimension. The "when matched" and "when not matched" clauses in the merge statement are self-explanatory. We matched the record set with the dim_product table on the product_sid key. If it matched, we set the is_current_row flag to ‘N’ and sent the effective_end_date to the last_modified_date. If it was not matched, records will be insert into the dimension table. Now, run the merge statement in section 2 to load the data into the dimension table.

4. Summary

As it is stated in the Oracle documentation, the MERGE statement can be used to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. Therefore, it is helpful in loading data into a slowly changing dimension type 2 table.

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement. Oracle Database does not implement fine-grained access control during MERGE statements.

If you are using the fine-grained access control feature on the target table or tables, then use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control.

Reference:

Related Note:

SQL Outer Join

Learn To Play Piano: If We Hold On Together

2011-04-06T07:32:00.000-07:00

The piano sheet can be found here: If We Hold On Together.

Live Crab Vending Machine In The Subway Station

2011-03-10T01:58:00.000-08:00

Vending machine sells live crabs.

"Honey, please pick up a live crab on your way home from the subway station vending machine."

SQL Outer Join Query

2011-03-10T00:58:00.000-08:00

Outer Join Query

SQL OUTER JOIN returns all records from both joined tables even there is no matching record found. There are three types of SQL OUTER JOIN:

LEFT OUTER JOIN
RIGHT OUTER,
FULL OUTER JOIN

In Outer Join, preserved table means that all the rows in the rows in preserved table will be returned from the query results with at least one row. Let's use the following two tables to show the different type of outer joins.

Employee table: employee id and name

Emp_id	Name
100	John
110	Mary
111	Smith
112	Bob
113	Jim
114	Joan
115	Jill

Employee_hours_worked table: daily total hours each employee worked.

Emp_id	Date	Total_hours_worked
1	2009-12-04	1
100	2010-01-12	3
110	2010-01-12	5
113	2010-01-13	2
111	2010-01-13	4
113	2010-01-13	6
110	2010-01-13	2

1. Left Outer Join

Left outer join preserve the table in the left side. Every row in the preserved table is presented in the result set with at least one result row.

select e.emp_id,e.name, w.date,w.total_hours_worked
from employee e
left join employee_hours_worked w
on e.emp_id = w.emp_id

emp_id	name	date	total_hours_worked
100	John	1/12/2010	3
110	Mary	1/12/2010	5
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
112	bob
113	Jim	1/12/2010	2
113	Jim	1/13/2010	6
114	Joan
114	Jill

Put search predicate (filter) in the WHERE clause in a left outer join query will filter out the result set.

select e.emp_id,e.name, w.date,w.total_hours_worked
from employee e
left join employee_hours_worked w
on e.emp_id = w.emp_id
where w.total_hours_worked <=4

emp_id	name	date	total_hours_worked
110	Joan	1/12/2010	3
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
113	Jim	1/12/2010	2

Put search predicate in the ON clause in a left outer join query will filter out only the matched records in the non preserved table.

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
left join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id and w.total_hours_worked <=4
emp_id name date total_hours_worked

emp_id	name	date	total_hours_worked
100	john	1/12/2010	3
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
112	Bob
113	Jim	1/12/2010	2
114	Joan
114	Jill

2 Right Outer Join

In Right outer join, the preserved table is on the right with is opposite to Left outer Join.

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
right outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id

emp_id	name	date	total_hours_worked
100	John	1/12/2010	3
110	Mary	1/12/2010	5
113	Jim	1/12/2010	2
111	Smith	1/13/2010	4
113	Jim	1/13/2010	2
110	Mary	1/13/2010	2
		12/4/2009	1

Notice that the result set include the record that has not emp_id and name (last record).

3. Full Outer Join

Full Outer Join preserved both tables

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
full outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id

emp_id	name	date	total_hours_worded
100	John	1/12/2010	3
110	Mary	1/12/2010	5
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
112	Bob
113	Jim	1/12/2010	2
113	Jim	1/13/2010	6
114	Joan
115	Jim
		12/4/2009	1

If a database server (like MySQL) dose not support full outer join, the query can be written with an union and a where clasue.

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
left outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id
union
select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
right outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id
where e.emp_id is null

The where clause in the second query ,'where e.emp_id is null', keep all the the records that only exists in the right table, employee_hours_worked.

Reference

Man Vs. Machine - The Final Jeopardy!

2011-02-17T05:07:00.000-08:00

Man Vs. Machine - The Final Jeopardy!

This is amazing!

IBM Watson

Performance Tuning Best Practices for MySQL

2010-12-02T01:18:00.000-08:00

Performance Tuning Best Practices for MySQL by Jay Pipes.

References:

OLAP

2010-12-02T01:01:00.000-08:00

OLAP and Business Intelligence

Reference:

A Song About Failed Data Warehouse

2010-12-02T00:42:00.000-08:00

Halloween 2010

2010-11-03T22:24:00.001-07:00

"Trick or treat" and Vote.

Dogs in Costumes

Pimpkin Craving

High Tech Costume

MYSQL SPEEPUP INSERT INTO A MYISAM TABLE

2010-10-26T00:02:00.000-07:00

Accordint to the MySQL Manual, there are a few ways to speedup inserts while adding data to a MyISAM table. Following are the original document from MySQL manual plus the notes I added while I was experiencing with real data loads.

1. Background Information

The time required for inserting a row, according to the MySQL manual, is determined by the following factors, where the numbers indicate approximate proportions:

· Cnnecting: (3)
· There are many server variables that can be used to help optimize the user connections as stated in the post:
· Sending query to server: (2)
· Parsing query: (2)
· Inserting row: (1 × size of row)
· Inserting indexes: (1 × number of indexes)
· Closing: (1)

There are two additional things which need to be considered regarding overheard for inserts:

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query. To try to reduce the times need to open table, try to cache them. To cache the table, set the table_cache variable. Please note that the table_cache, max_connections, and max_tmp_tables system variables affect the maximum number of files the server keeps open. If table_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable. You also have to take into account that the MyISAM storage engine needs two file descriptors for each unique open table. Please refer to this note for connection related variable setting.
The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

2. Methods Which Can Be Used to speed up Inserts

These are the methods which can be used to speed up inserts. You will notice that most of the methods mentions here are related to MySQL MyISAM tables.

(1.) If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.
Insert into t1 (a,b,c,d) values (1,2,3,’2010-01-01’), (3,5,6,’2010-02-01’), (7,8,9,’2010-03-01’);

Instead of:

   Insert into t1 (a,b,c,d) values (1,2,3,’2010-01-01’);
   Insert into t1 (a,b,c,d) values (3,5,6,’2010-02-01’);
   Insert into t1 (a,b,c,d) values (7,8,9,’2010-03-01’);

(2.) If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.

(3.) If multiple clients are inserting a lot of rows, you can get higher speed by using the INSERT DELAYED statement. “Insert Delay” hint delays the inserts by storing the insert statements in the memory queue utile the target table is free from reads. Hence, the insert statement will return quickly and all the insert statements that are stored in the queue can be written in one block, which is more optimized that written one statement at a time.

Hear is the syntax for insert delay:

Insert delay into t1 (a,b,c,d) values (1,2,3,’2010-01-01’), (3,5,6,’2010-02-01’), (7,8,9,’2010-03-01’);
See Section 12.2.5.2, INSERT
Please note that delay insert only works with MyISAM, MEMORY, ARCHIVE and BLACKHOLE(v. 5.1.19) table. And, there is downside of using “Insert Delayed”. There will be negative performance gain if the table is empty or not being used and add overhead to the server to hold additional thread for each table for which there is delayed rows, etc. Please read the manual for details on the negative impacts.
Theses are system status variables can be used to monitor delay insert uses. Here are the Status Variable meaning:

Delayed_insert_threads -- Number of handler threads
Delayed_writes -- Number of rows written with INSERT DELAYED
Not_flushed_delayed_rows -- Number of rows waiting to be written

(4.) For a MyISAM table, you can use concurrent inserts to add rows at the same time that SELECT statements are running, this works well for tables that have more reads than writes because it just keeps adding data at the end of the data file.

To enable concurrent inserts, we need to set the concurrent_insert system variable. The values for the variable are:

· 0 -- Disables concurrent inserts
· 1 -- Enables concurrent insert for MyISAM tables that do not have holes. A hole means that there is free space in the data file, i.e., rows were deleted from the table. This is the default value.
· 2 -- Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

To set the variable with other values (0 or 2) rather than the default value, for example concurrent_insert = 2, enter the parameter in the my.cnf file and restart the server. Or you can also dynamically set it in MySQL, for example:
MySQL> set global concurrent_insert = 2;

Here are a few things that need to keep in mind.

1. ) Under circumstances where concurrent inserts can be used, there is seldom any need to use the DELAYED modifier for INSERT statements.
2. ) If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation.
3. ) With LOAD DATA INFILE, if you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other sessions can retrieve data from the table while LOAD DATA is executing.
4)Use of the CONCURRENT option affects the performance of LOAD DATA a bit, even if no other session is using the table at the same time.
MySQL document has more information on using concurrent inserts.
(5.) Using bulk load command, LOAD DATA INFILE, to load data into a table from a text file can be 20 times faster than using INSERT statements.

Updating indexes takes time and resources during inserts. When data are loaded into a table that has many indexes, we can follow the steps bellow to speed up the loads.

1.) Disable the indexes
Non unique indexes of a MyISAM table can be disabled using the following statement:
MySQL> ALTER TABLE t1 DISABLE KEYS;
The alter table disable keys statement also dose a flush table.
2.) Load data into the table using LOAD DATA INFILE statement. This does not update any indexes and therefore is very fast.
3.) Enable the indexes
Indexes can be enabled using the following statement:

MySQL> ALTER TABLE tbl_name ENABLE KEYS;

Here is a note on MySQL Disable and Enable Indexes Command.

http://databobjr.blogspot.com/2010/05/mysql-alter-table-disable-keys.html

You can also use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to disable index keys and use myisamchk -rq /path/to/db/tbl_name to enable index keys. However, if you used myisamchk to disable or enable index keys, you must issue Flush Tables statement or a mysqladmin flush-tables command. More information can be found here: http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html
.
(6.) To increase performance for MyISAM tables, for both LOAD DATA INFILE and INSERT, enlarge the key cache by increasing the key_buffer_size system variable.

2. SUMMARY

The methodologies mentioned above were not THE only methods that could speed up inserts nor should they be used exclusively in that orders. It requires a lot of trails and errors to have one that works for a particular environment.

References:

MySQL Manual: Speed of Insert Statement
Book: High Performance MySQL

MySQL Thread States Repair By Sorting Vs. Repair With Key Cache

2010-10-21T00:25:00.000-07:00

When perform any of the REPAIR, ALTER or LOAD or ENABLE KEYS command on a MyISAM table, it also causes the rebuilding of the indexes. Indexes are rebuilt (repaired) in two modes:

Repair by sorting -- The repair code is using a sort to create indexes.
Repair using keycache -- The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

To start repairing, first it tests if the table can be repaired by sorting provided it meets the following requirements:

Table at least has one key
Total size needed for individual key is less than myisam_max_sort_file_size

If it meets the above requirements, then it uses either regular sorting if myisam_repair_threads = 1 (default) by building each key at a time or in parallel if myisam_repair_threads > 1 by using ‘n’ threads in parallel (n = total keys in the table).

If it fails to satisfy the above conditions, then it falls to expensive keycache repair mode.

Therefore, to avoid keycache repair, set the myisam_max_sort_file_size to the max index size.

Related Note:

MyISAM_MAX_SORT_FILE_SIZE Variable

References:

Cloud Computing

2010-09-29T22:47:00.000-07:00

During Oracle OpenWorld 2010, both Larry Ellison, CEO, Oracle and Marc Benioff, CEO, Salesforce.com gave their views on Cloud Computing. Very interesting indeed.

Here is Larry Ellison:

Here is Marc Benioff:

Setup Oracle Physical Standby Database Using Dataguard On Linux

2010-09-18T07:28:00.000-07:00

1. INTRODUCTION

This document summarizes the steps of creating an Oracle Physical Standby Database on Linux servers using Oracle Data Guard features. Oracle 10g Data Guard supports both Logical and Physical Standby databases.

The database version that is used here is 10.2.0.3. The Primary database server is pcdb-1.databob.net and Standby database server is pcdb-2.databob.net.The Primary database (db_name) is called PCDB and the Standby database is called PCDBS.
Oracle 10g documentation regarding Data Guard Physical Standby Database can be found here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm

2. CONFIGURATIONS
2.1 Prerequisites
Following are the prerequisites need to be met before creating the standby database.

1. Make sure that the operating system and platform architecture on the primary and standby systems are the same. Oracle user was created in both servers and ssh is configured for Oracle user on both servers
2. Install Oracle database software on the standby server and patch it if necessary. We are using Oracle 10g, release 10.2.0.3. Make sure the same Oracle software release is used on the Primary and Standby databases server, and Oracle home paths are identical on both servers.
3. Test the Standby Database creation and make sure the server was installed properly. Also make sure that all the directories for data and logs are the same on both servers.

2.2 Planning
During the configuration processes, it requires restarting the primary database, creating backup copy of data and log files in the primary database, and restoring them in the standby database. Therefore, if the primary database is a production database, proper planning is needed to limit downtime.

Also, this document separates primary and standby database configuration tasks for clarity reason. In fact, some of these steps can be rearranged to limit database downtime.

2.3 Configure Primary Database Server, pcdb-1.databob.net
Before you create a standby database you must first ensure the primary database is properly configured. After the primary database is set up and running, these are additional configuration tasks need to complete:

1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Additional Primary Database Initialization Parameters

Please note that during the configuration processes, it requires to restart the primary database. Proper planning is needed to reduce down time if the primary database is already in production.

You need to be the Oracle sys user to perform most the configuration tasks. To become the sys user, do the following:

#> sqlplus /nolog
SQL>connect sys/password as sysdba

Following are steps to configure Primary Database

1. Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn’t exist.
Create a password file if it dose not exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.

1) To check if a password file already exists, run the following command:

SQL> select * from v$pwfile_users;
no rows selected
2) Create an Oracle password file in Linux:
Password files are created with the Oracle orapwd tool which is located in $ORACLE_HOME/bin directory. To create the password file, execute the following commands:
#>cd $ORACLE_HOME/bin
#>./orapwd file=/u0/oracle/product/10.2.0/db_1/dbs/orapwPCDB password= force=y

Replace with the password of the SYS user. The password file needs to be named exactly in orapw format in Linux. For example, if the sid is ‘PCDB’ then the password file name needs to be named as orapwPCDB.
The password file is located in $ORACLE_HOME/dbs directory.
3) Check again if the password file was created and perform the following query. You should get the same result as show bellow for sys user.
SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER
-----------------------------------------
SYS TRUE TRUE

3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files, run the following query:

SQL> select bytes from v$log;
BYTES
--------------
10485760
10485760
10485760
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile:
GROUP# MEMBER
--------------------------------------------------------------------------------
1 /u0/oracle/log/PCDB/redo/PCDB_redo01_01.log
2 /u0/oracle/log/PCDB/redo/PCDB_redo02_01.log
3 /u0/oracle/log/PCDB/redo/PCDB_redo03_01.log

3) Create standby Redo log groups.
Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone. Here, we are going to create same number log groups for standby log as of data redo log group.
Before creating the standby log groups, please make sure that:
• log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
• Verify related database parameters and settings.
Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.
To create the standby log groups, user the following commands:

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 10M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 10M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS
---------- ---------- ---------- --------- ------------------------------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

4. Set Primary Database Initialization Parameters
Add (or edit) the following parameters to pFile, initPCDB.ora if they are not already in the pFile. If a pFile dose not exist, you can use the following command to create one from the existing spFile:
SQL>create pfile=’<$ORACLE_HOME>/dbs/initPCDB.ora’ from spfile;
(Note: replace oracle home with the full path.).
Add and verify if the following parameters are in the pFile, initPCDB.ora:
db_name=PCDB
db_unique_name=PCDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PCDB,PCDBS)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u0/oracle/log/PCDB/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PCDB'
LOG_ARCHIVE_DEST_2=
'SERVICE=PCDBS LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PCDBS'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=PCDBS
FAL_CLIENT=PCDB
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT=/u0/oracle/data/PCDB/data/datafile','/u0/oracle/data/PCDB/data/datafile'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=‘/u0/oracle/data/PCDB/onlinelog’, ‘/u0/oracle/data/PCDB/onlinelog’

5. Create spfile from pfile, and restart primary database using the new spfile and enable archive log.
Data Guard must use SPFILE. Create the SPFILE and restart database.
SQL> shutdown immediate;
SQL>create spfile from pfile=’/u0/oracle/admin/PCDB/scripts/initPCDB.ora’;

6. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘/u0/oracle/log/PCDB/ctl/control-PCDBS-103.ctl’;
Database altered.

7. Make a backup copy of the primary database data file.
1) If the database is running, shutdown it.
SQL>shutdown immediate;
2) Make a copy of the data files and temp files and online log files and scp them over to the standby database server.
You can copy the file manually or follow the following steps to create scp scripts:
SQL> set pages 50000 lines 300 head off veri off flush off ti off
SQL> spool ‘/tmp/cpfiles_prim.sh’
SQL> select 'scp -p '||file_name||' ${stan_host}:’||file_name from dba_data_files;
SQL> select 'scp -p '||file_name||' ${stan_host}:’||file_name from dba_temp_files;
SQL> select 'scp -p '||member||' ${stan_host}:’||file_name from v$logfile;
SQL> spool off;
Edit the file /tmp/cpfiles_prim.sh to define the ${stan_host} variable. To do so, add these tow lines to the top of the script:
#!/bin/bash
stan_host=

Replace hostname with the hostname or IP address for the standby server.
#>/tmp/cpfiles_prim.sh
#!/bin/bash
stan_host= pcdb-2.databob.net
scp -p /u0/oracle/data/PCDB/sys/datafile/PCDB_system.dbf ${stan_host}:/u0/oracle/data/PCDB/sys/datafile/PCDB_system.dbf
…..
Execute the script, /tmp/cpfiles_prim.sh, to scp the file over to the standby database server. Note, depend on the files size and the network traffic, it may take some time. Therefore, you may want to change script to copy the files to a temp directory is the primary server, tar and gzip the files and scp the gziped files to the standby server. Then, copy the files to the pproper directories.

Before copying the files, verify that standby server file system setting are the same as the primary server. This means that all the directories need to be the same.
#>chomd 766 /tmp/cpfiles_prim.sh
#>./tmp/cpfiles_prim.sh
3)Copy the standby control file that was generated in step 6 above to the /tmp directory in the standby server
#>scp ‘/u0/oracle/log/PCDB/ctl/control-PCDBS-103.ctl’ standby_server:/tmp

8. Restart the Primary database using the newly created spfile and enable archive log.
SQL>Startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u0/oracle/log/PCDB/arch
Oldest online log sequence 343
Next log sequence to archive 345
Current log sequence 345
SQL>

Now the primary database is setup for transmitting data to standby database server.

2.4 Configure Standby Database Server, pcdb-2.databob.net
To configure the standby database server, pcdb-2 , db_unique_name PCDBS, these configuration tasks need to complete:

1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly

Following are steps to configure Standby Database.

1. Install the Primary database data files on the Standby Server:

If you had not copy the data files which you created in section 2.3, step 7, please do so to copy the files to the proper directories from the backup copy.

2. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy initPCDB.ora from Primary server to Standby server,
2) Rename it to initPCDBS.ora, and modify the file as follow.
Note that most the parameters here do not need to change since they will be the same as those mentioned in section 2.3, step 4.
*.audit_file_dest=/u0/oracle/admin/PCDB/adump
*.background_dump_dest=/u0/oracle/admin/PCDB/bdump
*.core_dump_dest=/u0/oracle/admin/PCDB/cdump
*.user_dump_dest=/u0/oracle/admin/PCDB/udump
*.compatible='10.2.0.3.0'
control_files = (/u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl,
/u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl,
/u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl)
db_name='PCDB'
db_unique_name=PCDBS
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PCDB,PCDBS)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u0/oracle/log/PCDB/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PCDBS'
LOG_ARCHIVE_DEST_2=
'SERVICE=PCDBS LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PCDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=PCDBS
FAL_CLIENT=PCDB
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT=/u0/oracle/data/PCDB/data/datafile','/u0/oracle/data/PCDB/data/datafile'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=‘/u0/oracle/data/PCDB/onlinelog’, ‘/u0/oracle/data/PCDB/onlinelog’

3 Copy the standby control file,which was scp’ed over from the primary server in section 2.3, step 7
to the following directories and filename ;
/u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl,
/u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl,
/u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl

#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/admin/PCDB/pfile/control-PCDB-101.ctl
#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/backup/PCDB/ctl/control-PCDB-102.ctl
#>cp /tmp/control-PCDBS-103.ctl /u0/oracle/log/PCDB/ctl/control-PCDB-103.ctl

4. Create the password file for the standby server.
#>cd $ORACLE_HOME/bin
#>./orapwd file=/u0/oracle/product/10.2.0/db_1/dbs/orapwPCDBS password= force=y

5. Configure listeners for both the primary and standby databases.
Create or edit the listener.ora files on both primary and standby server. By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory.
1) On Primary server, pcdb-1, edit the listener.ora file for PCDB (primary) and PCDBS(standby).
# On primacy server, pcdb-1, PCDB
LISTENER_PCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_PCDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PCDB)
(ORACLE_HOME = /u0/oracle/product/10.2.0/db_1)
(SID_NAME = PCDB)
)
)
2) On Standby server, pcdb-2, edit the listener.ora file for PCDBS(standby).
# On primacy server, pcdb-2, PCDBS
LISTENER_PCDBS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-2)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_PCDBS =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PCDBS)
(ORACLE_HOME = /u0/oracle/product/10.2.0/db_1)
(SID_NAME = PCDBS)
)
)

6. Configure Oracle Net service names for both primary and standby servers.

Configure tnsnames.ora on both servers to hold entries for both databases. By default, the listener.ora file is located in the $ORACLE_HOME/network/admin directory.
1) On Primary database server, pcdb-1, edit the tnsnames.ora
LISTENER_PCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521)(IP = FIRST))
)
)

PCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)
PCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =pcdb-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2) On Standby server, pcdb-2, edit the tnsnames.ora
LISTENER_PCDBS=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-2)(PORT = 1521)(IP = FIRST))
)
)

PCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pcdb-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

PCDBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =pcdb-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PCDB)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
7. Start the listener and check tnsping on both nodes to both services
1) Restart listeners on primary servers
oracle@pcdb-1> lsnrctl stop
lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 15-SEP-2010 10:26:32

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
oracle@pcdb-1> lsnrctl start
2)Restart listener on standby server
oracle@pcdb-2> lsnrctl stop
oracle@pcdb-2> lsnrctl start

3)use tnsping to test both servers, PCDB and PCDBS

On primary server:
oracle@pcdb-1>tnsping PCDB
oracle@pcdb-1>tnsping PCDBS

On standby:
oracle@pcdb-2>tnsping PCDB
oracle@pcdb-2>tnsping PCDBS

8.Verify that the Oracle environment variables are set correctly on the standby server.
#>sudo – su oracle
#>env
ORACLE_SID=PCDB
ORACLE_BASE=/u0/oracle/product/10.2.0
….

9. Start up nomount the standby database and generate a spfile.
SQL> connect sys/password as sysdba;
Connected to an idle instance.
SQL> startup nomount pfile='/u0/oracle/admin/PCDB/scripts/initPCDB.ora';
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes

SQL> create spfile from pfile='/u0/oracle/admin/PCDB/scripts/initPCDB.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

10. Startup mount the standby database and perform recovery
1) Startup mount the instance
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes
Database mounted.

2) Start apply the logs
Issue the following command to start apply the logs.

SQL>alter database recover managed standby database disconnect from session;
Database altered.

Now, if everything is setup correctly, the standby database should start to apply the logs from the primary database.

11. Verify the Physical Standby Database Is Performing Properly

1) Check alert log of the standby server to see if the operations taking place or there is any error

#> vi /u0/oracle/admin/PCDB/bdump/alert_PCDBS.log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 15 15:03:16 2010
Attempt to start background Managed Standby Recovery process (PCDBS)
MRP0 started with pid=56, OS id=6637
Wed Sep 15 15:03:16 2010
MRP0: Background Managed Standby Recovery process started (PCDBS)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 3 processes
Media Recovery Waiting for thread 1 sequence 360
Wed Sep 15 15:03:22 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

2) Check the alert log of the primary server to see if the operations taking place or there is any error

The alert log of the primary will show how it recognize the standby and start shipping archived logs
#> vi /u0/oracle/admin/PCDB/bdump/alert_PCDB.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 364 for destination LOG_ARCHIVE_DEST_2
Wed Sep 15 17:05:24 2010
Thread 1 advanced to log sequence 365
Current log# 2 seq# 365 mem# 0: /u0/oracle/log/PCDB/redo/PCDB_redo02_01.log
Wed Sep 15 17:05:25 2010
LNS: Standby redo logfile selected for thread 1 sequence 365 for destination LOG_ARCHIVE_DEST_2

3). Check the Standby severer:
3.1). Check database unique_name and database name.
SQL> show parameters db_unique_name;
NAME TYPE VALUE
---------------------------------------------------------------------------
db_unique_name string PCDBS

SQL> SELECT NAME FROM V$DATABASE
NAME
---------------------------
PCDB
3.2). Check archived redo log on Standby
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------------
367 15-SEP-10 15-SEP-10
368 15-SEP-10 15-SEP-10
369 15-SEP-10 15-SEP-10
370 15-SEP-10 16-SEP-10
371 16-SEP-10 16-SEP-10
372 16-SEP-10 16-SEP-10
3.3)Check archived redo applied on Standby
SQL>select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
367 YES
368 YES
369 YES
370 YES
371 YES
372 YES

3.4) Check standby manager status
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH CLOSING 363
ARCH CLOSING 364
….
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 373
….
RFS IDLE 0
RFS IDLE 373
The above show MRP0 is waiting for log 372 from the primary server.
3.5) Switch log files on Primary.
Login to primary database and perform the following command to force a log switch.
SQL> alter system switch logfile;

System altered.

SQL>archive log list
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u0/oracle/log/PCDB/arch
Oldest online log sequence 372
Next log sequence to archive 374
Current log sequence 374

3.6) In Standby Database server, repeat verification steps, 3.2, 3.3 and 3.4.

You show notice that log 373 had been applied and MRP0/LNS is waiting for log 374 (step 3.4).
12. Errors and Troubleshoot
1) Error: ORA-19527: physical standby redo log must be renamed (in Standby server)
If you have the above error message in the alert log after you issued “alter database recover managed standby database using current logfile disconnect;” statement, it was due to the fact that the standby database file structure is exactly the same as the primary database server. To resolve that, do the following:

SQL> alter system set log_file_name_convert='dummy','dummy' scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;

Now, you can start the recover again by issue the following statement:
SQL> alter system set log_file_name_convert='dummy','dummy' scope=spfile;

Check the alert log again and you should not see the same error again. If you see error, “ORA-27037: unable to obtain file status”, try to shutdown the server again one more time and startup mount, you will not see any more error.

3)ORA-16191: Primary log shipping client not logged on standby

ORA-16191: Primary log shipping client not logged on standby

PING[ARCr]: Heartbeat failed to connect to standby 'PCDBS'. Error is 16191.
Wed Sep 15 16:50:49 2010
Errors in file /u0/oracle/admin/PCDB/bdump/PCDB_arcr_29633.trc:
ORA-01017: invalid username/password; logon denied
Wed Sep 15 16:50:49 2010
Error 1017 received logging on to the standby

The error was caused, most likely, the missing of password file or the password for sys user was not entered correctly in the password file. Run the following query to check if the password file was created in both servers (primary and standby) and the password was correctly. You can always recreate the password files.(see step 2, section 2.3)

SQL> select * from v$pwfile_users;

2.5 Useful Commands and Queries

• Check archive log status on standby database:
SQL>select sequence#, first_time, next_time,applied from v$archived_log;
• Start archive log recover (log apply service):
SQL>alter database recover managed standby database using current logfile disconnect;
• Start real-time apply on Standby
SQL> alter database recover managed standby database using current logfile disconnect;
• Stop archive log recover:
SQL> alter database recover managed standby database cancel;
• Check dataguard messages
SQL> select error_code, timestamp, message from v$dataguard_status;
• Check standby messages
SQL> select process,status,sequence# from v$managed_standby;
• Enable archivelog
SQL>alter database archivelog;
• Show current archive log list
SQL>archive log list;
• Force a log switch on primary
SQL>alter system switch logfile
• Check log file size
SQL>select bytes from v$log;
• Check current log group
SQL>select group#, member from v$logfile;
• Many more here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/toc.htm

Related Notes:

REFERENCES

Install Oracle Database Server Patch Using Silent Mode

2010-09-18T06:54:00.000-07:00

Oracle user patch to do bug fixed and upgade. Hear is an example of installing Oracle 10g 10.2.0.3 patch using Silent Mode.

1.Download the Patch
Download the patch from oracle support site: https://support.oracle.com/CSP/ui/flash.html(aka http://metalink.oracle.com). The patch file is p5337014_10203_LINUX.zip for 32 bit system and p5337014_10203_LINUX-x86-64.zip for 64 bit system/

To loacate the patch, after login to https://support.oracle.com, click:

Patches and Update (on the top menu)
Latest Patches (Left side bar, under Oracle Server/Tool
Oracle Database (under Latest Oracle Server/Tool Patch Sets)

All the patches are listed there.

2. Prepare for Patch Installation
Select a temporary directory to store the installation packages. For example, the /tmp directory. Use unzip to extract the packages. The packages will store in a directory call Disk1.

#>unzip p5337014_10203_LINUX x86-64.zip

3. Update Oracle Time Zone Definitions
Before installing the patch, check if the the Oracle Time Zone definitions had been installed in the existing installation by running an Oracle script, utltzuv2.sql, in the $ORACLE_HOME/rdms/admin/ directory.

SQL> @?/rdbms/admin/utltzuv2.sql;
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
PL/SQL procedure successfully completed.
Commit complete.

4 Shutdown the Oracle Server and Lisntner
To begin installing the patch, all the existing Oracle processes need to be shutdowned. First, shutdown the Oracle Server.

#>sqlplus /nolog
SQL>connect sys/password as sysdba;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit

Secondly, shutdown the listener.

#>lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-SEP-2010 17:59:09

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

If there is any other process running, shutdown it too. To check if all the Oracle processes had been shutdowned, do the following:

#>ps –ef | grep ora.

It should show any Oracle process running.

5.Install the Patch
Following are steps to install the pach package. It involves installing the binaries by running the installer, runInstaller, and excuting two Oracle sql scripts that are come with the pactch.

• Remove the existinting inventory directory: /u0/oracle/product/10.2.0/oraInventory
#>mv oraInventory/ oraInventory10201
• Change to the pactch directory:
#>cd /tmp/Disk1
• Run the installation utility:
#> ./runInstaller -silent -responseFile /tmp/enterprise_install.rsp –force
Make sure to check the installation log as show on the screen to check for errors in installation.
• Check Opatch Version
There is a untility script that we can use to checked the binary version. The script is in $ORACLE_HOME/OPatch directory.
#> cd $ORACLE_HOME
#> cd OPatch/
#> opatch version
Invoking OPatch 10.2.0.3.0
OPatch Version: 10.2.0.3.0
OPatch succeeded.

6.Upgrade the Database
Startup Oracle in upgrade mode and run the catupgrd.sql script, which is in the $ORACLE_HOME/rdbms/admin/ director, to upgrade the database. This script will take more than 20 minutes to complete.
#>Sqlplus /nolog
SQL>connect sys/password as sysdba;
SQL> startup upgrade
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

Once the upgrade is done. Check any errors in the spooled file. If every thing is OK, proceed to next step to recompile the invalids using utlrp.sql script present in ORACLE_HOME/rdbms/admin directory.

7. Recompile the Invalids
SQL> @? /rdbms/admin/utlrp.sql;

After recompiling, shutdown the database and startup for allowing access to the users which is show in next step.

7.1 Restart the server.
If there is no error after upgrading and recompiling, we can now shutdown the database and startup for allowing access to the users.

7.2 Restart Server
SQL> shutdown immediate;
SQL>startup mount;
ORACLE instance started.

Total System Global Area 1.2583E+10 bytes
Fixed Size 2094096 bytes
Variable Size 1728056304 bytes
Database Buffers 1.0838E+10 bytes
Redo Buffers 14680064 bytes
Database mounted.

SQL> alter database open;
Database altered.
Check the alert log for error:
#>cd /u0/oracle/admin/OVI/bdump
#>vi alert_ovi.log
If there is no error, the patch upgrade is completed.

7.3 Start Listner
#>lsnrctl start

Now the database is ready for user connections.

Related Notes:

Install Oracle Server Using Silent Mode and Response File

Precision

2010-09-11T17:06:00.000-07:00

Stand Up To Cancer

2010-09-11T16:31:00.000-07:00

Cancer dose not discriminate anyone. Let's Stand Up To Cancer!

Oracle OpenWorld

2010-09-04T00:11:00.000-07:00

Oracle OpenWorld 2010 will be held at the Moscone Center, San Francisco, CA; September 19-23. Keynote speakers are always one of a kind. If you are there, don't miss them.

Here a part of the keynote delivered by Arnold Schwarzenegger in Oracle OpenWorld 2009. Very interesting.

Movies which Gavernor Arnold Schwarzenegger mentioned during his speach.

References:

Map of Moscone Center, San Francisco, California, USA

View Larger Map

Linux Sed: Print Lines From File Using Sed

2010-09-02T23:35:00.000-07:00

Print Lines Using Sed

This is the third note on Text Manipulations with Linux Sed. The first note covers Append and Insert to a file using sed. It also covers the Option Switches. The second note covers Replace and Delete from a file using sed

To test each of the commands and options, let’s use the following files as an example:

#> cat sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

1 Use Sed ‘p’ to print line(s) by address

Following is the Syntax to print lines with address:

#sed -n ‘ADDRESS P’ inputfilename

Here some examples use print command with address to print line(s) from a input file.

• Example 1: print the 3r line from a file
#>sed -n '3p' sed_edit.txt
New Jersey, Garden Stata

• Example 2: print a range of lines
Print line 2 to to from the input file

#sed -n '2,4p' sed_edit.txt
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light

• Example 3: Print from line 2 to the end of file.
#>sed -n '2,$p' sed_edit.txt
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

2 Use Sed ‘p’ to print line(s) by matches of pattern

Following is the Syntax to print lines with address:

#>sed -n ‘/PATTERN/ P’ inputfilename

Here are some examples use print command with match of pattern to print line(s) from a input file.

• Example 1: print line matches a patter.
Print out the line that matches that pather “Paris”

$sed -n '/Paris/ p' sed_edit.txt
Paris, The City of Light

• Example 2: print number of lines from the matched pattern
Print line that matches ‘New Jersey’ and the next two lines

#sed -n '/New Jersey/,+2p' sed_edit.txt
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 3: print line starting from a given line to the parttern matched line.
Print from 2nd line to the line that matches “Paris”

#>sed -n '2,/Paris/p' sed_edit.txt
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light

• Example 4: print line between two match patterns
Print lines between match patterns “Washington” and “Paris”

#> sed -n '/Washington/,/Paris/p' sed_edit.txt
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light

• Show all the “Lock wait timeout exceeded” errors from the mysql server error log file

#>sed -n -e '/Lock wait timeout exceeded/,+1p' mysql-error.log

“grep” command can do the similar thing, but the +1 (plus 1 line) option, or change to multiple lines, comes handy sometime when we want to inclue messges after the error message. I found it can be helpful to extract messages that are related to an event from any logs (database server logs, syslogs,etc)

• Select all the enteries during a period of time from the Oracle alert log

#sed -n -e '/Thu Jun 4 14:/,/Thu Jun 4 17:/P' alert_ora.log

The above example select the log entries from 2:00pm to 5:00pm on Thursday, July 14.

References:

Linux Sed: REPLACE, AND DELETE LINES FROM A FILE USING SED

2010-09-02T23:18:00.000-07:00

REPLACE, AND DELETE LINES FROM A FILE USING SED

This is the second note on Text Manipulations with Linux Sed. The first note coverts Append and Insert to a file using sed. It also covers the Option Switches. And, the third note will covers Print Lines using sed.

To test each of the commands and options, let’s use the following files as an example:

#> cat sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

1. Use Sed ‘c\’ to replace a line indicate by the address

Following is the Syntax to replace a line with address:

#sed 'ADDRESS a\
Line which you want to replace' filename

‘ADDRESS’ is a number with represents the line, i.e.,3, represent the 3rd line.

Here am examples using sed replace with address command:

• Replace 3rd line with ,” San Francisco, Golden City”

#> sed '3 c\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
San Francisco, Golden City
Paris, The City of Light
Hong Kong, Pearl of the Orient

2 Use Sed ‘c\’ to replace a line that match with a pathern

Following is the Syntax to append a line with a match of a pattern:

#sed '/PATTERN/ c\
   Line which you want to replace' filename

Here is an example using sed replace to replace a line of match of pattern to a file.

• There is a typo in line “New Jersey, Garden Stata” with the word “Stata”. Let’s replace it.
#>sed '/New Jersey, Garden Stata/ c\New Jersey, Garden State' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden State
Paris, The City of Light
Hong Kong, Pearl of the Orient

2.Sed Delete
2.1 Use Sed ‘d’ to delete line(s) indicate by the address

Following is the Syntax to replace a line with address:
#>sed ‘ADDRESS d’ inputfilename

Please note there is no “backslash”(“\”) after d.

There are some examples use sed delete command to delete line(s) from a input file.
• Example 1: delete the 3rd line from the file.

# sed ‘3d’ sed_edit.txt
New York, Big Apple
Washington DC, The Capital
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 2: delete the line 2 to line 4 from the file.

# sed '2,4d' sed_edit.txt
New York, Big Apple
Hong Kong, Pearl of the Orient

2.2 Use Sed ‘d’ to delete line(s) indicate by match of pattern

Following is the Syntax to replace a line with address:

#sed ‘/PATTERN/ d’ inputfilename

Here some examples use sed delete command with match of pattern to delete line(s) from a input file.

• Example 1: delete line matches a given pattern.
Delete the line “New Jersey, Garden Stata” from the file
#>sed '/New Jersey, Garden Stata/ d' sed_edit.txt
   New York, Big Apple
   Washington DC, The Capital
   Paris, The City of Light
   Hong Kong, Pearl of the Orient

• Example 2: delete all the blank link from a file
Let’s change the file to look like this:
#>cat sed_edit.txt
New York, Big Apple

Washington DC, The Capital

New Jersey, Garden Stata

Paris, The City of Light
Hong Kong, Pearl of the Orient

Now, use delete to remove all blank lines.
#>sed '/^$/d' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 3: delete multiple lines starting from the the line that matches a given pattern
Delete the lines that match “Paris” and all lines that after this lines

#>sed '/Paris/,$d' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata

• Example 4: delete 1 addition lines from the match pattern
Delete the line that matches “Paris” and one more line after that

#sed '/Paris/,+1d' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata

References:

PERL REGULAR EXPRESSIONS: MATCHING TEXT WITH REGULAR EXPRESSIOS, ~M

2010-09-02T02:23:00.000-07:00

In Perl, The “m” means to attempt a regular expression match. The requrlar expressions, or search patterns, are put between to forwardslashes, “/../”. And the string that to be searched is linked by” =~”. Truse, if you want to search any digits from a string, you can do the following:

my $varString="George Washington was born on February 22, 1732";
print "Search string \"$varString\" for digits\n";
while ( $varString=~ m/(\d+)/g) {
print "Found digits: $1\n";
}

And the output will be:

Search string "George Washington was born on February 22, 1732" for digits
Found digits: 22
Found digits: 1732

Here the search pattern is “/(\d+)/”, “m” means match and ‘=~” tells perl to link to the string “$varString”. Lastly, “g” means do a global search on the string (from left to right). The search resulf, is stored in “$1”.

If we changed the code a little bit, without using the while loop and without using global search “g”::

my $varString="George Washington was born on February 22, 1732";
print "Search string \"$varString\" for digits\n";
$varString=~ m/(\d+)/;
print "Found digits: $1\n";

The outout will be:

Search string "George Washington was born on February 22, 1732" for digits
Found digits: 22

Perl find the first digits and stop and the resulf still store in $1.

Now, assuming that we forgot to use “~” and have the codes as the following:

my $varString="George Washington was born on February 22, 1732";
print "Search string \"$varString\" for digits\n";
$varString= m/(\d+)/;
print "Found digits: $1\n";

The output are:

Search string "George Washington was born on February 22, 1732" for digits
Use of uninitialized value in pattern match (m//) at ./perl_rex.pl line 8.
Use of uninitialized value in concatenation (.) or string at ./perl_rex.pl line 9.
Found digits:

The reason is when the “~” is omitted, perl is looking for a match of the regex in $_ and stores the search resulf to $varString.
Lets test it using the following codes:

my $varString="George Washington was born on February 22, 1732";
print "Search string \"$varString\" for digits\n";
$_="George Washington was born on February 22, 1732";
$varString= m/(\d+)/;
print "Found digits: $1\n";
print "Found digits: $varString\n";

And the output are:

Search string "George Washington was born on February 22, 1732" for digits
Found digits: 22
Found digits: 22

In fact, “m” is not reuested as long as you have used “~”, but using “~m” make the codes a bit easy to read, in my opions.

my $varString="George Washington was born on February 22, 1732";
print "Search string \"$varString\" for digits\n";
$varString=~ /(\d+)/;
print "Found digits: $1\n";

References:

ORACLE ERROR: ORA-01031: INSUFFICIENT PRIVILEGES

2010-09-02T01:57:00.000-07:00

ORA-01031: INSUFFICIENT PRIVILEGES (ORACLE LINUX)

When tried to sarted oracle, I got the following error:

ERROR: ORA-01031: insufficient privileges

Following were the steps that I used to start the database instance:

1) Start sqlplus with nolog

# > sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 12 14:40:02 2010
(c) 1982, 2005, Oracle. All rights reserved.
SQL>

2) Connect to oracle as sysdba

SQL> connect sys/password as sysdba;

ERROR:
ORA-01031: insufficient privileges

I hecked around and it turned out the problem was the parameter setting in the sqlnet.ora file.
In my case, the sqlnet.ora file (stored in the $ORACLE_HOME/network/admin directory) contained the following line:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

I changed it to:

SQLNET.AUTHENTICATION_SERVICES= (ALL)

And, I was able to connect to oracle using "connect sys/password as sysdba" and stated the database instance and open the database.

This pointed me to look at the SQLNET.AUTHENTICATION_SERVICES again.

The parameter can set to :

• NONE : cannot connect to database without a password as sysdba (sqlplus /as sysdba)
• NTS: set for using windows NT native authentication
• ALL: ALL for all authentication methods

Authentication Methods Available with Oracle Advanced Security:

• kerberos5 for Kerberos authentication
• cybersafe for Cybersafe authentication

• radius for RADIUS authentication
• dcegssapi for DCE GSSAPI authentication

If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

References:

LINUX SED: APPEND, INSERT AND WRITE TO A FILE FROM A FILE USING SED

2010-09-02T00:37:00.000-07:00

APPEND, INSERT AND WRITE LINES TO A FILE FROM A FILE USING SED

This is the first note on Text Manipulations Using Linux Sed.

This note covers how to using Linux sed command to do simple append, insert, and write lines to an output file using a text file as input. Sed provides lot of commands to perform number of operations with the lines in a file. This comes handy in situations that we want to use a script to update files.

1 Sed Dose Not Change Input file.

"SED" stands for Stream EDitor. Sed reads its input from stdin ("standard input," i.e., the console or pipe), or from files, and writes its results to stdout (“standard output”, i.e., the screen). Therefore, sed doesn't modify any input files. Sed takes any number of user-specified editing operations ("commands") and performed on each line in order on the input data.

2 Sed command syntax and option switches.

Sed commands need to be put inside a pair of sigle quotes ‘’. All text lines that involve append, insert and replace need to be preceded by a “backslash” ( “\”). Make sure that there is no space between the command switch and the “\”. For example, the following command adds a line “add this line after the 3rd line”, to the input file inputfile.txt:

#sed ‘3 a\add this line after the 3rd line’ inputfile.txt

Following are option switches and commands that use in this note. There is more information on sed can be found in the man page here.

Options:
• -n, --quiet, --silent suppress automatic printing of pattern space
• -e, script, --expression=script add the script to the commands to be executed
• -f, script-file, --file=script-file add the contents of script-file to the commands to be executed

Commands:
• P – Upper case P. Print up to the first embedded newline of the current pattern space.
• p – Lower case p. Print the current pattern space.
• = -- print current line number.
• W filename – Uper case W. Write the first line of the current pattern space to filename.
• w filename – Lower case w. Write the current pattern space to filename.
• a \text – Append text, which has each embedded newline preceded by a backslash.
• i \text – Insert text, which has each embedded newline preceded by a backslash.
• c\ text -- Replace the selected lines with text, which has each embedded newline preceded by a backslash.
• D – Upper case D. Delete up to the first embedded newline in the pattern space. Start next cycle, but skip reading from the input if there is still data in the pattern space.
• d – Delete pattern space. Start next cycle.

To test each of the commands and options, let’s use the following files as an example:

# cat sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

3 Sed Append
3.1 Use Sed ‘a\’ to appends a line after every line with the address

Following is the Syntax to append a line with address:

#sed 'ADDRESS a\
  Line which you want to append' filename
‘ADDRESS’ is a number with represents the line, i.e., 3, represent the 3rd line.

Here are some examples using sed append with address command:

• Example 1. Add a line after the 3rd line of the file.
Add the line “San Francisco, Golden City” after the 3rd line.

#sed ‘3 a\San Francisco, Golden City’ sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
San Francisco, Golden City
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 2: Add a line at the end of the file.
Sed also works with regurlar expression. The following command add the line “San Francisco, Golden City” at the end of the file

#sed ‘$,a\San Francisco, Golden City’ sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient
San Francisco, Golden City

Here, the ‘$’ is the regurlar expression for end of file.

3.2 Use Sed ‘a\’ to appends a line after every line that match with a pathern

Following is the Syntax to append a line with a match of a pattern:

#sed '/PATTERN/ a\
Line which you want to append' filename

‘/PATTERN/’ is the pattern that wants match,i.e., /Paris/. Pattern need to be put between two forwardslashes,”/”.

Here is an examples using sed append command with a match of pattern:

• Append a line,” San Francisco, Golden City”, after a match of pattern “Paris”

#sed '/Paris/ a\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
San Francisco, Golden City
Hong Kong, Pearl of the Orient

4 Sed Insert
The different between sed append and sed insert is that sed append adds the line after the address or match of pattern while sed insert adds the line before the address or match of the parttern.

4.1 Use Sed ‘i\’ to insert a line before every line with the address
Following is the Syntax to append a line with address:

#sed 'ADDRESS a\
Line which you want to append' filename

‘ADDRESS’ is a number with represents the line, i.e., 3, represent the 3rd line.

Here are some examples using sed insert to insert a line before an address.

• Example 1: Insert a line before the 3rd line of the file.
Insert the line “San Francisco, Golden City” before the 3rd line.

# sed '3 i\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
San Francisco, Golden City
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 2: insert a line at the beginning of the file.
Insert the line “San Francisco, Golden City” at the beginning of the file.

#sed '1 i\San Francisco, Golden City' sed_edit.txt
San Francisco, Golden City
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

Here address “1” represents the first line of the file.

4.2 Use Sed ‘i\’ to insert a line before every line that match with a pathern

Following is the Syntax to append a line with a match of a pattern:

#sed '/PATTERN/ i\
Line which you want to append' filename

Here is an example using sed insert to insert a line to a file.
• Insert a line,” San Francisco, Golden City”, before each match of pattern “Paris”

#sed '/Paris/ i\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
San Francisco, Golden City
Paris, The City of Light
Hong Kong, Pearl of the Orient

5 Sed Write To a File

Following is the Syntax to write to a file with address and pathern:

#sed 'ADDERSSw outputfilename' inputfilename

#sed '/PATTERN/w outputfilename' inputfilename

To store output of the above example, we can do the following:

#sed -n '/Washington/,/Paris/w sed_edit.out' sed_edit.txt

Sed_edit.out will contain the output of the sed command.

However, to save a copy of the file that after insert,delete or update(replace) you may find it easier just to use the “>outputfilename” syntax.

For example, to save the changes make to the input file with the replace command, we can do:

#sed '/New Jersey, Garden Stata/ c\New Jersey, Garden State' sed_edit.txt > sed_edit.out

References:

DATA WAREHOUSE CONCEPTS

2010-08-07T03:40:00.000-07:00

Data Warehouse Concepts

1. Introduction:

Over the years, I had done a few data warehouse projects and read a few data warehouse and business intelligence books and white pages. However, I had never really summarized the concepts, terminologies, and techniques that I had learned. Here, I will start to put together the basic concepts, and terminologies that are frequently referred to and mythologies and techniques that are frequently used and talked about.

I am going to organize the topic base on the following data warehouse concepts. In this note, I will provide summarize version of the concepts. In the future note, I will provide more explanation and example of the each concept.

Data warehouse and data mart
Fact and Dimension
Star Schema and Snowflake Schema
Extract, Transform and Load (ETL)
Meta Data
Cube

2. Data Warehouse and Data Mart
2.1 Data Warehouse

A data warehouse is a central repository for all or significant parts of the data that an enterprise's various business systems collect. The goal of the data warehouse is make enterprise data easily accessible for strategic decision making.

There are two well-known authors on data warehousing: Bill Inmon and Ralph Kimball. Following are definitions from each of them:

Bill Inmon: A data warehouse is a subject-oriented, integrated, nonvolatile, and time-variant collection of data in support of management’s decisions.

Subject-oriented - Data that gives information about a particular subject instead of about a company's on-going operations.
Integrated - Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
Time-variant - All data in the data warehouse is identified with a particular time period.
Non-volatile - Data is stable in a data warehouse. More data is added, but data is never removed. This enables management to gain a consistent picture of the business.

Ralph Kimball: The conglomeration of an organization’s data warehouse staging and presentation areas, where operational data is specifically structured for query and analysis performance and ease-of-use.

2.2 Data Mart

A data mart is a repository of data gathered from operational data and other sources that is designed to serve a particular community of knowledge workers. In scope, the data may derive from an enterprise-wide database or data warehouse or be more specialized. The emphasis of a data mart is on meeting the specific demands of a particular group of knowledge users in terms of analysis, content, presentation, and ease-of-use. Users of a data mart can expect to have data presented in terms that are familiar.

2.3 Data Warehouse vs. Data Mart:

A data warehouse tends to be a strategic but somewhat unfinished concept. The design of a data warehouse tends to start from an analysis of what data already exists and how it can be collected in such a way that the data can later be used. A data warehouse is a central aggregation of data (which can be distributed physically);
A data mart tends to be tactical and aimed at meeting an immediate need. The design of a data mart tends to start from an analysis of user needs. A data mart is a data repository that may derive from a data warehouse or not and that emphasizes ease of access and usability for a particular designed purpose.

3. Fcts and Dimensions

3.1 Facts

A fact table is the central table in a star join schema characterized by a composite key, each of whose elements is a foreign key drawn from a dimension table.

Represent a business process, i.e., models the business process as an artifact in the data model
Contain the measurements or metrics or facts of business processes.
Most are additive (sales this month), some are semi-additive (balance as of), some are not additive (unit price).
The level of detail is called the “grain” of the table.
Contain foreign keys for the dimension tables include time dimension.
Reolve many-to-many relationships

3.2 Dimensions

Represent the who, what, where, when and how of a measurement/artifact.
Represent real-world entities not business processes.
Give the context of a measurement (subject).
For example for the Sales fact table, the characteristics of the 'monthly sales number' measurement can be a Location (Where), Time (When), Product Sold (What).
The Dimension Attributes are the various columns in a dimension table. In the Location dimension, the attributes can be Location Code, State, Country, Zip code. Generally the Dimension Attributes are used in report labels, and query constraints such as where Country='USA'. The dimension attributes also contain one or more hierarchical relationships.
Hierarchical relationships.

4. Star Schemas and Snowflake Schemas

4.1 Star Schemas

A start Schema (Dimensional Model) consists a single fact table of metrics surrounded by multiple descriptive dimension tables. A start schema will consist at less on fact table and a few dimensions tables.

4.2 Snowflake Schema

A snowflake schema is a set of tables comprised of a single, central fact table surrounded by normalized dimensions. The main different between a star schema and a snowflake schema is that star schema tends to have demoralize dimensions while a snowflake schema prefers normalized dimensions.

5. Extract, Transform and Load (ETL)

Extract, Transform and Load (ETL) is the name for a process or a group of processes that transport data from source system into the data warehouse or data mart.

Extract is the fist part of an ETL process. It involves extracting the data from different source systems and different data formats.
Transform refers to Appling business rules and logics to the data that was extracted from the source system before loading them into the data warehouse. Data quality assurance can also be performed here.
Load processes load data, after the transform processes, to the data warehouse and data mart which some time also refer to as targets.

6. Meta Data

Meta data literally means "data about data.". It describes the characteristics of a resource. describes how and when and by whom a particular set of data was collected, and how the data is formatted. Metadata is essential for understanding information stored in data warehouses.

A data dictionary is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.

7. Cube

A cube contains dimensions, hierarchies, levels, and measures. Each individual point in a cube is referred to as a cell.

Cubes are OLAP objects consisting of related measures and dimensions that you configure within an Analysis Services database. You can define and configure multiple cubes within a single database and each cube can use some or all of the same dimensions. You can also define a single cube that contains multiple measure groups in the same database rather than defining separate cubes. When you define a cube with multiple measure groups, you need to define how dimensions relate to each measure group and customize, as appropriate, dimension objects within each cube and measure group. When defining a cube, you also define advance cube properties, including calculations, KPIs, actions, partitions and aggregations, perspectives and translations.

8. Smmary

The concepts which described above were the basic concepts would start with any data were house project. Understand those concepts provide a starting point in any data warehouse development. Each of the concepts mentioned above represent an important part in the data warehouse development life cycle. These concepts are fundamental to design and implement a data warehouse. Following diagram show each of the concepts tied into in each data warehouse layers.

References:

W. H. Inmon: Building The Data Warehouse ( 2005)
Rallph Kimball and Margy Ross: The Data Warehouse Toolkit (Second Edition)
Othere Reference On the Web

IP To Country Lookup Table Using MySQL Spatial Index

2010-07-30T06:38:00.000-07:00

I have an IP to Country lookup table that is used by various applications. The table is very simple:

CREATE TABLE `lu_ip_to_country` (
     id INT UNSIGNED NOT NULL auto_increment,
     start_ip_num int(10) UNSIGNED NOT NULL,
     end_ip_num int(10) UNSIGNED NOT NULL,
     country_id char(2) NOT NULL,
     iso_country_code varchar(3) DEFAULT NULL,
     country_name varchar(100) DEFAULT NULL,
     PRIMARY KEY (id),
     key start_end_ip (start_ip_num,end_ip_num)
);

Start_ip_num: Beginning IP Number
End_ip_num: Ending IP Number
Country_id: two character country abbreviations (US, BG, FR, etc.) similar to ISO code
ISO_country_code: ISO 3166 Country Code
Country_name: Country Name

Beginning IP Number and Ending IP Number are the number version of the IPs which are calculated as follows:

Ip number = 16777216*w + 65536*x + 256*y + z

Where w, x, y, z represent the IP address (w, x,y,z). For example: if ip is 61.94.7.255,then w=61,x=94,y=7 and z=255.

The queries that use to lookup country information are simple as well:

Select country_id from lu_ip_to_country

Where v_ip_num_lookup between start_ip_num and end_ip_num

In this query, the v_ip_num_lookup is a variable used to store the calculated ip number. For example, '61.94.7.255’=1029572607.

The other query utilized the MySQL INET_ATON() function, which convert the ip to ip number. i.e. INET_ATON('61.94.7.255') = 1029572607. The query is the following:

Select country_id from lu_ip_to_country
Where INET_ATON('61.94.7.255') between start_ip_num and end_ip_num

These tow queries worked fine but slow. It will take up-to 1.5 second to return the result. And they were even slower when there were concurrent requests to this table. I had tried different indexes, and even tried to use memory table, but the improvements were not notable.

The other week, while I was searching the web, I came across an article, which was written by Jeremy Cole, described how to use MySQL GIS with spatial R-tree indexes for similar ip-to-country lookup in MySQL.I decided to give it a try. And, it turn out that the result was excellence.

Jeremy Cole’s article gave a vary detail explanation on how GIS with spatial R-tree indexes work in this case. Here are the steps on how I implemented and tested it (following what were described in the article).

1. Reconstruct the table to using POLYGON type and spatial index

CREATE TABLE lu_ip_to_country_geoip (
     id INT UNSIGNED NOT NULL auto_increment,
     ip_poly POLYGON NOT NULL,
     start_ip_num int(10) UNSIGNED NOT NULL,
     end_ip_num int(10) UNSIGNED NOT NULL,
     country_id varchar(3) NOT NULL,
     iso_country_code varchchar(3) DEFAULT NULL,
    country_name varchar(100) DEFAULT NULL,
    PRIMARY KEY (id),
    SPATIAL INDEX (ip_poly)
);

2. Export the data from the existing table
     Select start_ip_num,
                end_ip_num,
                country_id,
               iso_country_code,
              country_name
             Into outfile ‘/tmp/ip-country-data.dat’
     FIELDS
            TERMINATED BY ","
              ENCLOSED BY "\""
     LINES
           TERMINATED BY "\n"
      FROM lu_ip_to_country;

If you don’t have the data in the first place, you can download them from MaxMind web site (http://www.maxmind.com/app/country) as mentioned in the article.

3. Load the data into the new table with the MySQL GIS function to build the POLYGON for ip_poly field from the start_ip_num and end_ip_num fields.

    LOAD DATA LOCAL INFILE "/tmp/ip-country-data.dat"
         INTO TABLE lu_ip_to_country_geoip
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @start_ip_num, @end_ip_num,
                @country_id,@iso_country_code, @country_name
             )
       SET
            id := NULL,
            start_ip_num := @start_ip_num,
           end_ip_num := @end_ip_num,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          /* clockwise, 4 points and back to 0 */
          POINT(@start_ip_num, -1), /* 0, top left */
          POINT(@end_ip_num, -1), /* 1, top right */
          POINT(@end_ip_num, 1), /* 2, bottom right */
          POINT(@start_ip_num, 1), /* 3, bottom left */
         POINT(@start_ip_num, -1) /* 0, back to start */))),
       country_id := @country_code,
       iso_country_code := @iso-country_code,
       country_name := @country_string;

4. Test queries

mysql> SELECT country_id,iso_country_code,country_name FROM lu_ip_to_country WHERE INET_ATON('61.94.7.255') BETWEEN start_ip_address AND end_ip_address;
+------------+------------------+--------------+
| country_id | iso_country_code | country_name |
+------------+------------------+--------------+
| ID | IDN | Indonesia |
+------------+------------------+--------------+
1 row in set (1.57 sec)

mysql> SELECT country_id,iso_country_code,country_name FROM lu_ip_to_country_geoip WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('61.94.7.255'), 0)));
+------------+------------------+--------------+
| country_id | iso_country_code | country_name |
+------------+------------------+--------------+
| ID | IDN | Indonesia |
+------------+------------------+--------------+
1 row in set (0.35 sec)

5. Perform A/B Test on ETL process.

I also selected a reasonable data file and ran through the process using old ip-to-country table for process A and new ip-to-country table for process B and QA’edfor the following:

Check to make sure all Country IDs/Codes match exactly on both out put
Compare times

Both processes produced the same country code lookups except the new table made the same process run much faster.

References:

Jcole’s Weblog: On efficiently geo-referencing IPs with MaxMind GeoIP and MySQL GIS
MySQL Forum: MySQL and GIS, GeoIP: FAQs, How-To, Articles, Blogs
MySQL Manual: Spatial Extensions
MySQL Manual: Creating Spatial Indexes
MySQL Manual: Class Polygon
MySQL Manual: Relations on Geometry Minimal Bounding Rectangles (MBRs)
MySQL Manual: Class Point
Other References on the web

Mouse Tricks - Point and Click

2010-06-03T19:59:00.000-07:00

What would have happened if he had used a touch screen instead a mouse at work ?

Funny Ad over Work - For more funny movies, click here

MYSQL UPGRADE 5.0 TO 5.1

2010-06-03T06:17:00.000-07:00

UPGRADE MYSQL 5.0.45 TO ENTERPRISE 5.1.40

I did a MySQL upgrade from 5.0.45 to 5.1.40 on two servers. Before starting the actual upgrade, I spent some time reading manuals and web postings. It turned out that the times were well spent. The upgraded version was Enterprise 5.1.40. However, the upgrade processes should be the same if it was upgraded to the same Community version.

These are some information on existing software:

· Server version: 5.0.45-log Source distribution
· OS: Redhat Linux 4. (2.6.18-92.el5), 64 bits.

Note that the existing installation was from the packages that were bundled with Redhat Linux packages.

Following were the steps I took to do the upgrade. I also included here all the issues that I had encountered in each step.

1. Obtain a full database dump (backup) and made a backup copy of the my.cnf file.

These two things are, no matter it was involving an upgrade or other major server works, important to do and should not skip.

#>mysqldump –u root –p dbs >dbs-dump.sql
#>cp /etc/my.cnf /tmp/my.cnf

2. Stop MySQL

#>./etc/init.d/mysqld stop

3. Upgrade (Install) MySQL Server

#> rpm -Uvh MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm

Here I got my first error:

error: Failed dependencies:
libmysqlclient.so.15()(64bit) is needed by (installed) perl-DBD-MySQL-3.0007-1.fc6.x86_64
libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by (installed) perl-DBD-MySQL-3.0007- 1.fc6.x86_64

Initially, I thought this error was due to some compatibility issues, so I decided to go ahead and to install the shared libraries (shared-compat package) first:

#> rpm -Uvh MySQL-shared-compat-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm

The package was installed without any error. I then tried to install the server package again.

#> rpm -Uvh MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm

This time, the server installation started without the dependency problems, but it gave me more warning and error messages:

Preparing... ########################################### [100%]
******************************************************************
A MySQL server package (mysql-server-5.0.45-7.el5) is installed.

The current MySQL server package is provided by a different vendor (Red Hat, Inc.) than MySQL AB or Sun Microsystems, Inc..

Some files may be installed to different locations, including log files and the service startup script in /etc/init.d/.
Upgrading directly from MySQL 5.0 to MySQL 5.1 may notbe safe in all cases. A manual dump and restore using mysqldump is recommended. It is important to review the MySQL manual's Upgrading
section for version-specific incompatibilities.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages. Usually this command will list the packages you should remove:
rpm -qa grep -i '^mysql-'

You may choose to use 'rpm --nodeps -ev ' to remove the package which contains the mysqlclient shared library. The library will be reinstalled by the MySQL-shared-compat package.

- Install the new MySQL packages supplied by Sun Microsystems, Inc.
- Ensure that the MySQL server is started
- Run the 'mysql_upgrade' program

This is a brief description of the upgrade process. Important details can be found in the MySQL manual, in the Upgrading section.
******************************************************************
error: %pre(MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64) scriptlet failed, exit status 1
error: install: %pre scriptlet failed (2), skipping MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4

Well, it turned out, there was no such thing as “Upgrade”. It required that we removed the existing packages, installed all the new packages, and then, run the upgrade script to upgrade the existing databases.

Following the instructions, I checked what was installed in the server:

#> rpm -qa grep -i '^mysql-'

mysql-5.0.45-7.el5
MySQL-shared-compat-advanced-gpl-5.1.40sp1-0.rhel4
mysql-server-5.0.45-7.el5

Since I just installed the MySQL-shared-compat-advanced-gpl-5.1.40sp1-0.rhel4 package, all I needed to remove were the following packages:

· mysql-5.0.45-7.el5
· mysql-server-5.0.45-7.el5

#> rpm -e mysql-server-5.0.45-7.el5.x86_64
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave

# > rpm -e mysql-5.0.45-7.el5
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

Now, I was ready to install the new packages again:

# > rpm -Uvh MySQL-server-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm

The installation was successful with the following messages:

Preparing... ########################################### [100%]
1:MySQL-server-advanced-g########################################### [100%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h mysql.domain.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
he latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL.[ OK ]
Giving mysqld 2 seconds to start

By checking the log and using ps –ef | grep mysql, I confirmed that the server was started.

4. Installed the client package.

Since the server was running, I wanted to install the client package so I could connect to the server.

#> rpm -Uvh MySQL-client-advanced-gpl-5.1.40sp1-0.rhel4.x86_64.rpm

No problem installing the client. So, I tried to connect to the server and it worked.

#>mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40sp1-enterprise-gpl-advanced MySQL Enterprise Server - Advanced Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;

+--------------------+
Database
+--------------------+
information_schema
ysql
test
+--------------------+
3 rows in set (0.00 sec)

So far so good.

5. Installed other packages.

There were other packages, such as share libraries, embedded MySQL server library and devel libraries and include files (MySQL-devel package),etc, that needed to install in my case . I installed all of them without any problem.

6. Restarted the server with my.cnf file.

Since the server were started without the my.cnf file, I needed to stop the server, copy the my.cnf back to the /etc/ directory (see step 1) and restarted the server. While executing the rpm –e command to remove the old packages, the my.cnf file was renamed to my.cnf.rpmsave (see step 3)

#>./etc/init.d/mysql stop
#>cp /tmp/my.cnf /etc/my.cnf
#>./etc/init.d/mysql start

Checked the error log, I noticed the following error messages:

100602 13:05:34 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
100602 13:05:34 [ERROR] mysql.user has no `Event_priv` column at position 29
100602 13:05:34 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
100602 13:05:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.40sp1-enterprise-gpl-advanced-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (GPL)
100602 13:44:57 [Warning] Statement may not be safe to log in statement format. Statement: ALTER TABLE general_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL
100602 13:44:57 [Warning] Statement may not be safe to log in statement format. Statement: ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL

These messages were expected because I had not run the mysql_upgrade script.

7. Executed the mysql_upgrade script

The script, in my case, was located in /usr/bin.

#>./usr/bin/mysql_upgrade

Again, it was a smooth run.

8. Loaded the database dump file

At this point, I could load the database back to the server.

#>mysql –u root –p < dbs-dump.sql

There was no error or warning message.

9. Restarted the server

Here, just to restart the server one more time to make sure there was no error message.

#>/etc/init.d/mysql stop
#>/etc/init.d/mysql start

Checked the error log and there was not error. I also ran some queries and connected an application to the database server. No error as well. So, Server was upgraded.

Summary: It is very important to read the MySQL manual, other articles such as those that listed under References, and search the web for any new information before doing the actual upgrade. Also, if it is possible, try it out in a development server first before doing it in a production environment. There are more than one way to do the upgrade, but each has its own upside and downside. I did the above upgrade on two QA servers and there was very little time pressure. One thing to keep in mind is that doing mysql dump and load takes time depends on the size of the database; therefore, a detail plan is need for large production system upgrade. There is other method, such as replication, that can be used to reduce times. It is also recommended that we should check for performance issues and query consistent issues after the upgrade. In some cases, mysql_upgrade may not run as smoothly as it was in my case. The MySQL manual also states some known bugs and issues which are worth to check out against the existing installation to make sure if or not they apply to the existing installation and there is a plan to due with them if they do.

Following are the summary of steps for the upgrade:

1. Backup databases and my.cnf file
2. Stop MySQL server
3. Remove existing MySQL packages
4. Install MySQL 5.1 server package
5. Install MySQL 5.1 client and other MySQL 5.1 packages
6. Restart MySQL with my.cnf file
7. Execute mysql_upgrade script
8. Load database backup to MySQL server (v.5.1)
9. Restart server and check error log.

References:

Related Posts:

MYSQL MYISAM_RECOVER OPTIONS

2010-05-27T00:44:00.000-07:00

MYSQL MYISAM_RECOVER OPTIONS

This variable sets the mode for automatic recovery when a MyISAM table crashed. The option value is any combination of the following values:

DEFAULT --Recovery without backup, forcing, or quick checking. It is the same as not giving any option.
BACKUP --If the data file was changed during recovery, save a backup of the tbl_name.MYD file as tbl_name-datetime.BAK.
FORCE --Run recovery even if we would lose more than one row from the .MYD file.
QUICK --Don't check the rows in the table if there aren't any delete blocks.

Multiple values can be set at the same tiem. To use multiple values, separate them by commas.

    myisam_recover=backup,force

To disable this option, set it to “”.

    myisam-recover=””

If one or more myisam-recover option is used, each time mysqld opens a MyISAM table, it checks if or not the table is marked as crashed or wasn’t closed properly. If it is the case, mysqld runs a check on the table and attempts to repair it if the table was corrupted. Following are the steps which mysqld will perform according to the MySQL Manual:

The server checks the table for errors.
If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).
If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.
If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

If the recovery wouldn't be able to recover all rows from previously completed statements and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log:

Error: Couldn't repair table: test.g00pages

If FORCE was specified, a warning like this is written instead:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if the automatic recovery value includes BACKUP, the recovery process creates files with names of the form tbl_name-datetime.BAK. You should have a cron script that automatically moves these files from the database directories to backup media.

Myisam-recover is not a dynamic variable; hence, it requires a server restart to reset the variable.

References:

Related Posts:

MySQL Delay Key Write Option

MYSQL MYISAM_MAX_SORT_FILE_SIZE Variable

2010-05-27T00:11:00.000-07:00

MYSQL MYISAM_MAX_SORT_FILE_SIZE Variable

MYISAM_MAX_SORT_FILE_SIZE sets the maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes and the default value is 2GB.

Set this values a bit larger thatn the largest index file if there is disk space is available to ensure that REPAIR TABLE is be done by sort rather than repair by key cache. Sorting is much faster than repair by key cache.

References:

Related Posts:

MYSQL BULK_INSERT_BUFFER_SIZE Variable

2010-05-26T22:05:00.000-07:00

MYSQL BULK_INSERT_BUFFER_SIZE SYSTEM VARIABLE

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. The BULK_INSERT_BUFFER_SIZE variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB. The maximum value is 4GB. The bulk_insert_buffer_size can be set both on system and session levels.

If data is being added to a non-empty table, tuning the bulk_insert_buffer_size variable can make data insertion faster. Normaily, it shows the improvement when the data to be inserted is more than 10k rows. But it is hard to say what is the right value, so, trail and try with incremental buffer size values.

Also, performance improvement will be more obvisoue if the following variables are also set:

MYISAM_SORT_BUFFER_SIZE
KEY_BUFFER_SIZE

In addition, when doing bulk inset, consider disable the indexes before loading the data using the following alter table command:

alter table t disable keys;

References:

Related Posts:

MYSQL MYISAM_SORT_BUFFER_SIZE Variable

2010-05-26T19:11:00.000-07:00

MYSQL MYISAM_SORT_BUFFER_SIZE Variable

This is the The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. The maximum size is 4 GB.

This variable should be set as large as the largest index in the table for index builds if there is sufficient RAM and it is not over 4 GB.

This variable can be set as global variable or session variable.

MYSQL>set session MYISAM_SORT_BUFFER_SIZE=1073741824;
or
MYSQL>set global MYISAM_SORT_BUFFER_SIZE=1073741824;

Or, to set it in my.cnf file:
MYISAM_SORT_BUFFER_SIZE=1073741824

References:

World Expo 2010 Shanghai China

2010-05-21T07:07:00.000-07:00

World Expo 2010 (aka World Fair) is under way in Shanghai China.

Ever since the first World Fair, which held in The Crystal Palace in Hyde Park, London, United Kingdom in 1851, there have been total 54 World Exhibition taking places in different countries according to Wikipedia. However, officially, Expo 2010 is the 41st World Expo. The Bureau International des Expositions (BIE), which governs the World Expo, provides information on organizations and history of the Word Expo. Following are some facts I had found which were related to World Expo 2010,Shanghai China.

The Participants:

189 countries and 57 international organizations are participating in the Shanghai World Expo. 18 corporations including Cisco and Coca-Cola.

The Site:

The Shanghai World Expo is the largest World's Fair site ever at 5.28 square km (2.5 square miles ). It costs $46 billion USD to build. The official cost of $4.2 billion covers just the Expo site and its operations.

The Pavilions:

The site itself is made up of a series of 100 pavilions. The China Pavilion, an inverted, imperial-red pyramid known as the Oriental Crown, built at an estimated cost of 1.5 billion RMB. Five permanent structures: China Pavilion, Expo Center, Expo Culture Center, Theme Pavilions, and Expo Boulevard designed for continual use after the Expo.

The Staff:

There are 50,000 Expo Bureau staff and other service staffs during the Expo, There are 72,000 volunteers, age from 16 to 99, working inside the Expo site, and 4,000 Expo-exclusive taxis dedicate for the Expo. There are another 100,000 volunteers will staff more than 1,000 service centers around the city during the event.

Visits:

The Expo will open for 184 days, from May 1st to October 3oth, 2010. It is expected to attract 70 million visitors, including an estimated 5 million foreign tourists. It also expects to receive almost 100 foreign leaders.

Tickes:

The ticket prices ranging from 90 to 200 yuan (or approximately $13 to $30 USD) depending on whether you visit on a standard day, peak day, or only during evening hours. All tickets are good for either 3 or 7 days, depending on which type of ticket you choose.

The Theme:

The World Expo 2010 theme is “Better City, Better Life”. There are five sub-themes:

               * Blending of Diverse Cultures in the City
               * Economic Prosperity in the City
               * Innovation of Science and Technology in the City
               * Remodeling of Communities in the City
               * Interactions Between Urban and Rural Areas

           Here is the Officer Video of the World Expo 2010

The Theme Song:

The theme song is called "City" (成市) which was sung by movie star and Expo Ambassador Jackie Chan. This MTV video also features NBA star Yao Ming and pianist Lang Lang. Both are Expo Ambassadors as well.

References:

MySQL: Optimize Table

2010-05-21T01:20:00.000-07:00

MySQL Optimize Table Command

The Optimize table command defragments, reclaims free space and makes concurrent inserts wrk again. For MyISAM table, optimize table command performs the following tasks:

Repaire the table if the table has deleted or split rows. This, in trun, help defragment and reclaim free spaces.
Sort index pages if it is needed.
Update the table’s statistics.

One way to find out if one should run optimize table on a table is to compare the data length and data free values from the show table status command.

Data_length is the length of the data file.
Data_free is the the number of allocated but unused byte. These included those deleted records.

When a data_free value is relative large in comparing to date_length, optimize table should be performed agaist the table. Please see the following example:

mysql> show table status like '%table_name%' \G;

*************************** 1. row ***************************

Name: table_name

Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1473839
Avg_row_length: 186
Data_length: 420392440
Max_data_length: 281474976710655
Index_length: 52712448
Data_free: 145156624
Auto_increment: NULL
Create_time: 2010-05-18 18:35:16
Update_time: 2010-05-20 13:44:47
Check_time: 2010-05-20 02:21:54
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: delay_key_write=1
Comment:

   mysql> optimize table table_name;

+--------------------------------+----------+----------+----------+
Table                                           Op             Msg_type Msg_text
+--------------------------------+----------+----------+----------+
dwstgdb.table_name                   optimize      status          OK
+--------------------------------+----------+----------+----------+
1 row in set (50.08 sec)

   mysql> show table status like '%table_name%' \G;

*************************** 1. row ***************************
Name: table_name
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1473839
Avg_row_length: 186
Data_length: 275235816
Max_data_length: 281474976710655
Index_length: 30075904
Data_free: 0
Auto_increment: NULL
Create_time: 2010-05-18 18:35:16
Update_time: 2010-05-21 00:33:58
Check_time: 2010-05-21 00:34:14
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: delay_key_write=1
Comment:

Now we can see that the data_free value down to 0 from 145156624 after performing optimize table. This will indeed deframent the table and speep up the queries. In general, optimize table command should be performed on a reqular basis.

Referemces:

MySQL: Alter Table Disable Keys

2010-05-21T01:09:00.000-07:00

MySQL Disable Indexes Command

When inserting,updating or deleting a high percenblege of the data from a table, it is a best pratce to disable indexes. Especaily, when doing a bulk load into a empty table. To disable indexes, one can drop all the index keys and recreate them later. Or, we can use Alter Table table name Disable Keys to deactivate indexes on a MyISAM table.

To do that, before performing the sql statements that involve with insert, update or delete, issue the following command:

         mysql> alter table tablename disable keys;

Now perform the tasks; for example:

        mysql> insert into tablename(...) values( ...);

After the task has been completed, issue the following command to reactivate the indexes:

         mysql>alter table tablename enable keys;

ALTER TABLE tablename DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE tablename ENABLE KEYS tells MySQL to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. When using ALTER TABLE tablename DISABLE KEYS, please keep in mind the following:

ALTER TABLE tablename DISABLE KEYS will keep UNIQUE keys active.
Using ALTER TABLE tablename DISABLE KEYS requires ALTER, INSERT, CREATE and the INDEX privilege for that table.
While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.
ENABLE KEYS and DISABLE KEYS were not supported for partitioned tables prior to MySQL 5.1.11.

References:

MySQ: Delay_Key_Write

2010-05-21T00:43:00.000-07:00

MySQL Alter Table t Delay_ Key_ Write Command

The delay_key_write option works with MyISAM tables. Delayed key writing tells storage engine not to wtire change index data to disk at the end of any query that changes the data. Instead, the changes are kept in the key buffer until the server prunes the buffer or close the table. This option can boots performance if the MyISAM table is changing frequenry, for example, has a lot of inserts and updates.

To turn on delay_key_write option for a table, you can include the following statement in to create table script:

Delay_key_write =1

Or for existing table, use the alter table statement:

MySQL> ALTER TABLE table name DELAY_KEY_WRITE = 1;

Please note that,in MySQL, Alter Table statement takes time to excute and locks the table. Therefore, for large tables, so do this with care.

There are a few thing need to keep in mind when using delay_key_write option as well. First, the changes, mainly, the index changes, will be kep in memory until the buffer cache prunes or the table is closed. A table is closed when:

When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than table_cache entries and a thread is no longer using a table.
When FLUSH TABLES command is issued.

This means that if the server or table is crashed, there will be indexes cruptions since the indexes on the index file (tablename.MYI) was not updated while the data file (tablename.MYD) was. Therefore, it is recomanded that the myisam-recover variable to be turned on during server startup. For example, set
--myisam-recover=BACKUP,FORCE in my.cnf file or start the server with –myisam-recover option. Secondly, if the table has many write delays, it will table longer for the table to close since it needs time to flush all the caches to the disk, and it will take longer to run FLUSH TABLES statements(flush table, flush table with read lock) Hence, flush tables should be used a bit frequely if you use delay_key_write option. Lastly, queries may need to wait for the MyISAM engine to free up space in the key buffer cache because the unflushed dirty blocks in the key buffer cache take up space.

Delay_Key_write option can also be enable globally for all MyISAM tables. To do so, set the delay_key_write option to ‘all’:

delay_key_write = all

However, it is not a commend pratics to most systems because for infrequently written tables, delay_key_write may increase the chance of index corruption.

References:

MySQL: Failed to CREATE PROCEDURE ERROR 1307 (HY000)

2010-05-21T00:28:00.000-07:00

ERROR 1307 (HY000): Failed to CREATE PROCEDURE …

When tried to create a procedure, I got the above error message. The user had the needed create routine privileges and the procedure syntax was corrected. Used the following command to check the version and found out that it was v.5.1.30.

mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.30-community-log |
+----------------------+
1 row in set (0.00 sec)

After some researches, it turned out that the database was a copy from an earlier version. So to resolve the problem, I need to apply the mysql_upgrade script. For Redhat Linux installation, it was located in /usr/bin. But it is easy to find out where it is by using the Linux ‘which’ command:

#> which mysql_upgrade
/usr/bin/mysql_upgrade

To excute the script, from the command line run the script using mysql root user and password:

#>/usr./bin/mysql_upgrade -u root –p

Now it should be fine. The script also printed out messages on the screen on what got updated during the execution.
References:

Download Oracle Softwares Using Wget and Browser

2010-05-11T07:19:00.000-07:00

Oracle softwares can be downloaded for free for developing and prototyping your applications (or for strictly self-educational purposes) from its download site. Please read the Development License term for more details. (http://www.oracle.com/technology/software/index.html)

To download the software, you first need to create an Orcale (some time call Oracle Technology Network) user account here.(https://login.oracle.com/mysso/signon.jsp). Please note, it is different from a MetaLink Support account and MetaLink account can be used as well. Following show two different ways of downloading the softwares.

1. Download Using Web Browser

It is easy to download using Web Browser. Just select the software from the download list, accept the license agreement, and download and save the software from the browser.

2. Download Using Linux Wget

If you are installing the software in a Linux box or don’t not have access to a browser, you can use wget
(http://www.gnu.org/software/wget/) to donwload the software. The syntax to download using wget is:

wget -vv --post -data="remoteIp=globalId=&redirectUrl=username=&password=&submit=Continue" https://profile.oracle.com/jsp/reg/loginHandler.jsp -o

Where:
remote IP = ip address of the server where wget is issued
login id = oracle account login id
password=oracle account password.
Log file = log file which can use to log information for the download. Just pick a file name.
download path and file name = the directory and file name of the file you want to download.

To get the download path and file name information, try to download the file from a browser. After starting the download, copy the url from the browser and past it on the notepad. For example, assuming your want to download database 10g, the url you copy will look like this:

https://profile.oracle.com/jsp/realms/otnLogin.jsp?remoteIp=192.168.1.1&globalId=&redirectUrl=http%3a%2f%2fdownload-llnw.oracle.com%3a80%2fotn%2flinux%2foracle10g%2f10201%2f10201_database_linux32.zip

And the download path and file is:
http%3a%2f%2fdownload-llnw.oracle.com%3a80%2fotn%2flinux%2foracle10g%2f10201%2f10201_database_linux32.zip

For example, to download the database 10g (10.0.2.1); and, remote IP = 192.168.1.1, oracle account id = bobsmith, password = password and log file = ora.log. The wget command is:

wget -vv --post-data="remoteIp=192.168.1.1&globalId=&redirectUrl=http:%2F%2Fdownload-llnw.oracle.com:80%2fotn%2flinux%2foracle10g%2f10201%2f10201_database_linux32.zip&username=bobsmith&password=password&submit=Continue" https://profile.oracle.com/jsp/reg/loginHandler.jsp -o ora.log

Please note, --post-data is one parameter.
Check the log file for error after the download to make sure a completed file was downloaded.

References:

Related Post:

Install Oracle On Linxu Using A Response File

MySQL: Startup MySQL Server Failed

2010-05-11T06:57:00.000-07:00

Starting MySQL/etc/init.d/mysql: line 159: kill: (9193) - No such process [FAILED]

When tried to start the MySQL server ( v. 5.0.51a, in RedHat Linux 4) using /etc/init.d/mysql start, I got the following error message and the server failed to start.

#>/etc/init.d/start
Starting MySQL/etc/init.d/mysql: line 159: kill: (9193) - No such process [FAILED]

Unforturalely, there was not error in the error log to tell what caused the problem. After some debugging, it turned out, it was because there was one of the server variable, which was added to the my.cnf file after the last successfull server start, was not supported. The server variable was:

open_file_limit

After command out this variable in the my.cnf file, there server started without problem,

In general, if there is any variable which was wrongly entered (misspelled, for example) in the my.cnf file, the server will fail to start. However, there will not be any error message in the error log to inform the user what cause the error.
References:

Related Post:

Mysql Start Up Error: MySQL..Manager of pid-file quit without updating file.[FAILED]

MySQL: query_cache_size

2010-05-11T06:36:00.000-07:00

MySQL Query Cache Variable: query_cache_size

The query cache in MySQL stores the text of SELECT statement as well as its result set. If an identical query is sent to the server later, the server retrieves the result set from the query cache instead parsing and exciting the query again. Therefore, it reduces time and resource that need to execute the query. However, please note that Query Cache only works for identical queries (exactly the same). For example, the following two queries are not considered identical:

Select city, state, zip from address where city = ‘new york’
Vs.
Select city, state, zip from address where city = ‘NEW YORK’

These two queries are also not considered identical:

Select city, state, zip from address where city = ‘NEW YORK’
Vs.
SELECT city, state, zip from address WHERE city= NEW YORK’

Because one use lower case clause word (select and where) and the other use upper case clause (SELECT and WHERE). Also, the cache is not used for queries of the following types:
· Queries that are a subquery of an outer query
· Queries executed within the body of a stored function, trigger, or event

(1.) Query Cache Variables

The following are server variables need to be set to make use of the query cache.

1. Have_query_cache: this variable indicates where or not query cache is available. The default is ‘Yes’ for standard installation.
2. Query_cahce_type: this variable sets the query cache type. It can be set in global and session level. The values for this variable can be 0,1 or 2:

0 - tells the server not to cache results or retrieve results from the query cache. However, this setting dose not deallocate the query cache buffer. Refer to query_cache_size section for how to allocate the query cache buffer.
1 – tells the server to cache all queries except for those that begin with select sql_no_cache. The default for Query_cahce_type is 1.
2 – tells the server to cache query results only for queries that begin with select sql_cache.

3. Query_cache_size: this variable sets the amount of memory allocated for caching query result sets. To disable or deallocate the query cache, set this variable value to 0, which is also the default. Please note that memory are allocated for query cache regardless if Query_cahce_type is set to 0 or not if query_cache_size variable is set to 40960 bytes or more. If the value is set to less than 40960, the server will reset the query_cache_size variable to 0 and place a warning message in the error log.

4. query_cache_min_res_unit: this variable sets the minimum size (in bytes) for) blocks allocated by the query cache. The default value is 4KB. According to the MySQL Manual, this should be adequate for most case. The query cache allocates blocks for storing query results by allocating memory blocks set by the query_cache_min_res_unit variable. If there are many queries with small result sets, the default block size may lead to memory fragmentation; moreover, it can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of the query_cache_min_res_unit variable.

5. Query_cache_limit: this variable sets the maximum size result set that the query cache will store. The default is 1MB.

(2.) Query Cache Status Variables and Monitoring

Qcache_free_memory: this status variable shows that amount of free memory for query cache. If the amount is low in comparing to the amount of the Query_cache_size, memory may be added to the Query_cache_size.
Qcache_not_cached: this status displays the number of queries that were not cached. Not all the queries are cacheable such as a non-select query.
Qchache_queries_in_cache: this status display the number of queries registered in the query cache.
Qcache_total_blocks: this status displays the total number of blocks in the query cache.
Qcache_hits: this status variable displays the number of query cache hits
Qcache_inserts: this status variable displays the number of queries added to the query cache.
Com_select: this status display number of times a select statement has been executed. If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select.

              Com_select = (Qcache_inserts + Qcache_not_cached + queries with errors found during the
                                       column-privileges check)
              The total number of SELECT queries = ( Com_select + Qcache_hits + queries with
                                                                                    errors found by parser)
              The total number of successfully executed SELECT queries = ( Com_select + Qcache_hits )

Query cache utilization = (Qcache_hits/ total number of successfully executed SELECT queries)

                          Or
               Query cache utilization = (Qcache_hits/( Com_select + Qcache_hits ))

           Please note that it all depends on the kind of applications, the % Query cache utilization will be
           different.

    8. Qcache_free_blocks: this variable display the number of free memory blocks in the query cache.
    9. Qcache_lowmem_prunes: this status displays the number of queries that were deleted from the query
        cache because of low memory. This status variable value also increases if the cache is not big enough to
       hold the query statements. Also, this variable increase if there were many small query results sets which
      indicates memory fragmentation. If the qcache_lowmem_prunes status variable is greater than 0,
       consider increasing the query_cache_size dynamically and monitor the Qcache_lowmem_prunes status
       variable to make sure that it stop increase.

(3.) MySQL Commands

To determine if or not the query cache has become fragmented, you can execute the FLUSH QUERY CACHE statement to defragment the cache... After issuing the statement, only a single free block remains. This command dose not removes queries from the cache.

To remove all the queries from the cache, use the RESET QUERY CACHE command.

Some other considerations need to be given when setting up Query Cache. As the size of the query cache grows, it will take longer to scan for matching queries, take longer to retrieve the result sets, and increase mutex contention.

References:

Related Posts:

MySQL Key_Buffer_Size System Variable

2010-05-04T21:45:00.000-07:00

MySQL MyISAM Key Cache: Key_Buffer_Size System Variable

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache. For 32-bit system, the maximum allow amount is 4 GB. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning).

You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads,so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be usingin addition to MyISAM.

Please note that the key cache is not used if the key_buffer_size value is too small to allocate the minimal number of block buffers (8).

1. Key_Buffer_Size Status Varaiables and Monitoring:

The following global status variables should be checked periodically:

Key_read_requests: the number of requests that have been executed to read a key block from the cache.
Key_reads: the number of physical reads of a key block from disk.

     Key cache hit (read miss) ratio = (1-(Key_reads/Key_read_requests)) * 100.

The key cache hit ratio represents the proportion of keys that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.

Key_write_requests: the number of requests to write a key block to the cache.
Key_writes: the number of actual writes, logical and physical key block, to disk.

      Kewrite miss ratio = (1-(Key_writes/Key_write_requests)) * 100

The key write ratio is usually near 100% if you are using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time or if you are using the DELAY_KEY_WRITE table option.

Key_blocks_unused: the number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time. The more blocks in the key cache, the less the server is using disk-based I/O to examine the index data, therefore, the value
of this variable should be high.
key_cache_block_size: the number of requests that have been executed to read key block from the cache.
The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable:

     1- ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)

This value is an approximation because some space in the key buffer is being allocated internally for administrative structures.

Key_block_not_flushed: the number of key blocks in the key caches that have changed but have not yet been flushed to disk. These can be considered ‘dirty” key blocks in the key cache. The index will be corrupted during a crash and will need to be repaired before it can be used again. Therefore, this value should be near zero (0).

Adjust the key_buffer_size variable and monitor the key cache hit ratio. Please don’t forget to put the corresponding value of key_buffer_size in your my.cnf; hence, the variable is set properly when the server is restarted.

References:

Related Post:

SQL: SELECT THE FIRST N RECORDS

2010-04-30T23:53:00.000-07:00

Assuming that we want to select the first 8 registrations from the following sample User table

Id	First_Name	Last_Name	City	Registration_Date
1	John	Smith	New York	2009-01-01
2	Adam	King	London	2008-12-01
3	Lin	Lin	Beijing	2009-12-01

Let's start with a MySQL query, which will use the LIMIT clause:

Select * from user order by registration_date limit 8;

The above query will give the first 8 users by registration date.

Now, if we want to have the first 8 users from New York, you can change the query to this:

     Select * from user WHERE city=’New York’ ORDER BY registration_date limit 8;

Now let’s try the following query:

    SELECT * FROM
       ( SELECT * FROM user ORDER BY registration_date LIMIT 8 ) As first_8
    WHERE city='New York';

This above query result actually returns the users from New York from the first 8 registrations. The number of records return will be between 0 and 8 in reality because the WHERE clause filter the records from the sub query result set.

Since each database venders use different non-standard result limit syntax, the above query needs to rewrite to accommodate the particular syntax . For example, in Oracle, the query can be rewritten in follow form:

   Select * from user order by registration_date where rownum >= 8;

And in SQL Server, it can be written like this:

   Select TOP 8 * from user order by registration_date;

Reference:

Mysql Replication Slave Stopped Due to “Lock Wait Timeout Exceeded” Error

2010-04-27T02:01:00.000-07:00

MySQL Replication: [ERROR] Slave: : Error 'Lock wait timeout exceeded; try restarting transaction' on query.

In a deadlock situation, the Mysql Replication server slave would let the transaction to retry up to the number of time which was set for the slave_transaction_retries system variable before stopping the slave and logging a message like the one bellow in the error log:

100419 0:03:47 [ERROR] Slave: : Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'mysqldb'. Query: 'UPDATE a_table SET colum_1 =a_value_colum + 1000,…,… WHERE id='12345'', Error_code: 1205
100419 0:03:47 [Note] Slave SQL thread exiting, replication stopped in log 'slave-1-log-bin.006034' at position 10470968

Refering to the MySQL manual; the 1205 error has the following description:

Error: 1205 SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT)
Message: Lock wait timeout exceeded; try restarting transaction.

Since the tables involved in the locks were InnoDB tables, we could check the InnoDB status by issuing the following command:
Mysql> show engine innodb status\G;
---------------------------------------------
LATEST DETECTED DEADLOCK
---------------------------------------------
*** (1) TRANSACTION:
TRANSACTION 0 2513168731, ACTIVE 0 sec, process no 17876, OS thread id 685890464 starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 8018, query id 317267495 user_id Updating
...
There were some useful information regarding the lock from the above output under the LATEST DETECTED DEADLOCK section which details the causes of the deadlock such as what kind of locks were used, and the query statements that involved in the lock.

To resolve the deadlock issues, we may need to debug the query statements and make code changes. In the mean time, we can check innodb_lock_wait_timeout and slave_transaction_retries to see if we can increase it a bit to avoid stopping the slave. To do so, issue the following statement from the mysql client:

Mysql> show variables;
innodb_lock_wait_timeout | 150
…
slave_transaction_retries | 10

The innodb_lock_wait_timeout variable set the length of time a transaction will wait for a row lock resource before “giving up”. The default value is 50 seconds. Please note that this variable applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks. InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

The slave_transaction_retries set the number of time the replication slave thread to retry before stopping with an error due to any of the following situation (the default for this variable is 10):

· an InnoDB deadlock
· the transaction's execution time exceeded InnoDB's innodb_lock_wait_timeout
· the transaction’s execution time exceeded NDBCLUSTER’s TransactionDeadlockDetectionTimeout
· the transaction’s execution time exceeded NDBCLUSTER's TransactionInactiveTimeout,

Please also notice that, if the slave_transaction_retries server variable is set, the transaction will hold the lock for (slave_transaction_retries * innodb_lock_wait_timeout) seconds before giving up. In the example above, it means that a lock will be held for proximately 1,500 seconds (10*150). Therefore, try to find out what is causing this lock and fix it maybe a better solution.

In any case, please remember to restart the slave because deadlock error stopped the slave as it had indicated in the error log.

Reference:

Linux Command: cp -f, --force, Not Working

2010-04-20T22:41:00.000-07:00

Today, when I tried to copy some files from one dirctory to the other in my Linux server, I issued the following Linux command:

#>cp -rf /dir1/* /dir2

However, the -f, --force switch, which was to tell cp command to overwrite the existing files (it meas if an existing destination file cannot be opened, remove it and try again), did not seen to work. I kept getting the following message:

cp: overwrite ‘/dir1/subdir1/file.dat with /dir2/subdir1/file.dat’

Since I had about 300 files to copy, it was not easy for me to keep typing ‘y’. So, I did some searches and found the following solution.

1. Run the alias command to check if cp was set to alias with a different command.

#> alias

alias cp='cp -i'
alias mv='mv -i'
alias rm='rm -i'

Which you can see, cp was acturaly alias as 'cp –i'. -i, --interactive, which means prompt before overwrite. Therefore, -f was ignored.

I want to remove the alias so I could copy the files. To do so, I needed to issue the following command:

#>unalias cp

However, in normally situation, we do want to use cp –i to prevent accidentally override files. Therefore, I issued the following command after done with copying the files:

#>alias ‘cp=cp –I’

One more detail, to perform the above steps, it required root access. So, what happen if we don’t have root access? Well, the following command works:

Command:
#> /bin/cp -rf /dir1/* /dir2

It is because that the /bin/cp copy is not alias to any other command switch.

Guide to NoSQL, redux

2010-04-17T11:22:00.000-07:00

I really like this presentation in MySQL Conference 2010. The speeker was Mark Atwood.

What Does $1 Trillion Look Like?

2010-04-15T05:41:00.000-07:00

CNBC shows an interesting way to look at money.

MySQL Individual Thread System Variables Setting And Tunning

2010-04-11T01:10:00.000-07:00

MySQL Performance Tunning: Individual Thread Variables Setting And Tunning

MySQL use many factors to determine the amount of memory assigned to each

individual thread that is running. Following is a list of variables that can be applied to an individual thread:

· Sort_buffer_size
· Join_buffer_size
· Read_buffer_size
· Tmp_table_size
· Max_heap_table_size

1. The System Variables

Sort_buffer_size: this variable determines the amount of memory to allocate for sourt operations. Each thread that needs to do a sort will allocates a buffer from the variable value. If there is too many records to be sorted in the memory, then a disk based sort takes place instead, Which take longer to perform. To ensure the ORDER BY and GROUP BY operation performance, the sort_buffer_size can be increase.

Join_buffer_size: this variable determines the buffer size for joins that don’t use indexes (use full table scan). Normally, the best way to get fast joins is to add indexes; however, if this is not possible, increase the values of this variable to get a faster full join.

Read_buffer_size: this variable determines the size of the buffer used for catching row data during full table scans. Each thread that does a sequential scn allocates a buffer tof this size(in bytes) fro each table it scans. The default is set to 131072.

Tmp_table_size: this system variable determines the maximum size allowed for a temporary table staored in memory tables before it is convertrd to an on-dis MyISAM table. This variable should be increased if GROUP BY queries involving many rows are performed and the is enough memory to support them.

Max_heap_table_size: This variable sets the maximum size to which MEMORY tables are allowed to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

2. Getting Server Status and Setting the variable values

Each of the status variables below should be monitored to determine the size of the sort_buffer_size variabl:
· Sort_merge_passes: this status variable displays the number of merge passes that the sort alforithm has had to perform. Increase the sort_buffer_size values if the status value is large.
· Sort_range: this status variable show the number of sorts that were done using ranges
· Sort_rows: this status show the numberof sourted rows
· Sort_scan: this status show number of sourts that were done table scan.

The following stauts measure the number of joins that did not using indexes:
· Select_full_join
· Select_full_range_join
· Select_range
· Select_range_check
· Select_scan
Please note that one join buffer is allocated for each full join between two tables; and, complex joins between several tables for which indexes are not used should be avoided at all costs.

If many sequential scans rae carried out, it is best to increase the read_buffer_size values. According to MySQL documentation, the normal size for this variable should set to ! MB to 2 MB, but can be set up to 8 MB.

The following status need to be taken into account when setting tmp_table_size variable value:

· Created_tmp_disk_tables: this status shows the number of temporary tables on disk cread automatically by the serv while queries were executed. If BLOB/TEXT fields are requested in the queries, MySQL will use disk based temporary tables automatically. We need to determine how many BLOB/TEXT fields reuested statements were ran on the server before deciding change the tmp_table_size variable value.
· Created_ tmp_files: this status shows then number of temporary files has created.
· Created_tmp_tables: this status shows the number of in-memory temporary tables created automatically by the server while excuting queries. If the ratio of created_tmp_disk_tables to created_tmp_tables is hight, consider increasing the tmp_table_size variable value. One or two creates per second is reasonable.

Please note that the max_heap_table_size variable places a maximum limit on tmp_table_size because memory storageengine is used for implicit in-memory temporary tables. For example, if tmp_table_size is 32M and max_heap_table_size is 16M, then the implicit MEMORY temporary tables will convert to on-disk MyISAM tables where they grow past 15M. Therefore, both these variable need to be considered when dealing with implicit temporary tables.

Also, it is best to have the individual session to set the values of the variables if only some queries need lare values and to avoid larger than necessary allocations for the queries which don't need large size.

References:

Related Posts:

Life Trapped in Amber from Age of Dinosaurs

2010-04-10T10:45:00.000-07:00

According to LiveScience, a host of bugs, fungus and other life forms have been found trapped in a 95-million-year-old amber.

Topeka Search

2010-04-01T06:04:00.001-07:00

Have a question on setting MySQL server variables? Topeka it.

MySQL Error: Too Many Connections, Error: 1040 SQLSTATE: 08004

2010-03-29T22:11:00.000-07:00

If you get a too many connections error,Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR), when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

To increase the concurrent connections what are allowed to connect to the server, change the max_connections system variable value. The default value was 100 prior to MySQL 5.1.15. The default value is 151. If you need to support more connections, you should set a larger value for this variable.

To check what were the max concurrent number of connections to the database server so far, check the Max_used_connections status. If the max_used_connections number is closed to max_connections variable value, the max_connections number should be increased.

Please check post Setting System Variables to see how to check status and set variables. There are other server connection related variables also need to be considered when setting max_connections variables. Those variables were covered in this post: Server Connection Variables Setting and Tuning