17.00 - Example: Using QryLogParamV - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

This example assumes the PARAMINFO option is enabled in the BEGIN/REPLACE QUERY LOGGING statement. The following SELECT statement converts all rows in the DBC.DBQLParamTbl table to a JSON document:

SELECT QueryID, RowNum, ParamJSON from QryLogParamV;

Result:

QueryID            RowNum ParamJSON
------------------ ------ ---------------------------------------------
307192920408671138 1      {"QueryID":"307192920408671138","HostCharSet":"127","ParamInfo":
                          [{"Name":"xABc","Type":"INTEGER","Size":4,"Position":1},
                          {"Name":"yBflt","Type":"REAL","Size":8,"Position":2},
                          {"Name":"zCDbl","Type":"REAL","Size":8,"Position":3},
                          {"Name":"fxStr","Type":"CHAR","Size":20,"Position":4},
                          {"Name":"varStr","Type":"VARCHAR","Size":25,"Position":5},
                          {"Name":"fxByte","Type":"BYTE","Size":4,"Position":6},
                          {"Name":"vrByte","Type":"VARBYTE","Size":25,"Position":7},
                          {"Name":"nmbr","Type":"NUMBER","Size":18,"Position":8},
                          {"Name":"dcml","Type":"DECIMAL","Size":8,"Position":9},
                          {"Name":"dt","Type":"DATE","Size":4,"Position":10},
                          {"Name":"ts","Type":"CHAR","Size":26,"Position":11},
                          {"Name":"blb","Type":"BLOB","Size":60,"Position":12},
                          {"Name":"clb","Type":"CLOB","Size":60,"Position":13},
                          {"Name":"intrvl","Type":"CHAR","Size":5,"Position":14},
                          {"Name":"tme","Type":"CHAR","Size":15,"Position":15}]}
307192920408671138 2      {"QueryID":"307192920408671138","Data
                          Record":{"xABc":"1","yBflt":"+5.78000000000000E000",
                          "zCDbl":"+9.86700000000000E-001","fxStr":null,
                          "varStr":"Test Var String 01","fxByte":"00005AB1",
                          "vrByte":"5ABCFE6789EFBCAB5EF0","nmbr":"1234.679","dcml
                          ":"54328567.45","dt":"2013/09/10","ts":"2013-09-10   
                          10:41:32.000000","blb":"BAABBCCDDEEFF123456789AABBCCDDEEFF",
                          "clb":"This is a CLOB column string 01","intrvl":" 7859",
                          "tme":"10:56:35.000000"}}