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_MODIFI ER is either 'identityoverri de' or '' and
ROWCHANGETIMEST AMP_MODIFIER is either 'rowchangetimes tampoverride' or ''?

select
case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and identity = 'Y'
) then 'identityoverri de'
else ''
end as identity_modifi er
, case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and rowchangetimest amp = 'Y'
) then 'rowchangetimes tampoverride'
else ''
end as rowchangetimest amp_modifier
from sysibm.sysdummy 1;

Results:

IDENTITY_MODIFI ER ROWCHANGETIMEST AMP_MODIFIER
----------------- ---------------------------
identityoverrid e rowchangetimest ampoverride

1 record(s) selected.

[Serge Rielau]

Re: SQL to find if table has identity and/or row change timestampcolumn s

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_MODIFI ER is either 'identityoverri de' or '' and
ROWCHANGETIMEST AMP_MODIFIER is either 'rowchangetimes tampoverride' or ''?
>
select
case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and identity = 'Y'
) then 'identityoverri de'
else ''
end as identity_modifi er
, case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and rowchangetimest amp = 'Y'
) then 'rowchangetimes tampoverride'
else ''
end as rowchangetimest amp_modifier
from sysibm.sysdummy 1;
>
Results:
>
IDENTITY_MODIFI ER ROWCHANGETIMEST AMP_MODIFIER
----------------- ---------------------------
identityoverrid e rowchangetimest ampoverride
>
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]

Re: SQL to find if table has identity and/or row changetimestamp columns

>>On 6/22/2008 at 6:58 PM, in message
<6c8apkF28va8mU 1@mid.individua l.net>,
Serge Rielau<srielau@ 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_MODIF IER is either 'identityoverri de' or '' and
>ROWCHANGETIMES TAMP_MODIFIER is either 'rowchangetimes tampoverride' or

''?

>>
>select
> case
> when exists (
> select *
> from syscat.columns
> where tabschema = 'FRANK'
> and tabname = 'INVOICE'
> and identity = 'Y'
> ) then 'identityoverri de'
> else ''
> end as identity_modifi er
> , case
> when exists (
> select *
> from syscat.columns
> where tabschema = 'FRANK'
> and tabname = 'INVOICE'
> and rowchangetimest amp = 'Y'
> ) then 'rowchangetimes tampoverride'
> else ''
> end as rowchangetimest amp_modifier
>from sysibm.sysdummy 1;
>>
>Results:
>>
>IDENTITY_MODIF IER ROWCHANGETIMEST AMP_MODIFIER
>----------------- ---------------------------
>identityoverri de rowchangetimest ampoverride
>>
> 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 'identityoverri de' else '' end) as
identity_modifi er
, max(case when rowchangetimest amp = 'Y' then
'rowchangetimes tampoverride' else '' end) as rowchangetimest amp_modifier
from syscat.columns
group by tabschema, tabname
;

select identity_modifi er
, rowchangetimest amp_modifier
from load_modifiers
where tabschema = 'CUSTOMER'
and tabname = 'ACCOUNTS'
;

IDENTITY_MODIFI ER ROWCHANGETIMEST AMP_MODIFIER
----------------- ---------------------------
identityoverrid e rowchangetimest ampoverride

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]

Re: SQL to find if table has identity and/or row change timestampcolumn s

Frank Swarbrick wrote:

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

<6c8apkF28va8mU 1@mid.individua l.net>,
Serge Rielau<srielau@ 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_MODI FIER is either 'identityoverri de' or '' and
>>ROWCHANGETIME STAMP_MODIFIER is either 'rowchangetimes tampoverride' or

>''?

>>select
>> case
>> when exists (
>> select *
>> from syscat.columns
>> where tabschema = 'FRANK'
>> and tabname = 'INVOICE'
>> and identity = 'Y'
>> ) then 'identityoverri de'
>> else ''
>> end as identity_modifi er
>> , case
>> when exists (
>> select *
>> from syscat.columns
>> where tabschema = 'FRANK'
>> and tabname = 'INVOICE'
>> and rowchangetimest amp = 'Y'
>> ) then 'rowchangetimes tampoverride'
>> else ''
>> end as rowchangetimest amp_modifier
>>from sysibm.sysdummy 1;
>>>
>>Results:
>>>
>>IDENTITY_MODI FIER ROWCHANGETIMEST AMP_MODIFIER
>>----------------- ---------------------------
>>identityoverr ide rowchangetimest ampoverride
>>>
>> 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