Software User guide

Retrieving Multirow Queries
Queries that are longer than 3200 bytes are broken into multiple rows. Three fields support
retrieving the complete text of these kinds of queries:
• QUERY_TEXT_IS_MULTIROW indicates whether multiple rows are required to
accommodate the text.
• SEQUENCE_SIZE gives the number of rows required to accommodate the text.
• SEQUENCE_NUM assigns sequential numbers to the rows.
Thus, the following query will retrieve the last 10 multirow entries:
SELECT [first 10]
left(query_id,95) as query_id1,
query_start_date
query_start_time,
sequence_num as seq_num,
sequence_size as seq_size,
statement_state,
query_text_is_multirow as multi_row,
left(sql_text,50) as sql_text1
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE query_text_is_multirow = 'Y'
ORDER BY QUERY_START_DATE DESC, QUERY_START_TIME DESC, SEQUENCE_NUM ASC;
This next query assumes that you know the specific QUERY_ID of interest:
SELECT [first 10]
left(query_id,95) as query_id1,
query_start_date,
query_start_time,
sequence_num as seq_num,
sequence_size as seq_size,
statement_state,
query_text_is_multirow as multi_row,
left(sql_text,50) as sql_text1
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_ID = 'MXID01001000969212041869954468496000000000214SUPER.SERVICES00_638_SQL_CUR_3053'
ORDER BY QUERY_START_DATE DESC, QUERY_END_DATE DESC, SEQUENCE_NUM ASC;
Retrieving UCS2 Columns
As indicated in Chapter 2 (page 17), if the Neoview platform is configured for the SJIS or Unicode
configuration, certain Repository views contain UCS2–encoded data. To retrieve such data (for
example, to SELECT by making a character comparison) from an ISO8859-1 workstation, you
must translate the character literals in your SQL statement to UCS2.
For example, if you issued the following statement from an ISO8859-1 workstation to retrieve
rows in which a specified column had the value 'abc'
Select * from <ucs2-view> where <column-name> = 'abc';
the query would fail with the following errors:
***ERROR[4041] Type CHAR(16) CHARACTER SET UCS2 cannot be compared with type CHAR(3) CHARACTER SET ISO88591.
***ERROR[8822] The statement was not prepared.
To perform the comparison and select the data successfully, you could use the _UCS2 character
set introducer, as in the following example:
Select * from <ucs2-view> where <column-name> = _UCS2'abc';
The _UCS2 introducer also works with hexadecimal character strings, as in the following example:
Select * from <ucs2-view> where <column-name> = _UCS2 X'0041 004E 0053 0049 0020 0054 0041 0042 004C 0045 005F
0030';
The TRANSLATE function is another option for specifying a UCS2 literal. For example, the
following query uses the TRANSLATE function to translate an ISO88591 character string to
UCS2:
Select * from <ucs2-view> where <column-name> = TRANSLATE('ISO_abc' USING ISO88592ToUCS2);
The next example translates a hexadecimal literal to UCS2:
Select * from <ucs2-view> where <column-name> = TRANSLATE(X'48 45 58 5F 61 62 63' USING ISO88592ToUCS2);
Retrieving Multirow Queries 73