group by cube or group by grouping

[juliane26]

Hi,

I am aware that a group by cube is not available in DB2 z/OS V8.
Any idea how to realize the same in a single SQL statement ?

I cannot find anything like that in DB2 z/OS V9 documentation either: does anybody know whether it is planned to be included in future versions ?

Thank you very much !

Juliane

 

[MarcLodge]

Sorry Julianne, but I'm not aware of this function.

Could you explain what it is you are after?

Marc

 

[blom0344]

CUBE will allow you generate output with sub-totals on different levels in the generated output of the SQL.
It's use is rather limited as it should only be used when taking the output as a direct means of displaying results.
(and when will this happen?)
The job of such a specific construct if often handled (in reporting terms) by using the capabilities of BI-tools. These take the simple set and use it to add subtotals in a user-friendly way..

Ties Blom

 

[juliane26]

Marc, Ties,

I am actually not looking for the cube itself ( as Ties noted the complete set is probably not needed) but for constructs like GROUP BY GROUPING SETS ...
there the subtotals can be defined for some level needed.
And yes, we could use that in some batches and if only for checking or controlling purposes.

Marc, you can see a sample here from DB2 LUW:

http://publib.boulder.ibm.com/infoc...com.ibm.db2.luw.sql.ref.doc/doc/r0000875.html

Further down this page are samples for how to use GROUP BY GROUPING SETS as equivalent to GROUP BY ROLLUP or GROUP BY CUBE. And I do think that GROUPING SETS can be useful. ROLLUP and CUBE migth be a little to much, but GROUPING SETS enable to define very specific subtotals.

Well, I am afraid nothing included for V9 as well.

Ties: we are still using QMF extensivly on the host and not implementing a SAS solution or other BI tool for every time some numbers are needed - to these things would be useful for us.

Thank you !

Juliane.

Juliane

 

[blom0344]

Birchall's Cookbooks goes quite intensively into grouping sets, but this is not aimed at Z/os I guess. However, perhaps it may serve as a source of inspiration?

Ties Blom