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

Search

Welcome to Tibero GTS!

How to Capture Bind Variables Using ILOG TAG

Document Type | Technical Information

Category | Administration

Applicable Product Version | 7FS02, 7FS02PS

Document Number | TADTI022

 

Overview

This explains how to enable the ILOG TAG feature to capture and check bind variable values in Tibero.
Caution
Patch FS06_128314a (based on Tibero 7 FS02 or later) must be applied.
Apply the patch through technical support provided by Tmax Tibero.
To check bind variables, the following TAGs must be enabled.
  • BODY_MSG_CSR_BIND_PARAM_INFO
  • BODY_SQL_PROCESS
  • CSR_USERPARAM_TO_STRNLOG_NULL
  • CSR_USERPARAM_TO_STRNLOG
  • CSR_USERPARAM_TO_STRNLOG_UNKNOWN

 

Method

1. Connect to Tibero and Enable TAG List

  • Enable TAG: System level
  • LEVEL: Detail
$ tbsql sys/tibero
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. Verify Bind Variable Capture (ILOG Monitoring)

Example of creating a table, inserting data, and committing.
-- Create table and insert data
$ tbsql tibero/tmax
SQL> create table ttt(a) as select * from dual;
Table 'TTT' created.

SQL> insert into ttt values('T');
1 row inserted.

SQL> commit;
Commit completed.
 
Verify bind variable capture using tbiv 
$ tbiv tsc_tibero-92.ilg
Tibero Internal Log Viewer (7   r283516, little-endian)
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

03/10 11:18:38.4 0-0092 tbsvr_sq:277 SQL PROCESS: sql=SELECT OPEN_MODE FROM SYS.V$DATABASE, csr=2
03/10 11:18:38.5 0-0092 tbsvr_sq:277 SQL PROCESS: sql=SELECT /*+ no_outline / ACTION, POLICY FROM CLIENT_ACCESS_POLICY WHERE CLIENT = 'tbSQL'   AND 
(USERID = 'PUBLIC' OR ? LIKE USERID), csr=3
03/10 11:18:38.5 0-0092 tbsvr_ms:955 BINDING[CSR_ID=3]: param #0, param_kind=IN, param_type=TB_TYPE_VARCHAR
03/10 11:18:38.5 0-0092    csr.c:3747 BINDING[CSR_ID=3]: param #0: param_val_len=6, param_val: [TIBERO]
03/10 11:18:39.4 0-0092 tbsvr_sq:277 SQL PROCESS: sql=SELECT /+ no_outline */ OBJECT_NAME _LS_OBJECT_NAME,       SUBOBJECT_NAME _LS_SUBOBJECT_NAME,
OBJECT_TYPE _LS_OBJECT_TYPE   FROM ALL_OBJECTS  WHERE OWNER = ?   AND OBJECT_TYPE NOT IN ('LOB')   AND OBJECT_NAME NOT LIKE '_TB_STAT_SNAP%%' ORDER BY OBJECT_TYPE, OBJECT_NAME, csr=4
03/10 11:18:39.4 0-0092 tbsvr_ms:955 BINDING[CSR_ID=4]: param #0, param_kind=IN, param_type=TB_TYPE_VARCHAR
03/10 11:18:39.4 0-0092    csr.c:3747 BINDING[CSR_ID=4]: param #0: param_val_len=6, param_val: [TIBERO]
03/10 11:18:44.4 0-0092 tbsvr_sq:277 SQL PROCESS: sql=create table ttt(a) as select * from dual, csr=5
03/10 11:18:44.4 0-0092 ddl_fram:228 Executing DDL: create table ttt(a) as select * from dual
03/10 11:18:44.4 0-0092 tx_sgmt.:2490 sgmt create: ts=3 sgmt=5083 type=1 extsz=16 blksz=8192
03/10 11:18:44.4 0-0092 tx_sgmt.:1980 sgmt creation completed. ts=3 sgmt=5083 type=1 extsz=16, extdba=02_00000215
03/10 11:18:44.4 0-0092  td_dp.c:447 init dp start: tsid=3, shdrdba=02_00000217, sgmt_id=5083, initrs=2, pctf=819, mexts=4294967295, aalloc=1, log=1, ifb=0, sup=0, dtf=0 pll=0 o_sync=0
03/10 11:18:44.4 0-0092  td_dp.c:495 init dp end: tsid=3, shdrdba=02_00000217, sgmt_id=5083, initrs=2, pctf=819, mexts=4294967295, aalloc=1, log=1, ifb=0, sup=0, dtf=0 pll=0 o_sync=0
03/10 11:18:44.4 0-0092 tc_fdpoo:654 fdelem alloc 00007f7dd18bf468
03/10 11:18:44.4 0-0092 ddl_fram:267 DDL execution succeeded
03/10 11:18:47.2 0-0092 tbsvr_sq:277 SQL PROCESS: sql=insert into ttt values('T'), csr=30
03/10 11:18:51.0 0-0092 tbsvr_sq:277 SQL PROCESS: sql=commit, csr=33
 
 
Command for real-time verification
 $ tbiv -f {filename}.ilg

 

3. Disable ILOG TAG

After completing the work, disable the tags that were enabled.
$ tbsql sys/tibero
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';
Caution
Enabling ILOG TAGs generates a large amount of logs, which may cause performance degradation or increased disk usage. Therefore, they must be disabled after use.
 
Be cautious when using this in a production environment, especially on systems with high loads.
 
 
 

Related to