Yep that's right.
-----Original Message-----
From: Rob Berendt <rob@xxxxxxxxx>
Sent: Tuesday, September 11, 2018 3:08 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Viewing packed decimal field as a string
Let me step back a bit...
Let's say someone had a packed number. Just for grins we'll say it was 12345. And you want to see all journal entries which contain 12345, but the packed representation? Basically someone keyed in a bogus number and you want to see all the tables which recently got updated with this entry so you can audit them.
Let's create a table.
CREATE TABLE ROB/MATTOLSON (MYPACKED DEC (7 , 0), MYCHAR CHAR (5 ))
DSPFD ROB/MATTOLSON
File is currently journaled . . . . . . . . : Yes
Current or last journal . . . . . . . . . . : USERS
Library . . . . . . . . . . . . . . . . . : #MXJRN
INSERT INTO ROB/MATTOLSON (MYPACKED, MYCHAR) VALUES(12345, 'HI')
DSPPFM ROB/MATTOLSON
F10=Display hexadecimal (toggle)
Now let me try this:
SELECT journal_code, journal_entry_type, object, object_type, X.*
FROM TABLE (QSYS2.Display_Journal(
JOURNAL_LIBRARY => '#MXJRN',
JOURNAL_NAME => 'USERS',
STARTING_TIMESTAMP => TIMESTAMP('2018-09-11-15.40.01'),
STARTING_RECEIVER_NAME => '*CURCHAIN'
) ) AS X
WHERE JOB_USER IN('ROB')
;
My entry_data is this
0012345FC8C9404040
Pretty visible, eh?
However I cannot do a LIKE comparison against that because it is an incompatible data type.
So let me try this
SELECT journal_code, journal_entry_type, object, object_type, cast(entry_data as char(32000)), X.* ...
Ok, now I see special characters.
Let me try this
SELECT journal_code, journal_entry_type, object, object_type, hex(substr(cast(entry_data as char(32000)), 1, 4)), X.* I see 0012345F in that column
...
as much fun as this is I've got to get back to "real work". But I think I understand what you are trying to do.
Basically the problem is that you cannot use a like because it's incompatible data types. And when you cast it to character it's no longer as readable.
Right?
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 12:30 PM
Subject: RE: Viewing packed decimal field as a string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
I was hoping to do this in SQL so that I can then do a LIKE '%12345%'
where 12345 is the string in the packed decimal field so I can globally
find all string values across all tables that contain that packed decimal
value.
I don't think that EXTJRNDTA would do that since it is on a file by file
basis and would take days to go through hundreds of files on a file by
file basis like that.
-----Original Message-----
From: Dan <dan27649@xxxxxxxxx>
Sent: Tuesday, September 11, 2018 11:23 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Viewing packed decimal field as a string
Carsten Flensburg has an excellent utility that handles this
automatically. EXTJRNDTA substitutes the fields from the record format of
the original table in place of the JOESD (entry data string).
- Dan
--
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
