and database statement

Fellow netters, Just spent an hour yesterday on figuring out a 934 (database connection no longer valid). I seem to recall seeing this a lot on the net recently so I thought I'd document how we resolved it. Situation: (Note field11 is field 1 in table 1, field21 = field1 table2...) DECLARE CURSOR curs FOR SELECT fld11, fld12, fld21, fld31 FROM dbase@engine:table1 tab1, dbase@engine:table2 tab2, dbase@engine:table3 tab3 WHERE tab1.fld13 = pgm_var1 AND tab1.fld14 = pgm_var2 AND tab1.fld11 = tab2.fld22 AND tab2.fld23 = tab3.fld32 Getting 4 fields from three joined tables on a remote engine. The engine in question was on the same platform. In ISQL this worked find and returned 3 rows. In 4gl the prepare worked, but when the cursor was opened it came back with a 934. The Istars for both engines were up and running. Each prepare resulted in an entry in the star_logs indicating that a connection had been made. (Mind you the ISQL statement did not result in an entry - until we got out of ISQL and came back in at which point it made a connection entry in the log). We tried a number of different things all revolving around specifying the table names correctly to the engine - we stopped using aliases and coded each table name in - we tried specifying "owner" as well. None of these things worked finally tried: CLOSE DATABASE OPEN DATABASE dbase@engine DECLARE CURSOR curs FOR SELECT fld11, fld12, fld21, fld31 FROM table1, table2, table3 WHERE table1.fld13 = pgm_var1 AND table1.fld14 = pgm_var2 AND table1.fld11 = table2.fld22 AND table2.fld23 = table3.fld32 which solved it. For those of you unfamiliar with database statement and the overhead involved I include a related note: (Names removed to protect ...) --------------------------------------------------------------------- xxxxxxx voiced a concern about the length of time it would take to do a DATABASE statement. I therefore wrote a short pgm which does a 'couple of these and prints times on how long each takes. This is the output for this pgm. Each loop (100) consists of a DATABASE xxx followed by a CLOSE DATABASE. I opened 7 different databases: db1_eng1@pltfrm1 (database 1 engine 1 platform 1) db2_eng1@pltfrm1 db3_eng1@pltfrm1 db4_eng2@pltfrm2 db5_eng3@pltfrm2 db6_eng4@pltfrm2 db7_eng5@pltfrm1 I was running with the db1_eng1@pltfrm1 engine. Start:18:05:11.910 Loop counter 100 db1_eng1 0:00:00.710 (H:MM:SS.FFF) db2_eng1 0:00:00.480 db3_eng1 0:00:00.550 db4_eng2 0:00:30.330 db5_eng3 0:00:28.980 db6_eng4 0:00:31.250 db7_eng5 0:00:23.050 End:18:07:09.050 Total elpased time : 0:01:57.140 Database statement times : 0:01:55.350 So: for the current engine (db1_eng1@pltfrm1) it takes .0058 per DATABASE statement set. For foreign engines (different TBCONFIG - different platform) it takes .27453 per DATABASE statement set. for a different TBCONFIG on the same engine (only 1 sample) it was .2305 per statement set. All times are in seconds. The overhead for all other statements in the program was .0179 per iteration. There where 28 lines of code not counting the for loop. --------------------------------------------------------------------- cheers j. _____________________________________________________________________________ Jack Parker - Contractor |"Is it weakness of intellect birdie" Hewlett Packard, BSMC Boise, Idaho, USA|I cried, "or a tough little worm in jparker@hpbs2561.boi.hp.com |your little inside", with a shake of (208) 396-5388 (W) (208) 384-1623 (H) |his head he sadly replied: | "Oh willow, Oh willow, tit willow". _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________