logging visibility inside of SQL Language Routines (PSM) on server

  • JMG AK

    JMG AK - 2014-08-23

    The attached files have 2 log extracts from each of 3 logs available with HSQLDB. They demonstrate where I have a problem debugging SQL Language Routines (PSM) on server. The following two problems are elaborated on in the attachment. (If there is not file upload option, I will paste into second post.)

    So problem # 1: Is there any logging visibility of the execution of statements within the PSM procedures/functions?
    The .sql.log shows only the interaction between the client and the server, not any sql executing on the server.
    I need to know which statement is generating the exception.

    Problem #2. From the session listing above, you can see that somehow a latch exists labeled for session 34 that session 51 has hung on.
    (Is still hung on for the total time I have been assembling this document.) But session 34 is closed.
    How do I clear that? What do you need to fix to prevent it?

  • JMG AK

    JMG AK - 2014-08-23

    file attached

  • JMG AK

    JMG AK - 2014-08-23

    spreadsheet listing sessions attached

  • Fred Toussi

    Fred Toussi - 2014-08-23

    At this time, I can only clarify the role of each log.

    The .SQL.LOG logs statements in the server as they are issued by clients, including both remote and in-process clients. It therefore does not log the statements inside PSM or JRT routines.

    For logging PSM during development, you can use a table dedicated to logging, perhaps a TEXT table, insert debugging values and observe the contents. With PSM you can use SIGNAL and RESIGNAL to manage the exceptions.

    Regarding a loose latch, it is probably a bug that shows itself in some rare combination of events. It will need some work to find out what. Your log will be useful.

    In general, all reported issues are eventually taken care of. We work with SupportWare customers on a priority basis on this type of issues and fix them. You can join this program if you want more time and effort spent on your issues.

  • JMG AK

    JMG AK - 2014-08-25

    I have used the table logging option, (not TEXT tables though) but the exception causes (apparently) rollback from my logging table as well. I did note that the data file had increased in size and the rolled back data was within the file but not queryable, but the text strings readable (but not dates and numbers). Would the text table allow me to pick through the crashed table for the logged values more readily?
    We are nearing a time to demonstrate our app to management and when it is usable, I will try to convince them to fund support with you. You have been very helpful.

  • Fred Toussi

    Fred Toussi - 2014-08-25

    I also said use SIGNAL. This is like a try / catch block. You catch all exceptions with SIGNAL write the logging data, then return from the PROCEDURE. Due to the clean exit the data is logged so long as the transaction is committed. Text tables are simply easier to check.


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks