15.10 - QryLogParamJSON - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

QueryID

DECIMAL(18,0)

NOT NULL

--Z(17)9

DBQLParamTbl.QueryID

RowNum

INTEGER

--,---,---,--9 (explicit)

None

ParamJSON

JSON

NOT NULL

X(64000)

DBQLParamTbl.ParamInfo

This view provides access to parameter and metadata information in Teradata JSON UDT type.

This view contains confidential user information and should be accessed only by trusted personnel with access to the restrictive user DBC password. For more information about the user DBC password, see Security Administration.

To populate the columns in the QryLogParamJSON view, you must enable the PARAMINFO option in the BEGIN/REPLACE QUERY LOGGING statement. For more information about this option, see SQL Data Definition Language.

RowNum

This column exists only in the QryLogParamJSON view, which is generated by the TD_SYSFNLIB.TD_DBQLParam table operator function at runtime. For more information about this function, see SQL Functions, Operators, Expressions, and Predicates.

ParamJSON

This column is generated by the TD_SYSFNLIB.TD_DBQLParam table operator function at runtime. This column converts the parameter values and metadata logged to the DBC.DBQLParamTbl.ParamInfo table column in Teradata JSON UDT type. For more information about this TD_SYSFNLIB.TD_DBQLParam table operator function, see SQL Functions, Operators, Expressions, and Predicates.

For more information about the DBC.DBQLParamTbl.ParamInfo table column, see Database Administration.

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

Run this query from BTEQ with width set to 500.

.width 500;
select QueryID, RowNum, ParamJSON from QryLogParamJSON;

The query returns the following results:

QueryID                RowNum        ParamJSON
--------------------   -----------   ------------------------------------------------
307190733539634351     1             {"QueryID":"307190733539634351","HostCharSet":"127",
                                      "ParamInfo":[{"Name":"x","Type":"INTEGER","Size":4,"Position":1}]}
307190733539634351     2             {"QueryID":"307190733539634351","Data Record":{"x":"10"}}

This example returns the PARMINFO information for each parameter in the JSON document in a row. This returns the name, type, position, and value for each parameter in the parameterized request.

select QueryID, RowNum, ParamJSON.ParamInfo from QryLogParamJSON;

The query returns the following results:

QueryID                RowNum        ParamJSON.ParamInfo
--------------------   -----------   ----------------------------------------------------
307190733539634351     1             [{"Name":"x","Type":"INTEGER","Size":4,"Position":1}]
307190733539634351     2             ?

This example selects particular name/value pairs from the JSON document using JSON string syntax. If a row does not have a specific name requested, null is returned.

select QueryID, RowNum, ParamJSON."Data Record" from QryLogParamJSON;

The query returns the following results:

QueryID                RowNum        ParamJSON.Data Record
--------------------   -----------   ---------------------------------------------
307190733539634351     1             ?
307190733539634351     2             {"x":"10"}

This example selects specific values from the JSON name/value pair using JSON string syntax. If a row does not have a specific name requested, null is returned.

select QueryID, RowNum, ParamJSON."Data Record".x from QryLogParamJSON;

The query returns the following results:

QueryID                RowNum        ParamJSON.Data Record.x
--------------------   -----------   ---------------------------------------------
307190733539634351     1             ?
307190733539634351     2             10