Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil Grainger

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
And how many of us still flinch when column LENGTHS are not an integral number of bytes (or even words?)

CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch

(And I KNOW it doesn’t matter anymore, but some habits die hard)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Michael Hannan <[login to unmask email]>
Sent: 01 August 2019 08:10
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format



In Reply to Joe Geller:

My pet peeve is column names with the data_type. If you change the datatype you also have to rename the column (which of course doesn't happen). But worse than a suffix of DATE, NUM etc. is a prefix of the datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE. That makes it harder to find all columns related to a business function (dt_shipdate, tm_shipdate, n_shippingcost, c_shipmethod) - they are no longer alphabetically close to each other.

Joe

A little surprised Joe. Does a DATE change? Does a NUM (number) change to be not a number. Does amoney AMT (amount) suddenly become not an amount? I know there are text type id numbers and other numbers suitable for arithmetic. I would have thought data type changes are quite rare unless original data type was a bad mistake, or maybe just to make the column bigger, but certainly column names should not reflect Smallint, Int, Dec, etc. That would indeed be silly. A more generic data type can maybe make SQL a slightly easier to understand, but I don't care too much really.

I hate to see a column as CHAR(80), in a table if length of the used portion of the 80 varies. It should usually be VARCHAR so if indexed can use the actual length. So CHAR is only for short character columns. Still see a long CHAR sometimes.

Was unfortunate in the old days we had Padded VARCHAR in indexes without the length.

One curiosity. FLOAT (length 8 and high precision) is used infrequently, but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most measuring data. We rarely need huge significant digits.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

Marcus Davage

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Phil Grainger)
What’s even worse is column definitions like:

FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
BIRTH_DATE VARCHAR(255),
SALARY VARCHAR(255)

Not that I’ve ever seen that before in third-party tools. Nope. Not me.

Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 – SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





From: Grainger, Phil <[login to unmask email]>
Sent: 01 August 2019 09:46
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

And how many of us still flinch when column LENGTHS are not an integral number of bytes (or even words?)

CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch

(And I KNOW it doesn’t matter anymore, but some habits die hard)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 01 August 2019 08:10
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format



In Reply to Joe Geller:

My pet peeve is column names with the data_type. If you change the datatype you also have to rename the column (which of course doesn't happen). But worse than a suffix of DATE, NUM etc. is a prefix of the datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE. That makes it harder to find all columns related to a business function (dt_shipdate, tm_shipdate, n_shippingcost, c_shipmethod) - they are no longer alphabetically close to each other.

Joe

A little surprised Joe. Does a DATE change? Does a NUM (number) change to be not a number. Does amoney AMT (amount) suddenly become not an amount? I know there are text type id numbers and other numbers suitable for arithmetic. I would have thought data type changes are quite rare unless original data type was a bad mistake, or maybe just to make the column bigger, but certainly column names should not reflect Smallint, Int, Dec, etc. That would indeed be silly. A more generic data type can maybe make SQL a slightly easier to understand, but I don't care too much really.

I hate to see a column as CHAR(80), in a table if length of the used portion of the 80 varies. It should usually be VARCHAR so if indexed can use the actual length. So CHAR is only for short character columns. Still see a long CHAR sometimes.

Was unfortunate in the old days we had Padded VARCHAR in indexes without the length.

One curiosity. FLOAT (length 8 and high precision) is used infrequently, but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most measuring data. We rarely need huge significant digits.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
________________________________
Attachment Links: image001.jpg (51 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10354&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=ZWqceorUHVTXxruhJsiwxXPMD444EYOZdXGno8oD4T0&e= image002.png (7 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10355&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=bGi4bNLHhRpWDqBlsNFrEQ9N-BO7eT6CXblLyera4I0&e= image003.jpg (2 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10356&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=g3vr85W2Z1zuW4xQVlvG8a7g1g9GWe8INX8clv6QaLE&e= image004.png (1 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10357&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=--m_rxJfkuudLfI37eBvUPrZwhfTKj4ehSXCcnJ4RtM&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D189769-26anc-3Dp189769-23p189769&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=098sSt2EBf1eQ0bbSX3r1AErGZkz1S-pUKSwcqVknpE&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=kQcSzRAqKlA3ewHtK_OHNeRtGkWgkKuOJKyvUI2Ucwk&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=08P33ozTSWcf1qrZn4IbN1iOzCc-0PlKpvwvJ7XfZvY&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=L3Hyk94fiX1YwbFXCM_2uu5EeJmsi3duK2T8yr0u7nY&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=mhV0yJKfitu0qAYB_odGUoLWNP8v4L54s7lNo_VjYxo&m=SgaRhdVLDhnPXN2SL2tQ0Ldsc2S9PfBZci7rtBZPcV4&s=0uxPHexpKK7PjJhHMrcsovSBtf13lukqKNWKsYEAtUA&e=

________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image006.jpg (49.7k)
  • image007.png (6.7k)
  • image008.jpg (1.6k)
  • image009.png (<1k)
  • image001.png (12.9k)

Raymond Bell

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Marcus Davage)
I used to twitch when I saw even-precision numerics. DECIMAL(8), etc. VARCHAR(1) is another favourite.

Almost as useful as having a CLOB store the SQL statements in a package…

Cheers,


Raymond

From: Davage, Marcus <[login to unmask email]>
Sent: 01 August 2019 10:01
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

What’s even worse is column definitions like:

FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
BIRTH_DATE VARCHAR(255),
SALARY VARCHAR(255)

Not that I’ve ever seen that before in third-party tools. Nope. Not me.

Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 – SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 01 August 2019 09:46
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

And how many of us still flinch when column LENGTHS are not an integral number of bytes (or even words?)

CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch

(And I KNOW it doesn’t matter anymore, but some habits die hard)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 01 August 2019 08:10
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format



In Reply to Joe Geller:

My pet peeve is column names with the data_type. If you change the datatype you also have to rename the column (which of course doesn't happen). But worse than a suffix of DATE, NUM etc. is a prefix of the datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE. That makes it harder to find all columns related to a business function (dt_shipdate, tm_shipdate, n_shippingcost, c_shipmethod) - they are no longer alphabetically close to each other.

Joe

A little surprised Joe. Does a DATE change? Does a NUM (number) change to be not a number. Does amoney AMT (amount) suddenly become not an amount? I know there are text type id numbers and other numbers suitable for arithmetic. I would have thought data type changes are quite rare unless original data type was a bad mistake, or maybe just to make the column bigger, but certainly column names should not reflect Smallint, Int, Dec, etc. That would indeed be silly. A more generic data type can maybe make SQL a slightly easier to understand, but I don't care too much really.

I hate to see a column as CHAR(80), in a table if length of the used portion of the 80 varies. It should usually be VARCHAR so if indexed can use the actual length. So CHAR is only for short character columns. Still see a long CHAR sometimes.

Was unfortunate in the old days we had Padded VARCHAR in indexes without the length.

One curiosity. FLOAT (length 8 and high precision) is used infrequently, but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most measuring data. We rarely need huge significant digits.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image002.png (7.2k)
  • image003.jpg (49.7k)
  • image004.png (6.7k)
  • image005.jpg (1.6k)
  • image007.png (<1k)

Neil Price

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Raymond Bell)
Of course some of those are due to porting from other RDBMS such as the one
with an "O".

On Thu, 1 Aug 2019 at 10:25, Bell, Raymond (Hosting Services, Technology) <
[login to unmask email]> wrote:

> I used to twitch when I saw even-precision numerics. DECIMAL(8), etc.
> VARCHAR(1) is another favourite.
>
>
>
> Almost as useful as having a CLOB store the SQL statements in a package…
>
>
>
> Cheers,
>
>
>
>
>
> Raymond
>
>
>
> *From:* Davage, Marcus <[login to unmask email]>
> *Sent:* 01 August 2019 10:01
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler
> Contoken Format)
>
>
>
>
> *********************************************
> " This message originates from outside our organisation. Consider
> carefully whether you should click on any links, open any attachments or
> reply. If in doubt, forward to ~ Phishing"
> *********************************************
>
> What’s even worse is column definitions like:
>
>
>
> FIRST_NAME VARCHAR(255),
>
> LAST_NAME VARCHAR(255),
>
> BIRTH_DATE VARCHAR(255),
>
> SALARY VARCHAR(255)
>
>
>
> Not that I’ve ever seen that before in third-party tools. Nope. Not me.
>
>
>
> Regards,
>
> *Marcus Davage*
>
> Lead Product Developer
>
> AMI-DevOps for Db2 – SQL Performance
>
> *Direct*
>
> +44 118 921 8517
>
>
>
> *Mobile*
>
> +44 7840 023 560
>
>
>
> *Email*
>
> [login to unmask email]
>
>
>
>
>
>
>
> *From:* Grainger, Phil <[login to unmask email]>
> *Sent:* 01 August 2019 09:46
> *To:* [login to unmask email]
> *Subject:* [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2
> Precompiler Contoken Format)
>
>
>
> And how many of us still flinch when column LENGTHS are not an integral
> number of bytes (or even words?)
>
>
>
> CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch
>
>
>
> (And I KNOW it doesn’t matter anymore, but some habits die hard)
>
>
>
> *Phil Grainger*
> Principal Enablement Manager
>
> [image: BMC Exchange 2019 - Global Event Series - REGISTER]
> https://www.bmc.com/ami
>
> *Direct*
>
> +44 1189 218 000
>
> *Mobile*
>
> +44 7808 643 479
>
> *Email*
>
> [login to unmask email]
>
> E2, Eskdale Road
> Winnersh
> Berkshire
> United Kingdom
> RG41 5TS
>
> [image: image001 (002)][image:
> https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]
>
>
>
>
>
> *From:* Michael Hannan <[login to unmask email]>
> *Sent:* 01 August 2019 08:10
> *To:* [login to unmask email]
> *Subject:* [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format
>
>
>
>
>
> In Reply to Joe Geller:
>
> My pet peeve is column names with the data_type. If you change the
> datatype you also have to rename the column (which of course doesn't
> happen). But worse than a suffix of DATE, NUM etc. is a prefix of the
> datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE. That makes it harder to
> find all columns related to a business function (dt_shipdate, tm_shipdate,
> n_shippingcost, c_shipmethod) - they are no longer alphabetically close to
> each other.
>
> Joe
>
> A little surprised Joe. Does a DATE change? Does a NUM (number) change to
> be not a number. Does amoney AMT (amount) suddenly become not an amount? I
> know there are text type id numbers and other numbers suitable for
> arithmetic. I would have thought data type changes are quite rare unless
> original data type was a bad mistake, or maybe just to make the column
> bigger, but certainly column names should not reflect Smallint, Int, Dec,
> etc. That would indeed be silly. A more generic data type can maybe make
> SQL a slightly easier to understand, but I don't care too much really.
>
> I hate to see a column as CHAR(80), in a table if length of the used
> portion of the 80 varies. It should usually be VARCHAR so if indexed can
> use the actual length. So CHAR is only for short character columns. Still
> see a long CHAR sometimes.
>
> Was unfortunate in the old days we had Padded VARCHAR in indexes without
> the length.
>
> One curiosity. FLOAT (length 8 and high precision) is used infrequently,
> but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most
> measuring data. We rarely need huge significant digits.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
>
> -----End Original Message-----
>
> BMC Software Limited Registered Office: Building E2, Eskdale Road,
> Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in
> England No. 1927903 The content of this email is confidential. If you are
> not the addressee, you may not distribute, copy or disclose any part of it.
> If you receive this message in error, please delete this from your system
> and notify the sender immediately.
>
> -----End Original Message-----
>
> BMC Software Limited Registered Office: Building E2, Eskdale Road,
> Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in
> England No. 1927903 The content of this email is confidential. If you are
> not the addressee, you may not distribute, copy or disclose any part of it.
> If you receive this message in error, please delete this from your system
> and notify the sender immediately.
> -----End Original Message-----
>
> The Royal Bank of Scotland plc. Registered in Scotland No 83026.
> Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank
> of Scotland is authorised by the Prudential Regulation Authority, and
> regulated by the Financial Conduct Authority and Prudential Regulation
> Authority. The Royal Bank of Scotland N.V. is authorised and regulated by
> the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands,
> and is registered in the Commercial Register under number 33002587.
> Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The
> Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are
> authorised to act as agent for each other in certain jurisdictions.
>
>
>
> National Westminster Bank Plc. Registered in England No. 929027.
> Registered Office: 250 Bishopsgate, London EC2M 4AA. National Westminster
> Bank Plc is authorised by the Prudential Regulation Authority, and
> regulated by the Financial Conduct Authority and the Prudential Regulation
> Authority.
>
>
>
> The Royal Bank of Scotland plc and National Westminster Bank Plc are
> authorised to act as agent for each other.
>
>
>
> This e-mail message is confidential and for use by the addressee only. If
> the message is received by anyone other than the addressee, please return
> the message to the sender by replying to it and then delete the message
> from your computer. Internet e-mails are not necessarily secure. The
> Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National
> Westminster Bank Plc or any affiliated entity (RBS or us) does not accept
> responsibility for changes made to this message after it was sent. RBS may
> monitor e-mails for business and operational purposes. By replying to this
> message you understand that the content of your message may be monitored.
>
>
>
> Whilst all reasonable care has been taken to avoid the transmission of
> viruses, it is the responsibility of the recipient to ensure that the
> onward transmission, opening or use of this message and any attachments
> will not adversely affect its systems or data. No responsibility is
> accepted by RBS in this regard and the recipient should carry out such
> virus and other checks as it considers appropriate.
>
>
> Visit our website at www.rbs.com
> -----End Original Message-----
>
Attachments

  • image002.png (7.2k)
  • image003.jpg (49.7k)
  • image004.png (6.7k)
  • image005.jpg (1.6k)
  • image007.png (<1k)

Raymond Bell

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Neil Price)
Mr. Price, greetings! Julian treating you well?

Ah, yes, our friends in Fisher-Price land. O, as in OMG, I’ve just been Tango’d. :o)

Cheers,


Raymond

From: Neil Price <[login to unmask email]>
Sent: 01 August 2019 10:52
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Of course some of those are due to porting from other RDBMS such as the one with an "O".

On Thu, 1 Aug 2019 at 10:25, Bell, Raymond (Hosting Services, Technology) <[login to unmask email]<mailto:[login to unmask email]>> wrote:
I used to twitch when I saw even-precision numerics. DECIMAL(8), etc. VARCHAR(1) is another favourite.

Almost as useful as having a CLOB store the SQL statements in a package…

Cheers,


Raymond

From: Davage, Marcus <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 01 August 2019 10:01
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
What’s even worse is column definitions like:

FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
BIRTH_DATE VARCHAR(255),
SALARY VARCHAR(255)

Not that I’ve ever seen that before in third-party tools. Nope. Not me.

Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 – SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 01 August 2019 09:46
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

And how many of us still flinch when column LENGTHS are not an integral number of bytes (or even words?)

CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch

(And I KNOW it doesn’t matter anymore, but some habits die hard)

Phil Grainger
Principal Enablement Manager

https://www.bmc.com/ami [BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami><https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 01 August 2019 08:10
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format



In Reply to Joe Geller:

My pet peeve is column names with the data_type. If you change the datatype you also have to rename the column (which of course doesn't happen). But worse than a suffix of DATE, NUM etc. is a prefix of the datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE. That makes it harder to find all columns related to a business function (dt_shipdate, tm_shipdate, n_shippingcost, c_shipmethod) - they are no longer alphabetically close to each other.

Joe

A little surprised Joe. Does a DATE change? Does a NUM (number) change to be not a number. Does amoney AMT (amount) suddenly become not an amount? I know there are text type id numbers and other numbers suitable for arithmetic. I would have thought data type changes are quite rare unless original data type was a bad mistake, or maybe just to make the column bigger, but certainly column names should not reflect Smallint, Int, Dec, etc. That would indeed be silly. A more generic data type can maybe make SQL a slightly easier to understand, but I don't care too much really.

I hate to see a column as CHAR(80), in a table if length of the used portion of the 80 varies. It should usually be VARCHAR so if indexed can use the actual length. So CHAR is only for short character columns. Still see a long CHAR sometimes.

Was unfortunate in the old days we had Padded VARCHAR in indexes without the length.

One curiosity. FLOAT (length 8 and high precision) is used infrequently, but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most measuring data. We rarely need huge significant digits.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 250 Bishopsgate, London EC2M 4AA. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.


Visit our website at www.rbs.com http://www.rbs.com
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image001.png (7.2k)
  • image002.jpg (49.7k)
  • image003.png (6.7k)
  • image004.jpg (1.6k)
  • image005.png (<1k)

Roy Boxwell

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Raymond Bell)
I have seen a CLOB for 255 bytes.... truly horrible...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

> On 1 Aug 2019, at 11:25, Bell, Raymond (Hosting Services, Technology) <[login to unmask email]> wrote:
>
> I used to twitch when I saw even-precision numerics. DECIMAL(8), etc. VARCHAR(1) is another favourite.
>
> Almost as useful as having a CLOB store the SQL statements in a package…
>
> Cheers,
>
>
> Raymond
>
> From: Davage, Marcus <[login to unmask email]>
> Sent: 01 August 2019 10:01
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
>
>
> *********************************************
> " This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
> *********************************************
>
>
> What’s even worse is column definitions like:
>
> FIRST_NAME VARCHAR(255),
> LAST_NAME VARCHAR(255),
> BIRTH_DATE VARCHAR(255),
> SALARY VARCHAR(255)
>
> Not that I’ve ever seen that before in third-party tools. Nope. Not me.
>
> Regards,
> Marcus Davage
> Lead Product Developer
> AMI-DevOps for Db2 – SQL Performance
> Direct
> +44 118 921 8517
>
>
> Mobile
> +44 7840 023 560
>
> Email
> [login to unmask email]
>
>
>
> From: Grainger, Phil <[login to unmask email]>
> Sent: 01 August 2019 09:46
> To: [login to unmask email]
> Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
>
> And how many of us still flinch when column LENGTHS are not an integral number of bytes (or even words?)
>
> CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch
>
> (And I KNOW it doesn’t matter anymore, but some habits die hard)
>
> Phil Grainger
> Principal Enablement Manager
>
> Direct
> +44 1189 218 000
> Mobile
> +44 7808 643 479
> Email
> [login to unmask email]
> E2, Eskdale Road
> Winnersh
> Berkshire
> United Kingdom
> RG41 5TS
>
>
>
> From: Michael Hannan <[login to unmask email]>
> Sent: 01 August 2019 08:10
> To: [login to unmask email]
> Subject: [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format
>
>
>
> In Reply to Joe Geller:
>
> My pet peeve is column names with the data_type. If you change the datatype you also have to rename the column (which of course doesn't happen). But worse than a suffix of DATE, NUM etc. is a prefix of the datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE. That makes it harder to find all columns related to a business function (dt_shipdate, tm_shipdate, n_shippingcost, c_shipmethod) - they are no longer alphabetically close to each other.
>
> Joe
>
> A little surprised Joe. Does a DATE change? Does a NUM (number) change to be not a number. Does amoney AMT (amount) suddenly become not an amount? I know there are text type id numbers and other numbers suitable for arithmetic. I would have thought data type changes are quite rare unless original data type was a bad mistake, or maybe just to make the column bigger, but certainly column names should not reflect Smallint, Int, Dec, etc. That would indeed be silly. A more generic data type can maybe make SQL a slightly easier to understand, but I don't care too much really.
>
> I hate to see a column as CHAR(80), in a table if length of the used portion of the 80 varies. It should usually be VARCHAR so if indexed can use the actual length. So CHAR is only for short character columns. Still see a long CHAR sometimes.
>
> Was unfortunate in the old days we had Padded VARCHAR in indexes without the length.
>
> One curiosity. FLOAT (length 8 and high precision) is used infrequently, but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most measuring data. We rarely need huge significant digits.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
> -----End Original Message-----
> BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
> -----End Original Message-----
> BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
> -----End Original Message-----
> The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.
>
> National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 250 Bishopsgate, London EC2M 4AA. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.
>
> The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.
>
> This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.
>
> Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
>
> Visit our website at www.rbs.com
> Attachment Links: image002.png (7 k) image003.jpg (51 k) image004.png (7 k) image005.jpg (2 k) image007.png (1 k)
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Paul Ogborne

Almost Friday - datatypes (was Db2 PrecompilerContoken Format)
(in response to Phil Grainger)
VARCHAR used to be a favourite for Java programmers who might have been calling Db2 stored procedures.
So I have even coded VARCHAR(1) in the past; I didn’t enjoy it though!

Regards,
Paul Ogborne

Sent from Mail for Windows 10

From: Grainger, Phil
Sent: 01 August 2019 09:45
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 PrecompilerContoken Format)

And how many of us still flinch when column LENGTHS are not an integral number of bytes (or even words?)

CHAR(8), CHAR(12), CHAR(16) – all good – but CHAR(11)?? Ouch

(And I KNOW it doesn’t matter anymore, but some habits die hard)

Phil Grainger
Principal Enablement Manager

Direct
+44 1189 218 000

Mobile
+44 7808 643 479

Email
[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS




From: Michael Hannan <[login to unmask email]>
Sent: 01 August 2019 08:10
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Db2 Precompiler Contoken Format



In Reply to Joe Geller:
My pet peeve is column names with the data_type.  If you change the datatype you also have to rename the column (which of course doesn't happen).  But worse than a suffix of DATE, NUM etc. is a prefix of the datatype - N_AMOUNT, C_LASTNAME, DT_SHIPDATE.   That makes it harder to find all columns related to a business function (dt_shipdate, tm_shipdate, n_shippingcost, c_shipmethod) - they are no longer alphabetically close to each other.
Joe
A little surprised Joe. Does a DATE change? Does a NUM (number) change to be not a number. Does amoney AMT (amount) suddenly become not an amount? I know there are text type id numbers and other numbers suitable for arithmetic. I would have thought data type changes are quite rare unless original data type was a bad mistake, or maybe just to make the column bigger, but certainly column names should not reflect Smallint, Int, Dec, etc. That would indeed be silly. A more generic data type can maybe make SQL a slightly easier to understand, but I don't care too much really.

I hate to see a column as CHAR(80), in a table if length of the used portion of the 80 varies. It should usually be VARCHAR so if indexed can use the actual length. So CHAR is only for short character columns. Still see a long CHAR sometimes.
Was unfortunate in the old days we had Padded VARCHAR in indexes without the length. 
One curiosity. FLOAT (length 8 and high precision) is used infrequently, but REAL (FLOAT(4)) is very rarely seen. Yet it would be adequate for most measuring data. We rarely need huge significant digits.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Attachment Links: image001.jpg (51 k)   image002.png (7 k)   image003.jpg (2 k)   image004.png (1 k)  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 0A919B8A8CD942F98F4E78C7F0EB9B5E.png (2.9k)
  • 4FAB0689FC924D1588324B86FE52FFAE.png (<1k)

Michael Hannan

RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Marcus Davage)

Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR.  Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Daniel Luksetich

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Michael Hannan)
Michael,

I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…



In the US:

“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”



Imagine in China…



And you have to find them in 126ms!



I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Michael Hannan <[login to unmask email]>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)



Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



-----End Original Message-----

Attachments

  • image001.png (18.4k)
  • image002.png (16.2k)
  • image003.png (20k)
  • image004.png (21.4k)
  • image005.png (17.8k)
  • image006.png (17.4k)

Phil Grainger

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Daniel Luksetich)
“I almost never pay attention to physical storage” – now I just feel very, very old ☹

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Daniel L Luksetich <[login to unmask email]>
Sent: 09 August 2019 14:52
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Michael,
I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…

In the US:
“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”

Imagine in China…

And you have to find them in 126ms!

I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e= | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

________________________________
Attachment Links: image001.png (19 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10437&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=BPWAOcamTf4lzZy4hFtxTY6kMG5Ipur7ZCKv4MrONp0&e= image002.png (17 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10438&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=3H2Jdt3Z4cItGd0svAL7BkqYZlY7KoLv0q0aac5fSG4&e= image003.png (21 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10439&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=wDo3ls5SETdsbNyPI3MnwmLDYKj8fIDDsG_qetxeLYc&e= image004.png (22 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10440&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=8UKV1DyOd_96LwAzhnAcHkCfvZZIXrfDvwwazRD8EFc&e= image005.png (18 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10441&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=0ORcpQAw7keXEyX-WYVktiRKrzprsduRqKMWwZQXeu4&e= image006.png (18 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10442&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=A2v2A4p4cqEDB-cHWurznffJcZaWb1q4eWebR_qayQY&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D189839-26anc-3Dp189839-23p189839&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=sQ-0ILgQe_NhYgb320LUZk_eINuNZqi17CW07iBsj48&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=MgNgkVhnN0gLAhM7HbROel0YjAf4esPx59srGO5Cy6k&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=QvfrkGa8YGf-9uYCsFcjdYPvgOJN84htk5lJY5q2DG0&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=25yMOmSuBolWPE6WCNUCX_9dvS52C1yMsbMTPTnxgDA&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=dgh4PNZ7wv_WKbXbi5BDc1pYYe635s9lF-BesQo5Qlc&e=

________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image007.jpg (49.7k)
  • image008.png (6.7k)
  • image009.jpg (1.6k)
  • image010.png (<1k)
  • image011.png (18.4k)
  • image012.png (16.2k)
  • image013.png (20k)
  • image014.png (21.4k)
  • image015.png (17.8k)
  • image016.png (17.4k)

Philip Sevetson

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Phil Grainger)
Phil G. –

My “now I’m old” realization of the year so far is that, if you’re on an SSD “Disk” but not doing sequential access, the level of disorganization on the table doesn’t matter. In SSD storage, there is no spin delay or seek time. It’s just calling a different address, and the access time is uniform.

The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load. All pages are “adjacent”.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]>
Sent: Friday, August 9, 2019 10:10 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“I almost never pay attention to physical storage” – now I just feel very, very old ☹

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 14:52
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Michael,
I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…

In the US:
“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”

Imagine in China…

And you have to find them in 126ms!

I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e= | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Phil Grainger

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Philip Sevetson)
“The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load” – unless you ARE doing sequential access!

We did measure performance degradation on disorganization even for SSDs if you are trying to read pages sequentially (and they’re not)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Sevetson, Phil <[login to unmask email]>
Sent: 09 August 2019 15:33
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil G. –

My “now I’m old” realization of the year so far is that, if you’re on an SSD “Disk” but not doing sequential access, the level of disorganization on the table doesn’t matter. In SSD storage, there is no spin delay or seek time. It’s just calling a different address, and the access time is uniform.

The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load. All pages are “adjacent”.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 10:10 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“I almost never pay attention to physical storage” – now I just feel very, very old ☹

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 14:52
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Michael,
I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…

In the US:
“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”

Imagine in China…

And you have to find them in 126ms!

I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e= | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10453&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=R-zmzQGY-crc93D8OX54uEaa2m1EksjxXJWypiiyadQ&s=N8LYo33OPM-Kljx56eaYSzcCG0wESMygUI2qkSWSXE8&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D189841-26anc-3Dp189841-23p189841&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=R-zmzQGY-crc93D8OX54uEaa2m1EksjxXJWypiiyadQ&s=TR_uzDyOzKmO1PfHw1-WRoHuK-D45rDLXQ-b-myxJpg&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=R-zmzQGY-crc93D8OX54uEaa2m1EksjxXJWypiiyadQ&s=mo7NekIZb6030laxDXVdBs4DPGAQsViUw1gow7NcrrM&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=R-zmzQGY-crc93D8OX54uEaa2m1EksjxXJWypiiyadQ&s=WY2AclIQZrjlPRjaHkqP6ZVq8YZAXv2fsWP07MIys_c&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=R-zmzQGY-crc93D8OX54uEaa2m1EksjxXJWypiiyadQ&s=jNMIyXfDnWXlKR44lBP8BDTb6IGv80n1Ifjy7_g0r8Q&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=R-zmzQGY-crc93D8OX54uEaa2m1EksjxXJWypiiyadQ&s=0BFe_hl0zbdeFyTouceqcr9xKIBcpPOYsuk_BNGA9yo&e=

________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

Philip Sevetson

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Phil Grainger)
Phil G.,

We agree on this. For sequential access, clustering definitely matters. Not arguing that. The number of _rows_ retrieved per prefetch request is tightly linked to number of page splits/relocated rows. An ordered request (ORDER BY) in clustering sequence would have to supplement prefetches with synchronous reads for far off rows.

The place where clustering doesn’t matter is in synchronous reads, because they’re all accessible without seek times. You’re not losing time because of widely separated pages.

-phil




Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]>
Sent: Friday, August 9, 2019 11:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load” – unless you ARE doing sequential access!

We did measure performance degradation on disorganization even for SSDs if you are trying to read pages sequentially (and they’re not)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 15:33
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil G. –

My “now I’m old” realization of the year so far is that, if you’re on an SSD “Disk” but not doing sequential access, the level of disorganization on the table doesn’t matter. In SSD storage, there is no spin delay or seek time. It’s just calling a different address, and the access time is uniform.

The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load. All pages are “adjacent”.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 10:10 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“I almost never pay attention to physical storage” – now I just feel very, very old ☹

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 14:52
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Michael,
I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…

In the US:
“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”

Imagine in China…

And you have to find them in 126ms!

I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e= | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image002.png (3.3k)

Mohammad Khan

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Philip Sevetson)
That sounds like the death of "death by random i/o"! BTW there no need for excuses like "Almost", it IS Friday … at least here in the US of A.
Khalid

From: Sevetson, Phil <[login to unmask email]>
Sent: Friday, August 09, 2019 10:19 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil G.,

We agree on this. For sequential access, clustering definitely matters. Not arguing that. The number of _rows_ retrieved per prefetch request is tightly linked to number of page splits/relocated rows. An ordered request (ORDER BY) in clustering sequence would have to supplement prefetches with synchronous reads for far off rows.

The place where clustering doesn’t matter is in synchronous reads, because they’re all accessible without seek times. You’re not losing time because of widely separated pages.

-phil




Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]>
Sent: Friday, August 9, 2019 11:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load” – unless you ARE doing sequential access!

We did measure performance degradation on disorganization even for SSDs if you are trying to read pages sequentially (and they’re not)

Phil Grainger
Principal Enablement Manager
[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami
Direct
+44 1189 218 000
Mobile
+44 7808 643 479
Email
[login to unmask email]<mailto:[login to unmask email]>
E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]


From: Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 15:33
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil G. –

My “now I’m old” realization of the year so far is that, if you’re on an SSD “Disk” but not doing sequential access, the level of disorganization on the table doesn’t matter. In SSD storage, there is no spin delay or seek time. It’s just calling a different address, and the access time is uniform.

The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load. All pages are “adjacent”.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 10:10 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“I almost never pay attention to physical storage” – now I just feel very, very old ☹

Phil Grainger
Principal Enablement Manager
[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami
Direct
+44 1189 218 000
Mobile
+44 7808 643 479
Email
[login to unmask email]<mailto:[login to unmask email]>
E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]


From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 14:52
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Michael,
I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…

In the US:
“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”

Imagine in China…

And you have to find them in 126ms!

I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e= | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at
(312) 653-6000 in Illinois; (800) 447-7828 in Montana;
(800) 835-8699 in New Mexico; (918) 560-3500 in Oklahoma;
or (972) 766-6900 in Texas.

Philip Sevetson

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Mohammad Khan)
Mohammad,

I hear you. The random I/O will still be a problem in the sense of a low density of retrieval, of course – one or two rows per page instead of dozens – but the elimination of seek delays will absolutely reduce I/O waits, probably by a good 95% (which implies a twenty-fold increase in speed of queries which experience this).

-phil (sevetson)


From: Mohammad Khan <[login to unmask email]>
Sent: Friday, August 9, 2019 11:45 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

That sounds like the death of "death by random i/o"! BTW there no need for excuses like "Almost", it IS Friday … at least here in the US of A.
Khalid

From: Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 09, 2019 10:19 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil G.,

We agree on this. For sequential access, clustering definitely matters. Not arguing that. The number of _rows_ retrieved per prefetch request is tightly linked to number of page splits/relocated rows. An ordered request (ORDER BY) in clustering sequence would have to supplement prefetches with synchronous reads for far off rows.

The place where clustering doesn’t matter is in synchronous reads, because they’re all accessible without seek times. You’re not losing time because of widely separated pages.

-phil




Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 11:06 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load” – unless you ARE doing sequential access!

We did measure performance degradation on disorganization even for SSDs if you are trying to read pages sequentially (and they’re not)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 15:33
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Phil G. –

My “now I’m old” realization of the year so far is that, if you’re on an SSD “Disk” but not doing sequential access, the level of disorganization on the table doesn’t matter. In SSD storage, there is no spin delay or seek time. It’s just calling a different address, and the access time is uniform.

The only reason for clustering any more is for maximizing the number of useful rows retrieved by a single page load. All pages are “adjacent”.


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 10:10 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

“I almost never pay attention to physical storage” – now I just feel very, very old ☹

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]<mailto:[login to unmask email]>

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][cid:[login to unmask email][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Daniel L Luksetich <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 09 August 2019 14:52
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)

Michael,
I work extensively with data about people. Some people don’t know when they were born, and so you have to find them. Some people didn’t properly record their birth date, but you still need to find them. That’s when character storage of dates become useful, and date math fails. Oh, and don’t even get me started about people who don’t remember their name…

In the US:
“My name is probably John Smith, and I think I was born sometime in the 40’s or maybe the 50’s”

Imagine in China…

And you have to find them in 126ms!

I love my job! I do not fear varchar. I almost never pay attention to physical storage, unless the table exceeds a dozen terabytes in size.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e= | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, August 9, 2019 12:17 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler Contoken Format)


Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or CHAR or VARCHAR are really bad ideas. I could relax this. I once met a person with two birth dates, an official one on documents and a real one (different). Different country passports could even have mismatching birth dates. Ha ha. Given that no arithmetic is to be done on birth date, we still need a way to keep the format consistent, so DATE type is best.

It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without holding microseconds.

Also anything requiring arithmetic ought to be stored in a numeric or date/Time type data type.
Not sure why Phil was affected by CHAR(11). There is no such thing as word boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me. Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet VARCHAR(4) would not bother me too much from the Database perspective. Certainly fine if the length varies.

CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no special reason to choose a max length of 255. Note that IBM Catalog has lots of longish VARCHARs as index columns. This performs well. CHAR(255) is horrible for an indexed column. I never want to see that, but could remain from the old days (padded indexes) when the length as not kept in the index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha. People just may not realise that CHAR(50) is generally not a good idea, where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can tolerate that.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at
(312) 653-6000 in Illinois; (800) 447-7828 in Montana;
(800)835-8699 in New Mexico; (918)560-3500 in Oklahoma;
or (972)766-6900 in Texas.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Neil Price

Almost Friday - datatypes (was Db2 Precompiler Contoken Format)
(in response to Philip Sevetson)
Db2 doesn't of course know that all pages are "adjacent" and will still use
prefetch (chained reads) or single-CI reads according to the "virtual"
layout of the data. Therefore the degree of clustering may still affect
DASD subsystem performance to a very small degree as well as CPU
consumption in Media Manager.

On Fri, 9 Aug 2019 at 17:06, Sevetson, Phil <[login to unmask email]> wrote:

> Mohammad,
>
>
>
> I hear you. The random I/O will still be a problem in the sense of a low
> density of retrieval, of course – one or two rows per page instead of
> dozens – but the elimination of seek delays will absolutely reduce I/O
> waits, probably by a good 95% (which implies a twenty-fold increase in
> speed of queries which experience this).
>
>
>
> -phil (sevetson)
>
>
>
>
>
> *From:* Mohammad Khan <[login to unmask email]>
> *Sent:* Friday, August 9, 2019 11:45 AM
> *To:* '[login to unmask email]' <[login to unmask email]>
> *Subject:* [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler
> Contoken Format)
>
>
>
> That sounds like the death of "death by random i/o"! BTW there no need for
> excuses like "Almost", it IS Friday … at least here in the US of A.
>
> Khalid
>
>
>
> *From:* Sevetson, Phil <[login to unmask email]>
> *Sent:* Friday, August 09, 2019 10:19 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler
> Contoken Format)
>
>
>
> Phil G.,
>
>
>
> We agree on this. For sequential access, clustering definitely matters.
> Not arguing that. The number of _*rows*_ retrieved per prefetch request
> is tightly linked to number of page splits/relocated rows. An ordered
> request (ORDER BY) in clustering sequence would have to supplement
> prefetches with synchronous reads for far off rows.
>
>
>
> The place where clustering doesn’t matter is in synchronous reads, because
> they’re all accessible without seek times. You’re not losing time because
> of widely separated pages.
>
>
>
> -phil
>
>
>
>
>
>
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> FISA-OPA
>
> 5 Manhattan West
>
> New York, NY 10001
>
> [login to unmask email]
>
> 212-857-1688 w
>
> 917-991-7052 m
>
> 212-857-1659 f
>
> [image: cid:[login to unmask email]
>
>
>
> *From:* Grainger, Phil <[login to unmask email]>
> *Sent:* Friday, August 9, 2019 11:06 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler
> Contoken Format)
>
>
>
> “The only reason for clustering any more is for maximizing the number of
> useful rows retrieved by a single page load” – unless you ARE doing
> sequential access!
>
>
>
> We did measure performance degradation on disorganization even for SSDs if
> you are trying to read pages sequentially (and they’re not)
>
>
>
> *Phil Grainger*
> Principal Enablement Manager
>
> [image: BMC Exchange 2019 - Global Event Series - REGISTER]
> https://www.bmc.com/ami
>
> *Direct*
>
> +44 1189 218 000
>
> *Mobile*
>
> +44 7808 643 479
>
> *Email*
>
> [login to unmask email]
>
> E2, Eskdale Road
> Winnersh
> Berkshire
> United Kingdom
> RG41 5TS
>
> [image: image001 (002)][image: cid:[login to unmask email][image:
> https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]
>
>
>
>
>
> *From:* Sevetson, Phil <[login to unmask email]>
> *Sent:* 09 August 2019 15:33
> *To:* [login to unmask email]
> *Subject:* [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2
> Precompiler Contoken Format)
>
>
>
> Phil G. –
>
>
>
> My “now I’m old” realization of the year so far is that, if you’re on an
> SSD “Disk” but not doing sequential access, the level of disorganization on
> the table doesn’t matter. In SSD storage, there is no spin delay or seek
> time. It’s just calling a different address, and the access time is
> uniform.
>
>
>
> The only reason for clustering any more is for maximizing the number of
> useful rows retrieved by a single page load. All pages are “adjacent”.
>
>
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> FISA-OPA
>
> 5 Manhattan West
>
> New York, NY 10001
>
> [login to unmask email]
>
> 212-857-1688 w
>
> 917-991-7052 m
>
> 212-857-1659 f
>
> [image: cid:[login to unmask email]
>
>
>
> *From:* Grainger, Phil <[login to unmask email]>
> *Sent:* Friday, August 9, 2019 10:10 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler
> Contoken Format)
>
>
>
> “I almost never pay attention to physical storage” – now I just feel very,
> very old ☹
>
>
>
> *Phil Grainger*
> Principal Enablement Manager
>
> [image: BMC Exchange 2019 - Global Event Series - REGISTER]
> https://www.bmc.com/ami
>
> *Direct*
>
> +44 1189 218 000
>
> *Mobile*
>
> +44 7808 643 479
>
> *Email*
>
> [login to unmask email]
>
> E2, Eskdale Road
> Winnersh
> Berkshire
> United Kingdom
> RG41 5TS
>
> [image: image001 (002)][image: cid:[login to unmask email][image:
> https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]
>
>
>
>
>
> *From:* Daniel L Luksetich <[login to unmask email]>
> *Sent:* 09 August 2019 14:52
> *To:* [login to unmask email]
> *Subject:* [EXTERNAL] [DB2-L] - RE: Almost Friday - datatypes (was Db2
> Precompiler Contoken Format)
>
>
>
> Michael,
>
> I work extensively with data about people. Some people don’t know when
> they were born, and so you have to find them. Some people didn’t properly
> record their birth date, but you still need to find them. That’s when
> character storage of dates become useful, and date math fails. Oh, and
> don’t even get me started about people who don’t remember their name…
>
>
>
> In the US:
>
> “My name is probably John Smith, and I think I was born sometime in the
> 40’s or maybe the 50’s”
>
>
>
> Imagine in China…
>
>
>
> And you have to find them in 126ms!
>
>
>
> I love my job! I do not fear varchar. I almost never pay attention to
> physical storage, unless the table exceeds a dozen terabytes in size.
>
>
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
> | Daniel L Luksetich | IBM Certified Advanced Database
> Administrator – |
>
> | IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
> Windows |
>
> | IDUG Content Committee Past-Chairman | IBM Certified Database
> Adminstrator – Db2 11 DBA for z/OS |
>
> | IDUG DB2-L Administrator | IBM Certified System
> Administrator – Db2 11 for z/OS |
>
> | URL: https://db2expert.com
> https://urldefense.proofpoint.com/v2/url?u=https-3A__db2expert.com_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=0x9bRab2KheA7xvCWwA7S3c1R7zpAIQYg7a4k9IUH9I&s=NqCK5koHOtkjMamTYfDKa8WAchGQzba7I1D69eQLyEY&e=
> | IBM Certified Application Developer – Db2 11 for z/OS |
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
> [image: cid:[login to unmask email][image:
> cid:[login to unmask email][image:
> cid:[login to unmask email][image:
> cid:[login to unmask email][image:
> cid:[login to unmask email][image:
> cid:[login to unmask email]
>
>
>
> *From:* Michael Hannan <[login to unmask email]>
> *Sent:* Friday, August 9, 2019 12:17 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Almost Friday - datatypes (was Db2 Precompiler
> Contoken Format)
>
>
>
> Agreed that BIRTH_DATE ought to be stored in a DATE column. Decimal or
> CHAR or VARCHAR are really bad ideas. I could relax this. I once met a
> person with two birth dates, an official one on documents and a real one
> (different). Different country passports could even have mismatching birth
> dates. Ha ha. Given that no arithmetic is to be done on birth date, we
> still need a way to keep the format consistent, so DATE type is best.
>
> It is good DB2 now allows a TIMESTAMP to be as little as 7 bytes without
> holding microseconds.
>
> Also anything requiring arithmetic ought to be stored in a numeric or
> date/Time type data type.
> Not sure why Phil was affected by CHAR(11). There is no such thing as word
> boundaries in DB2 for CHAR. CHAR(11) is just about the borderline for me.
> Any bigger and I start to prefer VARCHAR. Yes VARCHAR(1) is silly. Yet
> VARCHAR(4) would not bother me too much from the Database perspective.
> Certainly fine if the length varies.
>
> CHAR(255) is certainly a bad idea where VARCHAR(255) is O.K. although no
> special reason to choose a max length of 255. Note that IBM Catalog has
> lots of longish VARCHARs as index columns. This performs well. CHAR(255) is
> horrible for an indexed column. I never want to see that, but could remain
> from the old days (padded indexes) when the length as not kept in the
> index. IBM should deprecate CHAR for anything over 15, I reckon. Ha ha.
> People just may not realise that CHAR(50) is generally not a good idea,
> where VARCHAR(50) is. If CHAR(50) always uses the full 50 bytes, O.K. I can
> tolerate that.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
> BMC Software Limited Registered Office: Building E2, Eskdale Road,
> Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in
> England No. 1927903 The content of this email is confidential. If you are
> not the addressee, you may not distribute, copy or disclose any part of it.
> If you receive this message in error, please delete this from your system
> and notify the sender immediately.
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
>
> -----End Original Message-----
>
> BMC Software Limited Registered Office: Building E2, Eskdale Road,
> Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in
> England No. 1927903 The content of this email is confidential. If you are
> not the addressee, you may not distribute, copy or disclose any part of it.
> If you receive this message in error, please delete this from your system
> and notify the sender immediately.
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
>
> -----End Original Message-----
>
> HCSC Company Disclaimer
>
> The information contained in this communication is confidential, private,
> proprietary, or otherwise privileged and is intended only for the use of
> the addressee. Unauthorized use, disclosure, distribution or copying is
> strictly prohibited and may be unlawful. If you have received this
> communication in error, please notify the sender immediately at
> (312) 653-6000 in Illinois; (800) 447-7828 in Montana;
> (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma;
> or (972)766-6900 in Texas.
>
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>