Thread: DB2 feature
Hi I see this article about DB2 http://www-106.ibm.com/developerworks/db2/library/techarticle/dm -0411rielau/?ca=dgr-lnxw06SQL-Speed The listing 2 example: 1 SELECT D_TAX, D_NEXT_O_ID 2 INTO :dist_tax , :next_o_id 3 FROM OLD TABLE ( UPDATE DISTRICT 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1 5 WHERE D_W_ID = :w_id 6 AND D_ID = :d_id 7 ) AS OT I am not a expert in Rule System. But I ad a look to http://www.postgresql.org/docs/7.4/static/rules-update.html And it seems possible in PostgreSQL to build non standard SQL query to do thing like listing 2. I would like to know from an "expert" of PostgreSQL if such query is really a new stuff to DB2 as the artcile states ? or if PostgreSQL has already the same type of power ? Cordialement, Jean-Gérard Pailloncy
Jean-Gerard, > The listing 2 example: > 1 SELECT D_TAX, D_NEXT_O_ID > 2 INTO :dist_tax , :next_o_id > 3 FROM OLD TABLE ( UPDATE DISTRICT > 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1 > 5 WHERE D_W_ID = :w_id > 6 AND D_ID = :d_id > 7 ) AS OT A lot of this is non-standard SQL, so I can't really tell what DB2 is doing here. Can you explain it? -- --Josh Josh Berkus Aglio Database Solutions San Francisco
>> The listing 2 example: >> 1 SELECT D_TAX, D_NEXT_O_ID >> 2 INTO :dist_tax , :next_o_id >> 3 FROM OLD TABLE ( UPDATE DISTRICT >> 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1 >> 5 WHERE D_W_ID = :w_id >> 6 AND D_ID = :d_id >> 7 ) AS OT > > A lot of this is non-standard SQL, so I can't really tell what DB2 is > doing > here. Can you explain it? Quote from the article at: http://www-106.ibm.com/developerworks/db2/library/techarticle/dm -0411rielau/?ca=dgr-lnxw06SQL-Speed > First, DB2 deals with the DISTRICT table. Data needs to be returned > and an update needs to be performed. Conventional wisdom states that > this requires 2 SQL statements, and that the UPDATE ought to be done > prior to the SELECT; otherwise deadlocks may occur as concurrency > increases. > > DB2 however supports a new SQL feature which is in the process of > being standardized. This feature allows access to what is known as > transition tables in triggers. The OLD TABLE transition table holds > the original state of the affected rows before they are processed by > the UPDATE or DELETE statement. The NEW TABLE transition table holds > the affected rows immediately after an INSERT or UPDATE was processed. > That is the state prior to when AFTER triggers fire. Users with a > Microsoft or Sybase background may know these tables by the names > DELETED and INSERTED. So, if I understand they use only ONE query to get the UPDATE and the SELECT of the old value. Cordialement, Jean-Gérard Pailloncy
Clinging to sanity, jg@rilk.com (Pailloncy Jean-Gérard) mumbled into her beard: > I see this article about DB2 > http://www-106.ibm.com/developerworks/db2/library/techarticle/dm > -0411rielau/?ca=dgr-lnxw06SQL-Speed > > The listing 2 example: > 1 SELECT D_TAX, D_NEXT_O_ID > 2 INTO :dist_tax , :next_o_id > 3 FROM OLD TABLE ( UPDATE DISTRICT > 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1 > 5 WHERE D_W_ID = :w_id > 6 AND D_ID = :d_id > 7 ) AS OT > > I am not a expert in Rule System. > But I ad a look to > http://www.postgresql.org/docs/7.4/static/rules-update.html > And it seems possible in PostgreSQL to build non standard SQL query to > do thing like listing 2. > > I would like to know from an "expert" of PostgreSQL if such query is > really a new stuff to DB2 as the artcile states ? or if PostgreSQL has > already the same type of power ? This feature (which evidently was derived from something in Sybase, which Microsoft therefore brought into their version of SQL Server) allows the Gentle User to do a mass update on a table (what's parenthesized), and then do some manipulations on the rows that ware affected by that mass update, where OLD TABLE returns the _former_ state of rows that were updated/deleted, and NEW TABLE would return the _new_ state of rows that were inserted/updated. It would be possible to do something analagous using rules, but the implementation would look VERY different from this. In effect, you would have to add, temporarily, a rule that does the thing akin to "select d_tax, d_next_o_id into some table" for the three cases: 1. on insert, do something with NEW.D_TAX, NEW.D_NEXT_O_ID to correspond to the insert case; 2. on update, do something with NEW.D_TAX, NEW.D_NEXT_O_ID to correspond with an update, doing something with the NEW values; 3. on update, do something with OLD.D_TAX, OLD.D_NEXT_O_ID to correspond with an update, doing something with the OLD values; 4. on delete, do something with OLD.D_TAX, OLD.D_NEXT_O_ID... You'd create the a rule to do things row-by-row. The efficiency of this actually ought to be pretty good; such rules would be tightly firing over and over each time a row was affected by the query, and since the data being processed would be in cache, it would be eminently quickly accessible. But, compared to the DB2 approach, it involves creating and dropping rules on the fly... -- "cbbrowne","@","acm.org" http://www.ntlug.org/~cbbrowne/linuxdistributions.html Signs of a Klingon Programmer - 11. "By filing this bug report you have challenged the honor of my family. Prepare to die!"