Re: View row data in QSYS2.DISPLAY_JOURNAL

Many years ago, I wrote a tool to extract journal receiver data and dump it
into a copy of the table that contains the column / record layout, with
journal receiver columns tacked onto the right portion of the row, so the
data and journal column results are directly query-able. Works great
except it doesn't handle some of the more recent, exotic column types,
BLOBs being one of them. Adding support for exotic data types hasn't come
close to the top of my priority stack for my current role in recent years.

One difficult to handle scenario is when a table's column layout changes,
that can lead to data shifting in the BLOB journal entries (i.e. journal
entries with different column buffer positions inside the same journal
receiver). Aside from dealing with that scenario, providing a tool to do
this shouldn't be too hard, but like I said I never implemented data types
like BLOB, CLOB, datalink, XML, and a few others IIRC, that might turn into
a PITA.

I received a lot of benefit from using said tool many years ago in roles
with more production support duties. There's no substitute for knowing
"what actually occurred" (from the journal data) versus "what should have
happened" (from the opinions of people involved).

Mike


date: Thu, 19 Jan 2017 18:13:31 +0000

from: Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
subject: RE: View row data in QSYS2.DISPLAY_JOURNAL

RFE:
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=99899



-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Thursday, January 19, 2017 10:15 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: View row data in QSYS2.DISPLAY_JOURNAL

Can you create a UDTF which expands that UDTF?
I'm thinking of something which utilizes RPG or something to bounce that
blob against some externally defined data structures to break them down
into their individual columns.

I know nothing about XML but I saw a recent post on this list about using
XML within SQL to substring out data that just really opened my eyes. I
wonder if there's some capability in that?

You could create an RFE for this. I can see it as being really popular.

SELECT x.*
FROM table(
DISPLAY_JOURNAL( 'myLibrary', 'myJournal', '', '', CAST(null as
TIMESTAMP), CAST(null as DECIMAL(21,0)), '', '', '', '', '', '', '', '',
'')
) as X
lateral O.*
FROM table(
Blob_To_Columns(Entry_Data, StartingPositionOldValue, LengthOfRow)) as O
lateral N.*
FROM table(
Blob_To_Columns(Entry_Data, StartingPositionNewValue, LengthOfRow)) as N


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com