Re: make cust file look like a journal record

Joel,
If your Cust file is externaly defined at the field level, I do not think
that SQL will work.
You need to get the exact record image into the JOESD field.
An RPGLE program wolud let you define a data structure that was an image of
the complete record, then you could place that in the JOESD field.

1. Display a single journal entry to an outfile using the DSPJRN command.
2. Clear the output file
3. Define your CUST file as input and the journal file as output
4. Define the Journal file as output
5. Using an RPGLE pgm, read the file into a named external defined DS.
6. Set the JOESD field value to the external DS and write the journal
output file.

On Mon, Oct 1, 2012 at 12:12 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

I really like your train of thought here.

It will work perfectly, the only issue is some of the files I am doing
this with contain 10 million records. This may take awhile.

I was hoping to do something where I didn't have to copy the data twice -
once to create a journal record, and a second time to do the ETL.

If I could drop the cust record format into the JOESD field using an SQL
join to create a Cartesian join of all rows to all other rows. But maybe
that is copying data twice too.

I will give this a try - thanks Mark!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark S Waterbury
Sent: Monday, October 01, 2012 10:38 AM
To: Midrange Systems Technical Discussion
Subject: Re: make cust file look like a journal record - format

Joel:

Why go to all the trouble of trying to make a file that "looks just like
a journal record"?

Why not just use the real system journal facilities and then you can use
DSPJRN, as you do now?

For example, you could do something like this:

1. create a temporary library:

CRTLIB LIB(TEMPLIB)

2. create a temporary journal and journal receiver:

CRTJRNRCV JRNRCV(TEMPLIB/TEMPJRN0001)

CRTRJN JRN(TEMPLIB/TEMPJRN) JRNRCV(TEMPLIB/TEMPJRN0001)

3. create a copy of the file, but with no data, in the temporary library:

CRTDUPOBJ OBJ(filename) FROMLIB(filelib) OBJTYPE(*FILE)
TOLIB(TEMPLIB) DATA(*NO)

4. start journaling the temporary copy of the file:

STRJRNPF FILE(TEMPLIB/filename) JRN(TEMPLIB/TEMPJRN) IMAGES(*AFTER)
OMTJRNE(*OPNCLO)

5. copy all the trecords from the original file to the temporary copy,
to get them journaled:

CPYF FROMFILE(filelib/filename) TOFILE(TEMPLIB/filename) MBROPT(*ADD)

Then you can just issue DSPJRN against TEMPLIB/TEMPJRN and send the
output to an *OUTFILE, as I presume you are doing now ......

When all done, you can issue:

DLTLIB TEMPLIB

Does that help?

All the best,

Mark S. Waterbury

> On 10/1/2012 11:19 AM, Stone, Joel wrote:

I have pgmA which reads cust journal changes and sends them off for an

ETL app.

I would also like to be able to send the entire cust file using the same

pgmA with no changes.

How can I make the cust record format look just like a journal record

(the format that DSPJRN creates).

For example:

Cust file format:

CustName 30
CustAddr 30
CustCity 20
-----
Total 80

I want this to look like a DSPJRN record so I can use the same pgmA to

load up all CUST records for an initial load.

Desired format:

Field Field
Text Name Type Length
Length of entry JOENTL S 5

---------------------|

Sequence number JOSEQN S 10

|

Journal Code JOCODE A 1

|

Entry Type JOENTT A 2

|

Date of entry: Job date f JODATE A 6

|

Time of entry: hour/minut JOTIME S 6

|---------- journal header fields

Name of Job JOJOB A 10

|

Name of User JOUSER A 10

|

Number of Job JONBR S 6

|

Name of Program JOPGM A 10

|

Name of Object JOOBJ A 10

----------------------|

.
.
.
Entry Specific Data - Var JOESD A 80

<--------------------------------- I want my CUST data here!!

Most of the journal fields can empty - but I want my CUST data to be in

JOESD. Can SQL or OPNQRYF do this?

I think I need to take fields JOENTL thru JOOBJ and initialize those.

Then take all CUST fields CUST.* and string them together (alpha, packed,
zoned) into the one JOESD field.

Is this possible without using an HLL?

Thanks!

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs

Skyscan service.

For more information please visit http://www.symanteccloud.com
______________________________________________________________________

--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.