Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

Capturing Bind Variables Using ILOG TAG

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';