Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | Tibero 7.2.4
Document Number | TMOTI044
Overview
This is an example of using the bind variable capture feature through ILOG TAG activation.
Method
1. Activate ILOG TAG
Tags are activated at the System level, and the activation Level is set to Detail.
ALTER SYSTEM ILOG ENABLE NAME 'BODY_MSG_CSR_BIND_PARAM_INFO' LEVEL DETAIL; ALTER SYSTEM ILOG ENABLE NAME 'BODY_SQL_PROCESS' LEVEL DETAIL; ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_NULL' LEVEL DETAIL; ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG' LEVEL DETAIL; ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_UNKNOWN' LEVEL DETAIL;
2. ILOG Monitoring
After activating all TAGs, check whether bind variables are being captured correctly.
ILOG can be checked using the tbiv command, and real-time monitoring is possible with the -f option.
$ tbiv -f tb724-96.ilg
12/03 11:35:24.4 0-0096 tbsvr_sq:282 SQL PROCESS: sql=BEGIN :b_sal := 2000; END;, csr=2036
12/03 11:35:24.4 0-0096 tbsvr_sq:282 SQL PROCESS: sql=BEGIN :b_job := 'MANAGER'; END;, csr=2037
12/03 11:35:24.4 0-0096 tbsvr_sq:282 SQL PROCESS: sql=BEGIN :b_dt := TO_DATE('01-JAN-1981', 'DD-MON-YYYY'); END;, csr=2038
12/03 11:35:24.4 0-0096 tbsvr_sq:282 SQL PROCESS: sql=SELECT empno, ename, job, sal, hiredate
FROM emp
WHERE sal >= :b_sal
AND job = :b_job
AND hiredate >= :b_dt, csr=2039
12/03 11:35:24.4 0-0096 tbsvr_ms:960 BINDING[CSR_ID=2039]: param #0, param_kind=IN/OUT, param_type=TB_TYPE_NUMBER
12/03 11:35:24.4 0-0096 csr.c:3815 BINDING[CSR_ID=2039]: param #0: param_val_len=4, param_val: [2000]
12/03 11:35:24.4 0-0096 tbsvr_ms:960 BINDING[CSR_ID=2039]: param #1, param_kind=IN/OUT, param_type=TB_TYPE_VARCHAR
12/03 11:35:24.4 0-0096 csr.c:3815 BINDING[CSR_ID=2039]: param #1: param_val_len=7, param_val: [MANAGER]
12/03 11:35:24.4 0-0096 tbsvr_ms:960 BINDING[CSR_ID=2039]: param #2, param_kind=IN/OUT, param_type=TB_TYPE_DATE
12/03 11:35:24.4 0-0096 csr.c:3815 BINDING[CSR_ID=2039]: param #2: param_val_len=9, param_val: [01-JAN-81]
3. Deactivate ILOG TAG
After capturing all necessary bind variables, deactivate the activated TAGs.
If there are too many logs, it can affect performance or disk usage, so it is recommended to deactivate after use.
ALTER SYSTEM ILOG DISABLE NAME 'BODY_MSG_CSR_BIND_PARAM_INFO'; ALTER SYSTEM ILOG DISABLE NAME 'BODY_SQL_PROCESS'; ALTER SYSTEM ILOG DISABLE NAME 'CSR_USERPARAM_TO_STRNLOG_NULL'; ALTER SYSTEM ILOG DISABLE NAME 'CSR_USERPARAM_TO_STRNLOG'; ALTER SYSTEM ILOG DISABLE NAME 'CSR_USERPARAM_TO_STRNLOG_UNKNOWN';