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?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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?
file attached
spreadsheet listing sessions attached
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.
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.
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.