I'm pretty sure this information is available in the Visual Explain tool. If you use ACS, go to the SQL Peformace Centre, then "Show Statements" and from there use the filters to narrow down the statements to the user, time etc. and then double click on the statement when you've found it. This will open Visual Explain and you can see the Host Variable Values under the "Information about SQL statement executed" section.
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Robert Rogerson <rogersonra@xxxxxxxxx>
Sent: 25 November 2020 19:28
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Displaying values of dynamic sql parameter markers
Hi All, we have a query running from a dot.Net app that is causing
problems. Sometimes it completes fine and other times it doesn't seem to
complete. I looked at the job in sql
SELECT v_authorization_name, v_sql_statement_text, t1.*
FROM TABLE(QSYS2.GET_JOB_INFO('562416/QUSER/QZDASOINIT')) t1;
and v_sql_statement_text is displaying similar to
SELECT a.WHSENUM AS WHSENBR,
a.ITEMNUM AS ITEMNBR,
a.EXPIREDATE,
a.QTYCASES,
a.ON_HAND AS onHand,
coalesce(r.qtyOR,?) AS onReserve
FROM QS36F.WAREHOUSE_EXPIRATION_QTY a
LEFT JOIN onreserve r on a.WHSENUM = r.whseNbr and r.itemNbr = a.itemnum
WHERE ? = ?
AND a.whsenum in (?)
AND a.itemnum = ?
ORDER BY a.expiredate;
Is there any way to see the values of the parameter markers (the question
marks) used at run time?
Thanks,
Rob
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
