Re: Lost Invoices

Quoting Will Hartung (la4gen!villy@uunet.UU.NET) regarding his multi-table updating routine: > I think some of the lost invoices may be related to another problem. > For whatever reason, the invoicing programs have all managed to get > stuck, with about 30 of them queued up and waiting. With all of these > 4gi's running, and their sqlexecs, the OS goes into a fit about > File Table Overflow. I'm thinking that when that happens, some of the > rollbacks are failing (fair enough...can stand to well when the rug > gets pulled out from under you). Anyway, I haven't any idea why some > of these are going bannas and causing a traffic jam. I'll look into that > later. I'm not going to tune the problem because it isn't the solution. > Heck, if I increase the values then maybe I'll have 45 backed up instead > of 30 :-(. Why not take a look at your tunable kernel parameters? We discovered that the default values on our system were entirely too small for our INFORMIX applications. When we had more than about 7 or 8 users running INFORMIX-SQL, they would receive numerous (and random) error messages from the engine anytime they Added, Removed, Updated, etc. a record. The problem turned out to be the FLCKREC kernel parameter. This parameter specifies the number of records that can be locked by the system. The defualt value is 20 on our machine (Unisys 5000/80). We changed it to 200 and solved our problem. The manufacturer includes a default kernel with tunable parameter values that are (quoting the manual) "acceptable for most configurations and applica- tions." Well, the default value of 20 for FLCKREC is just too small. My OS manual says the error message "File Table Overflow" will be displayed on the console when you have exceeded the value of the NFILE parameter. This parameter specifies the number of open files. The default value is 175 on our machine. We had to change it to 900. Maybe you need to change your kernel? But of course this won't fix it for good, since as you note, you'll hit the roadblock again. Nevertheless, looking at your pseudo code, we see several UPDATE statements: > BEGIN WORK > LOCK CONTROL RECORD # To make sure only one sessions runs > UPDATE ORDER DATA (1 row) > UPDATE DETAIL DATA (several rows, usually < 5) > UPDATE SHIP DATA (similiar to detail) [ stuff deleted ] > INSERT INTO INVOICE VALUES (INVOICE_REC.*)> UPDATE SHIP DATA with new INVOICE DOCUMENT NUMBER. According to the 4GL ver 4.0 reference manual entry on the UPDATE statement, note #9 (page 7-236) says (the same is true for the INSERT statement; see note #13, page 7-156): "Each row affected by an UPDATE statement within a transaction is locked for the duration of the transaction;..." It goes on to say that the rows that UPDATE locks inside a transaction will be locked until the END/ROLLBACK WORK statement is encountered and that if you have a large number of rows, you can exceed the value of the maximum number of record locks (the FLCKREC kernel parameter). Try counting the number of record locks in your code above: Number of Locks --------------- > LOCK CONTROL RECORD 1 > UPDATE ORDER DATA (1 row) 1 > UPDATE DETAIL DATA (several rows, usually < 5) say 4 > UPDATE SHIP DATA (similiar to detail) 4 > INSERT INTO INVOICE VALUES (INVOICE_REC.*) 1> UPDATE SHIP DATA with new INVOICE DOCUMENT NUMBER. 1 ------- Total: 12 Multiply 12 times the number of users, say 30, and you end up with 360. I don't know what your default FLCKREC is, but if this were my application with 30 users, I would increase it to 400 (on one of our machines it's set at 600). You could also issue some LOCK TABLE statements, if you don't want to tune your kernel. Will's problem brings up one big question: How do you code in 4GL to handle unexpected error messages from the kernel? In SQL, of course, you can't (which is why we had so much trouble diagnosing the problem). Do you use SQLCA.SQLCODE? Can anyone give some sample code? --- John =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= John Baker, USAISC - Lex, Lexington - Blue Grass Army Depot, Lexington, KY (jbaker@lexington-emh2.army.mil) Phone: (606) 293-3644, 293-3743 DSN: 745- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=