Deleting duplicates in the DB2 with the IBM Control Center

Sometimes you have to do just a small task on the database and if you don't have/want to use a command line, you can use an interface like the Control Center.

If you have tables with duplicate values in the DB2 and the tables have primary keys, let say ID, it is easy to avoid the duplicates.

First copy the table. Use the

create table message_table_copy ...

query and check that all fields you want to copy have the same types. 

Than you can load the data into the message_table_copy. The table from which you are coping and the table to which you are coping, in this case  message_table and message_table_copy should belong to the same schema. Choose Load (right-click menu) -> Add data -> from cursor. Than you can choose the data you want to copy (Select * from message_table if you copy the whole table) and the folder where the status information have to be stored (usually on the server, so choose the home directory of your db2 instance on the server). All other tabs are default except of the tab where you have to choose to perform the task immediately or to add in the task manager.

Now you can check if all values were copied. You can just count the number of entries in both tables:

select count(*) from message_table 

If everything is ok, look for duplicates. Let say, you have message_id which should be used to define duplicates. Here is the SQL query to delete the duplicates from the message_table

delete from message_table where id in 
          (SELECT MIN(ID) FROM message_table
                          GROUP BY message_id 
                          HAVING COUNT(message_id) >1) )

The query searches for entries with same message_id (INT, SMALLINT, BIGINT) and chooses the id ( you can choose between MAX(id)/MIN(id)) that is used for deleting.

Users editing articles in several Wikipedia

I was wondering about contributors who are posting in several Wikipedia, Who are they? What country are they from?

Surely English Wikipedia is a leader in the number of contributors, articles, articles edits and number of users who contribute both in English and any other Wikipedia is high. I never doubt it. What about other Wikipedia? Looking at Wikipedia we can find which nations interact more often with each other than others.

A bit more than 1% of all contributors from our data set editing/creating articles in 2 Wikipedia. Only 12 of 4,919,026 users have ever worked in 5 Wikipedia.

Most of the cross-Wikipedia authors are registered; therefore, we cannot identify the place where the authors come from. A majority of the cross-Wikipedia anonymous authors are coming from Germany (15%) and the United States (11%)

The cross-Wikipedia authors contributing to more than 3 Wikipedia are summarized in following table. 

We find 1,718 contributors that did 618,584 edits. In the table we summarize the contributors work: the number of edits and the number of edits per cross-Wikipedia user. Cross-Wikipedia users contributing in 3 Wikipedia, are participating often in Russian, Japanese and any other Wikipedia from the set. The highest activity (more than 100 edits per cross-Wikipedia user) was shown in Macedonian, Russian, Catalan and Arabic Wikipedia instances. Generally cross-Wikipedia users are more active than contributors working only in one Wikipedia.

Data set:

We analyzed the Wikipedia data starting from June, 30th 2001 till January, 1 2009 and divide this time into 16 equal time intervals to keep visualizations simple. The period of the 1st interval is from June 30th, 2001 till December 31st, 2001, the period of the 2nd interval is from January 1st, 2002 till June, 29, 2002 and so on. Because of hardware limitations, we did not consider all revisions done in the period for constructing author networks. As Wikipedia instances have different number of articles, we choose a number of revisions depending on the number of all revisions in the instances. The revisions are picked up along an article timeline. The overall structure of the Wikipedia network and activities of Wikipedians are not impaired by this data reduction. Finally, we got datasets with number of revisions comparable to each other.

We chose both European and Asian Wikipedia. The instances were selected according to their size: large European Wikipedia (Spanish and Russian), large Asian Wikipedia (Japanese and Turkish), small European Wikipedia (Bulgarian, Catalan, Danish, Greek, Macedonian, and Ukrainian) and small Asian Wikipedia (Arabic, Hindi, and Korean). The list of small European Wikipedia list includes Wikipedia of different Slavic languages (Bulgarian, Macedonian, Ukrainian) and the Catalan Wikipedia, the Wikipedia of a minority language group in Spain.