A bit of background to start with today, I’ve recently been learning how to setup NodeJS on IBM i (again big plug here for Andy Youens series to get you started.) After completing a few tutorials, I started looking at pulling data out of the local tables using the idb-connector package, more on that another time if there’s interest.
I also have an old 820 sat in the corner running V5R4, I wonder if I can pull data from that and bring it back to my NodeJS instance on a POWER7?
The solution I’ve gone with, and I imagine there’s many ways to skin this particular cat, is WRKRDBDIRE.
For those who’ve never looked into this, the command is ‘Work with Relational Database Directory Entry’ and if you run it then odds are you’ll have a single entry for your local database (unless someone else has already beaten you to the punch.)
To add the 820s database to my power7 directory I’ll use the command:
ADDRDBDIRE RDB( ) RMTLOCNAME( IP) RMTAUTMTH(USRENCPWD ALWLOWER)
• Dbname I’ll get from my 820 server by running the WRKRDBDIRE command there and taking the name.
• Alias I’ll set to something meaningful, lets say MNIS820
• IP Address is the address of my 820 and I’ve also specified to access it via IP
• I’ve left the authorization parameters as default (USRENCPWD, *ALWLOWER) more on these in a moment…
If I now run WRKRDBDIRE again I’ll have an additional record, you can also do the add through this command instead if you prefer.
I’ll now run my SQL command and see what happens:
select * from mnis820.shearsd.emlrolk
Authorization failure on distributed database connection attempt.
This is because although I’ve defined MNIS820 as a database I haven’t setup any authentication to it (if you don’t get this error, I hope you already have authentication and it’s not that your server doesn’t use any….)
To get around this I’m going to add a server authentication entry on my PPOWER7:
ADDSVRAUTE USRPRF(SHEARSD) SERVER(MNIS820) USRID(SHEARSD) PASSWORD()
This is where the auth parameters from earlier come into play. I’ve said that I would prefer to use a user ID and an encrypted password, although I will work with a lower level of authentication if that’s what the server asks for. As it happens my 820 is set it to require userID & encrypted password (CHGDDMTCPA) so that’s not an issue here and if I were to change to *NOALWLOWER it will still work.
Running my SQL statement again I now get the records back from my 820 and can display them in a pretty webpage I’m writing in NodeJS (this is how you do modernization right? 😊 )
After writing this I have gone back and changed my entry to *NOALWLOWER because I’d like to make sure that I’m making sure both sides are using the level of authentication I’m comfortable with.
Also, as a side note the ADDSVRAUTE isn’t strictly necessary if you’re using the idb-connector package and you can put the credentials into the javascript (although that has its own security implications,) I find it helpful for the purposes of testing my SQL statements through Access client Solutions.
The same technique also works for partitions if you’re curious, one of my customers has a dual-partition power6 that they want to shuffle data between so I’m offering them some SQL select/update/insert/etc as a methodology.
I’m also interested in how other people would do this, do you have a cleaner/faster approach? Drop a comment as I’m always eager to learn, especially while I’m busy breaking node.
Leave a Reply