ENDING_TIMESTAMP is not a column which is provided by the table function, therefore you cannot use it in the ORDER BY clause.
However, it should work with CREATION_TIMESTAMP, as this one is provided by the table function. So you may double check your syntax. To verify the column name, just try (with iACS), something like "SELECT * TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*CURRENT'));". This statement will show you all the available column names.
Original Message:
Sent: Fri February 21, 2025 05:59 PM
From: support Me
Subject: Verifying network security parameters
Hi Marc !!!
Thank you so much for your suggestions !! However, I was trying to run it and there is a sintaxis error or something because I got "Creation_timestamp" doesn't exist, I was trying this query too but I have the same error with "ENDING_TIMESTAMP":
SELECT QUALIFIED_JOB_NAME FROM TABLE(QSYS2.SPOOLED_FILE_INFO(
USER_NAME => '*CURRENT',
STATUS => '*READY *HELD'
))
ORDER BY ENDING_TIMESTAMP DESC
FETCH FIRST 1 ROW ONLY
Thank you in advance for any ideas to solve the issue
------------------------------
support Me
Original Message:
Sent: Fri February 21, 2025 11:57 AM
From: Marc Rauzier
Subject: Verifying network security parameters
SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(JOB_NAME=>(
SELECT QUALIFIED_JOB_NAME FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*CURRENT')) WHERE SPOOLED_FILE_NAME = 'thenameofspooledefileproducedbyDSPCFGLsuchasQSYSPRT' ORDER BY CREATION_TIMESTAMP DESC FETCH FIRST 1 ROWS ONLY), SPOOLED_FILE_NAME =>'thenameofspooledefileproducedbyDSPCFGLsuchasQSYSPRT')
);
Adding a selection to spooled file name might be better to ensure you are retrieving the correct job.
------------------------------
Marc Rauzier
Original Message:
Sent: Fri February 21, 2025 11:37 AM
From: Marc Rauzier
Subject: Verifying network security parameters
Try this (as an SQL only script);
CALL QSYS2.QCMDEXC('DSPCFGL CFGL(QAPPNRMT) OUTPUT(*PRINT)');
SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(JOB_NAME=>
(SELECT QUALIFIED_JOB_NAME FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*CURRENT'))
ORDER BY CREATION_TIMESTAMP DESC FETCH FIRST 1 ROWS ONLY),
SPOOLED_FILE_NAME =>'thenameofspooledefileproducedbyDSPCFGLsuchasQSYSPRT'));
The use of the embedded select over QSYS2.SPOOLED_FILE_INFO is required in case you run the script from iACS, as the spooled files are not owned by the current job but by a QPRTJOB named job with an unknown job number.
Order by and fetch first are here to display only the very last spool file.
But again, you may ask knowing people to review APPC/APPN setup and clean it if no longer used. Within a security/audit context, this is an healthy behavior just like not starting not needed IP services.
------------------------------
Marc Rauzier
Original Message:
Sent: Fri February 21, 2025 10:57 AM
From: support Me
Subject: Verifying network security parameters
Hi Marc
I am just trying to automate some tasks that I have to but I actually do not know if technicians are still using APPC/APPN connections. But they appear with the command DSPCFGL as you say. I understand what you say about spooled-file-data-table-function, however I was trying to find a query tha allow me to get the data more quickly and straightforward, like this example https://www.ibm.com/support/pages/verifying-ddm-tcpip-attribute-lowest-authentication-method-password-required-current-setting
------------------------------
support Me
Original Message:
Sent: Fri February 21, 2025 10:47 AM
From: Marc Rauzier
Subject: Verifying network security parameters
Regarding #1, do you really still use APPC/APPN based connections (if yes for sure based on a HPR/IP aka Enterprise Extender setup)?
If yes, you can output the DSPCFGL command to a spool file, then display this spool file through SQL with https://www.ibm.com/docs/en/i/7.4?topic=services-spooled-file-data-table-function.
If no, you can simply delete all configuration lists. And use https://www.ibm.com/docs/en/i/7.4?topic=services-object-statistics-table-function with a selection on *CFGL object type to output an empty list showing that there is no more any configurgation list.
------------------------------
Marc Rauzier
Original Message:
Sent: Wed February 19, 2025 06:44 PM
From: support Me
Subject: Verifying network security parameters
I must get the actual value of some network security parameters, therefore I need to know:
1. How to view "Secure Loc" of remote locations without prompting on "DSPCFGL CFGL(QAPPNRMT)"
2. How to view "ALWSSL" without prompting on "CHGTELNA" and pressing F4
3. How to view "ALWSSL" without prompting on "CHGFTPA" and pressing F4
I really need an answer similar to this one: https://www.ibm.com/support/pages/verifying-ddm-tcpip-attribute-lowest-authentication-method-password-required-current-setting
Thank you in advance for your help
------------------------------
bluehat MAR
------------------------------