Climatological Database Technical description Administrator's Guide January 2003Climatological Database manual Introduction 2 2003 © Copyright 2002, by MicroStep-MIS All rights reserved. No part of this publication may be reproduced, stored in retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise without the prior written permission of MicroStep-MIS. Trademarks Windows is a registered trademark of the Microsoft Corporation. All other trademarks or registered trademarks mentioned belong to their respective owners. Version of edition Version of edition: 2.0 Date of the edition: 2003-01-22 Address MicroStep-MIS Monitoring and Information Systems Ilkovičova 3 84104 Bratislava 4 Slovakia +421 2 602 00 111 +421 2 602 00 122 fax: +421 2 602 00 180 [email protected] MicroStep-MIS develops, manufactures and markets monitoring and information systems. The key fields of our activities are meteorology, aviation, environmental sciences, seismology, power engineering, civil defense but also information systems in tourism. MicroStep-MIS operates worldwide. Our core customer groups are airports, meteorological and seismological institutes, environmental authorities, industry, power stations and electricity distribution companies.Climatological Database manual Introduction 3 2003 Contents 1. Introduction.................................................................................................................................... 5 2. Climatological Database installation ............................................................................................. 6 2.1. Installation of the Oracle............................................................................................................ 6 2.2. Database Installation ................................................................................................................. 6 2.2.1. Database Creation: ............................................................................................................ 6 2.3. Installation of utilities and applications: ..................................................................................... 6 3. Setting connection for clients ........................................................................................................ 7 3.1. Oracle 9i Client .......................................................................................................................... 7 3.1.1. Installation .......................................................................................................................... 7 3.1.2. Configuration .................................................................................................................... 18 3.2. Configuration of ODBC............................................................................................................ 25 3.3. Configuration of BDE............................................................................................................... 27 3.4. Users ....................................................................................................................................... 28 4. Utilities and applications.............................................................................................................. 29 4.1. DB Manager for CLDB............................................................................................................. 29 4.1.1. Stations ............................................................................................................................ 30 4.1.2. Variables .......................................................................................................................... 35 4.1.3. Regions ............................................................................................................................ 37 4.1.4. Users privileges................................................................................................................ 38 4.2. Insert........................................................................................................................................ 39 4.2.1. Configuration file .............................................................................................................. 40 4.2.2. Log-file.............................................................................................................................. 41 4.3. ImportAWOS ........................................................................................................................... 42 4.3.1. Configuration file .............................................................................................................. 42 4.3.2. Log-file.............................................................................................................................. 43 4.4. ImportClicom ........................................................................................................................... 46 4.4.1. Configuration file .............................................................................................................. 46 4.4.2. Log-file.............................................................................................................................. 47 4.5. JpegToDB................................................................................................................................ 49 4.5.1. Configuration file .............................................................................................................. 49 4.5.2. Log-file.............................................................................................................................. 50 4.6. Disk manager (DiskMngr)........................................................................................................ 51 4.6.1. Configuration file .............................................................................................................. 51 4.6.2. Log-file.............................................................................................................................. 52 4.7. Database cleaner (DBCleaner) ............................................................................................... 53 4.7.1. Configuration file .............................................................................................................. 53 4.7.2. Log-file.............................................................................................................................. 53 4.8. Quality control (QC)................................................................................................................. 54 4.8.1. Configuration file .............................................................................................................. 57 4.9. Log-file..................................................................................................................................... 57 4.10. ImageViewer ........................................................................................................................ 59 4.10.1. Configuration file........................................................................................................... 59 4.10.2. Log-file .......................................................................................................................... 59 4.11. Index Rebuilder.................................................................................................................... 60 4.11.1. Log-file .......................................................................................................................... 60 5. Developers information ............................................................................................................... 61 5.1. Overview of Climatological Database Structure...................................................................... 61 5.1.1. Stations ............................................................................................................................ 62 5.1.2. Regions ............................................................................................................................ 63 5.1.3. Variables .......................................................................................................................... 64 5.1.4. Data.................................................................................................................................. 65 5.1.5. Quality Control Rules ....................................................................................................... 66 5.2. Tables and views in Climatological database.......................................................................... 67 5.2.1. Tables dependencies ....................................................................................................... 67 5.2.2. Tables description ............................................................................................................ 67 5.2.3. VALUES_F table .............................................................................................................. 67 5.2.4. Variables table.................................................................................................................. 68 5.2.5. Stations table.................................................................................................................... 68Climatological Database manual Introduction 4 2003 5.2.6. VarProps table.................................................................................................................. 68 5.2.7. StationProps table............................................................................................................ 68 5.2.8. VarPropValues table ........................................................................................................ 68 5.2.9. StationPropValues table................................................................................................... 69 5.2.10. VariableTable table....................................................................................................... 69 5.2.11. viewStationPropValues view ........................................................................................ 69 5.2.12. viewVarPropValues view .............................................................................................. 69 5.2.13. viewStationVars view.................................................................................................... 69 5.3. Excel examples ....................................................................................................................... 71 6. Dual Database (Replication) ....................................................................................................... 76 6.1. Dual database system support................................................................................................ 76 6.2. Summary ................................................................................................................................. 77 Contact:.................................................................................................................................................. 78Climatological Database manual Introduction 5 2003 1. Introduction Climatological databases belong to the usual means of the meteorological institutes today. The main reason of their utilization is storage of all meteorological data in one unified structure. This avoids data inconsistencies and discrepancies and enables standard comfortable data access for users and software systems. No other data storage, no other data formats and no other data access are needed. Within our solution, the unified structure mentioned above is based on the SQL Database Server and the standard data access is based on the SQL language. The database format is based on 3NF, which enables later extension of all used type ranges. The database contains the following data types: Data from meteorological messages received via GTS: − SYNOP − METAR − TEMP − PILOT Data from automatic weather stations: − AWOS Radar and satellite images: − HRTP − PDUS − Local radar Every value has defined: − Type − Origin − Time − Value − Unit/units − Other properties Data access is based on the definition of: − Types (wind speed, temperature, …) − Period (hourly, x hours, daily, monthly, ...) − Statistical kinds (minimum, maximum, average, ...) − Output form (table, graph, report, ...) User can access data using developed applications: Select, Reports, ImageViewer etc... CLDB internet/intranet screens enable to get the near real-time data. Manual corrections of the data and insertion of them into the database is enabled during the standard climatological process of data quality control. The hardware and database are designed to store data for a long time period. All stored data are available online. All stored data can be regularly saved to CDs or tapes.Climatological Database manual Climatological Database installation 6 2003 2. Climatological Database installation There are four phases of the installation: 1. installation of the Oracle database services 2. creation of the Climatological Database 3. installation of the utilities and applications 4. initialization of the Climatological Database 2.1. Installation of the Oracle For more information on the Oracle Server installation, refer to Oracle Installation Manual. For more information on the Oracle Client installation, refer to chapter 3. 2.2. Database Installation 2.2.1. Database Creation: Follow the procedure described below for the automatic creation of en empty database: 1. copy entire directory structure from /install/create/ to root directory of the climatological database disk, it is usually drive d: 2. run "sqlCLDB.cmd" to create empty database 3. add the created database to oracle listener configuration file using network configuration utility or manually /oracle/ora81/net80/admin/listener.ora. Into the section SID_LIST_LISTENER add the following text: (SID_DESC=(SID_NAME=CLDB)) 4. restart computer or restart all oracle services including database services 2.2.1.1 Preparing database: Database must be prepared after creation. The following procedure performs this task. 1. login as system 2. exec datafiles.sql 3. exec users.sql 4. login as cldb 5. exec tables.sql 6. exec currentdata.sql 7. exec user_web.sql 8. exec user_region.sql 2.3. Installation of utilities and applications: There are many various utilities and applications coming with CLDB. Installation of the server is very similar to the installation of the clients. 1. create alias for database using oracle NetEasyConfig with name cldb.world 2. copy following directories on the climatological database disk or to any application directory on the server and on the clients a. /dbmanager b. /select c. /reports d. /mandataentry e. /insert f. /qcClimatological Database manual Setting connection for clients 7 2003 3. Setting connection for clients Applications that need access to Climatological database can use various connection types like: • ODBC - Open Database Connectivity, used in Excel examples 3.1. Oracle 9i Client Any client PC, which needs access to database server, must have a database client software. In the case of Oracle 9i, it is Oracle Client application suite. 3.1.1. Installation Installation follows several steps. It is user friendly; almost no errors are possible in this process. After insertion of the Oracle 9i Client CD into PC, the installation application is started automatically (Autorun). Click the button Install/Deinstall Products to begin the installation. Click hereClimatological Database manual Setting connection for clients 8 2003 Use the button Next to proceed to the next step of installation. The Destination… directory paths must be specified, where the programs will be stored. Important: Do not change proposed directory paths. Oracle is sometimes very sensible to this setting. Use the button Next to proceed to the next step of installation.Climatological Database manual Setting connection for clients 9 2003 The next step contains the selection of the installation type. For client PC there is only one possibility – Oracle9i Runtime. It requires about 125 MB of disk space. Use the button Next to proceed to the next step of installation. A summary appears, which has only informative character. Use the button Install to proceed to the next step of installation.Climatological Database manual Setting connection for clients 10 2003 Wait please, while the installation application copies the necessary files: After the files are copied, Configuration tools are started. Installation is nearly finished and configuration is launched automatically.Climatological Database manual Setting connection for clients 11 2003 Net Configuration Assistant is started: Choose the second option 'No,…' and press the button Next. Custom configuration begins: When question about Oracle database version appears, select 'Oracle 8i or later database or service' and press the button Next:Climatological Database manual Setting connection for clients 12 2003 In the next step specification of the database identification follows. It is always CLDB for the Climatological database. Please type service name cldb.world and confirm by clicking 'Next'. In the next step user specifies the type of connection with database server PC. Please select TCP.Climatological Database manual Setting connection for clients 13 2003 The client needs TCP/IP address of database server. It is specified in the following step, you have to ask your network administrator to get the TCP/IP address of database server (Host Name). Port number should be 1521. Set the numbers and click 'Next'. Now, the configuration is nearly finished. Choose option 'Yes perform a test' to test the newly created connection. Use the button Next to proceed to the next step.Climatological Database manual Setting connection for clients 14 2003 The name and password are required for the connection test. (Use Change Login button.). Use your database login and password: With correct password, the test has to be successful (if connection is working good): Click hereClimatological Database manual Setting connection for clients 15 2003 If the test is successful, type 'CLDB.WORLD' as Net Service Name and click Next. Answer No to the following question and press the button Next:Climatological Database manual Setting connection for clients 16 2003 Net service name configuration completed successfully: Press the button Next to proceed. Net configuration was completed successfully: Click the button Finish.Climatological Database manual Setting connection for clients 17 2003 Choose the button Exit to end the installation and reboot system.Climatological Database manual Setting connection for clients 18 2003 3.1.2. Configuration Follow the instructions below in the case connection to Oracle server was NOT configured during Oracle Client installation. After the Oracle Client software is installed, the connection to Oracle server with CLDB should be established. To perform this task, Oracle Net Configuration Assistant has to be started and new service or connection must be added. The Net Configuration Assistant is usually started automatically after Oracle Client installation. If it has not started automatically, launch it from Programs -> Oracle – OraHome81 -> Configuration and Migration Tools -> Net Configuration Assistant. Net Configuration Assistant is started: Click option Local Net Service Name configuration. Continue with the button Next.Climatological Database manual Setting connection for clients 19 2003 In following dialogue please select Add. In case you want to reconfigure existing connection please select Reconfigure. When question about Oracle database version appears, select Oracle 8i or later database or service:Climatological Database manual Setting connection for clients 20 2003 In the next step specification of the database identification follows. It is always CLDB for the Climatological database. Please type service name cldb.world and confirm by clicking 'Next'. In the next step user specifies the type of connection with database server PC. Please select TCP.Climatological Database manual Setting connection for clients 21 2003 The client needs TCP/IP address of database server. It is specified in the following step, you have to ask your network administrator to get the TCP/IP address of database server (Host Name). Port number should be 1521. Set the numbers and click 'Next'. Now, the configuration is nearly finished. Choose option 'Yes perform a test' to test the newly created connection. Use the button Next to proceed to the next step.Climatological Database manual Setting connection for clients 22 2003 The name and password are required for the connection test. (Use Change Login button.). Use your database login and password: With correct password, the test has to be successful (if connection is working good): Click hereClimatological Database manual Setting connection for clients 23 2003 If the test is successful, type 'CLDB.WORLD' as Net Service Name and click Next. Answer No to the following question and press the button Next:Climatological Database manual Setting connection for clients 24 2003 Net service name configuration was completed successfully: Press the button Next to proceed: Press button Finish to end the Configuration Assistant.Climatological Database manual Setting connection for clients 25 2003 3.2. Configuration of ODBC ODBC (Open Database Connectivity) is an universal mean for data interchange with database servers. It is supported directly in the operation system and also in database servers like Oracle, MS SQL, Sybase, Interbase. Many applications use the drivers to connect to database servers and to get data. Also MS Excel (at least version '95) can require data from database servers using ODBC. The application, which demands data, is obviously installed on client PC1 connected with the server PC through the network (LAN) connection. To establish connection between client PC and server PC, the ODBC drivers must be installed first. Usually, like in case of Oracle 8i Client, these drivers are installed automatically during the installation of the database client software on the PC. Before using software, which gets data using ODBC, the ODBC settings must be properly set by ODBC configuration utility from Control panels. Then new System DSN item must be created using "Add" button, as we can see below. 1 PC with database server should be used for the server applications only, not for the standard office work (e.g. MS Office 2000, Paintbrush,...).Climatological Database manual Setting connection for clients 26 2003 Then the new Oracle ODBC driver connection must be selected and confirmed. The next step is the configuration of the Oracle ODBC according to the following dialog. Now the ODBC connection is prepared and applications can access data from this new ODBC data source. In some cases restart of Windows must be performed before using this connection.Climatological Database manual Setting connection for clients 27 2003 3.3. Configuration of BDE BDE (Borland Database Engine) is a connection utility used to access shared data from database servers. Its function is almost equal to the ODBC. Climatological database uses BDE to connect to Oracle server PC. Therefore BDE must be also configured to run various CLDB applications. Firstly, the BDE administration utility must be started from Start -> Settings ->Control panel. The Native Oracle driver configuration must be changed after start of the utility. The "DLL32" and "VENDOR INIT" items default settings are not sufficient. CLDB uses Oracle 8, therefore, both items must be changed following the next picture.Climatological Database manual Setting connection for clients 28 2003 Firstly, "DLL32" value must be changed to "SQLORA8.DLL" by selecting it from the drop-down list, which is shown after a single mouse click on the value field. Only then the "VENDOR INIT" value can be set to "OCI.DLL". User name "system" has to be typed in the "USER NAME" field.The BDE is now configured properly and applications can access and manage CLDB. 3.4. Users Users accounts can be defined and managed in DB Manager application. Various levels of access rights for different users are supported to assure maximum system security.Climatological Database manual Utilities and applications 29 2003 4. Utilities and applications Applications and utilities can be split to several categories: • maintenance software: DB Manager • data import software: Insert, Import AWOS, Import CLICOM, JpegToDB • data decoding software: Decode • data storage software: Disk Manager, Database Cleaner • data processing and quality control software: QC module • client utilities: Select, Reports, Web CGW 4.1. DB Manager for CLDB The management of the CLDB is performed by the DB Manager application. It is used for performing changes in Climatological database. Examples of ordinary use are changing of station name, adding new variable for observation or updating monthly element extremes table. It archives station properties history and station maintenance. The application consists of four main configuration tab-pages: 1. Stations 2. Variables 3. Regions 4. Users Each page contains tools for creating, changing or deleting items.Climatological Database manual Utilities and applications 30 2003 4.1.1. Stations There are five sub-pages of the Stations tab-page: Settings, History, Observation, Extremes, and Maintenance. Stations - Settings This tab-page is used to add, delete or change stations and their attributes. Firstly, a station must be selected by its name from the drop-down list on in the upper part of the page. Then, Station properties can be edited in the right part of the page:
Station geographic location (latitude, longitude, elevation)
WMO and ICAO identifiers
Other parameters The editable window on the left side of the pane serves for inserting of any descriptive information, which can supplement the station properties. HOW TO change station property 1. Press Archive in History (or Backup) button to save current settings in the History tab. 2. Return to the Settings tab and change the property (e.g. station elevation) 3. Press Save button to save the changes HOW TO rename station 1. Press button Rename in the bottom part of the page 2. Insert new name and press OK 3. Press Save button to save the changesClimatological Database manual Utilities and applications 31 2003 To delete selected station, use button Delete in the bottom part of the page. The station will be deleted with all its properties. To add new station, use button Add in the bottom part of the page. The station will be added and its properties can be inserted in the right side of the page. Stations - History The History tab records changes in station settings. For example, when station was moved, its geographical coordinates change. There are dates of change in the left part of the window. The right part shows settings valid until selected date.Climatological Database manual Utilities and applications 32 2003 Stations - Observation This page describes observation at the station. Each variable observed at the station has the following attributes: Begin End Instrument Height Schema Interval Beginning of observation of particular meteorological element End of observation Observational instrument (sensor) type Height of instrument relative to the station ground (e.g. 2m for standard temperature measurements, 10m for wind measurements etc...) Observational schema: Regular or Irregular Interval of observations in regular schema: 15min, 1hour, 3hours, etc… Regular schema: Time Interval of observation can be chosen: 15 minutes, 1 hour, 3 hours, etc. Irregular schema: One of the irregular schemes is chosen in the Interval field.Climatological Database manual Utilities and applications 33 2003 Stations - Maintenance Helps to keep the maintenance records in order. The window is very simple. Responsible person inserts the date in the left field and notes the maintenance record to the central window. Any text can be typed into the central window. Note: Use Maintenance tab for records, which don't fit into the previous station tab-pages. Good examples are regular cleaning, new surface coating etc. When a change is made that affects observation (new instrument added, sensor shifted or replaced) it is to be noted in the Observation tab.Climatological Database manual Utilities and applications 34 2003 Stations - Extremes Previews extreme values for elements observed at the station. Element (variable) is chosen in the left part of the Extremes tab-page. The extreme values are displayed and administrator can edit them in the right part of the page. Quality Control (QC) checks values inserted for a station against the values in the Extremes tabpage2. Example: The operator (or an automatic process) is inserting Temperature value 11.7 for station 'Abu Dhabi'. Minimum value of air temperature (Temp.Dry.Mean) in November is 12.0 °C in the Extremes tab-page. The value will be considered as bad until one of the following is done: 1. The inserted value 11.7 °C will be corrected (it was a mistake) 2. Administrator will consider the value as correct new extreme – then, he updates the Abu Dhabi November Extremes value to 11.7 and QC will accept the value 2 Variables for which this QC checks will be performed can be chosen in the QC interface. Checks will be performed only for stations with filled-in Extremes table. See QC documentation for more details.Climatological Database manual Utilities and applications 35 2003 4.1.2. Variables The second CLDB Manager tab-page deals with variables and their properties. Variables tab-page is used to add, delete or change variables and their attributes. Firstly, a variable must be selected from the drop-down list on the left side of the page. Then, variable properties can be edited in the right part of the page. Button Save saves the changes. To delete selected variables, use button Delete in the bottom part of the page. The variable will be deleted with all its properties. Note: Deleting of a variable is possible only if NO data were inserted into this variable yet. To add new variable, use button Add in the bottom part of the page. The variable will be added and its properties can be inserted in the right side of the page.Climatological Database manual Utilities and applications 36 2003 Descriptive information on variables – variable properties: Property Example (for Temp.Dry.Mean) Explanation ShortName TEMPDB Short variable name is used e.g. in column header in Manual Data Entry. Must be specified for variables which are inserted through Manual Data Entry. LongName Air Temperature Name of the variable which is displayed for user in several applications (e.g. Select) Description Dry bulb temperature at observation term Long description of variable Unit deg. C Physical unit Basic ColumnLength 3 Number of digits (cursor in Manual Data Entry moves to the next field after insertion of here specified number of digits – increases insertion speed). Must be specified for variables which are inserted through Manual Data Entry. DecimalPlaces 1 Number of variable decimal places. Must be specified for variables which are inserted through Manual Data Entry. *Default TableName VALUES_F Database table in which the variable values are stored. For advanced users and developers only (see note). *Type FLOAT Variable type. For advanced users and developers only (see note). *FORMAT %f Format in which the variables are automatically decoded from meteorological messages. For advanced users and developers only (see note). *PREC s1 Format precision used with variables automatically decoded from meteorological messages. For advanced users and developers only (see note). isClicom Y isIris isManual Y isMetar Y isPilot isAWOS Y isStoziar isSynop Y isTemp These fields are filled either with Y (yes) or are blank. Informs user from which sources the values can be obtained. (However, the REAL value source is indicated in special flag – see description of VALUES_F table) Affects grouping of the variables in menus in almost all CLDB applications. For example, Temp.Dry.Mean values can be found in variable groups: Synop, Metar, AWOS, Clicom, Manual. Advanced isWindSpeed All variables with wind speed values (e.g. Wind.Avg.Speed, Wind.Gust.Speed) have Y (yes) here. Enables displaying of wind variables in knots (internal CLDB unit for wind speed is m/s) *Note: Variables marked by * significantly affect database operations with the variables. ONLY administrator with deep knowledge of the system can change them.Climatological Database manual Utilities and applications 37 2003 4.1.3. Regions The Regions tab-page is used to add and delete regions or modify the number of stations within one region. Firstly, a region must be selected from the drop-down list in the upper side of the page. Then, All stations in database appear on the left side of the page. Stations in region (current state) appear in the right side of the page. To move station from All stations to the region, select the station and click the button labelled '=>'. To remove station from the region, select it in Stations in region and click the button labelled '<='. Buttons '===>>>' and '<<<===' have similar functionality but moving is applied to all listed stations. To add new region, use button Add region in the bottom part of the page. The region will be added and stations can be added to it. Button Save saves the changes.Climatological Database manual Utilities and applications 38 2003 4.1.4. Users privileges Users tab-page enables configuration of users privileges. All user accounts are displayed on the left side of the page. The right side shows privileges of the selected user. Privileges can be added by marking the suitable check box. Privilege Description Recommended for: Connect to climatological database This privilege must be granted to all users who will use CLDB applications. Each CLDB application requires connection login and password when started. Select and read data from tables Data retrieval from CLDB is enabled. E.g. Select application users need this privilege. DATA USER Insert, update and delete data Data can be inserted, corrected or deleted from CLDB with this privilege. In has to be granted for Manual Data Entry application users. OPERATOR Manage meta data User with this privilege can update station history, station observation, variable properties and regions in DB Manager. Administer users and grant privileges Can create new users, set their privileges, change passwords. ADMINISTRATOR Note: Only administrator with privilege 'Administer users and grant privileges' can work with tab Users in DB Manager.Climatological Database manual Utilities and applications 39 2003 4.2. Insert This application decodes messages in various GTS formats, e.g. SYNOP, METAR, TEMP, PILOT, etc. All values obtained from GTS messages are converted to SI-units and stored into climatologic database. Data are retrieved from files of multiple directories. In a typical configuration, there are directories containing incoming SYNOP files, METAR files, and TEMP&PILOT files. Successfully parsed and decoded files are temporary stored in the directory OLD. Files containing errors are moved into ERR directory to be analyzed later. In both cases, successfully decoded values (or parts of GTS messages) are stored in the database only if both station and variable are in the local configuration of CLDB. Values of different variables can be stored in different data tables. The GTS decoder and importer normally works in auto-mode, i.e. there is no user's interactivity. Either this task is scheduled by the system scheduler, or its internal timer regularly scans all data directories and imports found data files. In the manual mode, the Insert button invokes importing procedure, and the Status button shows the number of successfully imported and error data files. The import procedure terminates after certain number of import cycles. The GTS decoder and importer works in cooperation with the quality control module, which checks the correctness of retrieved data. In this case, all one-day data are stored in the UncheckedDataTable. The quality control module checks these one-day data and distributes into predefined data tables (e.g. SYNOP_F, TEMP_F, METAR_S, etc.).Climatological Database manual Utilities and applications 40 2003 4.2.1. Configuration file The configuration file Insert.ini (Al Dhafra installation): ; description of database connection [InsForm->DB] ConnectString = cldb/****@CLDB.WORLD ServerName = CLDB.WORLD UserName = cldb SilentMode = true [Configuration] ; importer time period [in secs] Time = 5000 ; 1 iff AdminMode ... show/hide init [variable/station] buttons AdminMode = 0 ; if AdminMode, then (1 output a .sql file) GenerSQL = 0 ; verbose mode Verbose = 0 ; application heap is checked for the stack-overflow MaxHEAP = 50000 ; number of import cycles MaxCycle = 1 ; 1 iff WHO station indexes are used UseWMOStationIndexes = 0 ; take time/date information from the filename UseFileNameYYYYMM = 0 ; 1 iff quality control module is used UseQualityControl = 1 QualityControlTable = UncheckedDataTable ; internal parameter InsertIntoVariableTableName = 0 ; internal parameter MessageTerminator = -1 ; 1 iff terminate the application after one import cycle TerminateAfter = 1 ; initial delay if TerminateAfter mode StartAfter = 5 [Directories] Number = 3 Directory1 = c:\FromMISS\Data\Synop Directory2 = c:\FromMISS\Data\Temp Directory3 = c:\FromMISS\Data\Metar ; where to move successfully imported files DIROLDIES = OLD ; where to move unsuccessful files DIRERRORS = ERR [FILENAMES] STANICEFILE = Stanice.txt VARIABLES = Var.sql STATIONS = Stat.sql LOGFILE = C:\CLDB\Logs\Import\Import MaxLogLines = 21500 STATUSFILE = C:\CLDB\Status\Import DECODERCONFIGS = C:\CLDB\Insert\DecodeSynop\cfgClimatological Database manual Utilities and applications 41 2003 4.2.2. Log-file The description of the log files of the Insert application: An example of the Insert log file is shown below: 2/26/2002 5:33:08 PM Unable to connect to CLDB 2/26/2002 5:33:08 PM Operation aborted 2/26/2002 5:33:08 PM wrong password 2/13/2002 12:45:07 AM Connected to database CLDB 2/13/2002 12:45:00 AM Insert start 2/13/2002 12:45:07 AM Load variable dictionary 2/13/2002 12:45:08 AM Load station table 2/13/2002 12:45:08 AM AutoMode 2/13/2002 12:45:13 AM Start import cycle: 1 Heap: 0 2/13/2002 12:45:13 AM Import file: c:\FromMISS\Data\Synop\msg3582 2/13/2002 12:45:14 AM Import file: c:\FromMISS\Data\Synop\msg3614 2/13/2002 12:45:22 AM Import file: c:\FromMISS\Data\Synop\msg4095 2/13/2002 12:45:22 AM Import file: c:\FromMISS\Data\Synop\msg1574 2/13/2002 12:45:22 AM Error: DecodeBuletinSynop: SMOM10 OOMS 021800 2/13/2002 12:45:22 AM File msg1574 is overwritten . . . . . . . . . . . . . . . . . . 2/13/2002 12:45:36 AM ---------------------------------------------------- 2/13/2002 12:45:36 AM Files imported from: c:\FromMISS\Data\Synop 36 2/13/2002 12:45:36 AM Import errors (syntax error within GTS message) 1 2/13/2002 12:45:41 AM Import file: c:\FromMISS\Data\Temp\msg6498 . . . . . . . . . . . . . . . . . . 2/13/2002 12:45:42 AM ---------------------------------------------------- 2/13/2002 12:45:42 AM Files imported from: c:\FromMISS\Data\Temp 5 2/13/2002 12:50:33 AM Import file: c:\FromMISS\Data\Metar\msg7212 2/13/2002 12:50:40 AM Import file: c:\FromMISS\Data\Metar\msg7259 . . . . . . . . . . . . . . . . . . 2/13/2002 12:50:42 AM ---------------------------------------------------- 2/13/2002 12:50:42 AM Files imported from: c:\FromMISS\Data\Metar 99 2/13/2002 12:50:42 AM Number of inserted records: 2387 2/13/2002 12:50:42 AM Number of updated records: 322 2/13/2002 12:50:42 AM Total number records: 6747 2/13/2002 12:50:42 AM Stop the importing cycle: 1 2/13/2002 12:50:42 AM Heap: 215024 2/13/2002 12:50:42 AM c:\FromMISS\Data\Synop new: 5 2/13/2002 12:50:42 AM c:\FromMISS\Data\Synop old: 2830 2/13/2002 12:50:42 AM c:\FromMISS\Data\Synop error: 395 2/13/2002 12:50:42 AM c:\FromMISS\Data\Synop total: 8303 kB 2/13/2002 12:50:42 AM c:\FromMISS\Data\Temp new: 0 2/13/2002 12:50:42 AM c:\FromMISS\Data\Temp old: 1857 2/13/2002 12:50:42 AM c:\FromMISS\Data\Temp error: 0 2/13/2002 12:50:42 AM c:\FromMISS\Data\Temp total: 6514 kB 2/13/2002 12:50:42 AM c:\FromMISS\Data\Metar new: 2 2/13/2002 12:50:42 AM c:\FromMISS\Data\Metar old: 3879 2/13/2002 12:50:42 AM c:\FromMISS\Data\Metar error: 269 2/13/2002 12:50:42 AM c:\FromMISS\Data\Metar total: 14439 kB The first example shows connection failure due to typing wrong password in the login dialog. The second example illustrates one importing cycle of GTS data-files from subdirectories SYNOP, TEMP and PILOT of c:\FromMISS\Data. The processing of the SYNOP file msg1574 terminates with a syntactic error, probably because of the corrupted file. All correctly parsed and processed files are moved into OLD subdirectory, and other files into ERR subdirectory. The error message indicates the header of the corrupted GTS message and the position (in bytes) in this input file. A short summary with the number of correctly parsed and syntactically incorrect files is produced for SYNOP, METAR and TEMP subdirectories. After one import cycle a global summary reports the number of inserted, updated and totally parsed records, and the number of new-input, already processed, and corrupted files.Climatological Database manual Utilities and applications 42 2003 4.3. ImportAWOS The application ImportAWOS imports the daily log-files from Qualimetrics AWOS stations into the Climatological database. The application configuration is located in the ImportAWOS.ini file. The .ini file is described in the following section. 4.3.1. Configuration file The variable ConnectString in the section ConnectToDatabase is used for database connection settings. It is necessary to configure the directories which the application is working with – the section Directories: • the input directory from which the files are imported into the database (the variable InputDirectory) • the directory to which the already imported files will be moved (the variable Done) • the directory to which the files with invalid format will be moved (the variable Faulty) • the directory to which the log files will be written (the variable Logs) • the directory to which the status file will be written (the variable Status) The log file contains important information about the processing of the input files by the application. The status file contains the brief information about the application run. By default setting the values which are contained in the input files will be imported into their corresponding tables in the database. It is possible to change this default setting so that all values will be imported into a single table. To achieve this, it is necessarily to change the value of the UseQualityControl variable in the ini file to value 1 and write down the name of the table (the variable QualityControlTable) to which all values will be imported. The section Constants contains the setting of the various constants used in the application. It is recommended not to change these values. Variables in the AWOS log and their counterparts in the database The sections Variable0, Variable1, … Variable19 contain the information about each value located in the input file. The variable Name is set to the corresponding variable name, which name is located in 6th row of the input file (i.e. the log-file). The variable NameDB is set to the name of the variable of the particular database table which corresponds to the value of the variable Name. It is also recommended not to change any of the values in this sections. Example of variables configuration in importAWOS.ini file (Al Dhafra installation): [Variable1] Name = QNH RVR = 8 RVL = 10 NameDB = Press.QNH.Mean … [Variable4] Name = TEMP RVR = 30 RVL = 30 NameDB = Temp.Dry.Mean …Climatological Database manual Utilities and applications 43 2003 4.3.2. Log-file An example of the ImportAWOS log file follows: Each running of the application is recorded in the log file together with the current date and time. The first line always contains the information that the application started and the last line that the application finished. The three lines beneath the first line contain the information that the given tables are reading from the database (see Figure 3.). As the data can be imported into the database from different input directories, each line of the type: ***InputDirectory [name of the directory]*** gives us the information about the processing of the data file from that input directory (see Figure 3.). Each processing of the data file starts with the line like this one: "The processing of the file [name of the file] started." and ends with line like this one: "The file [name of the file] was processed...[error | correct ]". Between these two lines all important information concerning the file processing are located. Such lines always begin with on of the following words: 1. "INFO": This message begins with the name of the processed file followed by the name of the function which is responsible for this message. The body (the rest) of this message says what ID was found for the given station in the table Stations. Here is an example of such message: "INFO-> r200DEC1600.log: function SpracujFile: for the station Al Dhafra the ID 216 was found in the table STATIONS."Climatological Database manual Utilities and applications 44 2003 2. "ERROR": This error message mostly begins with the name of the file, followed by the number of the currently processing line and by the name of the function which is responsible for this message. The body of the message explains what type of error occurred. Here is an example of such message: "ERROR-> r200AUG0401.log, row 5: function NajdiHodnotePremennu: the right position 111 doesn't match any right position of the variables. This row is ignored." This error occurs if the value found in the file can't be bind with any given variable. Thus, this line contains errors and will be ignored - not processed. In some cases the message begins with the name of the file followed by the function name. Here are the examples of such messages: "ERROR-> r200DEC1600.log: function ConnectDB: ORA-12545: Connect failed because target host or object does not exist" This error occurs if the database cannot be contacted. More information about this error can be found in the Oracle Documentation Library according to "ORA-12545". "ERROR-> r200DEC1600.log : function SpracujFile: the row containing the names of the variables is missing." This error occurs if the given file doesn't contain the line with the names of the variables. And in some cases the message begins only with the function name. Here are some examples: "ERROR->function ConnectDB: ORA-01017: invalid username/password; logon denied" This error occurs if invalid username or password was specified in order to connect to the database. More information about this error can be found in the Oracle Documentation Library according to "ORA-01017". "ERROR->function PresunDoDone: moving of the file r200DEC1600.log into the directory OLD failed." This error occurs if moving of the particular file into the OLD directory failed. 3. "CHECK": Lines that begins with this word contain the information regarding the results of the check of the file. These are not such errors as the previous messages beginning with the word ERROR. These messages mostly begin with the name of the file, followed by the row number and the body of the message. Here is an example: "CHECK: r200APR0101.log, row 10->The value of the variable VISIB, tot, is missing." This message occurs if the line number 10 doesn't contain the values of the VISIB and tot variables. The check message can also start with the name of the file followed by "(Daily Report part)", for example: "CHECK: r200APR0101.log: (Daily Report part): the value of the Maximum Air Temperature is Missing" This message occurs if in the Daily Report part the value of the variable Maximum Air Temperature is missing 4. "CHECK_ERROR": These messages inform about errors that were found during the check of the file. The message begins with the words "(Daily Report part)" and is followed by the fileClimatological Database manual Utilities and applications 45 2003 name and body of the message which contains the description of the error. Here is an example: "CHECK_ERROR: (Daily Report part) file r200APR0101.log: the Maximum Air Temperature - the time has invalid format." This message occurs if time doesn't consist of four digits. 5. "CHECK_INSERT": These messages give the information about the check of variables Minimum Air Temperature and Maximum Air Temperature which are inserted into the database from the daily report part of the file. Here is an example of such message: "CHECK_INSERT: r200APR1201.log: (Daily Report part) file: the daily Minimum Air Temperature is > than hourly minimum Air Temperature", This message occurs if that inequality holds. The daily Minimum Air Temperature is the corresponding value from the daily Report part and hourly minimum Air Temperature corresponds to the TEMP Variable from the hourly report part. 6. "CHECK_INSERTED": This message type is associated with the CHECK_INSERT message type. It contains the full information about the inserted values of the Minimum Air Temperature and Maximum Air Temperature variables into the database from the daily report part. This message looks like this: "CHECK_INSERTED: (Daily Report part) and Minimum Air Temperature – TableName=UncheckedDataTable MeasTime=04/12/01 23:58:00 StationID=216 VarID=24 Value=16.9 Source=A Status=0" 7. "KEEP_FILE": This message gives only information that the processing of the given file is stopped (postponed), because the file seems to be not complete. It will be processed in the next time. "KEEP_FILE: The file r200JAN2102.log is postponed"Climatological Database manual Utilities and applications 46 2003 4.4. ImportClicom The application ImportClicom imports the text files exported by CLICOM into the Climatological database. These files can be imported automatically or manually. 4.4.1. Configuration file The application configuration is located in the ImportClicom.ini file. The variable CONNECTSTRING in the section DB is used for setting the connection to the database. The variable AUTOINSERT is used for switching between automatic (the value should be equal to 1) or manual (the value should be equal to 0) insertion of the files into the database. It is possible to select in manual mode which files will be imported into the database. The checkboxes in front of the file names are used for files selection. All files from the input directory (see READIRNAME configuration later) will be imported when ImportClicom is working in automatic mode. In both cases (in manual or automatic) it is necessary to configure the directories which the application is working with – the section DIRS: • the input directory from which the files are imported into the database (the variable READDIRNAME) • the directory to which the already imported files will be moved (the variable OLDDIRNAME) • the directory to which the files with invalid format will be moved (the variable ERRORDIRNAME) • the directory to which the log files will be written (the variable LOGDIRNAME) • the direcotory to which the status file will be written (the variable STATUSDIRNAME)Climatological Database manual Utilities and applications 47 2003 Contents of the DIRS section: [DIRS] READDIRNAME=c:\cldb\Data\Clicom LOGDIRNAME=C:\cldb\Logs\ImportClicom STATUSDIRNAME=C:\cldb\Status\ImportClicom OLDDIRNAME=OLD ERRORDIRNAME=ERR The log file contains important information about the processing of the input files by the application. The status file contains the brief information about the application run. The value of the UseQualityControl variable in the .ini file is set to 1. All values will be imported into a single table called UncheckedDataTable. The name of the single table is specified in the .ini file variable QualityControlTable. The values are inserted into CLICOM_F and CLICOM_S database tables after they pass the quality control procedures. CLICOM stations and variables The section STATIONNAMES contains the stations names and their codes in CLICOM files. The section CODENAMES contains the numerical codes of the CLICOM variables and corresponding names of the variables in CLDB. It is recommended not to change these sections. Contents of the CODENAMES section: [CODENAMES] 101=Temp.Dry.Mean 102=Temp.Wet.Mean 103=Temp.DewPoint.Mean 105=RelHumidity.Mean 106=Press.QFE.Mean 110=Visib.Mean 111=Wind.Avg.Speed 112=Wind.Avg.Dir 113=Wind.Mileage 114=Clouds.Amount 116=Clouds.1.Amount 120=Clouds.2.Amount 124=Clouds.3.Amount 128=Clouds.4.Amount 166=Vapour.Press.Mean 167=Weather 4.4.2. Log-file An example of the ImportClicom.log file follows: 2002-02-27 14:16:48 ****** ImportClicom application started ****** 2002-02-27 14:17:02 Inserting from file 'c:\cldb\Data\Clicom\ZFZR0.TXT' 2002-02-27 14:17:02 ERROR->function Insert: Invalid data in file 'c:\cldb\Data\Clicom\ZFZR0.TXT' at line 1, '199#' is not a valid integer value 2002-02-27 14:17:08 ****** ImportClicom application finished ****** Each running of the application is recorded in the log file together with the current date and time. The first line always contains the information that the application started and the last line that the application finished (see Figure 4.). The lines between them contain information about processing of the files from the input directory. Each processing of the file starts with the line like this one: ""Inserting from file ' [name of the file]'". Under this line all errors concerning the file processing are located. Such error lines always begin with the word "ERROR" that is followed by the name of the function where the error occurred. The rest of the line contains the short description of the error alone. Here are some examples of the error messages:Climatological Database manual Utilities and applications 48 2003 "ERROR->function LoadIni: no input parameter was given." This error can occur when no input parameter was given regarding the connection to the database and the application is running in the auto-insert mode (without user interaction). "ERROR->function LoadIni: Invalid Database settings or connection failed - OCI_ERROR ORA- 01017: invalid username/password; logon denied" This error can occur if invalid username or password was specified in order to connect to the database. More information about this error can be found in the Oracle Documentation Library according to "ORA-01017". "ERROR->function Insert: Invalid data in file 'c:\cldb\Data\Clicom\ZFZR0.TXT' at line 1, '199#' is not a valid integer value" This error can occur if some invalid data are found in the file. In this case it was the year (199#) that should consist of four digits (for example 1999 in this case).Climatological Database manual Utilities and applications 49 2003 4.5. JpegToDB The application JpegToDB imports the satellite and radar images (Hrpt,Pdus,Radar) into the Climatological database. 4.5.1. Configuration file The application configuration is located in the JpegToDB.ini file. The variable ConnectString in the section ConnectToDatabase of the .ini file is used for connecting to the database. It is necessary to configure the directories which the application is working with – the section Directories: • the input directory for Hrpt images (the variable DIR1NAME) • the input directory for Pdus images (the variable DIR2NAME) • the input directory for Radar images (the variable DIR3NAME) • the directory to which the already imported images will be moved (the variable Done) • the directory to which the log files will be written (the variable Logs) • the direcotory to which the status file will be written (the variable Status) Contents of the Directories section: [Directories] DIR1NAME=C:\FromMISS\Images\Hrpt DIR2NAME=C:\FromMISS\Images\Pdus DIR3NAME=C:\FromMISS\Images\Radar Done = C:\FromMISS\Images\OLD Logs = C:\FromMISS\Images Status = C:\FromMISS\Images The log file contains important information about the processing of the input files by the application. The status file contains the brief information about the application run.Climatological Database manual Utilities and applications 50 2003 4.5.2. Log-file An example of the JpegToDB log file is shown below: 2002-02-27 14:13:57 ****** JpegToDB application started ****** 2002-02-27 14:13:58 The Table IMAGEPRODUCTS is being processed.... 2002-02-27 14:13:58 2002-02-27 14:13:58 ***InputDirectory C:\FromMISS\Images\Hrpt*** 2002-02-27 14:13:58 2002-02-27 14:13:58 The processing of the file X_5_200107150317kn.JPG started. 2002-02-27 14:13:58 ERROR-> X_5_200107150317kn.JPG: function VratTimeID_H: the HRPT file name is not beginning with the letter H. 2002-02-27 14:13:58 The file X_5_200107150317kn.JPG was processed...error 2002-02-27 14:13:58 2002-02-27 14:13:58 ***InputDirectory C:\FromMISS\Images\Pdus*** 2002-02-27 14:13:58 2002-02-27 14:13:58 2002-02-27 14:13:58 ***InputDirectory C:\FromMISS\Images\Radar*** 2002-02-27 14:13:58 2002-02-27 14:13:58 The processing of the file 01100606389d.prd.GIF started. 2002-02-27 14:14:00 The file 01100606389d.prd.GIF was processed...correct 2002-02-27 14:14:00 ****** JpegToDB application finished ****** Each running of the application is recorded in the log file together with the current date and time. The first line always contains the information that the application started and the last line that the application finished. The line just below the first line contains the information that the table ImageProducts is reading from the database (see Figure 2.). As the images are imported into the database from three different input directories, each line of the type: ***InputDirectory [name of the directory]*** gives us the information about the processing of the images from that input directory (see Figure 2.). Each processing of the image file starts with the line like this one: "The processing of the file [name of the file] started." and ends with line like this one: "The file [name of the file] was processed...[error | correct]". If there occurred some error during the processing of that file, it is recorded between these two lines. Such messages always begin with the word "ERROR" that is followed by the name of the image file and function where the error occurred. The rest of the message contains the short description of the error alone. The error messages can look like these: 1. "ERROR-> X_5_200107150317kn.JPG: function ConnectDB: ORA-01017: invalid username/password; logon denied" This error occurs if invalid username or password was specified in order to connect to the database. More information about this error can be found in the Oracle Documentation Library according to "ORA-01017". or 2. " ERROR-> X_5_200107150317kn.JPG: function VratTimeID_H: the HRPT file name is not beginning with the letter H." This error occurs if the file name X_5_200107150317kn.JPG has invalid format (see Figure 2.). It should begin with the letter "H" as it is the file from the HRPT input directory. In the Figure 2. you can also observe that under the line " ***InputDirectory C:\FromMISS\Images\Pdus*** " there are no lines showing the information about the processing of the image files. The reason is that this input directory was empty.Climatological Database manual Utilities and applications 51 2003 4.6. Disk manager (DiskMngr) This application manages the disk space on multiple disk drives and multiple directories. 4.6.1. Configuration file The configuration file DiskMngr.ini allows specifying disk drives, where free space is regularly checked. The configuration file specifies all tested directories. If the current size of a directory (recursively counted) exceeds the TOTALMAX value of this directory, a cleaning procedure is launched. The cleaning removes either all files larger than the FILEMAX value, or older than HISTORY value (in days). Information about the current disk situation can be sent via e-mail to the technical support. The user confirmation of file cleaning can be switched on, when important files could be lost. When current size of a directory (recursively counted) exceeds the TOTALMAX value of this directory: send an e-mail warning to technical support remove files larger than FILEMAX remove files older than HISTORY prompt user before file deletion. Currently configured e-mail address to technical support: [email protected] Database manual Utilities and applications 52 2003 4.6.2. Log-file The description of the log files of the DiskMngr application follows. An example of the DiskMngr log file is shown below: 2/13/2002 4:05:05 AM : 45 percent disk space C is free: 1844864 kB = 1801 MB. 2/13/2002 4:05:05 AM : 100 percent disk space D is free: 4194303 kB = 4095 MB. 2/13/2002 4:05:05 AM : Directory C:\CLDB\Logs contains 19 MB 2/13/2002 4:05:05 AM : Directory C:\CLDB\Status contains 0 MB 2/13/2002 4:05:05 AM : Directory c:\FromMISS\Data\Synop\OLD contains 8 MB 2/13/2002 4:05:05 AM : !!! Directory c:\FromMISS\Data\Synop\OLD exceeds limit 3 MB !!! 2/13/2002 4:05:09 AM : 1858 files removed from c:\FromMISS\Data\Synop\OLD 2/13/2002 4:05:09 AM : Directory c:\FromMISS\Data\Metar\OLD contains 14 MB 2/13/2002 4:05:09 AM : !!! Directory c:\FromMISS\Data\Metar\OLD exceeds limit 4 MB !!! 2/13/2002 4:05:12 AM : 1603 files removed from c:\FromMISS\Data\Metar\OLD 2/13/2002 4:05:12 AM : Directory c:\FromMISS\Data\Temp\OLD contains 6 MB 2/13/2002 4:05:12 AM : Directory c:\FromMISS\Data\Synop\ERR contains 1 MB 2/13/2002 4:05:12 AM : Directory c:\FromMISS\Data\Metar\ERR contains 1 MB 2/13/2002 4:05:12 AM : Directory c:\FromMISS\Data\Temp\ERR contains 0 MB 2/13/2002 4:05:12 AM : Directory c:\FromMISS\Images\OLD contains 17 MB 2/13/2002 4:05:12 AM : !!! Directory c:\FromMISS\Images\OLD exceeds limit 7 MB !!! 2/13/2002 4:05:12 AM : 128 files removed from c:\FromMISS\Images\OLD The first two lines inform about free disk space on disks. Then, all directories described in the ini-file are consequently checked. If the directory size exceeds its limit, a cleaning procedure is launched and the log-file contains information about number of deleted files.Climatological Database manual Utilities and applications 53 2003 4.7. Database cleaner (DBCleaner) This application removes expired records from multiple database tables. For each table, an expiration time (in days) is specified. Records older than the expiration time are regularly removed. The cleaning procedure is applied only if the table contains certain amount of records. This condition allows to archive last records even if the whole application is stopped longer than the expiration time (from any technical reason). The name of date-time field used for the expiration test is specified in the configuration file. 4.7.1. Configuration file Definitions of expiration limits in Cleaner.ini configuration file: ; description of tables [TABLES] Tables = 1 Table1 = IMAGES TableMin1 = 1000 ArchTime1 = 92 DTField1 = MEASTIME 4.7.2. Log-file The description of the log files of the DBCleaner application: An example of the DBCleaner log file is shown in the Figure3. Inicialization Database CLDB connected Table IMAGES contains 9889 records Cleaning data older than 11/13/2001 4:15:12 AM from IMAGES [8] Table InvalidDataTable contains 3989 records Cleaning data older than 11/13/2001 4:15:13 AM from InvalidDataTable [0] Cleaning terminated This example illustrates cleaning of two data tables, IMAGES and InvalidDataTable, whereas records in both tables expire after 3 months. Expiration of records is disabled if the total number of records is less than a certain table limit.Climatological Database manual Utilities and applications 54 2003 4.8. Quality control (QC) The quality control module checks correctness of one-day data. These data are temporary stored in a separate data table called UncheckedDataTable. The process of quality control is launched once-aday, when all one-day data are already collected. This procedure checks the correctness of retrieved data with respect to different types of quality rules, and distributes correct data to data tables depending variables. It selects out the wrong and/or suspicious data into a table InvalidDataTable. These invalidated data must be manually corrected and consequently validated by the quality control procedure. Data not passed by this verification procedure can not be stored in valid data tables. There are several types of CLICOM-like quality control rules: • Global Limit Check, • Global Rate of Change, • Related Element Global Limits, • Related Element on Previous Line, • Related Element Same Line, • Special Values, • etc. The first panel "Rules" enables to edit the set of quality control rules. The buttons "Add" and "Modify" refers the rule specification in the bottom part of the panel. The button "Remove" removes the focused rule. The button "Check" forces the quality control procedure using the focused quality rule. The buttons "Export" and "Import" allows to store and restore certain rules. The bottom part of this panel provides two buttons for filtering rules. The panel "Check" displays currently unchecked data, and provides similar filtering facilities.Climatological Database manual Utilities and applications 55 2003 The panel "Monitor" displays both system messages, warnings, errors, and the progress during the quality control procedure. The number of invalidated data records and elapsed time of each procedure are displayed. This procedure can be stopped by the button "Cancel". Two progress bars show the progress within one quality control rule.Climatological Database manual Utilities and applications 56 2003 Two panels "Errors" and "Valid" display incorrect records and successfully passed records. The bottom parts of these panels enables select records in a certain time period, having values in a range, by a station, or by a variable name. The filters can be composed together using "and", "or" and "not" logical operators. Finally, filtered records can be sorted by several field names.Climatological Database manual Utilities and applications 57 2003 4.8.1. Configuration file The configuration file QC.ini (Al Dhafra installation): ; BDE configuration parameters to database [QCForm->DB] DatabaseName = CLDB AliasName= DriverName = ORACLE Params0=SERVER NAME=CLDB.WORLD Params1=USER NAME=CLDB Params2=NET PROTOCOL=TNS Params3=OPEN MODE=READ/WRITE Params4=SCHEMA CACHE SIZE=8 Params5=LANGDRIVER= Params6=SQLQRYMODE= Params7=SQLPASSTHRU MODE=SHARED AUTOCOMMIT Params8=SCHEMA CACHE TIME=-1 Params9=MAX ROWS=-1 Params10=BATCH COUNT=200 Params11=ENABLE SCHEMA CACHE=FALSE Params12=SCHEMA CACHE DIR= Params13=ENABLE BCD=FALSE Params14=ENABLE INTEGERS=FALSE Params15=LIST SYNONYMS=NONE Params16=ROWSET SIZE=20 Params17=BLOBS TO CACHE=64 Params18=BLOB SIZE=32 Params19=OBJECT MODE=TRUE Params20=PASSWORD=manager ; NCOCI configuration parameters to database [QCForm->OCIDB] ConnectString = cldb/[email protected] ServerName = CLDB.WORLD UserName=cldb SilentMode=true ; 1 iff run stand alone mode StandAlone = 1 ; Delay for StandAlone mode [in secs] StandAloneDelay = 30 ; the sequence of quality contrlo commands COMMAND1 = CHECK COMMAND2 = COMMIT COMMAND3 = ARCHIVE COMMAND4 = INVALIDATE COMMAND5 = REPORT [FILENAMES] LOGFILE = C:\CLDB\Logs\QC\QC MaxLogLines = 21500 STATUSFILE = C:\CLDB\Status\QC\QC ; default directory for export/import quality rules DEFRULEDIR = C:\CLDB\QC\Rules 4.9. Log-file The description of the log files of the quality control application can be found in this section. An example of the QC log file is shown below [shortened]: 2/13/2002 4:20:01 AM QC start at 2/13/2002 4:20:01 AM 2/13/2002 4:20:52 AM PRECALC 2/13/2002 4:20:52 AM Precalc Vapour presure started 2/13/2002 4:20:59 AM Precalc Vapour presure finishedClimatological Database manual Utilities and applications 58 2003 2/13/2002 4:20:59 AM Precalc Relative Humidity started 2/13/2002 4:21:06 AM Precalc Relative Humidity finished 2/13/2002 4:21:06 AM Precalc completed 2/13/2002 4:21:08 AM CHECK 2/13/2002 4:21:08 AM Quality control start 2/13/2002 4:23:04 AM ... rule 3041 errors: 0 time: 1 2/13/2002 4:23:06 AM ... rule 81 errors: 2 time: 1 2/13/2002 4:23:08 AM ... rule 7241 errors: 0 time: 1 2/13/2002 4:23:09 AM ... rule 91 errors: 0 time: 1 2/13/2002 4:23:11 AM ... rule 111 errors: 0 time: 1 2/13/2002 4:23:13 AM ... rule 712 errors: 30 time: 1 2/13/2002 4:25:48 AM ... rule 7561 errors: 1 time: 1 2/13/2002 4:26:04 AM ... rule 3501 errors: 3 time: 1 2/13/2002 4:27:37 AM ... rule 3121 errors: 1 time: 1 2/13/2002 4:27:39 AM ... rule 911 errors: 1 time: 1 2/13/2002 4:27:40 AM ... rule 7471 errors: 0 time: 1 2/13/2002 4:27:42 AM ... rule 891 errors: 0 time: 1 2/13/2002 4:31:03 AM ... rule 3042 errors: 0 time: 2 2/13/2002 4:31:25 AM ... rule 72 errors: 4 time: 22 2/13/2002 4:31:30 AM ... rule 52 errors: 16 time: 4 2/13/2002 4:31:32 AM ... rule 83 errors: 0 time: 2 2/13/2002 4:31:34 AM ... rule 7633 errors: 0 time: 2 2/13/2002 4:31:37 AM ... rule 93 errors: 0 time: 3 2/13/2002 4:31:40 AM ... rule 73 errors: 0 time: 3 2/13/2002 4:31:44 AM ... rule 63 errors: 0 time: 3 2/13/2002 4:31:47 AM ... rule 53 errors: 6 time: 3 2/13/2002 4:31:49 AM ... rule 3083 errors: 0 time: 1 2/13/2002 4:31:49 AM Quality control stop 2/13/2002 4:31:52 AM COMMIT 2/13/2002 4:31:56 AM COMMIT completed 2/13/2002 4:31:59 AM ARCHIVE 2/13/2002 4:32:00 AM Unable to move records of the variable 1 to the table SYNOP_F 2/13/2002 4:32:11 AM 1078 records moved, 22 records updated in the table SYNOP_F 2/13/2002 4:32:11 AM Unable to move records of the variable 2 to the table SYNOP_F 2/13/2002 4:32:37 AM 3310 records moved, 8 records updated in the table SYNOP_F 2/13/2002 4:32:37 AM Unable to move records of the variable 3 to the table SYNOP_F 2/13/2002 4:36:58 AM Values of the variable 25 moved to the table SYNOP_F 2/13/2002 4:36:59 AM Values of the variable 26 moved to the table SYNOP_F 2/13/2002 4:39:34 AM Values of the variable 104 moved to the table PILOT_F 2/13/2002 4:50:13 AM ARCHIVE completed 2/13/2002 4:50:14 AM INVALIDATE 2/13/2002 4:50:15 AM Unable to make a block-move to the tableInvalidDataTable 2/13/2002 4:50:16 AM Unable to move records of the variable 5 to the table InvalidDataTable 2/13/2002 4:50:17 AM 10 records moved, 0 records updated in the table InvalidDataTable 2/13/2002 4:50:19 AM Values of the variable 6 moved to the table InvalidDataTable 2/13/2002 4:50:20 AM Values of the variable 7 moved to the table InvalidDataTable 2/13/2002 4:50:30 AM INVALIDATE completed 2/13/2002 4:50:31 AM REPORT 2/13/2002 4:50:31 AM QC stop at 2/13/2002 4:50:31 AM 2/13/2002 9:20:17 AM QC start at 2/13/2002 9:20:17 AM 2/13/2002 9:20:57 AM QC stop at 2/13/2002 9:20:57 AM Before any application of all quality control rules, the pre-calculation of depending values is performed. In the example Figure 4, there are two pre-calculation routines, for vapour pressure and the relative humidity. After this pre-calculation phase PRECALC, all quality control rules are applied, and for any quality control rule its index, the number of produced errors and the elapsed time are referred. This is indicated ad the phase CHECK. In this moment, all values of UncheckedDataTable are determined to be moved either to archive tables, or to invalid data tables. The next two phases ARCHIVE and INVALIDATE illustrate these replacements of values to archive data tables and to the invalidated data table. Finally, a error report of the quality control process is generated.Climatological Database manual Utilities and applications 59 2003 4.10. ImageViewer The application ImageViewer allows users to select and display images stored in the Climatological database CLDB. 4.10.1. Configuration file The application configuration is located in the ImageViewer.ini file. The variable ConnectString in the section ConnectToDatabase of the .ini file is used for connecting to the database. It is necessary to configure the directory to which the log files will be written (the section Directories, the variable Logs). The log file contains important information about the application run. No additional settings are necessary for the ImageViewer application. The images selection, displaying and sequences playing is fully controlled by the user in main application window. 4.10.2. Log-file The description of the log files of the ImageViewer application can be found in this section. An example of the ImageViewer log file follows: 2002-02-27 14:08:08 ****** JpegViewer application started ****** 2002-02-27 14:08:10 ERROR->function ConnectDB: ORA-01017: invalid username/password; logon denied 2002-02-27 14:08:10 ****** JpegToDB application finished ****** Each running of the application is recorded in the log file together with the current date and time. The first line always contains the information that the application started and the last line that the application finished. The lines between them contain the description of the errors that occurred during the running of the application. These lines always begin with the word "ERROR" that is followed by the name of the function where the error occurred. The rest of the line contains the short description of the error alone. Here is an example of the error: "ERROR->function ConnectDB: ORA-01017: invalid username/password; logon denied" (see Figure 1). This error can occur if invalid username or password was specified in order to connect to the database. More information about this error can be found in the Oracle Documentation Library according to "ORA-01017".Climatological Database manual Utilities and applications 60 2003 4.11. Index Rebuilder The IndexRebuilder application is fully automatic and needs no man interaction or configuration. It rebuilds indexes in the database. Rebuilding of indexes improves the performance of database regularly. 4.11.1. Log-file The description of the log files of the IndexRebuilder application can be found in this section. An example of the IndexRebuilder log file follows: 2002-04-04 14:08:26 ----------------------------- 2002-04-04 14:08:26 indexRebuilder application starts... 2002-04-04 14:08:39 Rebuild button clicked 2002-04-04 14:08:40 REGIONS_UK31013411817036 rebuilt succesfull 2002-04-04 14:08:40 STATIONS_UK21013411833520 rebuilt succesfull 2002-04-04 14:09:31 SYS_C001008 rebuilt succesfull 2002-04-04 14:09:31 SYS_C001015 rebuilt succesfull 2002-04-04 14:09:56 SYS_C001029 rebuilt succesfull 2002-04-04 14:09:56 SYS_C001036 rebuilt succesfull 2002-04-04 14:10:12 SYS_C001050 rebuilt succesfull 2002-04-04 14:11:34 SYS_C001057 rebuilt succesfull 2002-04-04 14:11:35 SYS_C001064 rebuilt succesfull 2002-04-04 14:11:35 SYS_C001071 rebuilt succesfull 2002-04-04 14:11:53 SYS_C001078 rebuilt succesfull 2002-04-04 14:11:53 SYS_C001085 rebuilt succesfull 2002-04-04 14:11:53 SYS_C001089 rebuilt succesfull 2002-04-04 14:12:26 indexRebuilder application ends... 2002-04-04 14:12:26 ----------------------------- Each running of the application is recorded in the log file together with the current date and time. The first line always contains the information that the application started and the last line that the application finished. The lines between them contain list of successfully rebuild indexes during application run.Climatological Database manual Developers information 61 2003 5. Developers information In this chapter, administrators and developers can find useful information about Climatological Database architecture, data structures etc. It is also inevitable to know the database structure when creating enhanced custom queries. 5.1. Overview of Climatological Database Structure Climatological Database is Open Architecture Database, so any 3rd party developers can access it and develop applications for Climatological Database. There are the following basic categories of tables in Climatological Database: • stations tables • variables tables • data tables For data handling the following tables are created: • regions tables • quality control rulesClimatological Database manual Developers information 62 2003 5.1.1. Stations Every station can have many properties like name, longitude, address… These properties differ form station to station; count of station's properties can differ too. Database is designed to reflect all these requirements. Information about stations is distributed in 3 different tables: • stations – basic information about station: o unique id o name o description • stationprops – includes all available station's properties and information about properties. The stations are not required to have any property o unique id o name o description • stationpropvalues – includes information about property values for stations. The number of properties depends on the given station o station id o station property id o property valueClimatological Database manual Developers information 63 2003 5.1.2. Regions There can be a large number of stations configured in Climatological Database. In such cases stations are grouped into regions (e.g. one country can be held as one region). This makes database easier to use and user can browse through data faster. Information about stations is distributed in 3 different tables: • stations – basic information about station: o unique id o name o description • regions – includes all available station's properties and information about properties. The stations are not required to have any property o unique id o name o description • regionstable – assigns a region to a station o station id o region idClimatological Database manual Developers information 64 2003 5.1.3. Variables Variables can have many properties like name, unit… These properties differ form variable to variable, count of variable's properties can differ too. The amount of data can be very large for some variables so it is recommended to keep some variables or groups of variables in separate tables. The value of property 'DefaultTableName' is name of the table, where variable is stored. Information about variables are distributed in 4 different tables: • variables – basic information about variable o unique id o name o description • varprops – includes all available variable's properties and information about properties. The variables are not required to have any property o unique id o name o description • varpropvalues – includes information about property values for variables. The number of properties depends on the given variable. o station id o station property id o property valueClimatological Database manual Developers information 65 2003 5.1.4. Data There can be a lot of data tables in database - it depends on designers how to split variables into tables. Two types of values can be assigned to the data: • number – includes any floating point and integer • string – includes all other types There is a convention in naming of tables - numeric tables have name ended by _F (for example VALUES_F or SYNOP_F) and string tables end with _S (for example VALUES_S or SYNOP_S). Every table stores the following information: • measurement time • station id • variable id • measured value • options • sourceClimatological Database manual Developers information 66 2003 5.1.5. Quality Control Rules The following tables are of special use. They are designed to store a set of quality control rules for each variable in database. The table 'qcrules' contains the rules. The tables 'qcvariables' and 'qcvariables_1' contain variables which a particular rule is applied to. The tables 'relops' and 'relops_1' contain relation operators (=, >, <, …) which define relations that must be fulfilled in quality checks. Finally, values value1 and value2 define limits for variables. The set of quality control rules is supposed to be managed and changed by the QC application. See the 'Data Quality Control' manual for more information.Climatological Database manual Developers information 67 2003 5.2. Tables and views in Climatological database 5.2.1. Tables dependencies Most tables refer only to id from other tables. Id is defined in primary table and referenced in all others. Like station id is defined in Stations table and referenced in other tables, variable id is defined in Variables table and referenced in other tables. 5.2.2. Tables description A short description of basic tables and fields in them is given in next paragraphs. All Climatological Database tables are in table space CLDB. 5.2.3. VALUES_F table This is the core table in the climatological database architecture. The table contains values obtained from meteorological data sources. Temperature, Dew point, Wind, Pressure, Rainfall and other values from all stations are store in the table. List of all stored variables can be found in Appendix II: Variables. Column name Datatype Size Scale Nulls MeasTime DATE NO StationID NUMBER 4 0 NO VarID NUMBER 4 0 NO Value NUMBER 10 4 NO Source CHAR 1 Options CHAR 3 UserID NUMBER 4 Timestamp DATE Description of the VALUES_F table fields: MeasTime date and time StationID station of origin of the value (its ID) VarID meteorological variable (its ID) Value value of meteorological variable Source type of the meteorological message from which the value was decoded or other value origin A AWOS E Evaluated (derived variables) S SYNOP L Manually Entered M METAR C Imported from CLICOM database T TEMP P PILOT Options field is filled in if the meteorological message was retarded (RRA, RRB…), corrected (CCA, CCB, …) or it was an amendment (AAA, AAB, …) UserID identification of user who entered the value (valid only with source L, E) TimeStamp for dual databases with replication only; indicates date of last changeClimatological Database manual Developers information 68 2003 5.2.4. Variables table The Variables table contains IDs and names of all stored variables. List of all stored variables can be found in Appendix II: Variables. Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO Name VARCHAR 64 NO Description VARCHAR 64 5.2.5. Stations table The Stations table contains IDs and names of all stations from which data are stored in the database. Complete list of stations can be found in Appendix III: Stations. Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO Name VARCHAR 64 NO Description VARCHAR 64 5.2.6. VarProps table The table contains unique variable property id, CLDB property name and brief property description for developers. Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO Name VARCHAR 64 NO Description VARCHAR 64 5.2.7. StationProps table The table contains unique station property id, CLDB property name and brief property description for developers. Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO Name VARCHAR 64 NO Description VARCHAR 64 5.2.8. VarPropValues table The VarPropValues table contains variable id (ID), variable property id (PropID) and property value (Value). Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO PropID NUMBER 4 0 NO Value VARCHAR 250 NOClimatological Database manual Developers information 69 2003 5.2.9. StationPropValues table The StationPropValues table stores station properties such as station WMO identifier, geographical location, station elevation etc. Each row of the table contains station id (ID), property id (PropID) and property value (Value). Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO PropID NUMBER 4 0 NO Value VARCHAR 250 NO 5.2.10. VariableTable table The VariableTable table contains station id, variable id and table name for variable storage. Column name Datatype Size Scale Nulls ID NUMBER 4 0 NO PropID NUMBER 4 0 NO Value VARCHAR 32 NO 5.2.11. viewStationPropValues view The view viewStationPropValues is created as the following selection: AS select StationPropValues.ID, Stations.Name as StationName, PropID, StationProps.Name as PropName, Value from CLDB.StationPropValues, CLDB.StationProps, CLDB.Stations where PropID=StationProps.ID and StationPropValues.ID=Stations.ID; 5.2.12. viewVarPropValues view The view viewVarPropValues is created as the following selection: AS select VarPropValues.ID, Variables.Name as VarName, PropID, VarProps.Name as PropName, Value from CLDB.VarPropValues, CLDB.VarProps, CLDB.Variables where PropID=VarProps.ID and varPropValues.ID=Variables.ID; 5.2.13. viewStationVars view The view viewStationVars is created as the following selection: AS select StationID, Stations.Name as StationName, VarID, Variables.Name as VarName, TableName from CLDB.VariableTable,Climatological Database manual Developers information 70 2003 CLDB.Stations, CLDB.Variables where StationID=Stations.ID and VarID=Variables.ID;Climatological Database manual Developers information 71 2003 5.3. Excel examples CLDB Select application can produce SQL statements and place it into clipboard. Microsoft Excel can use SQL statements to retrieve data from database servers through ODBC. The examples describing how to retrieve data directly from CLDB to Excel can be found in this section. Firstly, an SQL statement should be prepared. To obtain an SQL command the 'SQL Text' tab-panel in the Select application can be used. User can also create his own SQL statements (an intermediate computer user can create custom SQL statements after short training). Using Excel ODBC connection for the first time When retrieving data from CLDB to Excel for the first time, a connection between CLDB and Excel must be configured. From Excel main menu choose 'Data -> Get External Data -> New Database Query…'. Dialog 'Choose Data Source' appears:Climatological Database manual Developers information 72 2003 Select item and press OK. Finally, a configuration dialog 'Create New Data Source' appears. In the dialog, fill in 1. Data source name (for example 'cldb') 2. Driver for database connection. In our case 'Oracle ODBC Driver' must be chosen and press the button Connect: After pressing Connect the Excel connects the database. Service name, Login name and Password are required:Climatological Database manual Developers information 73 2003 After the database is connected successfully, user is returned back to 'Create New Data Source' dialog. Pressing OK returns one more step back – to the dialog 'Choose Data Source'. If 'cldb' can be found in the list of data sources, the connection to CLDB was successfully configured. Executing SQL command in Excel Assuming that ODBC connection with name 'cldb' is configured (see previous paragraph), choose 'Data -> Get External Data -> New Database Query…' from Excel main menu. Then, choose data source 'cldb' in the list of databases and press OK. A query wizard appears: Press Cancel to continue (query wizard will not be used here). Microsoft Query will be used in the following:Climatological Database manual Developers information 74 2003 Close the 'Add Tables' dialog and press the SQL button to open dialog for SQL statements: Paste an SQL statement from CLDB Select (from the 'SQL Text' tab-page) into the opened dialog. Pressing OK executes the SQL statement. The retrieved data are displayed in Microsoft Query window:Climatological Database manual Developers information 75 2003 Paste it into clipboard from the CLDB Select macro window using standard Windows means (select text with mouse, then press Ctrl+C). Then the Excel menu Tools/Macro/Macros has to be used to insert macro into Excel workbook. Use the button pointed by the arrow to return retrieved data to Excel worksheet: In the Excel worksheet, data are no more linked with database source and can be further processed using standard Excel means.Climatological Database manual Dual Database (Replication) 76 2003 6. Dual Database (Replication) 6.1. Dual database system support The dual database system for CLDB is proposed for data storage and processing. The dual system consists of main (primary) CLDB1 PC and backup (secondary) CLDB2 PC. Both PCs will be connected to the local network and data will be stored on both computers simultaneously. Data access will be transparent - client software will connect automatically to that CLDB database, which will be able to process client requests. In case of failure of one PC, second PC will handle all requests without loss of data and functionality. This configuration requires advanced database connection module. The dual configuration is based on the Oracle Replication technology. The replication is a process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Oracle Replication is a fully integrated feature of the Oracle server; it is not a separate server. Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. Replication means that the same data is available at multiple locations. Oracle Replication supports various types of replication environments. For our purpose the Materialized View Replication is the best option. The materialized view contains a complete copy of a master from a single point in time. In our case, the master is the CLDB at CLDB1 PC and materialized view is the CLDB at CLDB2 PC. To ensure that the materialized view is consistent with its master, it is necessary to refresh the materialized view periodically. Oracle provides a configurable tool for refreshing the materialized view. The refreshing was configured in order to make the CLDB's at both sites consistent. Each Oracle replication environment must address the possibility of replication conflicts that may occur when, for example, two transactions originating from different sites update the same row at nearly the same time. When data conflict occurs, the mechanism to ensure that the conflict is resolved in accordance with some given rules is needed. It is necessary that the data are consistent at all sites. Oracle Replication offers a variety of prebuilt conflict resolution methods that enable the definition of a conflict resolution system for the database that resolves conflicts in accordance with given rules.Climatological Database manual Dual Database (Replication) 77 2003 6.2. Summary The used dual system configuration is following: • databases aliases: - cldb1 for CLDB at CLDB1 PC - cldb2 for CLDB at CLDB2 PC - cldb.world for CLDB at the site that is currently able to process the client requests (either at CLDB1 PC or at CLDB2 PC) • replication configuration - master site: CLDB at CLDB1 PC - materialized view: CLDB at CLDB2 PC • replication objects - schema: cldb - tables: commands invliddatatable msggrouppropvalues msggroups msggroupsprops msggroupsvariables options orderby qcrules qcvariables regions relops sources stationprops stationpropvalues stations uncheckeddatatable upperair values_f values_s variabledictonary variables varprops varpropvalues • replication conflicts resolution method - latest timestamp: this method resolves a conflict based on the most recent update done on the given row of the corresponding tableClimatological Database manual Contact: 78 2003 Contact: MicroStep-MIS, Monitoring and Information Systems Ilkovičova 3, 841 04 Bratislava, Slovak Republic Tel: +421 2 602 91 816 +421 2 602 91 496 Fax: +421 2 654 22 480 E-mail: [email protected] WEB : www.microstep-mis.com