from V7 to V8: ah! new SQL features?

[Truusvlugindewind]

At the office we've got V8 on the mainframe (test only for now) and at home I've got V9 in my linux-laptop.

And now the business case is: 1 table with actual data, and 1 table with history-data. Can you delete rows from the actual table and store those in the history?

Hmm ... all those new features... can I do this in only 1 SQL?

So I think of this (AA = actual table/HH = history)

insert into HH (a, b) 
       values 
         (select a, b from old table
                    (delete from aa))

No go. Not valid SQL. try this:

select a, b from old table
            (delete from aa)

even that does not work.
end-of-day: go home: laptop on lap:
1st SQL := no go
2nd SQL := yeah! it works

and now: confused. that "old table" thing was introduced in V8 right? Also for mainframe, right? then why does it not work?

 

[blom0344]

Mainframe DB2 always lags behind SQL-wise compared to what is possible on other platforms. The same goes for DB2/AS400.
Sometimes differences are really subtle, in other cases whole sets of functions are simply not available.
The first SQL is wrong in any case. The values clause always expects literal input (and not another select)

Ties Blom

 

[sathyarams]

As Ties said, mainframe DB2's SQL 'lags' behind LUW's ... DB2 zOS v9's SQL is much better than all previous versions and is more compatible to LUW ..

Well, about deleting from one table and storing the records in another in a single SQL Statement, it is possible usin a CTE, since LUW 8.1.4(IIRC) and DB2 9 on zos

with temp1 as (select * from old table (delete from aa)),
temp2 as (select * from new table(insert into hh select * from temp1)) 
select * from temp2

HTH

sathyaram


For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Truusvlugindewind]

HORROR: I just found out that INTERSECT and EXCEPT are also not supported by DB2 on Z.
I thought that was generic ANSI-SQL?

 

[sathyarams]

yep, it is annoying ;-(

On LUW, i have except and intersect being converted to union by the optimizer. If you want to use except/intersect on zos, you can consider firing the luw optimized query on zos.

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp