Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
STRDBMON question
has no ratings.
Published: 23 Mar 2016
Revised: 24 Mar 2016 - 2808 days ago
Last viewed on: 30 Nov 2023 (3634 views) 

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.

STRDBMON question Published by: Chris Proctor on 23 Mar 2016 view comments(7)

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!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: STRDBMON question
Posted: 7 years 8 months 8 days 15 hours 3 minutes ago

What do you mean by "Variables are replaced with question marks."? What do you mean by "Variables" in STRSQL?

Posted by: chrisp
Premium member *
Portland, OR
Comment on: STRDBMON question
Posted: 7 years 8 months 8 days 14 hours 57 minutes ago

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 <> ?

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: STRDBMON question
Posted: 7 years 8 months 8 days 14 hours 32 minutes ago

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.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: STRDBMON question
Posted: 7 years 8 months 7 days 15 hours 30 minutes ago

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!

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: STRDBMON question
Posted: 7 years 8 months 7 days 13 hours 16 minutes ago
Edited: Thu, 24 Mar, 2016 at 12:28:08 (2808 days ago)

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

 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: STRDBMON question
Posted: 7 years 8 months 7 days 12 hours 51 minutes ago

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

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: STRDBMON question
Posted: 7 years 8 months 7 days 12 hours 35 minutes ago

 That redbook is 15 years old. Things have changed, CCSIDS, Unicode, UTF-8, UTF-16 all supported now by SQL.