Re: [IBMiOSS] Using Nodev6 db2i/itoolkit

Aaron,

We are trying to figure out what our system is missing that is causing
this. We didn't have "XML Toolkit" and installed that, but it still is not
working. In the job log I noticed it said "ensure that the DB2 Multisystem
product is installed on your system", so our admin is going to check that
after a client visit in the morning and maybe see if that might be the
issue.

Do you have any idea what we might be missing? Below is a copy of the
message in the job log.

From user . . . . . . . . . : KEVIN
From module . . . . . . . . : QSQPROC
From procedure . . . . . . : CLEANUP

Statement . . . . . . . . . : 54610
To module . . . . . . . . . : QSQPROC
To procedure . . . . . . . : CLEANUP
Statement . . . . . . . . . : 54610
Message . . . . : IPLUG512K in QXMLSERV type *N not found.
Cause . . . . . : IPLUG512K in QXMLSERV type *N was not found. If the member
name is *ALL, the table is not partitioned. If this is an ALTER TABLE
statement and the type is *N, a constraint or partition was not found. If
this is not an ALTER TABLE statement and the type is *N, a function,
procedure, trigger or sequence object was not found. If a function was not
found, IPLUG512K is the service program that contains the function. The
function will not be found unless the external name and usage name match
exactly. Examine the job log for a message that gives more details on which
function name is being searched for and the name that did not match.
Recovery . . . : Change the name and try the request again. If the
object is a node group, ensure that the DB2 Multisystem product is installed
on your system and create a nodegroup with the CRTNODGRP CL command. If an
external function was not found, be sure that the case of the EXTERNAL NAME
on the CREATE FUNCTION statement exactly matches the case of the name
exported by the service program.

On Fri, Mar 30, 2018 at 11:15 AM, Aaron Bartell <aaronbartell@xxxxxxxxx>
wrote:

Hi Kevin,

There's something erroring when it runs the SQL (i.e. "SQLExecute(-1)") on
your system. Maybe check QSYSOPR for messages and also check WRKACTJOB for
issues.

I run the following example successfully.

var xt =
require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/xstoolkit/lib/itoolkit')
var conn = new xt.iConn("*LOCAL")
conn.debug(true)
conn.add(xt.iSh("system -i 'DSPSYSSTS'"))
conn.run((str) => {
console.log(str)
})


============
INPUT XML
============
<?xml version='1.0'?><myscript><sh error='fast'>system -i
'DSPSYSSTS'</sh></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [309d85b0] handler [2]
PrepareAsync().
undefined

SQLPrepare(0): call QXMLSERV.iPLUG512K(?,?,?,?)

BindParamAsync().
SQLBindParameter [1] = undefined
SQLBindParameter [2] = undefined
SQLBindParameter [3] = <?xml version='1.0'?><myscript><sh
error='fast'>system -i 'DSPSYSSTS'</sh></myscript>
SQLBindParameter [4] =
ExecuteAsync().
SQLExecute(0):
<?xml version='1.0'?><myscript><sh error='fast'>
System Status Information
Page 1
5770SS1 V7R3M0 160422
KT4001 03/30/18 12:00:54 EDT
% CPU used . . . . . . . . . . . . . . : 7.5 System
ASP . . . . . . . . . . . . . . : 534.4 G
Elapsed time . . . . . . . . . . . . . : 00:00:01 %
system ASP used . . . . . . . . . . : 30.3280
Jobs in system . . . . . . . . . . . . : 268 Total
aux stg . . . . . . . . . . . . : 534.4 G
% perm addresses . . . . . . . . . . . : .009 Current
temporary used . . . . . . . . : 8149 M
% temp addresses . . . . . . . . . . . : .093 Peak
temporary used . . . . . . . . . : 8468 M
Sys Pool Reserved Max -------DB------- -----Non-DB-----
Act-> Wait-> Act-> Paging
Pool Size M Size M Act Faults Pages Faults Pages
Wait Inel Inel Pool Subsystem Option
1 1122.69 577.32 +++++ .0 .0 .0 .0
52.2 .0 .0 *MACHINE *FIXED
2 4345.49 7.56 102 .0 .0 1.7 1.7
13954.7 .0 .0 *BASE *CALC
3 614.37 .00 154 .0 .0 .0 .0
.0 .0 .0 *INTERACT *CALC
4 61.43 .00 5 .0 .0 .0 .0
.0 .0 .0 *SPOOL *CALC
* * * * * E N D O F L I S T I
N G * * * * *
</sh>
</myscript>
SQLDisconnect: conn obj [309d85b0] handler [2]


Aaron Bartell
IBM i hosting, starting at $157/month. litmis.com/spaces


On Fri, Mar 30, 2018 at 11:02 AM, Kevin Nunn <nunn.kevin@xxxxxxxxx> wrote:

Hi Aaron,

I changed that and ended up with the exact same error.

[bash]$node cltest.js
CL statement : 'DSPSYSSTS'
============
INPUT XML
============
<?xml version='1.0'?><myscript><sh error='fast'>system -i
'DSPSYSSTS'</sh></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [309d29f0] handler [2]
PrepareAsync().
SQLPrepare(-1): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter [1] = undefined
SQLBindParameter [2] = undefined
SQLBindParameter [3] = <?xml version='1.0'?><myscript><sh
error='fast'>system -i 'DSPSYSSTS'</sh></myscript>
SQLBindParameter [4] =
ExecuteAsync().
SQLExecute(-1):
undefined:0



Error: SQLSTATE=PAERR SQLCODE=-1 SQLDescribeParam() failed.
at Error (native)


On Fri, Mar 30, 2018 at 7:39 AM, Aaron Bartell <aaronbartell@xxxxxxxxx>
wrote:

Hi Kevin,

Try the below. Note I added single quotes inside the double quotes.

var cl = "'DSPSYSSTS'";


Aaron Bartell
IBM i hosting, starting at $157/month. litmis.com/spaces


On Thu, Mar 29, 2018 at 3:40 PM, Kevin Nunn <nunn.kevin@xxxxxxxxx>

wrote:

Here is a simplified version of the CL attempted, along with debug

output.

var xt =
require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/

xstoolkit/lib/itoolkit');

var DBname = "*LOCAL";
var userId = "user";
var passwd = "pass";
var cl = "DSPSYSSTS";

console.log("CL statement : " + cl);
var conn = new xt.iConn(DBname, userId, passwd);
conn.debug(true);
conn.add(xt.iSh("system -i " + cl));
function cb(rs) {console.log(str);}
conn.run(cb);

------------------------------------------------------
[bash]$node cltest.js
CL statement : DSPSYSSTS
============
INPUT XML
============
<?xml version='1.0'?><myscript><sh error='fast'>system -i
DSPSYSSTS</sh></myscript>
============
OUTPUT XML
============
SQLConnect: conn obj [309d2e50] handler [2]
PrepareAsync().
SQLPrepare(-1): call QXMLSERV.iPLUG512K(?,?,?,?)
BindParamAsync().
SQLBindParameter [1] = undefined
SQLBindParameter [2] = undefined
SQLBindParameter [3] = <?xml version='1.0'?><myscript><sh
error='fast'>system -i DSPSYSSTS</sh></myscript>
SQLBindParameter [4] =
ExecuteAsync().
SQLExecute(-1):
undefined:0



Error: SQLSTATE=PAERR SQLCODE=-1 SQLDescribeParam() failed.
at Error (native)
-----------------------------------------------------------


On Thu, Mar 29, 2018 at 3:23 PM, Kevin Nunn <nunn.kevin@xxxxxxxxx>

wrote:

Well, chop me down with a herring!

I left for a meeting, came back and isolated the sql code to

simplify

it

and see if I break it down and figure it out. I was going to show a
co-worker the SQL problem and boom, it worked, and I have no idea

why!

But

the CL part of it is still giving me the SQLDescribeParam() failed

error.

Aaron, I know you are one of the Open Source gurus around here, are

there

any HowTo's to v8 on an iSeries, setting the correct paths and all

that?

It took me a few days to figure out I had to get all the PTFs for

5733OPS

to actually have all the actual software, and then it kept running

Node

v0.12, not v6, but I knew it was installed. So I ended up changing

the

symbolic links manually. I just found the documentation that tells

you

to

run a script to do that today. :P

Thanks,

Kev


On Thu, Mar 29, 2018 at 3:05 PM, Kevin Nunn <nunn.kevin@xxxxxxxxx>

wrote:

Thanks for the reply Aaron

It is version 6, the default install of 5733OPS plus PTFs,

although

I

did

install v8 rpm and then "yum erase"d it. V8 would not run,

probably

due

to

lib path issues and since I am new to all this, I decided it would

be

best

to stick with the defaults. But possibly the install/erase of v8

broke

things?

Below is the code. It's a basic copy/paste of what I found on the

link

listed in the first email. except for the db credentials, etc.

var http = require('http');
var fs = require('fs');
var url = require('url');
var db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/

lib/

db2a');
var xt = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/

xstoolkit/

lib/itoolkit');

var DBname = "*LOCAL";
var userId = "user";
var passwd = "pass";
var ip = "IPaddress";
var port = 8000;

var webserver = http.createServer((req,res) => {
var realPath = __dirname + url.parse(req.url).pathname;
fs.exists(realPath, (exists) => {
if(!exists){
var sql = url.parse(req.url, true).query.sql;
var cl = url.parse(req.url, true).query.cl;
if(sql && sql.length > 0) {
console.log("SQL statement : " + sql);
var dbconn = new db.dbconn();
dbconn.conn(DBname, userId, passwd); // Connect to the DB
var stmt = new db.dbstmt(dbconn);
stmt.exec(sql, (rs) => { // Query the statement
res.writeHead(200, {'Content-Type': 'text/plain'});
res.end(JSON.stringify(rs));
console.log(rs);
stmt.close();
dbconn.disconn();
dbconn.close();
});
}
if(cl && cl.length > 0) {
console.log("CL statement : " + cl);
var conn = new xt.iConn(DBname, userId, passwd);
conn.add(xt.iSh("system -i " + cl));
conn.run((rs) => {
res.writeHead(200, {'Content-Type': 'text/plain'});
res.end(xt.xmlToJson(rs)[0].data);
});
}
} else {
var file = fs.createReadStream(realPath);
res.writeHead(200, {'Content-Type':'text/html'});
file.on('data', res.write.bind(res));
file.on('close', res.end.bind(res));
file.on('error', (err) => {
res.writeHead(500, {'Content-Type':'text/plain'});
res.end("500 Internal Server Error");
});
}
});
});
webserver.listen(port, ip);
console.log('Server running at http://' + ip + ':' + port);


On Thu, Mar 29, 2018 at 2:34 PM, Aaron Bartell <

aaronbartell@xxxxxxxxx>

wrote:

What version of Node.js?

Can you post your code so we know exactly which example you're

talking

about?

FWIW, if you're using Node.js v6.x then I'd upgrade to Node.js

v8.x

and

work through issues with that version.

https://github.com/nodejs/Rele

ase

Aaron Bartell
IBM i hosting, starting at $157/month. litmis.com/spaces


On Thu, Mar 29, 2018 at 2:27 PM, Kevin Nunn <

nunn.kevin@xxxxxxxxx>

wrote:

I am researching db2i and itoolkit by starting with IBM's

examples

at

https://www.ibm.com/developerworks/ibmi/library/i-native-js-

app-ibmi-with-

nodejs/
at not having much luck.

The SQL part of the script is connecting to the DB2 server, but

any

"Select

* From library/db" results in an empty brackets []. If I send

query

"VALUES

current date", I do get [{"00001":"2018-03-29"}] back. If I put

in

bad

credentials, I do get auth errors, so it seems to be connecting

to

the

server.

The same queries work fine from STRSQL.

Also the CL part of the example just returns

Error: SQLSTATE=PAERR SQLCODE=-1 SQLDescribeParam() failed.
at Error (native)

I am new to nodejs, the plan was to find examples for the

iSeries,

watch

them work, then see how the work and start from there.

Also wondering if my other issue might have some relation to

this

problem

(see "Broken npm?" thread in OpenSource).

Thanks

Kev

--
*Kevin Nunn <https://about.me/kevinnunn>*
--
This is the IBMi Open Source Roundtable (OpenSource) mailing

list

To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
*Kevin Nunn <https://about.me/kevinnunn>*

--
*Kevin Nunn <https://about.me/kevinnunn>*

--
*Kevin Nunn <https://about.me/kevinnunn>*
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
*Kevin Nunn <https://about.me/kevinnunn>*
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/opensource.