× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. May 2019

RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL

Darren,

That is why I really liked, when I had access, to DBU from ProData because the utility had a way to view Journals with all the data fields.

Doug Belcher | IT Applications Specialist | White-Rodgers
Emerson Commercial & Residential Solutions | 8100 West Florissant Avenue | St. Louis, Mo | 63136 | USA
T 314 553 5872 | M 314 776 4461
Doug.Belcher@xxxxxxxxxxx

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Wednesday, May 1, 2019 1:24 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] 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://urldefense.proofpoint.com/v2/url?u=https-3A__www.scottklement.com_udtf_UDTFs-2520for-2520the-2520Win.html&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=lurZuKa7tjhrKeRUvSh2g8BY0ssbJusSY85e8xtdgdg&e=
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=ZSiKf68hiq5KAAxUTOQSDbjU-u9XQRvm-npMpPgULgo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=u5_lA9x3LYtCv_SDf_A74Tewv82oo91YZyE4rb340Kw&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=mZPYOFxkGQi0MGFGQm7Mlti85SO4xRTafQBOdMH2S_c&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=ZSiKf68hiq5KAAxUTOQSDbjU-u9XQRvm-npMpPgULgo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=u5_lA9x3LYtCv_SDf_A74Tewv82oo91YZyE4rb340Kw&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=mZPYOFxkGQi0MGFGQm7Mlti85SO4xRTafQBOdMH2S_c&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=ZSiKf68hiq5KAAxUTOQSDbjU-u9XQRvm-npMpPgULgo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=u5_lA9x3LYtCv_SDf_A74Tewv82oo91YZyE4rb340Kw&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=mZPYOFxkGQi0MGFGQm7Mlti85SO4xRTafQBOdMH2S_c&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=ZSiKf68hiq5KAAxUTOQSDbjU-u9XQRvm-npMpPgULgo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=u5_lA9x3LYtCv_SDf_A74Tewv82oo91YZyE4rb340Kw&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=mZPYOFxkGQi0MGFGQm7Mlti85SO4xRTafQBOdMH2S_c&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=ZSiKf68hiq5KAAxUTOQSDbjU-u9XQRvm-npMpPgULgo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=u5_lA9x3LYtCv_SDf_A74Tewv82oo91YZyE4rb340Kw&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=mZPYOFxkGQi0MGFGQm7Mlti85SO4xRTafQBOdMH2S_c&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=ZSiKf68hiq5KAAxUTOQSDbjU-u9XQRvm-npMpPgULgo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=u5_lA9x3LYtCv_SDf_A74Tewv82oo91YZyE4rb340Kw&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=jFFsrfJukeI3_1504_25tcj_qfI1gv7BeeMsv5LMb1M&m=w-7YcNcBtZQVOpmhjDZApUy7j0eCuAVHOJU-VmNstm8&s=mZPYOFxkGQi0MGFGQm7Mlti85SO4xRTafQBOdMH2S_c&e=

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.