Skip to main content

Search

Welcome to Tibero GTS!

Repeated Execution of SQL via OS Command (Linux)

Document Type | Technical Information

Category | Monitoring/Inspection

Applicable Product Versions | 5SP1FS01, 5SP1FS02, 5SP1FS03, 5SP1FS04, 5SP1FS06, 6FS01, 6FS02, 6FS03, 6FS04, 6FS05, 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS

Document Number | TMOTI013

 

Overview

To analyze load conditions and monitor the number and status of sessions, it is necessary to repeatedly execute the relevant SQL.
This guide explains a convenient method to perform repeated execution by combining OS commands and SQL.
 
 
 

Method

1. Create SQL

Create a session status monitoring query for DB connections from WAS1 and WAS2 in the TAC environment.

$ vi query.sql
SELECT (SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') FROM DUAL) DT
              , A.*
         FROM ( SELECT INST_ID, STATUS, COUNT(1) FROM GV$SESSION
                  WHERE MACHINE IN('WAS1','WAS2') GROUP BY INST_ID, STATUS) A;

 

$ tbsql sys/tibero

SQL> @query.sql

DT                   INST_ID STATUS       COUNT(1)
----------------- ---------- ---------- ----------
20250509 16:15:17          1 READY           1995
20250509 16:15:17          1 RUNNING            5
20250509 16:15:17          2 READY           1394
20250509 16:15:17          2 RUNNING            6

-- Confirm normal operation

 

2. Create Loop Shell Script

Create loop.sh and redirect the SQL execution results to test.txt.

$vi loop.sh

while true; do
tbsql -s sys/tibero @/tibero/work/query.sql >> test.txt
 sleep 2
done

 

3. Run Shell Script in Background

$ nohup sh /tibero/work/query.sql &

 

4. Monitoring

You can verify that the SQL is being executed repeatedly.

$ tail -f test.txt

DT                   INST_ID STATUS       COUNT(1)
----------------- ---------- ---------- ----------
20250509 16:16:23          1 READY           1995
20250509 16:16:23          1 RUNNING            5
20250509 16:16:23          2 READY           1394
20250509 16:16:23          2 RUNNING            6

DT                   INST_ID STATUS       COUNT(1)
----------------- ---------- ---------- ----------
20250509 16:16:25          1 READY           1995
20250509 16:16:25          1 RUNNING            5
20250509 16:16:25          2 READY           1394
20250509 16:16:25          2 RUNNING            6

....(omitted)

 

5. Termination

$ ps -ef| grep loop
tibero    2239  1951  0 04:35 pts/0    00:00:00 sh loop.sh
tibero    2378  1951  0 04:35 pts/0    00:00:00 grep --color=auto loop
 

$ kill -9 2239

[1]+  Killed                  nohup sh loop.sh

 

 

 

 

Related to