Use of Global Variables won't prevent the values from being accessed in Visual Explain. While the Plan Cache statement text may have the parameter marker (?), the Final Select icon in Visual Explain with display the value of the global variable.
Original Message:
Sent: Tue December 05, 2023 05:06 AM
From: Satid Singkorapoom
Subject: qsys2.http_post and sql plan cache
Dear Davis
Did you pass the URL text string directly in HTTP_POST? If so, please try assigning the URL string to a variable first and use the variable in HTTP_POST instead as Plan Cache will NOT keep the actual value of any variables (parameters) but keep them as symbol "?" (Parameter Marker) instead. Look at sample here : https://blog.faq400.com/en/db2-for-i/qsys2-http-functions-en/
Sample:
-- URL ... with my APi-KEY
set faq400.GV_URL='https://translation.googleapis.com/language/translate/v2?key=' concat faq400.GV_GoogleAPIKey;
-- Body ... with my words
set faq400.GV_BODY= (
select json_object('q' value JSON_ARRAYAGG( nameITA),
'source' value 'it',
'target' value 'en',
'format' value 'text') as "BodyMsg"
from qtemp.FruitNames ) ;
-- Let's create a new Header, JSON with a special option "sslTolerate"="true" if we don't need to check SSL Certificates
set faq400.GV_HEADERJSON=
json_object('header' value 'content-type,application/json',
'header' value 'content-length, ' concat length(trim(faq400.GV_BODY)),
'sslTolerate' value 'true'
);
-- Now we can try QSYS2.HTTP_POST (pay attention to parameter's sequence, not the same!)
select QSYS2.HTTP_POST(faq400.GV_URL,
cast(faq400.GV_BODY as clob(10k)),
cast(faq400.faq400.GV_HEADERJSON as clob(1k)) )
from sysibm.sysdummy1;
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Mon December 04, 2023 03:53 PM
From: David Hammond
Subject: qsys2.http_post and sql plan cache
When using qsys2.http_post I noticed that the SQL plan cache will show the statement along with the parameter values. Is it possible to prevent sensitive data from being included in the SQL plan cache when using qsys2.http_post?
------------------------------
David Hammond
------------------------------