SQL to find if table has identity and/or row change timestamp columns

[Frank Swarbrick]

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.

[Serge Rielau]

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.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

>>On 6/22/2008 at 6:58 PM, in message
<6c*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

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):

create view load_modifiers
as
select tabschema
, tabname
, max(case when identity = 'Y' then 'identityoverride' else '' end) as
identity_modifier
, max(case when rowchangetimestamp = 'Y' then
'rowchangetimestampoverride' else '' end) as rowchangetimestamp_modifier
from syscat.columns
group by tabschema, tabname
;

select identity_modifier
, rowchangetimestamp_modifier
from load_modifiers
where tabschema = 'CUSTOMER'
and tabname = 'ACCOUNTS'
;

IDENTITY_MODIFIER ROWCHANGETIMESTAMP_MODIFIER
----------------- ---------------------------
identityoverride rowchangetimestampoverride

1 record(s) selected.

Also gets good results when table does not include one or the other (or
both) types of columns.

Good stuff!

Frank

[Serge Rielau]

Frank Swarbrick wrote:

>>>On 6/22/2008 at 6:58 PM, in message

<6c*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

>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