Hi Marc,
You are getting back the first record which is the SQL you are currently executing..
You will probably have to ignore the first record and continue to the next available entry. I assume you are reading in a do-loop and reading the returned values into variables ...
Mit freundlichen Grüßen / Kind regards / Bien à vous,
Seán Courtney – 0172-8560663
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jean-Marc DUVAL via RPG400-L
Sent: Tuesday, 12 September 2023 17:32
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Jean-Marc DUVAL <jean-marc.duval@xxxxxxxxxxx>
Subject: RE: How to retrieve an SQL statement into a variable in SQLRPGLE
If I use the query :
SÉLECTIONNEZ JOB_NAME, AUTHORIZATION_NAME, CLIENT_IP_ADDRESS, ELAPSED_CPU_TIME, ELAPSED_PAGE_FAULT_COUNT, ELAPSED_TOTAL_DISK_IO_COUNT SQL_STATEMENT_TEXT DE LA TABLE(QSYS2. ACTIVE_JOB_INFO(JOB_NAME_FILTER =>'QPADEV0009', DETAILED_INFO =>'TOUS')) OÙ SQL_STATEMENT_TEXT <>' '
COMMANDE DE ELAPSED_CPU_TIME DESC
It return :
SELECT JOB_NAME, AUTHORIZATION_NAME, CLIENT_IP_ADDRESS,
ELAPSED_CPU_TIME, ELAPSED_PAGE_FAULT_COUNT,
ELAPSED_TOTAL_DISK_IO_COUNT, SQL_STATEMENT_TEXT FROM
TABLE(QSYS2.A CTIVE_JOB_INFO(JOB_NAME_FILTER =>?,
DETAILED_INFO =>?)) WHERE SQL_STATEMENT_TEXT <>? ORDER BY
ELAPSED_CPU_TIME DESC
That not very useful...
To log query in the processing, it is the previous query I want, not which is use to get it...
-----Message d'origine-----
De : Sean Courtney <scourtney@xxxxxxxxxxx> Envoyé : mardi 12 septembre 2023 17:22 À : 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx> Cc : Jean-Marc DUVAL <jean-marc.duval@xxxxxxxxxxx> Objet : RE: How to retrieve an SQL statement into a variable in SQLRPGLE
ATTENTION : Ce message provient d'une source EXTERNE. Veuillez n'ouvrir les liens ou pièces jointes que si vous êtes sûr de l'expéditeur.
Hi Marc,
I am not sure what you mean by that ... could you elaborate a little please ...
Mit freundlichen Grüßen / Kind regards / Bien à vous,
Seán Courtney - 0172-8560663
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jean-Marc DUVAL via RPG400-L
Sent: Tuesday, 12 September 2023 16:42
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Jean-Marc DUVAL <jean-marc.duval@xxxxxxxxxxx>
Subject: RE: How to retrieve an SQL statement into a variable in SQLRPGLE
If you only get the query you use to get it, the usage is limited...
SQL_S00001
SELECT JOB_NAME, AUTHORIZATION_NAME, CLIENT_IP_ADDRESS, ELAPSED_CPU_TIME, ELAPSED_PAGE_FAULT_COUNT,
******** Fin de données ********
-----Message d'origine-----
De : Sean Courtney <scourtney@xxxxxxxxxxx> Envoyé : mardi 12 septembre 2023
16:17 À : 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx> Cc :
Jean-Marc DUVAL <jean-marc.duval@xxxxxxxxxxx> Objet : RE: How to retrieve an SQL statement into a variable in SQLRPGLE
ATTENTION : Ce message provient d'une source EXTERNE. Veuillez n'ouvrir les liens ou pièces jointes que si vous êtes sûr de l'expéditeur.
Hi Marc,
Use : QSYS2.ACTIVE_JOB_INFO()
You need to replace the value in JOB_NAME_FILTER with your jobname
SQL_STATEMENT_TEXT contains the SQL'S executed
SELECT JOB_NAME, AUTHORIZATION_NAME, CLIENT_IP_ADDRESS, ELAPSED_CPU_TIME, ELAPSED_PAGE_FAULT_COUNT, ELAPSED_TOTAL_DISK_IO_COUNT, SQL_STATEMENT_TEXT FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER =>'your Job name..*', DETAILED_INFO =>'ALL')) WHERE SQL_STATEMENT_TEXT <>' '
ORDER BY ELAPSED_CPU_TIME DESC
Mit freundlichen Grüßen / Kind regards / Bien à vous,
Seán
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jean-Marc DUVAL via RPG400-L
Sent: Tuesday, 12 September 2023 15:31
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: Jean-Marc DUVAL <jean-marc.duval@xxxxxxxxxxx>
Subject: How to retrieve an SQL statement into a variable in SQLRPGLE
Hi,
With a view to creating logs for my SQLRPGLE processing, I would like to know if there is a possibility of recovering the text of the query submitted into a character variable.
Thanks for ideas;
Cordialement,
Jean-Marc DUVAL
Pôle développement IT
Développeur IBM i consultant
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/rpg4
00-l__;!!J76aczzK!07UsCLZF9LCLgn3HCDELo9HwpprkXIC-5XzVYHHK31ssTkjYTgRjvXhEb6
gxgV0jsxdRg9LJHNLFTHtdYoToII4aaf-d$
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!J76acz
zK!07UsCLZF9LCLgn3HCDELo9HwpprkXIC-5XzVYHHK31ssTkjYTgRjvXhEb6gxgV0jsxdRg9LJH
NLFTHtdYoToIDTUakgD$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/rpg400-l__;!!J76aczzK!2XQBNzidd63_q9Dhxf2CkKuwkZghcHwg-Af6nZ3dLfdc0PCzH3RayDsAKwGb5SutlqDsUq-WPMRkZr5bLrrlvdGJXesX$
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!J76aczzK!2XQBNzidd63_q9Dhxf2CkKuwkZghcHwg-Af6nZ3dLfdc0PCzH3RayDsAKwGb5SutlqDsUq-WPMRkZr5bLrrlvfRyAkrF$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
