SQL1822N error when you run a query in Data Virtualization

When you try to run a query, you get an error with code SQL1822N.

Symptoms

When you run a query, you receive an error message with code SQL1822N and the following content.

SQL1822N Unexpected error code "GDB_ERROR" received from data source
"DV-FMP". Associated text and tokens are "Query failed at some sources. 
Check remote warnings". SQLSTATE=560BD

Causes

You get this message when the query contains an error in at least one of the data sources involved in the query execution.

Resolving the problem

To try to resolve the problem, follow these steps.

Retrieve the full error message by querying the LISTREMOTEWARNINGS catalog view.

Option 1 To retrieve all warnings in the last hour, use the following query.

SELECT NODE_NAME, 
       TSTAMP, 
       CAST(WARNING AS VARCHAR(250)) 
    FROM DVSYS.LISTREMOTEWARNINGS 
    WHERE TIMESTAMPDIFF(8, CURRENT TIMESTAMP - TSTAMP) <= 1 ORDER BY TSTAMP

Option 2 To retrieve all warnings, use the following query.
```
SELECT * FROM DVSYS.LISTREMOTEWARNINGS;
```

Identify the root cause of the problem. The full remote error message is in the "WARNING" column. Use this column to identify and correct the root cause. If you used option 1 to review all warnings in the last hour, the warning might be truncated due to the CAST specification. Increase the size of the varchar() or remove the cast to see the full message.

Example 1: The message indicates that you are unable to connect to the remote data source.

qpendpoint_1:6415   2021-04-27-08.43.59.428000000 
RESULT_DS_EXEC_QUERY_ERROR: Query exec failure against Data Source 
GDB_GTW_TPCDS_CATALOG_RETURNS_DB210001: java.lang.Exception: 
Unreachable RDBMS in Data Source: 
GDB_GTW_TPCDS_CATALOG_RETURNS_DB210001

You can take the following actions.

Check whether the user and password definitions are up to date on the Data Virtualization connection.
Verify that the remote data source is running.
Check that network connectivity between the Data Virtualization instance and the remote data source exists.

Example 2: The message indicates that some queries with row_number() over() or rownumber() over() fail.

fmp_1 2022-06-17-02.49.56.313000000 2022-06-17-02.49.56.313000000 1 
RESULT_DS_EXEC_QUERY_ERROR: Origin: 3 node(s), 0/1 sources (0 successful, 0 excluded, 1 failed, 0 missing) 
Warnings (1) >"GDB_NODE yanlixu_yanlixuiptt8x86_Endpoint1:60009 QUERYING SOURCE Netezza VIA GDB_SUBQ_NETEZ10000: 
Unable to get physical meta data for table: (SELECT ROW_NUMBER() OVER( ) C0 FROM ""TEST"".""TESTNUMBER"" A0) 
SUBQ : org.netezza.error.NzSQLException: ERROR: ROW_NUMBER must include ORDER BY specification

"-1822: Remote Exception: Origin: 3 node(s), 0/1 sources (0 successful, 0 excluded, 1 failed, 0 missing) 
Warnings (1) >"GDB_NODE yanlixu_yanlixuiptt8x86_Endpoint1:60009 QUERYING SOURCE Netezza VIA GDB_SUBQ_NETEZ10000: 
Unable to get physical meta data for table: (SELECT ROW_NUMBER() OVER( ) C0 FROM ""TEST"".""TESTNUMBER"" A0) SUBQ : 
org.netezza.error.NzSQLException: ERROR: ROW_NUMBER must include ORDER BY specification

yanlixu_yanlixuiptt8x86_Endpoint1:60009 2022-06-17-02.49.56.298000000 2022-06-17-02.49.56.298000000 1 
RESULT_DS_EXEC_QUERY_ERROR: Query exec failure against Data Source GDB_SUBQ_NETEZ10000: java.lang.Exception: 
Unable to get physical meta data for table: (SELECT ROW_NUMBER() OVER( ) C0 FROM "TEST"."TESTNUMBER" A0) SUBQ : 
org.netezza.error.NzSQLException: ERROR: ROW_NUMBER must include ORDER BY specification

You can take the following action.

Add an order by clause for this function as shown in this example.

select row_number() over(order by $columnname) from <table_name>

Example 3: The message indicates that some queries fail in a Data Virtualization Manager data source.

qpendpoint_3:6417    2022-08-31-08.31.55.624000000 2022-08-31-08.31.55.624000000                    1 
ENGINE_STATEMENT_PREPARE_ERROR_SQL: GDB_SUBQ_DVM10000 
Unable to PREPARE statement - (empty result for this data source): 
Unable to get physical meta data for table: (SELECT A0.C1 C0, A0.C8 C1 FROM DVSQL.CB1A_TSDCV_TESTNUMBER A0) SUBQ : 
java.sql.SQLException: [DV][JDBC Driver][3.1.202201120442] [DV][JDBC Driver][3.1.202201120442] 
Unexpected end of input stream java.net.SocketInputStream@30edda80 
(read 0 out of an expected 4 bytes), while executing userSql=select * from (SELECT A0.C1 C0, A0.C8 C1 FROM DVSQL.CB1A_TSDCV_TESTNUMBER A0) SUBQ where 0=1 - 'call listrdbc()' to identify the data source.

To resolve issues with query performance, see Performance Tuning and Query Optimization.

This issue might also be caused by the configuration of the PASSTHROUGH parameter on the Data Virtualization Manager server. To request changes to this parameter, open a case to request support.

Example 4: The message indicates that the query is against a table that has columns with the type PERIOD on a Teradata data source.

Change the query by using the optimizer guidelines for Teradata.

For example, a table with the following specification

bluadmin.testtera (C1 int, C2
interval year, C3 interval day to hour, C4 interval minute, C5 PERIOD(DATE), C6
PERIOD(TIME))

is queried.

select c5 from bluadmin.testtera

This query can be changed as shown in the following example.

select c5 from bluadmin.testtera 
/* <OPTGUIDELINES> <SERVER NAME='QPLEX'> <OPTION NAME='collating_sequence_dv' VALUE='TERAD10001:N'/> </SERVER> </OPTGUIDELINES> */

Where TERAD10001 is the CID for table testtera.

Example 5: The message indicates that a remote data source has been removed from Data Virtualization, but the existing tables are kept when you attempt to create a view or access the remote tables.

Failed to get join result.:
Unexpected error code "GDB_ERROR" received from data source "DV-FMP". Associated text and tokens are "Query failed at some sources. Check remote warnings".. SQLCODE=-1822, SQLSTATE=560BD, DRIVER=4.32.28