IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
#Operatingsystems
#Servers
 View Only

  • 1.  Using python with QSYS2.DISPLAY_JOURNAL and EOF_DELAY

    Posted 07/22/21 03:40 PM
    Edited by Sofia Losada 07/26/21 01:00 PM
    I have a python script which I run in PASE which imports ibm_db2_dbi and executes the following SQL:

    SELECT syslog_facility, syslog_severity, syslog_event FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE,GENERATE_SYSLOG => 'RFC5424', EOF_DELAY => 5))"

    This does not return a result set and just sits showing me a blank screen.

    If I remove ", EOF_DELAY => 5" the result set is returned and I can process the data as normal.

    If I run the same SQL from db2util all the rows are displayed and the screen is refreshed every 5 seconds as new rows are added to the table.

    I've also tried the same SQL with unixODBC from PASE too. This displays the initial result set but doesn't refresh as new rows are added.

    Any suggestions how I can use ibm_db2_dbi or unixODBC in a python script with the EOF_DELAY function of the  QSYS2.HISTORY_LOG service?

    Thanks
    Glenn



    ------------------------------
    Glenn Robinson
    ------------------------------


  • 2.  RE: Using python with QSYS2.DISPLAY_JOURNAL and EOF_DELAY

    Posted 08/04/21 08:36 AM
    When using Python ibm_db or PyODBC, how are you fetching the data? When using EOF_DELAY, the table function will never return EOF, so if you use fetchall() or any fetch function which expects to get EOF before returning data, you could encounter this situation.

    ------------------------------
    Kevin Adler
    ------------------------------



  • 3.  RE: Using python with QSYS2.DISPLAY_JOURNAL and EOF_DELAY

    Posted 08/04/21 08:54 AM
    Edited by Glenn Robinson 08/04/21 09:03 AM
    Thanks for this Kevin.

    I've tried fetchone() with a generator function (using yield) and I can retrieve all of the rows. However, when new entries are added to history log these aren't retrieved by fetchone().

    ------------------------------
    Glenn Robinson
    ------------------------------

    Original Message


  • 4.  RE: Using python with QSYS2.DISPLAY_JOURNAL and EOF_DELAY

    Posted 08/04/21 09:05 AM
      |   view attached
    Here's the test script I'm using at the moment.

    ------------------------------
    Glenn Robinson
    ------------------------------

    Attachment(s)

    py
    syslogReader.py   1 KB 1 version
    Original Message


  • 5.  RE: Using python with QSYS2.DISPLAY_JOURNAL and EOF_DELAY

    Posted 08/05/21 12:01 PM
    I think the problem is your UNION ALL in the query. From the docs:

    This means that a query requiring a copy of data, such as one using an ORDER BY clause or UNION DISTINCT, will issue an error and not be allowed.

    In addition, you may want to consider this advice from the docs:

    When using eof-delay, consider using a simple query to avoid blocking of rows. When rows are blocked for data transport efficiency, rows won't be returned until the block is full. Therefore, you should decide whether you favor data transport efficiency or moving events as soon as they occur.

    You probably want to specify BlockFetch=0 in the DSN or the connection string to disable internal block fetching by the driver or adjust the driver's block size with BlockSizeKB. The default is BlockFetch=1 and BlockSizeKB=256K, so the first fetchnext() will wait until there's 256K of data to return. This could take a very long time, especially if there is little activity on the system.

    In addition, you do not need to write your own generator as PyODBC cursors are iterators: https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#cursors-are-iterable Although, there still may be a benefit to doing so, utilizing fetchmany() to fetch blocks of rows eg. 5 or 10 at a time then return 1 at a time from the generator:

    while True:
   rows = cursor.fetchmany(5)
   if rows is None:
       # Should never happen, since HISTORY_LOG_INFO never returns EOF with EOF_DELAY set
       break
   for row in rows:
       yield row


    This would be more efficient at the cost of potential delays waiting for the block to fill. For a syslog tool, this may not be desired, but that's up to you.

    I was able to run your script successfully after I changed the query to remove the UNION and set BlockFetch=0 (also needed to add EOF_DELAY, which was not specified but I'm guessing that was just from messing around with it before you uploaded it). I did see new records coming in every so often (easy to test using isql to connect over ODBC, which causes a CPIAD09 message; or use SNDMSG TOUSR(*SYSOPR) so it is possible to do over ODBC.



    ------------------------------
    Kevin Adler
    ------------------------------

    Original Message


  • 6.  RE: Using python with QSYS2.DISPLAY_JOURNAL and EOF_DELAY

    Posted 08/10/21 09:32 AM
    Thank you Kevin, that works just fine now.

    I have never looked at changing the DSN before so that's something new learned.

    I guess I was trying to be too clever with the UNION so that I only need one SQL statement to execute but I think the EOF_DELAY is better for this scenario.

    Thanks once again for your time and efffort.

    Glenn

    ------------------------------
    Glenn Robinson
    ------------------------------

    Original Message


Related Content