ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Knowing Job details using SQL

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

  • Knowing Job details using SQL

    Hi,

    Is there any way by which we can know job's details t what time it started whether ended normally or not using any SQL query by just mentioning the job name?


    i know using DSPLOG itt's details could be taken in spool file and that can be copied to any flat file and on that flat file we can run DSPPFM and then do shift + f4 to search for a specific job name over there.

    But is there any better way like directly running any sql query which could provide this details in a faster way?


    Thanks

  • #2
    i tried below but i am getting error active_job_info in qsys2 type * file not found for this sql query what to do now?
    Code:
    SELECT
    JOB_NAME,
    CHAR(STATUS) AS STATUS,
    SUBSTR(CHAR(START_TIMESTAMP),12,8) AS START_TIME,
    SUBSTR(CHAR(END_TIMESTAMP),12,8) AS END_TIME
    FROM
    QSYS2.ACTIVE_JOB_INFO
    WHERE
    JOB_NAME = 'job_name'
    ​

    Thanks

    Comment


    • #3
      https://www.ibm.com/docs/en/i/7.4?to...table-function

      ACTIVE_JOB_INFO is a table function, not a table

      An active job won't have an end time.

      Example:

      select *
      FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO=>'ALL')) X
      WHERE JOB_TYPE <> 'SYS'
      ORDER BY JOB_USER , JOB_NAME_SHORT
      limit 100;​
      Last edited by MFisher; March 15, 2023, 02:12 PM.

      Comment


      • #4
        but on this sql i can not do f4 prompt in strsql session to filter out specific job name's details plus it does not show me yesterday's job for which i wanted to know at what time it was started and when it got ended and did it end normally or abnormally and when was it restarted like that details.


        Comment


        • #5
          I think what you need is HISTORY_LOG_INFO, it shows the same data as DSPLOG

          Code:
          select message_timestamp, message_id, message_text from table(HISTORY_LOG_INFO()) where from_job = 'job name'
          Nicolas

          Comment


          • #6
            when i ran below sql query it did not display any data ''No Data selected for output'.

            Comment


            • #7
              Originally posted by John192 View Post
              when i ran below sql query it did not display any data ''No Data selected for output'.
              Code:
              select message_timestamp, message_id, message_text from table(HISTORY_LOG_INFO()) where from_job = 'job name'

              Comment


              • #8
                See the link in my previous message, the default for start_time is CURRENT DATE - 1 DAY, maybe you have to change this.

                Code:
                select
                  message_timestamp,
                  message_id,
                  message_text
                from
                  table(
                    HISTORY_LOG_INFO(
                      start_time => current date - 5 days,
                      end_time => current timestamp
                    )
                  )
                where
                  from_job = ?
                ​
                Nicolas

                Comment


                • #9
                  Originally posted by vazymimil View Post
                  See the link in my previous message, the default for start_time is CURRENT DATE - 1 DAY, maybe you have to change this.

                  Code:
                  select
                  message_timestamp,
                  message_id,
                  message_text
                  from
                  table(
                  HISTORY_LOG_INFO(
                  start_time => current date - 5 days,
                  end_time => current timestamp
                  )
                  )
                  where
                  from_job = ?
                  ​

                  Comment


                  • John192
                    John192 commented
                    Editing a comment
                    for this query also i did not get any output same result "No Data selected for output".

                • #10
                  Have you used this sql embedded in an SQLRPGLE program? I can not get past the pre-compiler:

                  Position 34 Variable RSET not defined or not usable.
                  Reason: A member of the host structure does not have an
                  equivalent SQL data type.


                  [CODE]
                  DDoIt S 256 Inz('select * -
                  D from table(-
                  D qsys2/history_log_info() ) -
                  D where message_id in (-
                  D ''CPF1124'', ''CPF1164'')')
                  D RSet DS Qualified
                  D Ordinal_...
                  D Position 10i 0 Integer
                  D Message_...
                  D Id 7 Varying VarChar(7)
                  D Message_...
                  D Type 13 Varying VarChar(13)
                  D Message_...
                  D SubType 22 Varying VarChar(22)
                  D Severity 5i 0 SmallInt
                  D Message_...
                  D TimeStamp Z TimeStamp
                  D From_User 10 Varying VarChar(10)
                  D From_Job 28 Varying VarChar(28)
                  D From_Program 10 Varying VarChar(10)
                  D Message_...
                  D Library 10 Varying VarChar(10)
                  D Message_...
                  D File 10 Varying VarChar(10)
                  D Message_...
                  D Tokens 4096C Varying VarChar(4096) for bit
                  D Message_...
                  D Text 1024G Varying VarGraphic(1024)
                  D Message_Second_Level_...
                  D Text 4096G Varying VarGraphic(4096)
                  D Syslog_event 2048G Varying VarGraphic(2048)
                  D SysLog_...
                  D Facility 10I 0 Integer
                  D SysLog_...
                  D Severity 10I 0 Integer
                  D SysLog_...
                  D Priority 10i 0 Integer

                  Exec SQL Prepare SqlStmt from oIt ;
                  If SqlCode <> 0;
                  DsplText = 'SQL Prepare Error';
                  Dsply dsplText '' Answer;
                  *InLR = *On;
                  Return;
                  EndIf;
                  Exec SQL Declare C1 Cursor for SqlStmt;

                  Exec SQL Open C1;
                  If SqlCode <> 0;
                  DsplText = 'SQL Open Error';
                  Dsply dsplText '' Answer;
                  *InLR = *On;
                  Return;
                  EndIf;
                  DoU SqlCode <> 0 or @End;
                  Exec SQL Fetch C1 into :RSet;
                  If SqlCode = 0;[
                  /CODE]​

                  Comment


                  • #11
                    Unfortunately, there is no direct SQL query that can provide details about a specific job. You can use the DSPLOG command or look at the system log in order to get details about a job.

                    Comment


                    • #12
                      I would not recommend doing a "select * from" in embedded SQL in RPGLE from a system table function, as occasionally IBM does add more columns to the result which may break the program.

                      I would strongly suggest you select only the columns that you need.

                      Comment


                      • #13
                        Vectorspace:

                        I agree with you. Not wise to use * as updates will cause it to crash.

                        I started with that, shorter field names in my DS to the fields names in the documentation, on to qualified/not qualified, then Dim'd/not.

                        Comment

                        Working...
                        X