I agree with Daniel that OBJECT_STATISTICS is the way to go.
to read the last spooled file from your ODBC job takes a bit more to find it.
There exists a pre-built example from IBM, that i just tried and it "found" the output from TAATOOL/DSPOBJD
-- category: IBM i Services
-- description: Spool - Consume my most recent spooled file
--
-- Query my most recent spooled file
--
WITH my_spooled_files (
job,
FILE,
file_number,
user_data,
create_timestamp
)
AS (SELECT job_name,
spooled_file_name,
file_number,
user_data,
create_timestamp
FROM qsys2.output_queue_entries_basic
WHERE user_name = USER
ORDER BY create_timestamp DESC
LIMIT 1)
SELECT job,
FILE,
file_number,
spooled_data
FROM my_spooled_files,
TABLE (
systools.spooled_file_data(
job_name => job, spooled_file_name => FILE,
spooled_file_number => file_number)
);
------------------------------
Bryan Dietz
------------------------------
Original Message:
Sent: Thu November 03, 2022 01:45 AM
From: Ojasva Bhardwaj
Subject: acs upgrade 1.1.9.0
Hi Daniel,
This is new for me but I think something went wrong while trying. Here are the steps I took and the error I received.
Right after running the DSPOBJD CL command, I tried running following SQL query using the job for 'RUN SQL Scripts'
SELECT * FROM
TABLE (SYSTOOLS.SPOOLED_FILE_DATA (JOB_NAME =>'987658/QUSER/QZDASOINIT', SPOOLED_FILE_NAME =>'QPRTOBJD'))
ORDER BY ORDINAL_POSITION
--QPRTOBJD - Output spool for DSPOBJD
Error:
SQL State: 42704 Vendor Code: -438 Message: [SQL0438] FAILURE ON CPYSPLF Cause . . . . . : An application has executed a SIGNAL or RESIGNAL statement, the RAISE_ERROR function has been invoked, or an error was signalled within a MERGE statement. If the application is an SQL procedure, function, trigger, or a compound (dynamic) statement, the SQLSTATE was not handled in the SQL routine. The message returned is FAILURE ON CPYSPLF. Recovery . . . : See the documentation for the application that issued the SIGNAL or RESIGNAL statement or invoked the RAISE_ERROR function.
Any clue how this could work?
-
Best,
Ojsv
------------------------------
Ojasva Bhardwaj
Original Message:
Sent: Wed November 02, 2022 09:14 AM
From: Daniel Gross
Subject: acs upgrade 1.1.9.0
Hi,
you can use the SPOOLED_FILE_DATA service in SYSTOOLS or the READSPLF table function from Bob Cozzis SQLTOOLS (if you have these).
Just make sure, to always read the spoils from the right job (*).
HTH
Daniel
Original Message:
Sent: 11/2/2022 8:26:00 AM
From: Ojasva Bhardwaj
Subject: RE: acs upgrade 1.1.9.0
Hello Christian,
I've encountered an issue while using IBM ACS 1.1.8.8, I could successfully run CL commands from "Run SQL Scripts" window, but I'm unable to get/read the results. The CL command I'm using is "CL: DSPOBJ QSYS *LIB;"
Is there any temporary table, or cache or any env variable storing this result?? or it simply should not accept such commands?
Please let me know your opinion on this.
Thanks
-
Ojsv
------------------------------
Ojasva Bhardwaj
Original Message:
Sent: Wed May 25, 2022 04:06 AM
From: Christian Jorgensen
Subject: acs upgrade 1.1.9.0
Hi Michael.
If you have different version number in Run SQL Script and 5250 session, then you are running two instances and two different versions of ACS.
You can have as many ACS installed as you like - it's just a JAR file on your workstation (or network drive). If you want to know the version and location of the ACS JAR file, open Help -> About... it is shown just below the build date:
If you open your 5250 sessions by clicking (shortcuts to) the .hod files, then ACS is registered in Windows as the application to run .hod files and it is this one instance of ACS, that you should upgrade to 1.1.9.0.
It is nice to be able to have multiple ACS installed, e.g. for testing new versions of ACS before upgrading - but it can also be confusing, if you don't clean up afterwards. Been there, done that! ;-)
Best regards,
Christian
------------------------------
Christian Jorgensen
IT System Administrator
Network of Music Partners A/S
Original Message:
Sent: Tue May 24, 2022 10:06 AM
From: Michael Smith
Subject: acs upgrade 1.1.9.0
One of the things i like about this upgrade is the tabs on Run SQL Scripts
but i have noticed that my 5250 sessions all still say 1.1.8.4 and as a result the Run Sql Scripts when opened from the 5250 session are also 1.1.84 and don't have the tab function available.
I've tried recreating my .hod files but this still just creates 1.1.84 5250 sessions.
Even opening 5250 from the main ACS panel creates 1.1.84 5250 sessions.
Is this just a piece that did not get updated, or is there something else i need to to do get the 5250 sessions at 1.1.9
Not a huge deal, but it would be nice to lauch the new run sql scripts from my 5250 session.
Thanks
mike
------------------------------
Michael Smith
------------------------------