Matt-
The "2" is probably to select OUTFILFMT(*TYPE2)
We've been retrieving journal entries for error tracking for years. You have to build a formatted file to receive the entries in a readable state.
Run the DSPJRN command:
DSPJRN JRN(JOURNALLIB/JOURNAL) FILE((FILENAME)) RCVRNG(*CURCHAIN) FROMTIME(MMDDYY 000000)
TOTIME(MMDDYY 235959) ENTTYP(*RCD) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE2) OUTFILE(WORKFILELIB/WORKFILE) ENTDTALEN(*CALC)
The work file begins with all of the journal data (who did it, when they did it), followed by an image of the record data from the affected file.
Create a DDS for a file to hold the formatted data (this is a once-and-done step):
0001.00 A*************************************************************************
0002.00 A** **
0003.00 A** Object Name: JRNPF **
0004.00 A** Description: Template DDS that describes the DSPJRN outfile **
0005.00 A** Reference: 99xxxx **
0007.00 A** Date Created: 02/24/99 **
0008.00 A** **
0009.00 A*************************************************************************
0010.00 A REF(RF$)
0011.00 >>>>>A R RF$O6 <--- this should be a format name similar to the PF you're retrieving
0012.00 A JOENTL 5S 0
0013.00 A JOSEQN 10S 0
0014.00 A JOCODE 1A
0015.00 A JOENTT 2A
0016.00 A JODATE 6A
0017.00 A JOTIME 6S 0
0018.00 A JOJOB 10A
0019.00 A JOUSER 10A
0019.00 A JOUSER 10A
0020.00 A JONBR 6S 0
0021.00 A JOPGM 10A
0022.00 A JOOBJ 10A
0023.00 A JOLIB 10A
0024.00 A JOMBR 10A
0025.00 A JOCTRR 10S 0
0026.00 A JOFLAG 1A
0027.00 A JOCCID 10S 0
0028.00 A JOUSPF 10A
0029.00 A JOSYNM 8A
0030.00 A JORES 20A
NO JOESD FIELD!! <<<<<<<<
0031.00 A*** Copy DDS of physical file HERE <<<<<<< don't forget this part!! This replaces the JOESD field
0032.00 A*** Add keys if you want
****************** End of data *************************************************
Copy the unformatted work file to the formatted file you created:
CPYF FROMFILE(&WORKLIB/&WORKFILE) TOFILE(&OUTLIB/&OUTFILE) TOMBR(&TOMBR)
MBROPT(*REPLACE) CRTFILE(*YES) FROMRCD(1) INCREL((*IF JOOBJ *EQ &FILE)) FMTOPT(*NOCHK)
The important bit is the FMTOPT(*NOCHK). This will plop the unformatted data into the proper fields in the special file you created.
The INCREL(()) is in case you've captured data from multiple files into the workfile and want to extract the file data one-by-one.
Paul E Musselman
Paulmmn@xxxxxxxxxxxxx
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Matt Olson
Sent: Tuesday, September 11, 2018 12:54 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Viewing packed decimal field as a string
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
