NodeJS 10 -- IDB Connector 1.1.9 and SQL Functions

[mikebohlen]

We have discovered and issue when calling a Function on a remote DB using DRDA that if we fully qualify the call then the function will run just fine. However, if we do not then we get an SQL0204 in the JobLog as follows:

in LIBL type N was not found.... After some testing we discovered this issue is not present when connecting to a LOCAL DB and only applies when we are using a Remote DB. All of our functions are affected by this issue. I have also checked the LIBL and auth.

[markdirish]

This really sounds like an issue with your local connection using *LOCAL to create a connection, which (I believe) uses the library list for the user that is running the job. Since your remote connection won't have access to *LOCAL, you would pass a username and password, and it might not be setting the library list to search through correctly...

Do you have any code that you could share, like just the snippets where you are connecting to the database (removing any sensitive information!)?

[mikebohlen]

sure, I can get that shortly,

I checked the LIBL of the remote DRDA job and it is correct, also this only applies to Functions, SPs, DB2 tables and views are unaffected.

[mikebohlen]

Oh, and the Functions, and SPs are in the same library.

[mikebohlen]

let connection = new IdbConnection.IDB.dbconn();

                //The connection properties don't appear to matter. It uses the id/pw of who ever starts the server.
                connection.conn(process.env.dbName, process.env.dbId, process.env.dbPw);
                connection.setConnAttr(IdbConnection.IDB.SQL_ATTR_DBC_SYS_NAMING, IdbConnection.IDB.SQL_TRUE);

[markdirish]

Hi @mikebohlen,

Could you reformat that last post? When you reply in an email, it sends all of the email chain to the GitHub issue and it makes it difficult to parse. It is probably a better idea to reply directly on GitHub so your email chains and email signatures are not plastered on this public forum. I have gone through your previous posts and removed the email signature.

[mikebohlen]

Just curious if you have found anything on this?

[dmabupt]

Hello @mikebohlen , Would you tell us the Function name of your project? I tried to call QSYS2.MEMORY_POOL() on both local and remote system. Would you confirm that?

const {dbconn, dbstmt} = require('idb-connector');

const sSql = 'SELECT * FROM TABLE(QSYS2.MEMORY_POOL()) X';
const connection = new dbconn();
// connection.conn('*LOCAL');
connection.conn('SS1BLD1', 'username', 'password');
const statement = new dbstmt(connection);

statement.exec(sSql, (x) => {
  console.log(JSON.stringify(x));
  statement.close();
  connection.disconn();
  connection.close();
});

[krisbaehr]

@dmabupt I've tested your code above and the function is found for both *LOCAL and a remote DB. I also removed the QSYS2 library qualifier and surprisingly it worked again both ways.

Then I modified the sql statement to call one of our own functions (SELECT getMake(4864) FROM sysibm.sysdummy1) and called it with a remote db. The result is:

node server.js
SQLExecDirect(-1): SELECT getMake(4864) FROM sysibm.sysdummy1

** ERROR ***
SQLSTATE: 42704
Native Error Code: -204
GETMAKE in LIBL type N not found.
null
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
SQLDisconnect: conn obj [1803454f0] handler [2]
SQLFreeConnect: conn obj [1803454f0] handler [2]
SQLFreeConnect[0]

But by qualifying the library it works fine: node server.js
SQLExecDirect(0): SELECT dunc.getMake(4864) FROM sysibm.sysdummy1
SQLNUMRESULTSCOLS(0) Column Count = 1
SQLDescribeCol(0) index[0] sqlType[1] colScale[0] colPrecise[10] [{"00001":"TEXTRON "}]
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
SQLDisconnect: conn obj [1803454f0] handler [2]
SQLFreeConnect: conn obj [1803454f0] handler [2]
SQLFreeConnect[0]

`const idb = require('idb-connector');

//const sSql = 'SELECT * FROM TABLE(MEMORY_POOL()) X'; const sSql = 'SELECT dunc.getMake(4864) FROM sysibm.sysdummy1';

const connection = new idb.dbconn();

// connection.conn('*LOCAL'); connection.conn('REMOTE', '', ''); connection.setConnAttr(idb.SQL_ATTR_DBC_SYS_NAMING, idb.SQL_TRUE);

connection.debug(true); const statement = new idb.dbstmt(connection);

statement.exec(sSql, (x) => { console.log(JSON.stringify(x)); statement.close(); connection.disconn(); connection.close(); });`

[krisbaehr]

We did a little bit more testing because it sounded to us like a library list issue but the library list is correct. We also tried moving DUNC to the SYS portion of the library list (like QSYS2) but we ran into the same error.

[abmusse]

We did a little bit more testing because it sounded to us like a library list issue but the library list is correct.

Hello @krisbaehr

I was just about to say this sounds like a LIBL issue.

Once connected to the remote db, did you run the following to view the current library list?

statement.exec('SELECT * from qsys2.library_list_info', (result, error) => {
    if (error){
        throw error;
    }
    console.log(result)
});

[ThePrez]

For library list to work, you may need to turn on system naming (SQL_ATTR_SYS_NAMING). Or, is that on by default, @abmusse?

[krisbaehr]

@ThePrez I am setting that: connection.setConnAttr(idb.SQL_ATTR_DBC_SYS_NAMING, idb.SQL_TRUE);

[krisbaehr]

@abmusse I ran that statement to get the library list info and it is in the list... {"ORDINAL_POSITION":"13","SCHEMA_NAME":"DUNC","SYSTEM_SCHEMA_NAME":"DUNC","TYPE":"USER","IASP_NUMBER":"0","TEXT_DESCRIPTION":"SOME DESCRIPTION"}

[ThePrez]

Wow, sorry for the red herring. Not sure how I missed that you were already setting system naming. I wonder if system naming isn't being propagated to the remote db (either by design or defect)

[dmabupt]

For library list to work, you may need to turn on system naming (SQL_ATTR_SYS_NAMING). Or, is that on by default, @abmusse?

No, we only enabled autocommit by default -> https://github.com/IBM/nodejs-idb-connector/blob/master/src/db2ia/dbconn.cc#L49

@krisbaehr Would you try this workaround -> https://github.com/worksofliam/nodejs-idb-pconnector/blob/setlibl2/lib/connection.js#L178

[mikebohlen]

Just curious if you found anything further on this. It appears to be a bug.

[krisbaehr]

@dmabupt I tried to set the library list as in your example above and the SQL UDFs are still not found. I also tried executing addlible but it already existed in the library list.

Why would our custom SQL functions behave any differently than system provided functions?

FYI, our tables are being found through the library list, just not the UDFs.

[krisbaehr]

I ran another quick test and our Stored Procedures are NOT being found if unqualified.

[ThePrez]

@krisbaehr, I ran this by some database experts who suggested a PMR to IBM to verify your Db2 environment/attributes. For now, we can leave this issue open until you get to a resolution.

[krisbaehr]

@ThePrez Okay, thanks!

[krisbaehr]

@ThePrez We heard back from IBM and the piece we were missing was calling the 'SET PATH' SQL statement. Calling "SET PATH 'DUNC'" after creating connections worked for us. We'll be adding other libraries to the list too. This fixes calls to both functions and stored procedures. Are there any examples or documentation that need to be provided/updated?

As far as we're concerned this can be closed.

[ThePrez]

Nice!! @abmusse/@dmabupt, can you please update doc where it makes most sense?

[abmusse]

@ThePrez @krisbaehr

How about adding a note within the dbconn.conn method?

Something like:

NOTE

To access functions and stored procedures without fully qualifying ensure to set your path.

SET PATH MYSCHEMA

[krisbaehr]

@ThePrez @krisbaehr

How about adding a note within the dbconn.conn method?

Something like:

NOTE

To access functions and stored procedures without fully qualifying ensure to set your path.

SET PATH MYSCHEMA

Perfect

[kadler]

https://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html

The initial value of the SQL path depends on the naming convention that is used for the first SQL statement within an activation group.

I suppose that the problem here is that the SQL PATH is set during the first CALL QSYS2.QCMDEXC('ADDLIBLE ...'), so it's missing all the libraries that are being added to the library list. If we had a built-in way to set the library list outside of using SQL to call ADDLIBLE, it would be "seamless".

[kadler]

Hmm, I missed some nuance here:

If the System naming convention was used for the first SQL statement, the initial value for the SQL path is set to the special value, *LIBL.

So perhaps the problem is that SQL is being run before the naming format is changed (or perhaps the rule spelled out there is not exactly right, but right most of the time enough to seem true). It'd be interesting to see if there was a way to set the naming format during the connect() if that made this work right.

[worksofliam]

Just going to mention this here:

    statement = new Statement(conn.connection);
    await statement.exec(`SET PATH = ` + libraryList.join(', '));
    await statement.close();

[kadler]

@worksofliam If you want to use the library list, you can just set it to *LIBL as well