IBM i Global

 View Only
  • 1.  Logging ODBC activity

    Posted Fri July 15, 2022 12:05 AM
    I'm running this query from a python script using pyodbc:

    SELECT SYSLOG_EVENT, MESSAGE_TIMESTAMP, FROM_JOB, MESSAGE_ID, FROM_USER
    FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => '{start}',GENERATE_SYSLOG => 'RFC5424', EOF_DELAY => 5))
    WHERE MESSAGE_ID IN ('CPF1124','CPF1164')
    '''.format(start=startTime)

    This returns data and then seems to hang since i applied the latest V7R3 PTFs. I realise the EOF_DELAY will sit and wait until more data is available but I'm not seeing new history log entries being returned.

    I use the odbc connection as follows:

    # Run the SQL statement
    def runSql(sqlstmt):
    try:
    conn = pyodbc.connect("DSN=*LOCAL;BlockFetch=0;BlockSizeKB=0K")
    cur = conn.cursor()
    cur.execute(sqlstmt)

    # Retrieve each row of the data set
    while True:
    row = cur.fetchone()

    if row == None:
    break

    yield row
    except pyodbc.DatabaseError as err:
    raise err


    Is there a way to get debug info so i can see what it is, or isn't, doing.

    Thanks

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


  • 2.  RE: Logging ODBC activity

    IBM Champion
    Posted Fri July 15, 2022 04:58 AM
    Edited by Satid Singkorapoom Fri July 15, 2022 05:01 AM
    Dear Glenn

    I notice is that the description of this IBM i service says :
    [QUOTE]
    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.
    [UNQUOTE]
    So, I ask you to try appending this clause to the end of the SELECT statement to see if it helps return new history log entries or not :  OPTMIZE FOR 1 ROW. This is to use the optimization goal of *FIRSTIO to avoid blocking of rows.


    If this does not help, please try running the SELECT against HISTORY_LOG_INFO (with OPTIMIZE FOR 1 ROW) from "Run SQL Script" tool session to see if you get new history log entries or not?  If so, at least you will know HISTORY_LOG_INFO service works fine.


    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------



  • 3.  RE: Logging ODBC activity

    Posted Fri July 15, 2022 03:21 PM
    Satid,

    Thanks for this and good idea, I will try that.

    As you can see I have the following in my connection:

    BlockFetch=0;BlockSizeKB=0K

    I was advised, when I originally created this script, that this would do exactly what you're suggesting i.e. return data as soon as it is available.

    Glenn

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



  • 4.  RE: Logging ODBC activity

    IBM Champion
    Posted Fri July 15, 2022 09:47 PM
    Edited by Satid Singkorapoom Sat July 16, 2022 02:57 AM
    Dear Glenn

    I forgot to say I noticed your specified ODBC connection parameters. But, based on my past experience, I suspect it may not work smoothly in all respects due to possible discrepancy in ODBC protocol behaviors from differing levels of ODBC specifications used by your client-side PYODBC driver and what is in your IBM i and thus my two suggestions.

    BTW, the ODBC connection parameter BlockSizeKB works ONLY when BlockFetch = 1 as indicated here :  https://www.ibm.com/docs/en/i/7.4?topic=details-connection-string-keywords

    If my suggestions fail to solve your issue, I ask you to consider using client-side ODBC driver for PASE delivered in IBM i ACS to see if it helps or not.  It also provides an option for SQL log trace file that you can use to see what happens in ODBC link.
    https://techchannel.com/SMB/8/2019/ODBC-Driver-for-IBM-i
    https://www.ibm.com/support/pages/odbc-driver-ibm-i-pase-environment


    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------



  • 5.  RE: Logging ODBC activity

    Posted Sat July 16, 2022 06:31 AM
    Unfortunately OPTIMIZE FOR 1 ROW doesn't appear to make any difference.

    Is there a way to force the logging of ODBC activity? 

    When run the SQL in Run SQL statements it works fine.

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



  • 6.  RE: Logging ODBC activity

    IBM Champion
    Posted Sun July 17, 2022 09:04 PM
    Edited by Satid Singkorapoom Sun July 17, 2022 09:05 PM
    >>>> Is there a way to force the logging of ODBC activity?  <<<<

    ODBC driver for PASE in IBM i ACS provides this feature as described in one of the two URLs I provided above.


    >>> When run the SQL in Run SQL statements it works fine.  <<<<

    This result is one good reason for you to try IBM's ODBC for PASE.

    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------



  • 7.  RE: Logging ODBC activity

    Posted Mon July 18, 2022 04:39 PM
    Satid,

    Thanks again for this.

    I am already using the PASE i Access ODBC driver, this has always been a requirement of this application. I will try the trace parameters to see what results I get.

    I also note your comment about BLOCKFETCH =1. As the range of BLOCKSIZE is 1-8192KB and I'm trying to optimise for 1 row I will need to play around with this to see how/if it impacts the database processing.

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



  • 8.  RE: Logging ODBC activity

    IBM Champion
    Posted Tue July 19, 2022 08:14 PM
    Edited by Satid Singkorapoom Tue July 19, 2022 08:28 PM
      |   view attached
    Dear Glenn

    I just found that with Python, in addition to IBM i ODBC for PASE and PYODBC,  you can also use IBM i SQL CLI interface in PASE.  Have you tried the CLI yet?  If not,  you can consider trying it to see if this works without the issue you encountered or not.  The instruction for using the CLI in Python is on page 19 onwards of the presentation file I upload with this post. 

    Additional information from this IBM i OSS Docs URL may also be useful for you : https://ibmi-oss-docs.readthedocs.io/en/latest/python/README.html


    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------

    Attachment(s)



  • 9.  RE: Logging ODBC activity

    Posted Wed July 20, 2022 04:37 PM
    Thanks a lot for this.

    I am aware of the SQL CLI, in fact I started off using this then chose to use ODBC.

    Anyway, the good news is that replacing the BLOCKFETCH=0 with =1 and removing the BLOCKSIZE attributes from the connection request has dramatically improved the SQL to QSYS2.HISTORY_LOG_INFO.

    Thanks once again for your help on this matter.

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