Physical File Format

[jcne...@prodigy.net]

First, thanks to everyone on this group for the help you have given me
over the past few days.

I've found the files I need, and I am looking through them on a
green-screen console. What is interesting is that some of the data is
visually readable, and some not. Some of the display looks like a
binary file in windows - ampersands, umlats, accented characters, and
blank spots. I believe this is packed data, not sure - I don't have a
lot of experience with EBCDIC environements...

On looking at the file read statements in the RPG source code, the
input file structure is as follows - this is from column 56 to 75 in
the RPG (This may be off in formatting, sorry)

1 6 ACTNO L1M1
P 7 100DATE
P 14 160CHKNO
P 17 222DEBIT
P 23 282CREDIT
29 63 TDESC

On a green-screen, the only readable fields on the file itself are
"ACTNO" and "TDESC", which do not have the "P", or the number after the
column position. Ultimately, I'd like to transfer this to a PC or Unix
workstation for analysis and reporting - what's the best way to make
this readable in that environment? I currently have a BosaNova card
that I am trying to make work - it will yield eventually!!!

It would also be really useful to be able to read the data in a green
screen/terminl emulator environement, to be able to do sanity checks
without trannsfering the data.

Thanks in advance

[jcne...@prodigy.net]

Some more information:

This is not an externally described file, it's in an system/36 env on
OS/400, stored in QS36F.

[Jonas Temple]

What I would suggest is to create an externally described database file
and then copy the file from QS36F to your newly created file. The
basic steps would be:

1. Create a source member in a source physical file.
2. Edit the source member and create the DDS to externally describe the
file (I would suggest NOT naming the file the same as the file in
QS36F).
3. Create the physical file with the CRTPF command.
4. Copy the file using the CPYF command. Be sure to specify
FMTOPT(*NOCHK) on the command.

You then should have an externally described version of the file. Then
you can use whatever you like to query/extract the file (Access, Excel,
etc.).

HTH,

Jonas

[gb]

000N0000000000WEBAS400  0N "     N N
FFFDFFFFFFFFFFECCCEFFF44FD00000744444D000D
0005000000000065212400000500F03F00000500F5

[George Applegate]

When you are looking at the file (WRKF Filename), to be able to read
the packed data, do an "F10" and then an "F11" and you should be able
to read the packed data. You have to look at the column positions the
fields occupy and then read the data up and down:

for instance it will look something like this in positions 17-22

002468
01357F

This would be 123,456.78 Positive ("F" means positive in a packed)
If it were the same with a "D" in place of the "F", it would be
123,456.78 negative.

You have to read packed data up and down, if that sort of makes sense.
Packed data allows the system to store two numeric characters in the
space normally it takes to occupy a single alphameric character.

There are other ways you might be able to view the data, i.e. DFU or
something like that, but if you just want to look at raw data, that's
a starting point.

If these files are in S/36E and you want to transfer them, you may
have to write programs to unpack the data and transfer them as flat
files to the PC and then go from there, or write programs to unpack
the data and create comma separated variable files:

For instance you could write a program that put out a new data file
like:

123456,00011306,12345,12345678901,12345678901,DESCRIPTION

You could then transfer that file to a PC and pull it into a
spreadsheet or something like that, or once in a flat file unpacked,
maybe ftp the file.

ga

jcne...@prodigy.net wrote:

George Applegate
gapp...@fscoop.com

[gb]

I have sent this as an HTML email, formatted in Courier - it works for me with Outlook Express so apologies to all who read it as text... If you cut & paste to WORD and then format to Courier, all should become clearer.

 

JC,

 

If it doesn't have DDS (it isn't externally described) then the simplest way is to define a version of the file with DDS with the packed fields defined and copy the data in or to define a version of the file with the fields zoned (unpacked) and use an RPG program to copy the data across. Once it is externally described, the options to manipulate it increase dramatically.

 

To assist in reading the file, this is how to decode the input specs:-

 

    1   6 ACTNO positions 1-6 (6 bytes) alpha
P   7  100DATE  positions 7-10 (4 bytes), 0dp, packed (unpacked "length" 6 or 7)

P  14  160CHKNO positions 14-16 (3 bytes), 0dp, packed (unpacked "length" 4 or 5)
P  17  222DEBIT positions 17-22 (6 bytes), 2dp, packed (unpacked "length" 10 or 11)
P  23  282CREDIT positions 17-22 (6 bytes), 2dp, packed (unpacked "length" 10 or 11)
   29  63 TDESC positions 29-63 (35bytes), alpha

 

How to see & read packed.

 

Assuming that you are using DSPPFM

 

-    You can see ACTNO and TDESC clearly as text.

-    The other fields will basically be gobbledy gook.

 

Press F10 and then F11 this will display the data as the character representation and the EBCDIC underneath (this will work best if you change it to courier font!)

 

000N0000000000WEBAS400  0N A "     N N
FFFDFFFFFFFFFFECCCEFFF44FD000C00744444D000D
0005000000000065212400000500F103F00000500F5

                          ^^^ ^^^

The first line is the character equivalent, the second line is the first EBCDIC character, the third row is the second EBCDIC character. The ^^^ ^^^ point to packed data (values 00000 and 00037) the F delimits the field (and represents a positive).

 

1357

246F

 

The above is positive 1234567. Hopefully you can now see how numerics are packed!

 

The DATE field I have described as 6 or 7 bytes, this is because if we pack a date field (6 bytes) we might get:-

 

0521 = 95/12/31 (yy/mm/dd - I am in the UK)!

913F

 

The packed version has to have a leading zero. The actual definition will be in the program - the input specification cannot tell you what the true size is.

 

Looking at the field names and using experience, DATE will be 6,0, CHKNO could be either 4 or 5 - probably 5, DEBIT and CREDIT will be 11,2 i.e. 11 digits total, of which 2 are decimal places and could therefore hold a value of 999,999,999.99+/-

 

Phew,

 

HTH

 

GB

 

<jcne...@prodigy.net> wrote in message news:1137183985.1...@g43g2000cwa.googlegroups.com...

[jcne...@prodigy.net]

Thank you!

I'll be working with the box on Tuesday, so this be -enormously-
helpful. This is great stuff - I was having a very hard time finding
documentation on how packing worked.

And writing an external description sounds like a good idea. This
project is a bi of a one-off, but I suspect it will need to be done
again several times. Might as well make it easy for the next person!

[Saml]

I've never worked on the S/36 or the S/36 Env on the iSeries, but I believe
there is a utility that allows you to assign field & format info to S/36
file so you can query it with the RUNQRY command.

It is the interactive data definition utility or IDDU. You start it with
the STRIDD command or from the menu GO IDDU.

Goggle IDDU and you should find a beginner level PDF.

Sam

"Jonas Temple" <jte...@yhti.net> wrote in message
news:1137186484.1...@g44g2000cwa.googlegroups.com...

[jcne...@prodigy.net]

George,

What's the best way to do this? I've had a couple of good suggestions
here, and I've found that using IDDU is a bit painful. Can I create a
DDS source directly to do this?

Sorry for the noobishness of teh question - I'm sort of in a place
where I don't know the questions to ask.