Re: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

Temporal tables don't come without problems. Managing the history data being one of them. I'm looking to make our current journal parsing process more efficient, not change every file on the system and the drama that would bring.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Wednesday, May 1, 2019 2:19 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

I agree that you might need a UDTF for each table you want to scrape out of the journal. It would be nice if IBM did figure out a flexible way to join DISPLAY_JOURNAL and DISPLAY_JOURNAL_ENTRY_DATA.

What's the likelihood of converting those files into Temporal Tables and stop journal scraping? I played around and converted the RCO file into a temporal table and it didn't bomb any of the vendor supplied programs.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Wednesday, May 1, 2019 2:07 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

I'm not returning a single row, and, the bigger issue is that the columns vary for every journal file you parse. It looks to me like you have to define the result set up front for a UDTF, and the whole point of this issue, and why I suspect IBM has avoided this thing, is that its so dynamic.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Wednesday, May 1, 2019 1:48 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

Here is a Scott Klement UDTF from RPG.
https://www.scottklement.com/udtf/UDTFs%20for%20the%20Win.html
Yours may be a lot easier since you will always be returning a single row.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Wednesday, May 1, 2019 1:46 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

Can you convert your RPG into a UDTF? If so, you should be able to easily incorporate that into a view.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Wednesday, May 1, 2019 1:34 PM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

I developed a hybrid RPG and SQL program to query the DISPLAY_JOURNAL SQL UDTF from IBM, which if you use it, you know that the ENTRY_DATA column comes in as an incomprehensible BLOB. I've used RPG to overlay this BLOB onto a data structure and written this out to a file with the journalled file structure pre-built to be able to view this data. What I'm wondering is, if there is an SQL way to dump this data onto a file and/or structure so that I might be able to build this process as a view, rather than an outfile to further reduce unnecessary file I/O. If it were a view, then I imagine that the program would just create the view, in say QTEMP, and then I could query the fields as desired before the data was actually written anywhere.

The column I'm trying to parse is currently coded as follows:
cast(entry_data as char(5000) for bit data)
--
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
--
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
--
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
--
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
--
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