15.00 - TD_DBQLParam - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

TD_DBQLParam

Purpose  

Converts BLOB data into a CLOB of JSON format document with 1 to M mapping to each row in DBC.DBQLParamTbl.

Syntax  

where:

 

Syntax element …

Specifies …

TD_SYSFNLIB

the name of the database where the function is located.

DBC.DBLParamTbl.QueryID

the QueryID column in the following table: DBC.DBQLParamTbl.

DBC.DBQLParamTbl.paraminfo

the BLOB column in the following table: DBC.DBQLParamTbl.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Invocation

TD_DBQLParam is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

Argument Types

TD_SYSFNLIB.TD_DBQLParam takes two input arguments and produces, as output, 3 table columns.

The following help statement provides detailed description of the function argument and output parameters.

help function TD_SYSFNLIB.TD_DBQLParam;
 

Parameter Name

Type

Comment

QID

D

Input parameter QueryID from DBC.DBQLParamTbl.QueryID

data

BO

Input parameter BLOB data from DBC.DBQLParamTbl.ParamInfo

QueryID

D

Output table column #1 (Foreign Key) System wide unique value to join DBQL tables.

RowNum

I

Output table column #2 Row number indicating the sequence of ParamInfo and Data Record JSON documents.

ParamJSON

CO

Output table column #3 Parameter and data rows in JSON format.

where:

D = Decimal(18,0)
BO = BLOB
I = Integer
CO = CLOB

Result Type

TD_DBQLParam returns a JSON type document for each data parcel in the supplied BLOB data. If, for example, a request had 1 using row, the resulting number of JSON documents would be 1+1. If a request had n using rows, there would be n +1 JSON documents.

Usage Notes

This function is activated as part of DIPSYSFNC, the DIP script executed when DIP ALL is executed. But the DIP script does not grant rights to TD_DBQLParam. DBC will have to be explicitly granted rights. For example:

GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.TD_DBQLParam TO DBC WITH GRANT OPTION;

For queries that have parameter markers/positional parameters with question marks (?) in the query, variable names are now replaced with proper names only in DBQL’s ParamInfo logging to P1, P2 ... Pn. The numbering 1 to n stands for these parameter position from left to right as they appear in the query text.

Example  

The following query returns a JSON document for each using row data parcel in:

  • The BLOB column paraminfo
  • The associated QueryID that maps into DBC.DBQLogTbl's QueryID.
  •  
    SELECT JsonTbl.QueryID (FORMAT '--Z(17)9'), JsonTbl.RowNum, JsonTbl.ParamJSON  from table (TD_SYSFNLIB.TD_DBQLParam(DBC.DBQLParamTbl.QueryID,DBC.DBQLParamTbl.paraminfo)) as JsonTbl order by JsonTbl.QueryID, JsonTbl.RowNum;
     

    Output of a Request with 1 Using Row

     

    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 String01","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"}}