SP Calls to remote DB returning SQLSTATE 466 on fetchAll()

[krisbaehr]

We're trying to call Stored Procedures when connecting to a remote system (DRDA) instead of *LOCAL. We're using prepare(), bind(), execute(), fetchAll(). The execute() works and am getting the expected return parameter (it's correctly being changed). But the call to the fetchAll() is returning SQLSTATE 466 and no result set is available.

This same call works fine when connecting to the *LOCAL DB.

[dmabupt]

Hello @krisbaehr , SQLSTATE 466 should be a successful indicator. Would you enable the debug mode and collect the output? Then I can identify in which API the 466 code is returned. And if you can show me the Stored Procedure, that would be more helpful.

SQLSTATE Value	Meaning	SQLCODE Values
0100C	One or more ad hoc result sets were returned from the procedure.	+466

[krisbaehr]

@dmabupt Here is some additional info. I can't provide the code for the SP, but any SP call that returns a result set is problematic when connecting to a remote db.

SP call

const {
    dbconn, dbstmt, INOUT, IN, OUT, CHAR, CLOB,
  } = require('idb-connector');

  const sql = 'CALL LIB.MYSP(?,?)';
  const connection = new dbconn();
  connection.conn('remotedb', 'user', 'pass');
  const statement = new dbstmt(connection);
  connection.debug(true);

  const gAct = 'RD';
  const srLocn = 'RRT';

  statement.prepare(sql, () => {
    statement.bindParam([
      [gAct, INOUT, CHAR],
      [srLocn, IN, CHAR],
    ], () => {
      statement.execute((out, err) => {
      if (err) {
        console.error(err);
      } else {
          for (let i = 0; i < out.length; i += 1) {
            console.log(out[i]);
          }
          statement.fetchAll((result, err) => {
            if (result) {
                  console.log(`Result is : ${JSON.stringify(result)}`);
            } else {
                console.log(`Error is :`);
                console.error(err);
            }
            statement.close();
            connection.disconn();
            connection.close();
          });
       }
      });
    });
  });

PuTTY Output OK Error is : [Error: SQLSTATE=HY010 SQLCODE=-99999 Error occurred in SQL Call Level Interface ] SQLDisconnect: conn obj [18034c4f0] handler [2] SQLFreeConnect: conn obj [18034c4f0] handler [2] SQLFreeConnect[0]

Note

• SQL Performance Monitor tells me that the sql state was 466, I'd like to be able to get this out of idb connector instead of this HY010 without having to do anything special.

[dmabupt]

Unfortunately I still can not recreate the issue by now. If you have XMLSERVICE installed, you could try this script --

const {
  dbconn, dbstmt, IN, OUT, CHAR, CLOB,
} = require('idb-connector');

const sql = 'CALL QXMLSERV.iPLUGR512K(?,?,?)';
const connection = new dbconn();
connection.conn('SS1BLD1', 'XUMENG', 'PASSWORD');
const statement = new dbstmt(connection);

const ipc = '*NA';
const ctl = '*here';
const xmlIn = `<xmlservice><sh>system 'wrksbs'</sh></xmlservice>`;

statement.prepare(sql, () => {
  statement.bindParam([
    [ipc, IN, CHAR],
    [ctl, IN, CHAR],
    [xmlIn, IN, CLOB]
  ], () => {
    statement.execute(() => {
      statement.fetchAll((result) => {
        console.log(result);
        statement.close();
        connection.disconn();
        connection.close();
      });
    });
  });
});

The result set should be retrieved correctly from the remote database.

And would you show me more debugging log please? The number in the parentheses indicates the first failed API call. For example, SQLConnect(0), SQLPrepare(-1) ... Like below --

SQLConnect(0): conn obj [180444350] handler [2]
Prepare().
SQLPrepare(-1): CALL QXMLSERV.iPLUGR512K(?,?,?,?)

 **** ERROR *****
SQLSTATE: 42884
Native Error Code: -440
Routine IPLUGR512K in QXMLSERV not found with specified parameters. 

BindParamAsync().
SQLDescribeParam(-1)
Execute().
SQLExecuteAsync(-1):
FetchAllAsync()

[krisbaehr]

Hey, @dmabupt. IBM is working on a fix for this that was (I believe) recently introduced. If fixed, I'll let you know.

[krisbaehr]

@dmabupt We were able to apply IBM's fix for this today and it works. Thanks!

[abmusse]

@krisbaehr

Can you describe what the fix was?

Would like to document this for future reference.

[krisbaehr]

@abmusse I'm not entirely sure what IBM's fix was. I think it had something to do with a cursor getting closed too early, but couldn't say for sure.