My response got a bit mangled.
Here is the SQL command:
call qsys2.display_journal_entry_info(2,'journal_library', 'journal name', Sequence_number, 'receiver library', 'receiver name','journal code (aka PT, DL, etc)');
-----Original Message-----
From: Matt Olson <Matt.Olson@xxxxxxxx>
Sent: Tuesday, September 11, 2018 11:41 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Viewing packed decimal field as a string
The only way I have found is the undocumented function called qsys2.display_journal_entry_info
This returns data in the exact table format (so it makes the JOESD aka ENTRY_DATA) easily readable. This was obtained by running a wireshark trace against how IBMi Navigator does it.
Here is how it works:
call qsys2.display_journal_entry_info(2, --not sure what 2 means 'journal_library', 'journal name', Sequence_number, 'receiver library'
'receiver name',
'journal code (aka PT, DL, etc)'
You can basically feed the results of the previous query to this and get the readable data.
The problem this has though is that it is very slow, and I would need to call it thousands of times for every journal sequence number I hit and save that data to a temp table somewhere and then ultimately query that data for the decimal packed field.
Regards,
Matt Olson
ARRT
651-681-3136
-----Original Message-----
From: Rob Berendt <rob@xxxxxxxxx>
Sent: Tuesday, September 11, 2018 11:30 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Viewing packed decimal field as a string
One technique I've wondered about was creating a UDTF in RPG which would take the ENTRY_DATA and bounce it against an externally defined data structure and return all the columns. Of course, if you add/modify the columns you have to at least recompile the RPG program.
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
From: "Matt Olson" <Matt.Olson@xxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 09/11/2018 11:56 AM
Subject: Viewing packed decimal field as a string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Folks,
When you are viewing journal data in the ENTRY_DATA field and it contains packed decimal information how do you get the decimal data to be represented as a string so you can read it rather then hexadecimal/binary gibberish (shows up as boxes in my attempt to convert it using this: cast( cast(entry_data as varchar(32000) for bit data) as varchar(32000) ccsid 37))?
Example here (plug in your own file name that has a packed field and own journal name and receiver as needed):
SELECT ENTRY_TIMESTAMP, "CURRENT_USER",
cast( cast(entry_data as varchar(32000) for bit data) as
varchar(32000) ccsid 37)
,
X.* FROM TABLE ( QSYS2.Display_Journal(
'PUTlibrarynameHERE', --Journal Library
'PUTjournalnameHERE', -- Journal Name
CAST(NULL as VARCHAR(10)),
'*CURCHAIN', -- Receiver library and name CAST(NULL AS TIMESTAMP), -- Starting timestamp Example: CURRENT TIMESTAMP
- 7 DAYS,
CAST(null as DECIMAL(21,0)), -- Starting sequence number '*ALL', -- Journal codes (R, F) '*ALL', -- Journal entries (PT, DL, UP, SV, etc) 'PUTlibrarynameHERE','PUTfilenameHERE','*FILE','*ALL', -- Object library, Object name, Object type, Object member '', -- User '', -- Job '', -- Program CAST(NULL AS VARCHAR(10)), '*CURCHAIN', CAST(NULL AS TIMESTAMP), CAST(NULL AS DECIMAL(21,0))
) ) AS x ORDER BY entry_timestamp DESC
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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:
http://amzn.to/2dEadiD
midrange.com
