SQL binary question - IBM AS400

This is a discussion on SQL binary question - IBM AS400 ; All, Admittedly I know relatively little about journaling on the as/400, but here goes. I have a file that I journal against and I have a maximum receiver size specified. After a few receivers filled up and detached I did ...

+ Reply to Thread
Results 1 to 7 of 7

Thread: SQL binary question

  1. SQL binary question

    All,

    Admittedly I know relatively little about journaling on the as/400,
    but here goes. I have a file that I journal against and I have a
    maximum receiver size specified. After a few receivers filled up and
    detached I did a dspjrn against the journal, and had it dump the
    output to an outfile which I put in my personal library.

    Then I fired up DBVisualizer and did a select * against the outfile. I
    see a bunch of fields that identify the user, session device ID, date,
    time, etc. The file I am journaling against has a 75 byte per record
    data structure. This data is apparently stored as the last column in
    the outfile record for a type 1 format, and DBVisualizer shows it as
    "Binary" and nothing else on the screen. I can't even view the raw
    data in the snapshot, which was my original goal.

    If I do a dspjrn and have it put the output to the green screen I can
    see the "Entry specific data" and I can make out certain fields, but
    others are packed and cannot be viewed as they would appear if I was
    looking at an actual record in DFU or DBVisualizer.

    I guess my question is, how the heck can I make the binary info at the
    end of the journal record useable and break it back down into a
    formatted data structure as it appears if I do a select against the
    actual database file?

    Chris


  2. Re: SQL binary question

    On Sep 18, 11:14 am, Hal wrote:
    > All,
    >
    > Admittedly I know relatively little about journaling on the as/400,
    > but here goes. I have a file that I journal against and I have a
    > maximum receiver size specified. After a few receivers filled up and
    > detached I did a dspjrn against the journal, and had it dump the
    > output to an outfile which I put in my personal library.
    >
    > Then I fired up DBVisualizer and did a select * against the outfile. I
    > see a bunch of fields that identify the user, session device ID, date,
    > time, etc. The file I am journaling against has a 75 byte per record
    > data structure. This data is apparently stored as the last column in
    > the outfile record for a type 1 format, and DBVisualizer shows it as
    > "Binary" and nothing else on the screen. I can't even view the raw
    > data in the snapshot, which was my original goal.
    >
    > If I do a dspjrn and have it put the output to the green screen I can
    > see the "Entry specific data" and I can make out certain fields, but
    > others are packed and cannot be viewed as they would appear if I was
    > looking at an actual record in DFU or DBVisualizer.
    >
    > I guess my question is, how the heck can I make the binary info at the
    > end of the journal record useable and break it back down into a
    > formatted data structure as it appears if I do a select against the
    > actual database file?
    >
    > Chris


    look at the ENTDTALEN parm of the DSPJRN command. It allows you to set
    the size of the entry specific data field. ( or I think you can create
    the outfile ahead of time and set that field to be the size you
    want. )

    to load the data into columns of a table you likely have to write some
    CL code. First you have to select the proper journal entry types so
    that only put and after image updates are selected. Create a table
    that contains only the entry specific field. Use CPYF to copy from the
    journal outfile to this intermediate file using the fmtopt(*map *drop)
    parm. Then CPYF from the intermediate file to the actual table to
    load using fmtopt(*nochk).

    -Steve



  3. Re: SQL binary question

    The ENTDTA [entry data] is an /undescribed/ binary string. The
    database does not know the format of the data because it is not defined
    as separate fields of the output file itself. It is defined to the
    database only as ENTDTA CHAR ( x ) FOR BIT DATA. See files
    QSYS/QADSPJR* which define the supported layouts [the /model/ outfiles].
    Create a file which adds the columns to suffix those fixed columns
    defined for the chosen outfile format. See the help text for the
    OUTFILFMT() or DSPFFD of the current *OUTFILE as the definition of the
    fixed columns to define. Then for your own file, add the columns from
    the journaled file, to the source [statement] for the create [physical
    file or TABLE] request.
    IIRC the DSPJRN can output data directly to that file; if not, use
    CPYF existing_outfile TOFILE( new_file_with_columns ) FMTOPT(*NOCHK)
    I believe I have seen programs others have created to automate the
    creation of such files, that could be found on the internet -- FMTJRN I
    think is one. I had one once, but I never published it anywhere [it
    depended on existing source for effecting CRTPF SRCFILE() where the
    source file.mbr came from RTVOBJD]. Or if I did post it, that was
    before Google wiped out much of DejaNews.
    A user defined function or user defined table function could be used
    instead, but seems much easier to me, to just create a modified file for
    the output.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    Hal wrote:
    > Admittedly I know relatively little about journaling on the as/400,
    > but here goes. I have a file that I journal against and I have a
    > maximum receiver size specified. After a few receivers filled up and
    > detached I did a dspjrn against the journal, and had it dump the
    > output to an outfile which I put in my personal library.
    >
    > Then I fired up DBVisualizer and did a select * against the outfile. I
    > see a bunch of fields that identify the user, session device ID, date,
    > time, etc. The file I am journaling against has a 75 byte per record
    > data structure. This data is apparently stored as the last column in
    > the outfile record for a type 1 format, and DBVisualizer shows it as
    > "Binary" and nothing else on the screen. I can't even view the raw
    > data in the snapshot, which was my original goal.
    >
    > If I do a dspjrn and have it put the output to the green screen I can
    > see the "Entry specific data" and I can make out certain fields, but
    > others are packed and cannot be viewed as they would appear if I was
    > looking at an actual record in DFU or DBVisualizer.
    >
    > I guess my question is, how the heck can I make the binary info at the
    > end of the journal record useable and break it back down into a
    > formatted data structure as it appears if I do a select against the
    > actual database file?


  4. Re: SQL binary question

    On Sep 18, 8:14 am, Hal wrote:
    > All,
    >
    > Admittedly I know relatively little about journaling on the as/400,
    > but here goes. I have a file that I journal against and I have a
    > maximum receiver size specified. After a few receivers filled up and
    > detached I did a dspjrn against the journal, and had it dump the
    > output to an outfile which I put in my personal library.
    >
    > Then I fired up DBVisualizer and did a select * against the outfile. I
    > see a bunch of fields that identify the user, session device ID, date,
    > time, etc. The file I am journaling against has a 75 byte per record
    > data structure. This data is apparently stored as the last column in
    > the outfile record for a type 1 format, and DBVisualizer shows it as
    > "Binary" and nothing else on the screen. I can't even view the raw
    > data in the snapshot, which was my original goal.
    >
    > If I do a dspjrn and have it put the output to the green screen I can
    > see the "Entry specific data" and I can make out certain fields, but
    > others are packed and cannot be viewed as they would appear if I was
    > looking at an actual record in DFU or DBVisualizer.
    >
    > I guess my question is, how the heck can I make the binary info at the
    > end of the journal record useable and break it back down into a
    > formatted data structure as it appears if I do a select against the
    > actual database file?
    >
    > Chris


    Create a file with the journal fields and the file being journaled
    fields and then copy the output to that. For example the file being
    journaled conatins two CHAR 10 fields, FIELDA nad FIELDB. Create the
    following file and copy the outfile to that. Make sure the journal
    fields match as I think you can change what journal info gets written.

    A JOENTL 5S 0 TEXT('Length of entry')
    A JOSEQN 10S 0 TEXT('Sequence number')
    A JOCODE 1A TEXT('Journal Code')
    A JOENTT 2A TEXT('Entry Type')
    A JODATE 6A TEXT('Data of entry')
    A JOTIME 6S 0 TEXT('Time of entry')
    A JOJOB 10A TEXT('Name of job')
    A JOUSER 10A TEXT('Name of user')
    A JONBR 6S 0 TEXT('Number of job')
    A JOPGM 10A TEXT('Name of Program')
    A JOOBJ 10A TEXT('Name of Objet')
    A JOLIB 10A TEXT('Objets library')
    A JOMBR 10A TEXT('Name of Member')
    A JOCTRR 10S 0 TEXT('Count or relative ')
    A JOFLAG 1A TEXT('Flag: 1 or 0')
    A JOCCID 10S 0 TEXT('Commit cycle identifier')
    A JOUSPF 10A TEXT('User Profile')
    A JOSYNM 8A TEXT('System Name')
    A JORES 20A TEXT('Not used')
    A FIELDA 10A
    A FIELDB 10A

    Basically the journal puts all the file data into one field and you
    have to map it out.

    Thad Rizzi


  5. Re: SQL binary question


    "Hal" skrev i en meddelelse
    news:1190128456.725577.310690@g4g2000hsf.googlegro ups.com...
    > All,
    >
    > Admittedly I know relatively little about journaling on the as/400,
    > but here goes. I have a file that I journal against and I have a
    > maximum receiver size specified. After a few receivers filled up and
    > detached I did a dspjrn against the journal, and had it dump the
    > output to an outfile which I put in my personal library.
    >
    > Then I fired up DBVisualizer and did a select * against the outfile. I
    > see a bunch of fields that identify the user, session device ID, date,
    > time, etc. The file I am journaling against has a 75 byte per record
    > data structure. This data is apparently stored as the last column in
    > the outfile record for a type 1 format, and DBVisualizer shows it as
    > "Binary" and nothing else on the screen. I can't even view the raw
    > data in the snapshot, which was my original goal.
    >
    > If I do a dspjrn and have it put the output to the green screen I can
    > see the "Entry specific data" and I can make out certain fields, but
    > others are packed and cannot be viewed as they would appear if I was
    > looking at an actual record in DFU or DBVisualizer.
    >
    > I guess my question is, how the heck can I make the binary info at the
    > end of the journal record useable and break it back down into a
    > formatted data structure as it appears if I do a select against the
    > actual database file?
    >
    > Chris
    >


    Yes, it is irritating that one only get a dump of the record from DSPJRN. As
    others have said you have to do some work yourself in order to map the data
    to usable fields.

    The tool OYDSPJRN tool does automate this process some (albeit with a few
    anoying caveats). Anyway, maybe you'd want to try it out:

    http://home.hccnet.nl/c.olthuis/AS400-en/indexeng.html




  6. Re: SQL binary question

    > > I guess my question is, how the heck can I make the binary info at the
    > > end of the journal record useable and break it back down into a
    > > formatted data structure as it appears if I do a select against the
    > > actual database file?

    >
    > > Chris

    >
    > Create a file with the journal fields and the file being journaled
    > fields and then copy the output to that. For example the file being
    > journaled conatins two CHAR 10 fields, FIELDA nad FIELDB. Create the
    > following file and copy the outfile to that. Make sure the journal
    > fields match as I think you can change what journal info gets written.


    <>

    > Basically the journal puts all the file data into one field and you
    > have to map it out.
    >
    > Thad Rizzi


    All,

    I worked on this some more this afternoon. I copied/pasted the data
    that Thad posted into a member in my personal library and followed the
    rest of the advice I read as best as I could. I created croberts/
    qfilesrc(JRNLTEST) as a type physical file. I can compile the PF
    successfully, and the record structure for the student transcript data
    that I am journaling against is 75 bytes long. I am attempting to
    display the journal in output format type 1.

    I initially tried to dump the journal with the 75 byte data structure
    and received an error "Format of output file JRNLTEST in library
    CROBERTS not valid." So I went back into the source file, and put a
    'filler' string on the end so that positions 76 thru 100 would be
    accounted for when the journal is displayed, as it is my understanding
    that the minimum record length for the data snapshot is 100 bytes. I
    then tried the dspjrn again. Still no go, I get the same error. The
    command I am trying to execute and the source code follow. If anyone
    has any suggestions I would much appreciate the help.

    DSPJRN JRN(CROBERTS/PGRD450) NBRENT(5000) JRNCDE(*ALL) CCIDLRG(*ALL)
    OUTPUT(*OUTFILE) OUTFILE(CROBERTS/JRNLTEST)

    0001.00 A
    REF(SASDEFS)
    0002.00 A R
    JRNLTST
    0003.00 A JOENTL 5S 0 TEXT('LENGTH OF
    ENTRY')
    0004.00 A JOSEQN 10S 0 TEXT('SEQUENCE
    NUMBER')
    0005.00 A JOCODE 1A TEXT('JOURNAL
    CODE')
    0006.00 A JOENTT 2A TEXT('ENTRY
    TYPE')
    0007.00 A JODATE 6A TEXT('DATA OF
    ENTRY')
    0008.00 A JOTIME 6S 0 TEXT('TIME OF
    ENTRY')
    0009.00 A JOJOB 10A TEXT('NAME OF
    JOB')
    0010.00 A JOUSER 10A TEXT('NAME OF
    USER')
    0011.00 A JONBR 6S 0 TEXT('NUMBER OF
    JOB')
    0012.00 A JOPGM 10A TEXT('NAME OF
    PROGRAM')
    0013.00 A JOOBJ 10A TEXT('NAME OF
    OBJET')
    0014.00 A JOLIB 10A TEXT('OBJETS
    LIBRARY')
    0015.00 A JOMBR 10A TEXT('NAME OF
    MEMBER')
    0016.00 A JOCTRR 10S 0 TEXT('COUNT OR
    RELATIVE ')
    0017.00 A JOFLAG 1A TEXT('FLAG: 1 OR
    0')
    0018.00 A JOCCID 10S 0 TEXT('COMMIT CYCLE
    IDENTIFIER')
    0019.00 A JOUSPF 10A TEXT('USER
    PROFILE')
    0020.00 A JOSYNM 8A TEXT('SYSTEM
    NAME')
    0021.00 A JORES 20A TEXT('NOT
    USED')
    0022.00 A TRNSTU R
    REFFLD(STUID)
    0023.00 A COLHDG('Universal'
    'Student ID')
    0024.00 A TRNYR R
    REFFLD(SCHOOLYEAR)
    0025.00 A COLHDG('Year'
    'Completed')
    0026.00 A TRNTRM R
    REFFLD(TERM#)
    0027.00 A COLHDG('Term'
    'Completed')
    0028.00 A TRNSEQ 3
    0
    0029.00 A COLHDG('Sequence
    number')
    0030.00 A TRNDIS R
    REFFLD(DISTRICT)
    0031.00 A COLHDG('District'
    'ID')
    0032.00 A TRNSCH R
    REFFLD(SCHOOL)
    0033.00 A COLHDG('School'
    'ID')
    0034.00 A TRNCLS R
    REFFLD(CODE)
    0035.00 A COLHDG('Current'
    'Class')
    0036.00 A TRNREQ R
    REFFLD(ID)
    0037.00 A
    COLHDG('Graduation' 'Requirement')
    0038.00 A TRNTCE R
    REFFLD(CREDIT)
    0039.00 A COLHDG('Credit'
    'Earned')
    0040.00 A TRNTCA R
    REFFLD(CREDIT)
    0041.00 A COLHDG('Credit'
    'Attempted')
    0042.00 A TRNTGP 8 4 COLHDG('Grade'
    'Points' 'Earned')
    0043.00 A TRNGRD R
    REFFLD(ID)
    0044.00 A COLHDG('Final'
    'Grade')
    0045.00 A TRNSUB R
    REFFLD(DESC16)
    0046.00 A COLHDG('Subject'
    'Title')
    0047.00 A TRNGPA R
    REFFLD(FLAG)
    0048.00 A COLHDG('Use in
    GPA')
    0049.00 A TRNPDT R
    REFFLD(CDATE)
    0050.00 A COLHDG('YMD'
    'Posted/Entered')
    0051.00 A TRNCDT R
    REFFLD(CDATE)
    0052.00 A COLHDG('YMD'
    'Changed')
    0053.00 A TRNSTS R
    REFFLD(FLAG)
    0054.00 A
    COLHDG('Status')
    0055.00 A TRNCOR R
    REFFLD(COURSE)
    0056.00 A
    COLHDG('Course')
    0057.00 A TRNGLV R
    REFFLD(FLAG)
    0058.00 A COLHDG('Grading'
    'Level')
    0059.00 A TRNGTL R
    REFFLD(ID)
    0060.00 A COLHDG('Grade'
    'Title')
    0061.00 A TRNEXA 3 0 COLHDG('Excused'
    'Absenses')
    0062.00 A TRNUNA 3 0 COLHDG('Un-
    Excused' 'Absenses')
    0063.00 A JUNK
    24
    0064.00
    A**---------------------------------------------------------------
    0065.00 A** Physical key goes
    here:
    0066.00
    A**
    0067.00 A** K TRNSTU
    SIGNED
    0068.00 A** K TRNYR
    SIGNED
    0069.00 A** K TRNTRM
    SIGNED
    0070.00 A** K TRNSEQ
    SIGNED


  7. Re: SQL binary question

    There is a missing step. You need to CPYF *NOCHK the file in your personal
    libary, the one that contains the output of the DSPJRN, to the new physical
    file you created, the one with the JO* fields at the front.

    If you plan to do this a lot, you might want to investigate and install the
    EXPJRNE command from this site:
    http://www.tools400.de/English/Freew...utilities.html

    It's free and it works. You give it a file name,it reads all the online
    receivers and produces a file, complete with the correct field names. You
    can then read this file into an RPG program. Or analyse it with SQL or
    Query.

    Sam


    "Hal" wrote in message
    news:1190157043.872273.228170@q3g2000prf.googlegro ups.com...
    >> > I guess my question is, how the heck can I make the binary info at the
    >> > end of the journal record useable and break it back down into a
    >> > formatted data structure as it appears if I do a select against the
    >> > actual database file?

    >>
    >> > Chris

    >>
    >> Create a file with the journal fields and the file being journaled
    >> fields and then copy the output to that. For example the file being
    >> journaled conatins two CHAR 10 fields, FIELDA nad FIELDB. Create the
    >> following file and copy the outfile to that. Make sure the journal
    >> fields match as I think you can change what journal info gets written.

    >
    > <>
    >
    >> Basically the journal puts all the file data into one field and you
    >> have to map it out.
    >>
    >> Thad Rizzi

    >
    > All,
    >
    > I worked on this some more this afternoon. I copied/pasted the data
    > that Thad posted into a member in my personal library and followed the
    > rest of the advice I read as best as I could. I created croberts/
    > qfilesrc(JRNLTEST) as a type physical file. I can compile the PF
    > successfully, and the record structure for the student transcript data
    > that I am journaling against is 75 bytes long. I am attempting to
    > display the journal in output format type 1.
    >
    > I initially tried to dump the journal with the 75 byte data structure
    > and received an error "Format of output file JRNLTEST in library
    > CROBERTS not valid." So I went back into the source file, and put a
    > 'filler' string on the end so that positions 76 thru 100 would be
    > accounted for when the journal is displayed, as it is my understanding
    > that the minimum record length for the data snapshot is 100 bytes. I
    > then tried the dspjrn again. Still no go, I get the same error. The
    > command I am trying to execute and the source code follow. If anyone
    > has any suggestions I would much appreciate the help.
    >
    > DSPJRN JRN(CROBERTS/PGRD450) NBRENT(5000) JRNCDE(*ALL) CCIDLRG(*ALL)
    > OUTPUT(*OUTFILE) OUTFILE(CROBERTS/JRNLTEST)
    >
    > 0001.00 A
    > REF(SASDEFS)
    > 0002.00 A R
    > JRNLTST
    > 0003.00 A JOENTL 5S 0 TEXT('LENGTH OF
    > ENTRY')
    > 0004.00 A JOSEQN 10S 0 TEXT('SEQUENCE
    > NUMBER')
    > 0005.00 A JOCODE 1A TEXT('JOURNAL
    > CODE')
    > 0006.00 A JOENTT 2A TEXT('ENTRY
    > TYPE')
    > 0007.00 A JODATE 6A TEXT('DATA OF
    > ENTRY')
    > 0008.00 A JOTIME 6S 0 TEXT('TIME OF
    > ENTRY')
    > 0009.00 A JOJOB 10A TEXT('NAME OF
    > JOB')
    > 0010.00 A JOUSER 10A TEXT('NAME OF
    > USER')
    > 0011.00 A JONBR 6S 0 TEXT('NUMBER OF
    > JOB')
    > 0012.00 A JOPGM 10A TEXT('NAME OF
    > PROGRAM')
    > 0013.00 A JOOBJ 10A TEXT('NAME OF
    > OBJET')
    > 0014.00 A JOLIB 10A TEXT('OBJETS
    > LIBRARY')
    > 0015.00 A JOMBR 10A TEXT('NAME OF
    > MEMBER')
    > 0016.00 A JOCTRR 10S 0 TEXT('COUNT OR
    > RELATIVE ')
    > 0017.00 A JOFLAG 1A TEXT('FLAG: 1 OR
    > 0')
    > 0018.00 A JOCCID 10S 0 TEXT('COMMIT CYCLE
    > IDENTIFIER')
    > 0019.00 A JOUSPF 10A TEXT('USER
    > PROFILE')
    > 0020.00 A JOSYNM 8A TEXT('SYSTEM
    > NAME')
    > 0021.00 A JORES 20A TEXT('NOT
    > USED')
    > 0022.00 A TRNSTU R
    > REFFLD(STUID)
    > 0023.00 A COLHDG('Universal'
    > 'Student ID')
    > 0024.00 A TRNYR R
    > REFFLD(SCHOOLYEAR)
    > 0025.00 A COLHDG('Year'
    > 'Completed')
    > 0026.00 A TRNTRM R
    > REFFLD(TERM#)
    > 0027.00 A COLHDG('Term'
    > 'Completed')
    > 0028.00 A TRNSEQ 3
    > 0
    > 0029.00 A COLHDG('Sequence
    > number')
    > 0030.00 A TRNDIS R
    > REFFLD(DISTRICT)
    > 0031.00 A COLHDG('District'
    > 'ID')
    > 0032.00 A TRNSCH R
    > REFFLD(SCHOOL)
    > 0033.00 A COLHDG('School'
    > 'ID')
    > 0034.00 A TRNCLS R
    > REFFLD(CODE)
    > 0035.00 A COLHDG('Current'
    > 'Class')
    > 0036.00 A TRNREQ R
    > REFFLD(ID)
    > 0037.00 A
    > COLHDG('Graduation' 'Requirement')
    > 0038.00 A TRNTCE R
    > REFFLD(CREDIT)
    > 0039.00 A COLHDG('Credit'
    > 'Earned')
    > 0040.00 A TRNTCA R
    > REFFLD(CREDIT)
    > 0041.00 A COLHDG('Credit'
    > 'Attempted')
    > 0042.00 A TRNTGP 8 4 COLHDG('Grade'
    > 'Points' 'Earned')
    > 0043.00 A TRNGRD R
    > REFFLD(ID)
    > 0044.00 A COLHDG('Final'
    > 'Grade')
    > 0045.00 A TRNSUB R
    > REFFLD(DESC16)
    > 0046.00 A COLHDG('Subject'
    > 'Title')
    > 0047.00 A TRNGPA R
    > REFFLD(FLAG)
    > 0048.00 A COLHDG('Use in
    > GPA')
    > 0049.00 A TRNPDT R
    > REFFLD(CDATE)
    > 0050.00 A COLHDG('YMD'
    > 'Posted/Entered')
    > 0051.00 A TRNCDT R
    > REFFLD(CDATE)
    > 0052.00 A COLHDG('YMD'
    > 'Changed')
    > 0053.00 A TRNSTS R
    > REFFLD(FLAG)
    > 0054.00 A
    > COLHDG('Status')
    > 0055.00 A TRNCOR R
    > REFFLD(COURSE)
    > 0056.00 A
    > COLHDG('Course')
    > 0057.00 A TRNGLV R
    > REFFLD(FLAG)
    > 0058.00 A COLHDG('Grading'
    > 'Level')
    > 0059.00 A TRNGTL R
    > REFFLD(ID)
    > 0060.00 A COLHDG('Grade'
    > 'Title')
    > 0061.00 A TRNEXA 3 0 COLHDG('Excused'
    > 'Absenses')
    > 0062.00 A TRNUNA 3 0 COLHDG('Un-
    > Excused' 'Absenses')
    > 0063.00 A JUNK
    > 24
    > 0064.00
    > A**---------------------------------------------------------------
    > 0065.00 A** Physical key goes
    > here:
    > 0066.00
    > A**
    > 0067.00 A** K TRNSTU
    > SIGNED
    > 0068.00 A** K TRNYR
    > SIGNED
    > 0069.00 A** K TRNTRM
    > SIGNED
    > 0070.00 A** K TRNSEQ
    > SIGNED
    >




+ Reply to Thread