Experience While Copying a 42GB file into a Seagate USB Hardrive in Ubuntu Linux…

First of all i will split the file into 10GB pieces. The file name is: enwiki-20070908-stub-meta-history.xml

fawad@crete:~/wiki$ split -b 4000m enwiki-20070908-stub-meta-history.xml

Now i will copy a piece by piece into my seagate external hardrive. I am copying this file from a remote server to my machine

root@fawad-laptop:/home/fawad# scp fawad@crete.ex.nii.ac.jp:/home/fawad/wiki/enwiki-20070908-stub-meta-history.xml /media/usb0/niidata/split/
fawad@crete.ex.nii.ac.jp’s password:
enwiki-20070908-stub-meta-history.xml 10% 4091MB 10.5MB/s 57:03 ETAFile size limit exceeded (core dumped)
This means only files less than 4GB are supported. So i again spilled the files to 4GB file each.

Another important thing to note is that i had to use root login to copy file to my external seagate USB hardrive.

Now once i have splited the files into 4GB chunks. Now use the following command to copy all the files from this folder to one of the folder in the seagate harddrive.

root@fawad-laptop:/home/fawad# scp -r fawad@crete.ex.nii.ac.jp:/home/fawad/wiki/wikidata/ /media/usb0/niidata/wikifawad@crete.ex.nii.ac.jp’s password:
.nfs0000000000d5005100000001 100% 435MB 10.6MB/s 00:41
xad 27% 1101MB 9.5MB/s 05:05 ETARead from remote host crete.ex.nii.ac.jp: Connection reset by peer
xad 100% 4000MB 9.8MB/s 06:49
xag 100% 1233MB 9.8MB/s 02:06
xac 38% 1522MB 10.6MB/s 03:53 ETAh
xac 43% 1734MB 11.1MB/s 03:25 ETA
xac 100% 4000MB 10.1MB/s 06:38
xaf 100% 4000MB 10.2MB/s 06:32
xae 100% 4000MB 10.5MB/s 06:22
xaa 100% 4000MB 9.9MB/s 06:46
xab 39% 1573MB 9.9MB/s 04:05 ETARead from remote host crete.ex.nii.ac.jp: Connection reset by peer
xab 100% 4000MB 9.6MB/s 06:57

Installing MySql on Ubuntu Linux

fawad@fawad-desktop:~$ sudo apt-get install mysql-server

fawad@fawad-desktop:~$ mysqladmin -u root password 123456

fawad@fawad-desktop:~$ mysql -u root -p

mysql> CREATE DATABASE realitymining;
Query OK, 1 row affected (0.00 sec)

Upgrade Ubuntu from Edgy to Feisty

http://www.ubuntugeek.com/upgrade-ubuntu-610-edgy-eft-to-ubuntu-704-feisty-fawn-2.html

Method 2 – Using apt-get

Edit your /etc/apt/sources.list as root. Change every occurrence of edgy to feisty.

Use any prefered editor. If you have a CD-ROM line in your file, then remove it.

sudo vi /etc/apt/sources.list

or

use the following Simple command

sudo sed -e ’s/\edgy/ feisty/g’ -i /etc/apt/sources.list

Now you need to update the source list using the following command

sudo apt-get update

Upgrade using the following command

sudo apt-get dist-upgrade

Double check your process was finished properly using the following commd

sudo apt-get -f install

sudo dpkg –configure -a

Now you need to Reboot your machine to take your new ubuntu 7.04 installation to effect all changes.

Testing Your Upgrade

You can check the ubuntu version installed using the following command

sudo lsb_release -a

Output Looks like below

Distributor ID: Ubuntu
Description: Ubuntu feisty (development branch)
Release: 7.04
Codename: feisty

or

Just type the following command in your terminal

cat /etc/issue

Output Lokks like below

Ubuntu feisty (development branch) \n \l

Graph Visualization File Format...

Few visualization formats.

1. dot

2. fdp

3. neato

4. graphml

5. gml

6. ygf

Limiting the rows being displayed in Oracle...

Link: http://www.adp-gmbh.ch/ora/sql/rownum.html

ROWNUM in SQL
		rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum. After issuing a select statement, one of the last steps that oracle does is to assign an increasing (starting with 1, increased by 1) number to each row returned. The value of this row number can always be queried with rownum in a select statement: select rownum, column_1, column_2 from table_1, table_2 where field_3 = 'some value' It is important to realize that the first row's rownum is always 1. This implies that the following query won't return a single row: select column_1, column_2 from table_1, table_2 where field_3 = 'some value' and rownum > 5 This is so because the first row would have to meet the following two mutually excluding criterias: rownum is 1 rownum is 6 (rownum > 5) In order to do this query in the (probably) intended spirit, a sub-query must be executed: select column_1, column_2 from ( select rownum r_, column_1, column_2 from table_1, table_2 where field_3 = 'some value' ) where r_ > 5

Substituting NULL in Oracle using NVL...

LINK: http://www.techonthenet.com/oracle/functions/nvl.php

Oracle/PLSQL: NVL Function

---

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Example #1:

select NVL(supplier_city, 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Example #2:

select supplier_id,
NVL(supplier_desc, supplier_name)
from suppliers;

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

Example #3:

select NVL(commission, 0)
from sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

Frequently Asked Questions

---

Question: I tried to use the NVL function through VB to access Oracle DB.

To be precise,

select NVL(DIstinct (emp_name),'AAA'),................ from.................

I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.

Answer: It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

select distinct NVL(emp_name, 'AAA')
from employees;

Hope this helps!

---

Question: Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this:

NVL(column1;column2 ...... , here is the default value for all )

Answer: You will need to make separate NVL function calls for each column. For example:

select NVL(table_name, 'not found'), NVL(owner, 'not found')
from all_tables;

Oracle/PLSQL: Lag Function

REF: http://www.techonthenet.com/oracle/functions/lag.php

In Oracle/PLSQL, the lag function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the lead function.

The syntax for the lag function is:

lag ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

expression is an expression that can contain other built-in functions, but can not contain any analytic functions.

offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

Let's take a look at an example. If we had an orders table that contained the following data:

ORDER_DATE	PRODUCT_ID	QTY
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

And we ran the following SQL statement:

select product_id, order_date,
lag (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders;

It would return the following result:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
1000	25/09/2007
2000	26/09/2007	25/09/2007
1000	27/09/2007	26/09/2007
2000	28/09/2007	27/09/2007
2000	29/09/2007	28/09/2007
1000	30/09/2007	29/09/2007

Since we used an offset of 1, the query returns the previous order_date.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders before. If we had used an offset of 3, it would have returned the order_date from 3 orders before....and so on.

If we wanted only the orders for a given product_id, we could run the following SQL statement:

select product_id, order_date,
lag (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders
where product_id = 2000;

It would return the following result:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
2000	26/09/2007
2000	28/09/2007	26/09/2007
2000	29/09/2007	28/09/2007

In this example, it returned the previous order_date for product_id = 2000 and ignored all other orders.