Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
Good morning, all! I'm new to STRDBMON and I'm trying to figure out how to appease auditors (I know, good luck with that! Lol) by capturing database changes and I found an article where we could allow our BAs to use STRSQL or RUNSQLSTM to update records and the STRDBMON would capture those changes in a file. Much cleaner to look at then journal entries!! Lol.
Anyway, I noticed that if the update is done with RUNSQLSTM I see the complete SQL statement, but if I use STRSQL the variables are replaced with question marks. Is there a way to see the full SQL statement like I do with RUNSQLSTM? I'm hoping maybe it's a value in the STRDBMON command.
Any suggestions would be greatly appreciated!
What do you mean by "Variables are replaced with question marks."? What do you mean by "Variables" in STRSQL?
Sorry, Bob. I don't mean variables in STRSQL. When I view the database montoring file in iNav the values in the SQL statement are replaced with question marks. Does that make sense? Here's an example:
UPDATE MM610DVC/TBLFLD SET TBLDEF = ? WHERE TBLNAM=? and TBLVAL <> ?
Those are Parameter Markers to SQL. They are used in the monitor in a way similar to the CL command attributes for passwords (don't display or whatever it is). They don't want to log confidential information.
However, in RUNSQLSTM you're sort of running a static SQL statement so it probably figures you've published the information in there so what's the problem with replicating it in the log.
There is the HOSTVAR parameter, however it defaults to HOSTVAR(*BASIC) and the other options provide less information.
I noticed that, Bob. When I view the entries in iNav I can see the EBCDIC values for the variables. I was curious if there's a way for me to programmatically convert those to there ASCII characters so that I can report on them? I found an article that you wrote in 2005 (lol) using QDCXLATE to convert fields and I've tried it, but am having no luck. I'm assuming that things have come a long way since then, or maybe I'm hoping they have!
Using SQL you can convert them too.
There's a CCSID parameter/keyword that you can add to cast a field to another CCSID.
I used it on CCSID(1200) fields before. Something similar to this:
SELECT SYS_NAME, "TYPE", cast(text as VarChar(50) ccsid 37 ) FROM qsys2.liblist
-Bob
Thanks, Bob, I'll give that a try. I'm wondering if somehow I have a parm somewhere that I'm not entering or something like that because I'm looking at the following redbook and it's showing the variables in normal characters where mine are showing as x'C1C1E3E8D7C5', etc. Weird
http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf
That redbook is 15 years old. Things have changed, CCSIDS, Unicode, UTF-8, UTF-16 all supported now by SQL.