TWiki> Astrogrid Web>DocStore>DataDocs>DbmsEvaluations>DB2Evaluation (2002-11-04, PatricioOrtiz)EditAttach

DB2 Evaluation

DB2 was tested under the following circumstances:

Platform: Linux Red Hat 7.3, in a 1.7Ghz AMD processor PC
DB2 requires some kernel modifications (kernel capacity expansions)
Version: V 7.1
Data-sets: USNOA2, restricted to 3 million entries
GCS2, 275000 entries
The same as those used in the previous MySQL and Postgress evaluations.
Installation: Instructions were reasonably good, although some omissions caused to repeat the installation.
Time to install: a few hours
Operation mode: The test database account was used during the test.
command line was mostly used, although a java based GUI is quite useful to do some checkup.
Language: SQL (but not the same flavour as MySQL or Postgress or Oracle) plus IBM's own set of commands.
There is also a Perl interface which was not tested but could be used as a CGI script or for Web Services.
Loading time: USNO: 30 seconds
GSC: < 5 seconds
Index creation: USNO: 50 seconds. , disk usage: ~50Mbytes
GSC: 5 seconds
Cone search: The main test was to cross correlate GSC with USNO to a certain limit. The PCODE method suggested by C.Page was used.

Time to perform the cross correlation only using Pcode : 74 seconds

Other queries: select count(*) from usno where rmag < 10: 5 sec
select avg(bmag) from usno where bmag < 13; 5 sec
select stddev(bmag) from usno: 9 sec

Performance issues: An index was created on PCODE to make the search faster, but after having to rebuild the system (omitting by accident to do the index), DB2's performance was at the same level as with the indexation on PCODE done.

In a second try, three columns were added to each database: the cosine directors, i.e., a cartesian representation of the sky position. Distance in the sky is the arc-cosine of the dot product between the cosine directors of the two points in question. This is done to avoid computing trigonometric functions over and over in data on both sides of the cross correlation.

Not using PCODE and no indexation in any of the other variables suggested that the same cross correlation would take about 10 days (running continuously).

Other issues

  • Rounding a number (using round) has no effect on the displayed number of decimal places shown. The round operation is correctly performed, but the result looks odd. This may be a drawback in I/O if one is forced to interpret the ASCII output.

  • The inclusion of the 3 extra variables with the cosine directors reduces the need to compute a number of trigonometric function for each pair of objects (GSC and USNO) located in the same PCODE area.

    A cone search in which one writes:
    ang_dist < 3.5 arcsec
    where one has to go all the way to compute the distance, can be transformed into an equivalent query in the cosine(angle) space:
    cos(ang_dist) >= cos(3.5 arcsec)

    The quantity cos(ang_dist) is the dot product of the cosine directors (2 plus and 3 multiplication operations). cos( cone_limit) can be computed once for all the request saving so CPU usage.

  • The use of an inverse trigonometric function like acos requires that the argument be within -1 and +1. In a number of cases, roundoff problems made two sources with identical position produce an argument slightly greater than one, making DB2 crash. In this comparison it was found that for 6 sources this condition was satisfied (out of 250000) A user procedure should be used to determine the distance to avoid this problem.

  • IBM has a package called Spatial Extender, which sounds appealing to us as it claims to handle positional queries on the Earth's surface. At this moment it seems to be available only for windows and attached to the Enterprise edition of DB2; the price tag is around 5000 GBP.

-- PatricioOrtiz - 04 Nov 2002

Edit | Attach | Print version | History: r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r1 - 2002-11-04 - 16:15:00 - PatricioOrtiz
 
AstroGrid Service Click here for the
AstroGrid Service Web		 This is the AstroGrid
Development Wiki

This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback