----------------------------------------------------------- DEBUGGING DATABASE CONNECTION PROBLEMS This gives a list of SQL and shell commands which can be used to debug possible problems with the database when you get error messages from the connection. A. ORACLE DATABASES AT FERMILAB 1) Do you have the required software installed ? > env | grep -i "ORACLE_TNSNAMES" > env | grep -i "SETUP_ORACLE_CLIENT" > which sqlplus If you get 'sqlplus: Command not found.' or the above environment variables are not set then you should email : cdf-code-management@fnal.gov and ask that the oracle client libraries and oracle tnsnames packages be installed and or activated in the cdfsoft2 ups table. 2) Try to just connect using the cdf_reader account and sqlplus. (this is the type of connection used by AC++) > sqlplus cdf_reader/reader@cdfonprd (for online database) > sqlplus cdf_reader/reader@cdfofprd (for offline database) If you get to the 'SQL>' prompt then you can connect to oracle as a read-only client. If not then either : i) your oracle_client cannot resolve where the database (cdfonprd; cdfofprd) is. This can be caused by having an incorrect installation of the oracle_tnsnames package ii) the server is down. Try > ping b0dau35.fnal.gov Certain cdfsga usernames have been assigned write access (and read) # to the database aswell. You can check if you are one of these users by typing just : > sqlplus /@cdfonprd (for online database) > sqlplus /@cdfofprd (for offline database) If you get to the 'SQL>' prompt then you have an account already setup on the database server. The privileges for this account are listed in the file : http://www-cdf.fnal.gov/internal/upgrades/computing/calibration_db/ role-call.html. These accounts are provided primarily to people who need to modify the contents of the database. Send an email to markl@hep.ucl.ac.uk for an account on the offline machine and cranshaw@fnal.gov for an account on the online machine. Such an account is not required by users who just need read access to the database. If you do NOT get the SQL> prompt in this instance, then there are two further possibilities in addition to the two above : i) you do not have an account on that oracle server. ii) your username on your machine is not the same as your username on the oracle server. There should be an error message which should help resolve between the above possibilities. YOU CAN EXIT sqlplus BY TYPING quit. IF YOU CAN DO 1) THEN THERE IS NO BASIC PROBLEM WITH CONNECTING TO THE DATABASE. IT IS TIME TO START LOOKING AT YOUR CODE. 3) Check your iomap reference in your code. The CDF Run 2 calibration database API uses managers to handle connections to the database. The managers use an "iomap file". This file is searched for in the following order : i) Via an AC++ talk-to setting viz : module talk CalibrationManager IomapFile set Myiomap_File.txt exit ii) A file called "./iomap.txt" in the current directory iii) A file pointed to by the environment variable "IOMAP_FILE" iv) By using the default file : $CDFSOFT2_DIR/DBUtils/Examples/iomap.txt WARNING! DO NOT USE A LOCAL iomap.txt UNLESS ABSOLUTELY NECESSARY. The defaults in (iv) are validated and work. >From this sequence you should be able to determine which iomap file your code is using. Now look at your code :: The manager will use a database name from the iomap file for initialization in one of two ways. a) direct initialization of the manager : Is the database name valid? This means you have a line in your code like CEMQie_mgr cemqie_mgr(DBname, "CEMQie", "CEM") Check what string is being set for DBname. For example, it could be 'database_oracle_online'. Check whether this string is in the iomap file you are using. The string will be the in first column of the iomap file. The second column will say what type of manager you are using : OCI, text, MSQL, OTL and the third is the actual database connection string e.g. > more $CDFSOFT2_DIR/bin/$BFARCH/iomap.txt production_calibration_database Msql calibration test_calibration_database Msql calibration_test database_msql Msql calibration_test database_text Text caltest database_oracle_offline OCI cdf_reader/reader@cdfofprd database_oracle_offline_rw OCI /@cdfofprd database_oracle_online OCI /@cdfonprd database_oracle_cal OCI newcalib/newcalib@cdfonprd test_file_catalog Msql data_file_catalog_test online_test_msql Msql database_name production_file_catalog OTL filecatalog_readuser/read@cdfofprd production_file_catalog_write OTL /@cdfofprd If the string does not exist in the iomap file then you should try and use one that does exist appropriate to the database you want to connect to. b) Initialisation of the manager using the AC++ CalibrationManager module The CalibrationManager module is one of the CDFrequired modules, so it gets automatically included if you have #include "FrameMods/addCDFrequiredModules.hh" .... // ---> Get CDF input,output,calibmanager addCDFrequiredModules( this ); in your AC++ source file. The talk-to in your tcl file should look like the following. # Designate default database in CalibrationManager module talk CalibrationManager IomapFile set Myiomap_File.txt DataDB set database_oracle_online Database set database_oracle_online show exit You can set a specific iomap.txt file using the IomapFile designation. Again, for non-experts this is discouraged. DataDB and Database should be set to one of the database names in the iomap.txt file that are using. "DataDB" refers to the database containing the actual calibration data, wherase "Database" refers to the database containing the administration tables. Only rarely, and if you know what you are doing, will these two database locations be different. If the Talk-to is not activated then the defaults are to use $CDFSOFT2_DIR/DBUtils/Examples/iomap.txt as the iomap file. (unless you have a file "./iomap.txt" or the env. variable IOMAP_FILE set). and use for DataDB and Database the value : database_oracle_offline 4) Check that all the libraries you need were built. If you have a valid database name and you still get an error when initializing the manager, check that all of the libraries have been compiled on your machine. > cd $CDFSOFT2_DIR/lib/$BFARCH/ > ls libCalibDB*.a > ls CalibDBTables_Reg*.o There should be entries for : _text (for the Text database), _msql (for the mSQL database), and _oci (for the Oracle database) versions of libCalibDB, libCalibDBTables and CalibDBTables_Reg*.o If you are missing one of them then you will not have the necessary libraries required to access the database. Check with whoever takes care of the nightly build on the machine you are using. 5) does the API know about the manager you are trying to use? I make the implicit assumption that you are not working with a local copy of CalibDBTables in your release. If you are, you should know what you are doing, and you don't need this help. Look for the manager class that you are using in the generated files. Below is an example for CEMQie. > cd $CDFSOFT2_DIR/CalibDBTables/CalibDBTables/gen/ > grep CEMQie_mgr * CEMQie.Defs.hh:typedef Manager CEMQie_mgr; If it does not return the manager found, then the manager you for the class you are trying to use was not generated, and therefore does not exist in the CalibDBTables libraries. Contact one of the people in charge of the checking in the calibration data table definitions: offline Mark Lancaster (markl@hep.ucl.ac.uk), online Jack Cranshaw (cranshaw@fnal.gov). 6) Does the table you are trying to access exist in the database? [Note the sql commands used by sqlplus must be terminated with a ";" string. sqlplus is exited by typing "quit"] Look for the table with sqlplus. Again I use CEMQie as an example. Sqlplus is case-insensitive. It makes everything capitals by default. > sqlplus cdf_reader/reader@cdfonprd SQL> desc CEMQIE; You should get. Name Null? Type ------------------------------- -------- ---- CID NOT NULL NUMBER(38) CHANNEL NOT NULL NUMBER(38) GEOMID NUMBER(38) CAP NUMBER(38) RANGE NUMBER(38) SLOPE NUMBER(63) SLOERR NUMBER(63) OFFSET NUMBER(63) OFFERR NUMBER(63) If you do not, then the table is not defined in the database, and therefore the manager has nothing to manage. Contact the people in charge of the calibration data table definitions, and ask them to add your table to the database. 7) Do you have permission to write to that table? Read privilege is in general granted to everyone on CDF, but you have to be a member of the appropriate role to write to a calibration data table. The list of roles,tables,users is at: http://www-cdf.fnal.gov/internal/upgrades/computing/calibration_db/ role-call.html 8) Does the run,version,data_status combination you are trying to retrieve exist in the CALIBRUNLISTS table? Search for the run,version,data_status key with sqlplus. I use run 142, version 0, RAW data as an example. You can of course replace them with the ones that interest you. > sqlplus cdf_reader/reader@cdfonprd SQL> select calib_table,calib_run,calib_version,data_status from calibrunlists where calib_run=142 and calib_version=0 and data_status='RAW'; This gives a result like CALIB_TABLE CALIB_RUN CALIB_VERSION DATA_STATUS -------------------- ---------- ------------- ---------------- CEMQIE 142 0 RAW CHAQIE 142 0 RAW PEMQIE 142 0 RAW PHAQIE 142 0 RAW If it returns no rows selected, then you need to check which run,version,data_status key you should be using. One can also omit qualifiers and look for all versions or status for a given run. For example: > sqlplus cdf_reader/reader@cdfonprd SQL> select calib_table,calib_run,calib_version,data_status from calibrunlists where calib_run=123; CALIB_TABLE CALIB_RUN CALIB_VERSION DATA_STATUS -------------------- ---------- ------------- ---------------- COTCTT 123 0 UNDEFINED CEMQIE 123 0 RAW CEMQIE 123 1 RAW PEMQIE 123 0 RAW PEMQIE 123 1 RAW CHAQIE 123 0 RAW CHAQIE 123 1 RAW PHAQIE 123 0 RAW PHAQIE 123 1 RAW B. MSQL DATABASES MSQL databases currently exist in several places as our example of a database flavor to use for offsite distribution. The commands differ from oracle commands for 1),2),6) and 8), but are otherwise the same. 1_mSQL) Is the msql software installed ? > env | grep -i MSQL_DIR > which msql If you get 'msql: Command not found.' or the above environment variables are not set then you should email : cdf-code-management@fnal.gov and ask that the msql package be installed and or activated in the cdfsoft2 ups table. 2_mSQL) See if the server is running : > ps -ex | grep -i msql2d or > relshow will work if the server is running and list the names of the databases installed. If the server is not running it must be started by the username that "owns" the database. That username is listed under the entry : mSQL_User and Admin_User in the file $MSQL_CONF_FILE. The server can be started by that user with the command > ups start msql msql_version where msql_version can be determined from a command "env | grep -i msql" If the server is running, try to just connect with one of the databases named by "relshow" > msql database_name. If you get to the 'mSQL>' prompt then you can connect to the database. You can check whether you have write access to the msql database by going to the directory that contains the file MSQL_CONF_FILE and looking at the usernames under the entry "write" in the file "msql.acl". This is the file to change to add users with write privileges (and then restart the server). YOU CAN EXIT msql BY TYPING \q. 6_mSQL) Does the table you are trying to access exist in the database? [Note the sql commands used by msql must be terminated with a "\g" string. msql is exited by typing "\q"] Look for the table with msql. Again I use CEMQie as an example. msql is case-sensitive. It is best to turn on caps lock > relshow database_name TABLE_NAME You should get. +-----------------+----------+--------+----------+--------------+ | Field | Type | Length | Not Null | Unique Index | +-----------------+----------+--------+----------+--------------+ | CID | int | 4 | N | N/A | | CHANNEL | int | 4 | N | N/A | | GEOMID | int | 4 | N | N/A | | CAP | int | 4 | N | N/A | | RANGE | int | 4 | N | N/A | | SLOPE | real | 8 | N | N/A | | SLOERR | real | 8 | N | N/A | | OFFSET | real | 8 | N | N/A | | OFFERR | real | 8 | N | N/A | | CEMQIE_IDX | index | N/A | N/A | Y | +-----------------+----------+--------+----------+--------------+ If you do not, then the table is not defined in the database, and therefore the manager has nothing to manage. Contact the people in charge of the calibration data table definitions, and ask them to add your table to the database. 8_mSQL) Does the run,version,data_status combination you are trying to retrieve exist in the CALIBRUNLISTS table? Search for the run,version,data_status key with msql. I use run 142, version 0, RAW data as an example. You can of course replace them with the ones that interest you. > msql jackdb mSQL> SELECT CALIB_TABLE,CALIB_RUN,CALIB_VERSION,DATA_STATUS FROM CALIBRUNLISTS WHERE CALIB_RUN=341 AND CALIB_VERSION=0 AND DATA_STATUS='UNDEFINED'\g This gives a result like +----------------------+-----------+---------------+----------------+ | CALIB_TA | CALIB_RUN | CALIB_VERSION | DATA_STATUS | +----------------------+-----------+---------------+----------------+ | CEMQIE | 341 | 0 | UNDEFINED | | CHAQIE | 341 | 0 | UNDEFINED | | CEMPED | 341 | 0 | UNDEFINED | | CHAPED | 341 | 0 | UNDEFINED | +----------------------+-----------+---------------+----------------+ If it returns no rows selected, then you need to check which run,version,data_status key you should be using. One can also omit qualifiers and look for all versions or status for a given run. For example: > msql jackdb mSQL> SELECT CALIB_TABLE,CALIB_RUN,CALIB_VERSION,DATA_STATUS FROM CALIBRUNLISTS WHERE CALIB_RUN=341\g +-----------------------+-----------+---------------+----------------+ | CALIB_TABLE | CALIB_RUN | CALIB_VERSION | DATA_STATUS | +-----------------------+-----------+---------------+----------------+ | CEMQIE | 341 | 1 | RAW | | CHAQIE | 341 | 1 | RAW | | CEMPED | 341 | 1 | RAW | | CHAPED | 341 | 1 | RAW | | PEMQIE | 341 | 1 | RAW | | PHAQIE | 341 | 1 | RAW | | PEMPED | 341 | 1 | RAW | | PHAPED | 341 | 1 | RAW | | PEMQIE | 341 | 2 | RAW | | PHAQIE | 341 | 2 | RAW | | PEMPED | 341 | 2 | RAW | | PHAPED | 341 | 2 | RAW | | CEMQIE | 341 | 0 | UNDEFINED | | CHAQIE | 341 | 0 | UNDEFINED | | CEMPED | 341 | 0 | UNDEFINED | | CHAPED | 341 | 0 | UNDEFINED | +-----------------------+-----------+---------------+----------------+