There is a new INTERPRET function that's supposed to be ideal for converting entry_data.
https://www.ibm.com/support/pages/node/6172749
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don Brown via MIDRANGE-L
Sent: Wednesday, November 18, 2020 12:33 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: Re: QSYS2.Display_Journal inserts x'000A' between some fields ?
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
For anyone interested this works for me now.
-- Drop the table used to receive journal output drop TABLE qtemp.JRN_XX;
-- Retrieve the journal data required
-- Cast the variable length fields returned to make them fixed length create table qtemp.jrn_xx as (
Select x.entry_timestamp,
x.journal_entry_type,
cast(x.Job_NAme as char(10)) as JobName,
cast(x.Job_USer as char(10)) as JobUser,
cast(x.Job_number as char(6)) as JobNumber,
cast(x.Program_Name as char(10)) as Program,
cast(x.Entry_data as char(56)) as entry_data
-- x.*
FROM TABLE (QSYS2.Display_Journal('JRN', 'JRN',
-- JOURNAL_CODES => 'D,F,R',
JOURNAL_CODES => 'R',
STARTING_RECEIVER_NAME => '*CURCHAIN' )) as x
) with data;
-- View journal data retrieved
Select * from qtemp.jrn_xx ;
-- Delete the work table
drop table qtemp.jrn_work5;
-- Create table to show formatted journal data create table qtemp.JRN_WORK5 as
(select x.entry_timestamp,
x.journal_entry_type,
cast(x.JobName as char(10)) as JobName,
cast(x.JobUser as char(10)) as JobUser,
cast(x.JobNumber as char(6)) as JobNumber,
cast(x.Program as char(10)) as Program, a2.*
from qtemp.JRN_XX x , mcsdta.MCSDOAPF a2 )
with NO data;
-- Copy the journal data to the table
CL: cpyf qtemp/JRN_xx qtemp/JRN_WORK5 mbropt(*replace) fmtopt(*NOCHK) ;
-- View the data
Select * from qtemp.jrn_work5 ; -- add selection criteria such as where
program = 'program';
-- ===========================================================
Don
From: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxxxxxxxx
Date: 17/11/2020 12:40 AM
Subject: Re: QSYS2.Display_Journal inserts x'000A' between some
fields ?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
It looks to me like those are lengths, and that you're somehow getting VARCHAR results.
On 11/16/2020 12:19 AM, Don Brown via MIDRANGE-L wrote:
The system is on 7.3 and a little behind on ptf's
I am trying to use a method that I think Rob Berendt provided some
time back.
I run the following which produces the file.
create table test_lib.jrn_xx as (
Select x.entry_timestamp, x.journal_entry_type, x.Job_NAme,
x.Job_USer, x.Job_number, x.Program_Name, CAST(x.Entry_data as
char(1000))
as entry_data
FROM TABLE (QSYS2.Display_Journal('JRN', 'JRN',
JOURNAL_CODES => 'D,F,R',
STARTING_RECEIVER_NAME => '*CURCHAIN' )) as x
) with data;
My problem is that between some of the fields x'000A' is inserted - I
have
highlighted the position with the ?? below.
2020-11-16-14.04.01.906560JF QPADEV001W USER01 383701 PGM994C
000
FFFF6FF6FF6FF4FF4FF4FFFFFFDC00DDCCCEFFFE00DECCCC444400FFFFFF00DDEFFFC444FFF
2020011016014B04B01B906560160A87145500160A4244320000063837010A4549943000000
?? ?? ??
So we get this;
entry_timestamp
journal_entry_typ
x'000A'
Job_NAme
x'000A'
Job_USer
x'0006'
Job_number
x'000A'
Program_Name
entry_data
Why am I getting these hex values between fields ?
Thanks for any suggestions.
Don
--
This email has been scanned for computer viruses. Although MSD has
taken
reasonable precautions to ensure no viruses are present in this email, MSD cannot accept responsibility for any loss or damage arising from the use of this email or attachments..
--
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=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lZ01R0f9aogOdl9PVccnVqOTXewXCJ0IZZ6Z7Qygzs8&s=mTEtRbaVN3TZ4WCtKlflpfISga3wBKa_dfCcw9ezkos&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=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lZ01R0f9aogOdl9PVccnVqOTXewXCJ0IZZ6Z7Qygzs8&s=X6W-in-IS9ADwDpabPgeVtQ3WFlOIeg5_udEB_TROuw&e=
.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support
https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lZ01R0f9aogOdl9PVccnVqOTXewXCJ0IZZ6Z7Qygzs8&s=bVU9dE-s2MHsMpaUQ4NC-53-WXyQyOlrQ4Bgjws_kHw&e=
by shopping at
https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lZ01R0f9aogOdl9PVccnVqOTXewXCJ0IZZ6Z7Qygzs8&s=MP1gp2yaBm0HgYsBYsqELRRlx0D1KO-qU9PXDZcIrmE&e=
with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=lZ01R0f9aogOdl9PVccnVqOTXewXCJ0IZZ6Z7Qygzs8&s=E04sNcxbB5ILAryM9EvRg7ETGN9udyCM2fH5Ju8O7As&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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
