ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Display_Journal UDTF (V6R1)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Display_Journal UDTF (V6R1)

    DSPPTF LICPGM(5761SS1) SELECT(SI39822)

    Displaying a journal entry from a GUI interface today either requires using APIs or writing the journal entries to an outfile. The APIs are labor intensive and the outfile is somewhat restrictive and slower since a copy of the data required.

    Download the SI39822 PTF and enjoy

    Code:
    select * from table (Display_Journal(
    'MJATST', 'QSQJRN',		-- Journal library and name
    '', '',				-- Receiver library and name
    CAST(null as TIMESTAMP),	-- Starting timestamp
    CAST(null as DECIMAL(21,0)),	-- Starting sequence number
    '',				-- Journal codes
    '',				-- Journal entries
    '','','','',			-- Object library, Object name, Object type, Object member
    '',				-- User
    '',				-- Job
    ''				-- Program
    ) ) as x;
    Select all entries from the *CURCHAIN of journal mjatst/qsqjrn whose time is equal or after 2010-03-31-19:01.15 with user MJA.
    Code:
    select * from table (mjatst.Display_Journal(
    'MJATST', 'QSQJRN',					-- Journal library and name
    ' ','*CURCHAIN',					-- Receiver library and name
    CAST('2010-03-30-19.01.15.000000' as TIMESTAMP),	-- Starting timestamp
    CAST(null as DECIMAL(21,0)),				-- Starting sequence number
    '',							-- Journal codes
    '',							-- Journal entries
    '','','','',						-- Object library, Object name, Object type, Object member
    'MJA',							-- User
    '',							-- Job
    ''							-- Program
    ) ) as x;
    Patrick

  • #2
    Re: Display_Journal UDTF (V6R1)

    PHP Code:
    select from table (Display_Journal(           
    '''QAUDJRN',                                  
    ' ','*CURCHAIN',                                
    CAST('2010-12-27-19.00.00.000000' as TIMESTAMP),
    CAST(null as DECIMAL(21,0)),                    
    '',                                             
    '',                                             
    'DCPRODLB','CM007C','','',                      
    '',                                             
    '',                                             
    ''                                              
    ) ) as 
    I get the message:

    Query cannot be run. See lower level messages.
    User-defined function error on member QSQPTABL.

    Any ideas?

    Comment


    • #3
      Re: Display_Journal UDTF (V6R1)

      Try with 'QSYS', 'QAUDJRN', and look the code reason on the message

      For the audit journal you have also the CPYAUDJRNE command (V5R4)
      Patrick

      Comment


      • #4
        Re: Display_Journal UDTF (V6R1)

        Same Message as before ... with same reason

        Comment


        • #5
          Re: Display_Journal UDTF (V6R1)

          In the joblog, a help on the CPF503E User-defined function error on member QSQPTABL message give a code reason.
          What's the reason code and the SQLSTATE ?

          For an object, you have to give all informations as specicied.

          Example :
          If CM007C is a file then try
          'DCPRODLB','CM007C','*FILE','*FIRST',
          Patrick

          Comment


          • #6
            Re: Display_Journal UDTF (V6R1)

            OK, I see what you mean. The Object was a Program (*PGM?) which is why it was crashing...

            Comment


            • #7
              Re: Display_Journal UDTF (V6R1)

              There is an article on the subject here : http://ibmsystemsmag.blogs.com/i_can/2010/11/index.html

              If the specified Object_Name is the null value, or an empty string, or a blank string, no object name is used and the Object_Library, Object_ObjType, and Object_Member are ignored. If the specified Object_Name contains the special value *ALL, the Object_Library, must contain a library name. Object_ObjType, and Object_Member are ignored. Otherwise, the Object_Library, Object_Name, Object_ObjType, and Object_Member must identify a valid object. *LIBL and *CURLIB may be used as a value of the Object_Library. The Object_ObjType must be one of *DTAARA, *DTAQ, *FILE, or *LIB (*LIB is 6.1 only). The Object_Member may be *FIRST, *ALL, *NONE or a valid member name. If the specified object type was not *FILE, the member name is ignored. Only one object may be specified.
              Patrick

              Comment


              • #8
                Re: Display_Journal UDTF (V6R1)

                Is there a way to incorporate the ENDING timestamp?

                Comment


                • #9
                  Re: Display_Journal UDTF (V6R1)

                  Originally posted by FaStOnE View Post
                  Is there a way to incorporate the ENDING timestamp?
                  You can add a WHERE statement.
                  Example :

                  Code:
                  select * from table (Display_Journal(                        
                  'QSYS', 'QAUDJRN',                                           
                  ' ','*CURCHAIN',                                             
                  timestamp('2010-12-29', '15.00.00'),
                  CAST(null as DECIMAL(21, 0)),                                
                  '',                                                          
                  '',                                                          
                  '','','','',                                                 
                  '',                                                          
                  '',                                                          
                  ''                                                           
                  ) ) as x                                                     
                  where ENTRY_timestamp < timestamp('2010-12-29', '16.00.00')
                  In this example, all records are given between 3PM and 4PM
                  Patrick

                  Comment


                  • #10
                    Re: Display_Journal UDTF (V6R1)

                    Hey Patrick --

                    Can't tell you how useful this has really been lately ... and what great timing too! Just had requests for QAUDJRN research on specific items and this has helped TREMENDOUSLY!! Thank you!

                    One quick question ... is there a way to determine total job run time? How long the program was active?

                    Comment


                    • #11
                      Re: Display_Journal UDTF (V6R1)

                      From QAUDJRN or QACGJRN ?
                      What entry do you extract to know the name of the program?
                      If it's about calculation between two timestamps it's not complicated with SQL.
                      Can you be more precise with examples ?
                      Patrick

                      Comment


                      • #12
                        Re: Display_Journal UDTF (V6R1)

                        I have a program name "PRJ000c" that runs daily at about 22:00 hours. I need to track how long this job runs. It usually takes around 45 minutes, but we need to keep a tab so we can tweak the job if necessary.

                        I can pull the starting time from the QAUDJRN easily with the Select statement you gave me in this thread.

                        Is there something that can give me that information from the QACGJRN?

                        Please advise...

                        Comment


                        • #13
                          Re: Display_Journal UDTF (V6R1)

                          For me, the best way to track jobs is through the accounting journal QACGJRN but without the UDTF.

                          1) Create a receiver
                          2) Create the QACGJRN in QSYS
                          3) CHGSYSVAL QACGLVL *JOB

                          If your schedule job name's MYJOB :

                          Code:
                          CRTDUPOBJ OBJ(QAJBACG4) FROMLIB(*LIBL) OBJTYPE(*FILE) TOLIB(QTEMP) NEWOBJ(MYFILE)
                          DSPJRN JRN(QACGJRN) FROMTIME(060111 220000) JOB(MYJOB) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE4) OUTFILE(QTEMP/MYFILE)
                          SELECT * FROM QTEMP/MYFILE
                          You can use it to know if people really work at office
                          Patrick

                          Comment

                          Working...
                          X