scripts/rmt/runList_john.sh

0001 #!/bin/bash
0002 # John Hakala, 9/14/15
0003 # To use this script, run it like this:
0004 # ./runList.sh <lower limit run number> <upper limit run number>
0005 # Example:
0006 # ./runList.sh 255000 255200
0007 #
0008 # It must be run for the cmsusr network, or a tunnel must be set up to forward the query
0009
0010 # Build an sql script for checking whether the run is local or global
0011 echo -n 'SELECT  "STRING_VALUE" FROM "CMS_RUNINFO"."RUNSESSION_PARAMETER" WHERE "RUNNUMBER"=' > checkGlobal.sql
0012 echo -n "'&1'"  >> checkGlobal.sql
0013 echo -n 'AND "NAME" LIKE'  >> checkGlobal.sql
0014 echo  "'%RUN_TYPE';" >> checkGlobal.sql
0015 echo "exit;" >> checkGlobal.sql
0016
0017 # Start building an sql query for dumping the desired info from local runs
0018 # All these first lines are just to make the output pretty
0019 echo 'set linesize 300;' > tmp.sql
0020 echo 'set wrap off;' >> tmp.sql
0021 echo 'set trimout on;' >> tmp.sql
0022 echo 'set tab off;' >> tmp.sql
0023 echo 'column NAME format a30' >> tmp.sql
0024 echo 'column TIME format a50' >> tmp.sql
0025 echo 'column STRING_VALUE format a80' >> tmp.sql
0026
0027 # Loop over user-specified run-number range
0028 for ((i=$1; i<=$2; i++))
0029 do
0030         # Use the script for checking if a run is global or local
0031         sqlplus cms_hcl_runinfo/run2009info@cms_omds_adg @checkGlobal.sql $i | grep -q GLOBAL
0032         # If it's global, then do nothing
0033         if [ "$?" = "0" ]; then
0034                 echo -n
0035         # Otherwise, add the local run number to the query for looking at local runs.
0036         else
0037                 echo -n 'SELECT  "RUNNUMBER","TIME", "NAME","STRING_VALUE" FROM "CMS_RUNINFO"."RUNSESSION_PARAMETER" WHERE "RUNNUMBER"=' >> tmp.sql
0038                 echo -n $i >> tmp.sql
0039                 echo -n ' AND ("NAME" LIKE' >> tmp.sql
0040                 echo -n " '%FULLPATH'" >> tmp.sql
0041                 echo -n ' OR "NAME" LIKE ' >> tmp.sql
0042                 echo -n "'%TRIGGERS%'" >> tmp.sql
0043                 echo ');' >> tmp.sql
0044         fi
0045 done
0046 # Finish building the query for all the local runs
0047 echo 'exit;' >> tmp.sql
0048
0049 # Execute the query to dump the desired info for the local runs
0050 sqlplus cms_hcl_runinfo/run2009info@cms_omds_adg @tmp.sql | grep -v "rows selected" | cat -s
0051