Re: Viewing the resultant SQL in an RPG program

Pete

Here's more than you ever wanted to know about the DB monitor - you can run it from the command line with STRDBMON and control the name of the output file, or from Navigator and just specify which library the data is in - the output file then will have a name like QZGnnnnnnn and the text will be 'Output file for STRDBMON'. To get the statement that was actually used, you have to find certain records in that file - it's an ugly feller - lots of record 'type' indicators and far-too-overloaded fields, dependent on that record type. Anyhow, the statement will be found in field QQ1000 of records with recid 1000. You will likely see DECLARE CURSOR statements - have to get the SELECT out of that.

The values used in the host variables will be in the same field, QQ1000, in a record just ahead of the record with recid 3010 - a comma+space-delimited list. They are to replace the parameter markers in order - the markers are ': H '.

FETCH INTO statements will be there, too, and have parameter markers - but they are output - there won't be values anywhere that I remember.

I use RUNQRY () lib/file to see this stuff, because the recid is a binary field.

You COULD go to Navigator, run the monitor, end it, right click on the monitor and take the "List explainable statements...' option - not limited to just the last statement - copy it to notepad or something, then find the output file on the iSeries and get the values fromt the 3010 record - you could use the HEX function in sql to see it, if needed -

select hex(qq1000) from lib/outfile where qqrid = 3010

and for the statements themselves -

select hex(qq1000) from lib/outfile where qqrid = 1000

maybe combine as -

select rrn(outfile), qqrid, qq1000, hex(qq1000) from lib/outfile where qqrid in (1000, 3010) order by 1

or some variant thereof.

You might contact Elvis at Centerfield - I think their tool actually resolves the host variable values, IIRC.

-------------- Original message --------------
From: Pete Helgren <Pete@xxxxxxxxxx>

No. But I just did and it works.

Now, to figure out what the problem is with the select statement.

This gets me going again. Thanks,

Pete


Joe Pluta wrote:

From: Pete Helgren

I'll do some trial and error testing of the statement using different
variables and commenting portions of the statement and see if I can
figure out why the SQL is not returning records like I expect. The
*LOVAL/*HIVAL technique is a good one, I just need to know what to
submit in the SQL for *LOVAL and *HIVAL when the field is a 10 character
numeric.

Did you try x'00000000000000000000' and x' FFFFFFFFFFFFFFFFFFFF'?

Joe

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.