wiki:queryDerby

Version 2 (modified by niles, 8 years ago) (diff)

--

Examining the JMD Derby database

As discussed, the JMD uses an underlying database. Querying this database can give useful information when debugging the JMD.

One can connect to the derby database using the $JMD_HOME/bin/jmdij script, which will bring up the "ij>" prompt. This prompt accepts SQL style queries. For the JMD, the table of interest is named "drms.su_cache", which can be described with :

ij> describe drms.su_cache;          

The general state of the database can be queried :

ij> select count(*), state, request_type from drms.su_cache group by state, request_type order by REQUEST_TYPE, STATE;
1          |STA&|REQUE&
-----------------------
7          |OFLN|MIRROR
81         |OFLN|USER
ij> select SERIES_NAME, STATE, REQUEST_TYPE, count(*) from drms.su_cache group by series_name, STATE, REQUEST_TYPE order by count(*) DESC;
SERIES_NAME                                                                                                                     |STA&|REQUE&|4
--------------------------------------------------------------------------------------------------------------------------------------------------------
hmi.ic_45s                                                                                                                      |OFLN|USER  |6168
hmi.m_45s                                                                                                                       |OFLN|USER  |1826
hmi_test.v_45s                                                                                                                  |OFLN|USER  |78
hmi.v_45s                                                                                                                       |OFLN|USER  |73
aia.lev1                                                                                                                        |DONE|USER  |14
aia_test.lev1                                                                                                                   |OFLN|MIRROR|5
hmi.rdvflows_fd15_frame                                                                                                         |OFLN|MIRROR|1
aia_test.synoptic2                                                                                                              |OFLN|MIRROR|1

Occasionally the JMD will try repeatedly to download a sunum without success, in which case something like this will appear in the JMD log :

Jan 28, 2014 9:17:50 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[44]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:19:50 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[37]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:21:50 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[49]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:23:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[59]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:25:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[41]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:27:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[54]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:29:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[74]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:31:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[79]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:33:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[64]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]
Jan 28, 2014 9:35:45 AM org.vso.jmd.Downloader.SCPDownloader call INFO: Th ID:[69]; SU:[97359684]; RN:[0];[aia_test.lev1];[USER]; Sz:[9990778]; STP:[SCP]; ST:[FAIL] [N/A] [SAO]

In this case, the endless failures are occurring because the vendor in fact no longer serves out these data, so we want to stop trying (this is unusual). First, we establish if this storage unit number (sunum) is still in the derby database :

ij> select * from drms.su_cache where sunum in (97359684);

and if it is, we delete it :

ij> delete from drms.su_cache where sunum in (97359684);

The "ij>" prompt can be exited with CNTL-D, or by entering "exit;".