==========================
General
==========================
The main idea is to get data from a table, from several Databases, and load the data into a single table.
1. main_capacity_loader.bat
- Set run date in format YYYYMMDD_hh24mmss
- Sets environment variables
- Calls main_extact_capacity.bat
2. main_extact_capacity.bat:- Calls extract_capacity.bat
- Moves generated file to input directory for SQL Loader
- Calls sql_loader_main.bat
- Moves processed file to output directory for SQL Loader
3. extract_capacity.bat
- Reads ini file, with DB connections details
- Per each connections calls run_sql.bat
4. run_sql.bat
- Wrapper to get_capacity_counters.sql
5. get_capacity_counters.sql
- Calls Handles the actual access to remote DB.
6. sql_loader_main.bat
- Recieved output file from extract_capacity.bat. The fields are comma delimited, and loads, via SQL LOADER into Database.
==========================
main_capacity_loader.bat
==========================
ECHO OFF
setlocal
cls
ECHO.
ECHO =============================================
ECHO main is starting
ECHO =============================================
ECHO.
ECHO =============================================
ECHO set run date
ECHO =============================================
set run_year=%date:~-4%
set run_month=%date:~4,2%
set run_day=%date:~7,2%
set hh=%time:~0,2%
set mm=%time:~3,2%
set ss=%time:~6,2%
IF %run_month% LSS 10 (set run_month=0%run_month:~1,1%)
IF %run_day% LSS 10 (set run_day=0%run_day:~1,1%)
IF %hh% LSS 10 (set hh=0%hh:~1,1%)
IF %mm% LSS 10 (set mm=0%mm:~1,1%)
IF %ss% LSS 10 (set ss=0%ss:~1,1%)
set RUN_DATE=%run_year%%run_month%%run_day%_%hh%%mm%%ss%
ECHO Start Run at %RUN_DATE%
ECHO.
ECHO =============================================
ECHO init parameters
ECHO =============================================
SET HOME_DIR=d:\RDG\Capacity\CAPACILY_LOADER
SET LOG_FILE=%HOME_DIR%\Logs\capacity_load_%RUN_DATE%.log
SET INIFILE=%HOME_DIR%\db_list.ini
SET SPOOL_TO_FILE=%HOME_DIR%\capacity_counters.dat
ECHO LOG_FILE: %LOG_FILE%
ECHO.
ECHO. > %LOG_FILE%
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE% >> %LOG_FILE%
CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
ECHO.
ECHO =============================================
ECHO.
ECHO ============================================= >> %LOG_FILE%
ECHO main_capacity_loader.bat has finished >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
REM EXIT
==========================
main_extact_capacity.bat
==========================
ECHO OFF
setlocal
cls
ECHO.
ECHO =============================================
ECHO main is starting
ECHO =============================================
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
SET HOME_DIR=%1
SET RUN_DATE=%2
SET LOG_FILE=%3
SET INIFILE=%4
SET SPOOL_TO_FILE=%5
ECHO Inside main_extact_capacity.bat >> %LOG_FILE%
ECHO.
ECHO =============================================
ECHO init parameters
ECHO =============================================
SET SQL_LOADER_DIR=%HOME_DIR%\SQL_LOADER
SET SQL_LOADER_DIR_HANDLED=%SQL_LOADER_DIR%\HANDLED
SET SQL_LOADER_MAIN=%SQL_LOADER_DIR%\sql_loader_main.bat
SET SQL_LOADER_FILE=%SQL_LOADER_DIR%\input_capacity.dat
SET SQL_LOADER_FILE_HANDLED=%SQL_LOADER_DIR_HANDLED%\input_capacity_%RUN_DATE%.dat
ECHO.
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO Delete old capacity_counters.dat file
ECHO Delete old capacity_counters.dat file >> %LOG_FILE%
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO DEL %SPOOL_TO_FILE%
ECHO DEL %SPOOL_TO_FILE% >> %LOG_FILE%
DEL %SPOOL_TO_FILE%
ECHO.
ECHO. >> %LOG_FILE%
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO CALL %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%
ECHO CALL %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE% >> %LOG_FILE%
CALL %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%
ECHO.
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO.
ECHO ============================================= >> %LOG_FILE%
ECHO Move capacity_counters.dat file to SQL_LOADER >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
ECHO =============================================
ECHO Move capacity_counters.dat file to SQL_LOADER
ECHO =============================================
ECHO MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE% >> %LOG_FILE%
ECHO MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE%
MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE%
ECHO.
ECHO ============================================= >> %LOG_FILE%
ECHO Running SQL_LOADER >> %LOG_FILE%
ECHO CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
ECHO =============================================
ECHO Running SQL_LOADER
ECHO =============================================
ECHO CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR%
CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR%
ECHO =============================================
ECHO %SQL_LOADER_MAIN% has Finished
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO %SQL_LOADER_MAIN% has Finished >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO =============================================
ECHO Move capacity_counters.dat file to SQL_LOADER_HANDLED
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO Move capacity_counters.dat file to SQL_LOADER_HANDLED >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
ECHO MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED%
ECHO MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED% >> %LOG_FILE%
MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED%
ECHO.
ECHO =============================================
ECHO main_extact_capacity.bat has finished
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO main_extact_capacity.bat has finished >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
==========================
extract_capacity.bat
==========================
ECHO OFF
setlocal
cls
ECHO.
ECHO =============================================
REM CALL extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%
SET HOME_DIR=%1
SET SPOOL_TO_FILE=%2
SET INIFILE=%3
SET LOG_FILE=%4
SET SQL_FILE=%HOME_DIR%\code\get_capacity_counters.sql
ECHO ============================================= >> %LOG_FILE%
ECHO Inside extract_capacity.bat >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO =============================================
ECHO extract_capacity.bat is starting
ECHO This Program read entries from file %inifile%
ECHO =============================================
ECHO.
FOR /F "tokens=*" %%i IN (%inifile%) DO (
ECHO --------------------------------------------------------
ECHO Handling %%i
ECHO --------------------------------------------------------
ECHO -------------------------------------------------------- >> %LOG_FILE%
ECHO Handling %%i >> %LOG_FILE%
ECHO -------------------------------------------------------- >> %LOG_FILE%
call :process_one_db %%i %LOG_FILE% %SQL_FILE% %SPOOL_TO_FILE% %HOME_DIR%
)
ECHO.
ECHO =============================================
ECHO extract_capacity has finished
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO extract_capacity has finished >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%
GOTO :EOF
:process_one_db
SET CONNECTION_NAME=%1
SET LOG_FILE=%2
SET SQL_FILE=%3
SET SPOOL_TO_FILE=%4
SET HOME_DIR=%5
REM ECHO CONNECTION_NAME %CONNECTION_NAME%
SET IS_REMARK=%CONNECTION_NAME:~0,1%
IF [%IS_REMARK%]==[#] (
ECHO Skip Processing %CONNECTION_NAME%
ECHO Skip Processing %CONNECTION_NAME% >> %LOG_FILE%
) ELSE (
ECHO call %HOME_DIR%\code\run_sql.bat %CONNECTION_NAME% %SQL_FILE% %SPOOL_TO_FILE% >> %LOG_FILE%
call %HOME_DIR%\code\run_sql.bat %CONNECTION_NAME% %SQL_FILE% %SPOOL_TO_FILE%
ECHO --------------------------------------------------------
ECHO Done
ECHO --------------------------------------------------------
ECHO.
ECHO -------------------------------------------------------- >> %LOG_FILE%
ECHO Done >> %LOG_FILE%
ECHO -------------------------------------------------------- >> %LOG_FILE%
ECHO. >> %LOG_FILE%
)
==========================
run_sql.bat
==========================
SET db_name=%1
SET sql_file_name=%2
SET spool_to_file=%3
REM ECHO db_name: %db_name%
REM ECHO sql_file_name: %sql_file_name%
REM ECHO spool_to_file: %spool_to_file%
REM ECHO sqlplus %db_name% @%sql_file_name% %spool_to_file%
sqlplus %db_name% @%sql_file_name% %spool_to_file%
==========================
get_capacity_counters.sql
==========================
SET SHOW OFF
SET VERIFY OFF
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 0
SET TRIMS ON
SET TERMOUT OFF
SET COLSEP ,
SET LINESIZE 140
--SPOOL capacity_counters.dat APPEND;
SPOOL &&1 APPEND;
SELECT TRIM(USER),
SUBSTR(TO_CHAR(static_id),1,30) AS static_id,
TO_CHAR(VALUE_DATE,'YYYYMMDD hh24:mi:ss') AS RUN_DATE,
ROUND(SUM(CNT_VALUE)/900) AS value
FROM SUPPORT_CNT_LOG, DUAL
WHERE VALUE_DATE >= ADD_MONTHS(SYSDATE,-6)
AND TO_CHAR(static_id) NOT LIKE '9999999%'
AND TO_CHAR(static_id) NOT LIKE '1011500104190000'
AND CNT_VALUE IS NOT NULL
GROUP BY TO_CHAR(static_id), TO_CHAR(VALUE_DATE,'YYYYMMDD hh24:mi:ss');
EXIT;
==========================
db_list.ini
==========================
# Use # to mark out lines
#-------------------------------------------------------
USER_A/PASS_A@CONN_STR_A
USER_B/PASS_B@CONN_STR_B
USER_C/PASS_C@CONN_STR_C
USER_D/PASS_D@CONN_STR_D
#USER_E/PASS_E@CONN_STR_E
==========================
sql_loader_main.bat
==========================
ECHO OFF
REM sqlldr collector/ncgwcol@igt control=${SA_LOAD_HOME}/${SA_LOAD_CST}/loader_ct_aps1.dat log=${SA_LOAD_HOME}/${SA_LOAD_CST}/loader_aps1_log.log
SET SQL_LOADER_DIR=%1
REM SET SQL_LOADER_DIR=D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER
sqlldr CAPACITY/CAPACITY@CGW_SA control=%SQL_LOADER_DIR%\loader_capacity.ctl log=%SQL_LOADER_DIR%\loader_capacity.log
==========================
loader_capacity.ctl
==========================
LOAD DATA
INFILE 'D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER\input_capacity.dat'
BADFILE 'D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER\bad_capacity.bad'
discardmax 0
TRUNCATE
into table CAPACITY_COUNTER_DATA
fields terminated by ','
(
schema_name "TRIM(:schema_name)",
counter_id "TRIM(:counter_id)",
run_date "TRIM(:run_date)",
counter_value "TRIM(:counter_value)"
)
