SQL to find if table has identity and/or row change timestampcolumns
in [DB2]
|
Prev: SQL to find if table has identity and/or row changetimestampcolumns
Next: Reorgchk, Reorg, Runstats not having any effect after completion. Why is this?
From: Serge Rielau on 23 Jun 2008 11:41 Frank Swarbrick wrote: >>>> On 6/22/2008 at 6:58 PM, in message > <6c8apkF28va8mU1(a)mid.individual.net>, > Serge Rielau<srielau(a)ca.ibm.com> wrote: >> Frank Swarbrick wrote: >>> How can I fix this so that it doesn't do essentially the same scan >>> twice, but it gives the same results (a single row where >>> IDENTITY_MODIFIER is either 'identityoverride' or '' and >>> ROWCHANGETIMESTAMP_MODIFIER is either 'rowchangetimestampoverride' or >> ''? >>> select >>> case >>> when exists ( >>> select * >>> from syscat.columns >>> where tabschema = 'FRANK' >>> and tabname = 'INVOICE' >>> and identity = 'Y' >>> ) then 'identityoverride' >>> else '' >>> end as identity_modifier >>> , case >>> when exists ( >>> select * >>> from syscat.columns >>> where tabschema = 'FRANK' >>> and tabname = 'INVOICE' >>> and rowchangetimestamp = 'Y' >>> ) then 'rowchangetimestampoverride' >>> else '' >>> end as rowchangetimestamp_modifier >>> from sysibm.sysdummy1; >>> >>> Results: >>> >>> IDENTITY_MODIFIER ROWCHANGETIMESTAMP_MODIFIER >>> ----------------- --------------------------- >>> identityoverride rowchangetimestampoverride >>> >>> 1 record(s) selected. >> You need to select straight from syscat.columns and then use GROUP BY to >> >> pivot the two rows into columns. > > Thanks, Serge! You made me work a bit <g>, but here's what I now have > (placed into a VIEW): You got it right. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |