In summary, what Darren is doing can be duplicated by the following.
First, find a valid entry in your journals to use and test with.
Next, execute the following:
STRSQL, F13, change commit to *CHG, *NONE or whatever blows your hair back.
Run his statement twice:
CALL QSYS2.DISPLAY_JOURNAL_ENTRY_INFO(
2,'#MXJRN','BPCS_ALL',21120741,'#MXJRN','ALLRCV7602','PT')
The second time you will get the error SQL0443, preceded by CPF3220,. Apparently IBM themselves are the one building a table in QTEMP to work with this data.
Which is VERY interesting.
Now, in Run SQL Scripts I cannot duplicate this error irregardless of how I set the commitment level. IBM probably uses this as their test bed and not STRSQL. (probable cause for pmr).
The interesting thing I find is this:
Known: This command:
CALL QSYS2.DISPLAY_JOURNAL_ENTRY_INFO(
2,'#MXJRN','BPCS_ALL',21120741,'#MXJRN','ALLRCV7602','PT');
Discovered: Generates this table
select * from qtemp.Q_J_E_D2;
Allowing this:
WITH J as (
SELECT *
FROM TABLE (QSYS2.Display_Journal(
JOURNAL_LIBRARY => '#MXJRN',
JOURNAL_NAME => 'BPCS_ALL',
STARTING_RECEIVER_LIBRARY => '#MXJRN',
STARTING_RECEIVER_NAME => '*CURCHAIN'
)) x
where sequence_number = 21120741
)
select q.*, j.*
from qtemp.Q_J_E_D2 q, j
;
The big draw back is this is backwards from how you normally want to query the data. You want to start with display_journal and lateral the data from the return of display_journal_entry_data on to that.
Not start with a known receiver entry and then go back and find the data from display_journal.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Tuesday, May 7, 2019 11:20 AM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: close result set from stored procedure
I've called a stored procedure, such as below from the STRSQL interface. If I call again, I get an error saying that a file in QTEMP cannot be cleared because its in use. Is there an SQL command to deallocate or close a result set to indicate back to the stored procedure that I'm done with the result set, if I didn't use a HLL to read the cursor?
CALL QSYS2.DISPLAY_JOURNAL_ENTRY_INFO(
2,'#MXJRN','BPCS_ALL',21120741,'#MXJRN','ALLRCV7602','PT')
--
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@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
