Re: Another IDS Feature Request

bozon wrote: > Art S. Kagel wrote: > >>bozon wrote: >> >>>It would help us. I think it would be a feature that would really fit >>>in with the new redundant fault tolerant application structure that is >>>popular today. >> >><SNIP> >> >>That raises an additional, related request. That the global cursor ID be >>sharable across ER replicants. That would allow cost free failover to a >>backup server! >> By George I think he's got it! > Yes this would be nice to replicate cursors (this would be a nice extra > but if it kills the whole feature I could wait on it) what would the > syntax be: > > connect to global cursor <global_ID>@<instance_name> ; Or: ATTACH TO GLOBAL SESSION <session_id>; The response would be one of: -- No error - SQLCODE=0 -- Session id in use. -- Unknown session id. When your physical session is ready to relinquish control of the logical session you would: DETACH FROM GLOBAL SESSION; and perhaps explicitely reattach to the physical connection's original unnamed session: ATTACH TO DEFAULT SESSION; When you're finished with a logical session: DROP GLOBAL SESSION <session_id>; In this way while you are attached to a logical session you have access to all of its cursors and prepared statements and cursor/statement names do not clash across sessions as the name scope remains, as it is now, session scope. You would also have to rebind host variables, so you might need to DESCRIBE a statement if the current process has not described it before, but that's not a problem, and most of the time just as the cursor names are known, the data bindings are likely known so building an sqlda structure or describe structure should not be impossible. We do that now. Every copy of an app prepares every statement at startup. The dynamic stmts are DESCRIBED and an sqlda structure bound to input and output host vars and data structures. It's just that now we either have to rerun the query skipping data already returned, adjust the query to skip that data and rerun, or cache the entire result set (or a substanital part thereof) in shared memory. This would reduce the load on the IDS servers be a large margin releasing performance bandwidth for growth and more data and databases per instance. > or would the global_ID be really global accross all ER instances. I am > happy either way. Although there may be hang ups if you have to know > what machine a cursor was on before you connect to it. > > Of course now I am understanding what you want is to replicate the > cursor across the ER databases so that if one ER goes down you can > connect to the cursor on another instance or if you are very redundant > the next time you access the query you will happen to access it from an > unknow ER replicant and you just don't care because you know it was > replicated. Very nice indeed. Yes, yes! Not more coding for 'Oo I lost my connection, reconnect, reprepare all statements, redeclare all cursors, reestablish any current contexts...'. When a server goes down the applications will never even know it except for the time delay on the next request. The esql/c library (let the ODBC guys fend for themselves I say! ;-} ) can reconnect and reattach to the current session on the failover server, if any, and reissue the current request under the hood. >>>To use this feature we can include some secure link to the cursor on >>>the web-page, so that the next connection will know which cursor to >>>connect to. (I think we could come up with a way to do this securely.) >>>So when the page is submitted the connection can look at the data form >>>to get the cursor key if it is needed. >> >>Not hard. Store the cookie->cursor mapping locally on your servers, even in >>a database table. > > Thanks, this is what I was thinking to. You're welcome. >>>I just talked to one of my developers and he mentioned that you would >>>also want some way of closing the cursors externally. His example is >> >> > that a user has many choices on a screen not all of them would end up >> > paging through the cursor. In this case he would hate for all of the >> > code to have to handle server side global cursor cleanup if the cursor >> > was no longer needed. We could just create a job to determine that any >> > cursor older than 3 hours should be closed. (We would create a table >> > that stored the creation time and global cursor ID along with the >> > secure ID that we would pass around.) >> >>Yes, a related thing would be an analog to onmode -z and onmode -Z, say >>onmode -g <global sessionid>, and an SQL level interface, but that could be>>handled by connecting to the session then doing a disconnect instead of a >>detach. > > I was thinking that a sql command would be more appropriate since if > you have a global ID you can get to it from any session so: > > dbacces <database> >>EOF > close global cursor <global_ID>; or > dissconnect from global cursor <global_ID>; > EOF > > would be how I would see it. You misunderstand. You could always, from in an application, ATTACH TO a session then DROP SESSION it, but as a DBA I want to be able to kill an orphaned session from outside. Thus the onmode command. >>Someone mentioned a timeout, but, unless that were configurable, I >>don't like that. > > Yes, if it is configurable I am fine with that. > > For efficiency the server could suspend a logical global Art S. Kagel

"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:43E0E317.2080907@bloomberg.net... > bozon wrote: > > Art S. Kagel wrote: > > > >>bozon wrote: > >> > >>>It would help us. I think it would be a feature that would really fit > >>>in with the new redundant fault tolerant application structure that is > >>>popular today. > >> > >><SNIP> > >> > >>That raises an additional, related request. That the global cursor ID be > >>sharable across ER replicants. That would allow cost free failover to a > >>backup server! > >> > > By George I think he's got it! > Ain't gonna happen... The main purpose of a Cursor is to keep a pointer into the table as the rows are being processed. Since ER doesn't enforce the physical placement of the data, then a cursor on one server is meaningless on another. In fact Since ER is selective in what is replicated, we can't guarentee that the query plans on differing nodes will be the same.... M.P.

Madison Pruet wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:43E0E317.2080907@bloomberg.net... > >>bozon wrote: >> >>>Art S. Kagel wrote: >>> >>> >>>>bozon wrote: >>>> >>>> >>>>>It would help us. I think it would be a feature that would really fit >>>>>in with the new redundant fault tolerant application structure that is >>>>>popular today. >>>> >>>><SNIP> >>>> >>>>That raises an additional, related request. That the global cursor ID > > be > >>>>sharable across ER replicants. That would allow cost free failover to a >>>>backup server! >>>> >> >>By George I think he's got it! >> > > Ain't gonna happen... Oh damn. I knew someone smart was going to throw a jabberwock into my daydream. > The main purpose of a Cursor is to keep a pointer into the table as the rows > are being processed. OK, I buy that. Migrating the cursor would be VERY DIFFICULT. As for the physical ordering of rows, isn't that my problem as a developer? I'd want an ORDER BY clause in that query to insure the rows were returned in the desired order. If I include that then a failover scenario that just gives me back the Nth row in the select set will indeed work as expected. There's a bigger issue that I skirted, with the whole idea of a sharable logical connection. The engine actually returns many rows in a single communication buffer and much of cursor handling is performed in the ESQL/C, ODBC, or other front-end libraries. The cursor communications protocol would have to have the front-end pass state information back to the server at the time a physical session 'detaches' from a logical session saying the equivalent of 'you sent me 35 rows, but I've only delivered 17 to the user so far. Back up the cursor by 18 rows for the next guy.' That has nothing to do with replication, I know, but it also addresses a failover issue which we are discussing. This is solvable, Madison. I believe it's all solvable you've got a bunch of smart folk working on IDS. > Since ER doesn't enforce the physical placement of the data, then a cursor > on one server is meaningless on another. > > In fact Since ER is selective in what is replicated, we can't guarentee that > the query plans on differing nodes will be the same.... This last would be my problem as well. If I'm not replicating all of my data, I have no right to expect the query to complete sensibly on a replicant. Of course, this too can be solved I'm sure in several ways. One that comes to mind is a sqlhosts option indicating that a replicant is a failover candidate and the order of failover if there are multiple failover servers. Another is a cdr command establishing the existance, hierarchy, and perhaps even load balancing information for server failover. At physical connection time the current server can retrieve failover data from the CDR tables or sqlhosts file and return it to the front-end code for later use. Back to the partial replication problem, several solutions: - It's illegal to set up failover to a partial replicant or only FROM a partial replicant to an equivalent partial or to it's master replicant. - Table or row level failover granularity (OK that's more than a bit ambitious). Madison, this is not, I admit, dbaseII level technology. If IDS could do this it would not just be the technology leader, it would redefine the entire meaning of database availability! Imagine, IDS technology leadership noone could ignore. Art S. Kagel

Well someone's gonna fuss at me for top posting.. Oh well. It's good to have a vision. What Art is describing is a vision. When I say "it ain't gonna happen" I'm speaking in terms of what the technology can and can't support. This is always a problem with product development. When we ask for ideas to include in the product, we too often get comments about how to implement. We really need to hear the vision and the problem. My comment about "it ain't gonna happen" was in context to the implementation of a solution to a problem. I did not say the vision was not going to happen. Correct me if I'm wrong, but I'm hearing two distinct problems with this thread. 1) I'd like to have a pre-existing statements and declared cursors so that my application can just attach to them. 2) I want my application to fail-over as well as my data. "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:43E1245C.2010001@bloomberg.net... > Madison Pruet wrote: > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > > news:43E0E317.2080907@bloomberg.net... > > > >>bozon wrote: > >> > >>>Art S. Kagel wrote: > >>> > >>> > >>>>bozon wrote: > >>>> > >>>> > >>>>>It would help us. I think it would be a feature that would really fit > >>>>>in with the new redundant fault tolerant application structure that is > >>>>>popular today. > >>>> > >>>><SNIP> > >>>> > >>>>That raises an additional, related request. That the global cursor ID > > > > be > > > >>>>sharable across ER replicants. That would allow cost free failover to a > >>>>backup server! > >>>> > >> > >>By George I think he's got it! > >> > > > > Ain't gonna happen... > > Oh damn. I knew someone smart was going to throw a jabberwock into my daydream. > > > The main purpose of a Cursor is to keep a pointer into the table as the rows > > are being processed. > > OK, I buy that. Migrating the cursor would be VERY DIFFICULT. As for the > physical ordering of rows, isn't that my problem as a developer? I'd want > an ORDER BY clause in that query to insure the rows were returned in the > desired order. If I include that then a failover scenario that just gives > me back the Nth row in the select set will indeed work as expected. > > There's a bigger issue that I skirted, with the whole idea of a sharable > logical connection. The engine actually returns many rows in a single > communication buffer and much of cursor handling is performed in the ESQL/C, > ODBC, or other front-end libraries. The cursor communications protocol > would have to have the front-end pass state information back to the server > at the time a physical session 'detaches' from a logical session saying the > equivalent of 'you sent me 35 rows, but I've only delivered 17 to the user > so far. Back up the cursor by 18 rows for the next guy.' That has nothing > to do with replication, I know, but it also addresses a failover issue which > we are discussing. This is solvable, Madison. I believe it's all solvable > you've got a bunch of smart folk working on IDS. > > > Since ER doesn't enforce the physical placement of the data, then a cursor > > on one server is meaningless on another. > > > > In fact Since ER is selective in what is replicated, we can't guarentee that > > the query plans on differing nodes will be the same.... > > This last would be my problem as well. If I'm not replicating all of my > data, I have no right to expect the query to complete sensibly on a > replicant. Of course, this too can be solved I'm sure in several ways. One > that comes to mind is a sqlhosts option indicating that a replicant is a > failover candidate and the order of failover if there are multiple failover > servers. Another is a cdr command establishing the existance, hierarchy, > and perhaps even load balancing information for server failover. At > physical connection time the current server can retrieve failover data from > the CDR tables or sqlhosts file and return it to the front-end code for > later use. Back to the partial replication problem, several solutions: > - It's illegal to set up failover to a partial replicant or only FROM a > partial replicant to an equivalent partial or to it's master replicant. > - Table or row level failover granularity (OK that's more than a bit ambitious). > > Madison, this is not, I admit, dbaseII level technology. If IDS could do > this it would not just be the technology leader, it would redefine the > entire meaning of database availability! Imagine, IDS technology leadership > noone could ignore. > > Art S. Kagel

Madison Pruet wrote: > Well someone's gonna fuss at me for top posting.. Oh well. > > It's good to have a vision. What Art is describing is a vision. When I say > "it ain't gonna happen" I'm speaking in terms of what the technology can and > can't support. This is always a problem with product development. Ya, and for an aging myopic programmer who's discovering that one CAN be both nearsighted and farsighted at the same time, having vision is impressive. > When we ask for ideas to include in the product, we too often get comments > about how to implement. We really need to hear the vision and the problem. I hope I wasn't overstepping. My 'syntax' was more to the purpose of illustrating it idea than implementation detail. > My comment about "it ain't gonna happen" was in context to the > implementation of a solution to a problem. I did not say the vision was not > going to happen. > > Correct me if I'm wrong, but I'm hearing two distinct problems with this > thread. YES! REEEAL close. > 1) I'd like to have a pre-existing statements and declared cursors so that > my application can just attach to them. That was what someone else asked for (peraps boson?), I want/need: 1) I'd like to have the ability to pass an entire logical session between physical connections including all prepared statements, cursors, and transactions. Access control would be similar to passing connections between threads within a multi-threaded app. The purpose is twofold: I) to be able to limit the number of database connections while permitting a larger number of outstanding contexts and II) to be able to pass those contexts not just between threads in a single process but also between processes and even between machines. That gives me failover capability in the event of software or hardware failure between the IDS server and the client/user/data consumer. > 2) I want my application to fail-over as well as my data. Yes! DB2 I believe goes more than part of the way there. I remember some kind of connection failover on their side. Art S. Kagel