UPPER function in partitioned indexes (V11 Z/os)

Art McEwen

UPPER function in partitioned indexes (V11 Z/os)
Hi Listers,

Trying to migrate some Oracle code to DB2 and I'm stymied by the desire to maintain the UPPER function in an index (because the old app relies on it) while at the same time effectively partitioning the object. I've looked thru the SQL reference 'til I'm blind and I can't figure out what the mutually exclusive items are (it's not FOR BIT DATA). In this case this is a non-clustering index where the partitioning/clustering key is a char(10,0).

Given a common table definition except where one is partitioned and the other isn't I get:

CREATE TABLE HESKDB2.UPPER_TEST_NONPARTITIONED
( POSTAL_AREA_CD CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME CHAR ( 30 ) NOT NULL)
IN PBJJDB01.S95;
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+--

CREATE TABLE HESKDB2.UPPER_TEST_PARTITIONED
( POSTAL_AREA_CD CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME CHAR ( 30 ) NOT NULL)
IN PBJJDB01.S96
PARTITION BY (POSTAL_AREA_CD)
(PARTITION 1
ENDING AT ( 'L00 000')
,PARTITION 2
ENDING AT ( 'ZZZZZZZZZZ'));
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0



--Non-partitioned w/ UPPER

CREATE INDEX HESKDB2.S95T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_NONPARTITIONED
( UPPER(MUNICIPALITY_NAME,' '))
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


--Partitioned w/o UPPER

CREATE INDEX HESKDB2.S96T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_PARTITIONED
( MUNICIPALITY_NAME)
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
PARTITIONED
PARTITION BY RANGE
(PARTITION 1
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
,PARTITION 2
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
)
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

-- Partitioned w/ UPPER

CREATE INDEX HESKDB2.S96T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_PARTITIONED
( UPPER(MUNICIPALITY_NAME,' '))
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
PARTITIONED
PARTITION BY RANGE
(PARTITION 1
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
,PARTITION 2
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
)
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -628, ERROR: THE CLAUSES ARE MUTUALLY EXCLUSIVE
DSNT418I SQLSTATE = 42613 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5X SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 78 0 0 -1 225 2905 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000004E' X'00000000' X'00000000' X'FFFFFFFF'
X'000000E1' X'00000B59' SQL DIAGNOSTIC INFORMATION

Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

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

Office 613-548-6622
Cell 613-539-3903

Raymond Bell

UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)
Looks like you can't partition using an 'extended index'. It doesn't like your attempt to partition on an index defined using a function.

'...A CREATE INDEX or ALTER INDEX statement attempted to specify
either CLUSTER, PARTITION BY or both for an XML index or an
extended index...'

Soz, no can do. :o(

Cheers,


Raymond


From: McEwen, Art (MOHLTC) [mailto:[login to unmask email]
Sent: 22 November 2018 16:56
To: '[login to unmask email]'
Subject: [DB2-L] - UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************

Hi Listers,

Trying to migrate some Oracle code to DB2 and I'm stymied by the desire to maintain the UPPER function in an index (because the old app relies on it) while at the same time effectively partitioning the object. I've looked thru the SQL reference 'til I'm blind and I can't figure out what the mutually exclusive items are (it's not FOR BIT DATA). In this case this is a non-clustering index where the partitioning/clustering key is a char(10,0).

Given a common table definition except where one is partitioned and the other isn't I get:

CREATE TABLE HESKDB2.UPPER_TEST_NONPARTITIONED
( POSTAL_AREA_CD CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME CHAR ( 30 ) NOT NULL)
IN PBJJDB01.S95;
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+--

CREATE TABLE HESKDB2.UPPER_TEST_PARTITIONED
( POSTAL_AREA_CD CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME CHAR ( 30 ) NOT NULL)
IN PBJJDB01.S96
PARTITION BY (POSTAL_AREA_CD)
(PARTITION 1
ENDING AT ( 'L00 000')
,PARTITION 2
ENDING AT ( 'ZZZZZZZZZZ'));
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0



--Non-partitioned w/ UPPER

CREATE INDEX HESKDB2.S95T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_NONPARTITIONED
( UPPER(MUNICIPALITY_NAME,' '))
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


--Partitioned w/o UPPER

CREATE INDEX HESKDB2.S96T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_PARTITIONED
( MUNICIPALITY_NAME)
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
PARTITIONED
PARTITION BY RANGE
(PARTITION 1
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
,PARTITION 2
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
)
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

-- Partitioned w/ UPPER

CREATE INDEX HESKDB2.S96T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_PARTITIONED
( UPPER(MUNICIPALITY_NAME,' '))
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
PARTITIONED
PARTITION BY RANGE
(PARTITION 1
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
,PARTITION 2
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
)
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -628, ERROR: THE CLAUSES ARE MUTUALLY EXCLUSIVE
DSNT418I SQLSTATE = 42613 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5X SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 78 0 0 -1 225 2905 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000004E' X'00000000' X'00000000' X'FFFFFFFF'
X'000000E1' X'00000B59' SQL DIAGNOSTIC INFORMATION

Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

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

Office 613-548-6622
Cell 613-539-3903


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

Art McEwen

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Raymond Bell)

How does the UPPER function make it an extended or XML index?

In Reply to Raymond Bell:

Looks like you can't partition using an 'extended index'. It doesn't like your attempt to partition on an index defined using a function.

Horacio Villa

UPPER function in partitioned indexes (V11 Z/os)
(in response to Raymond Bell)
Is it because of the "PARTITION BY RANGE" clause in the Create Index?


Art McEwen

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Horacio Villa)

In that if it's not partitioned, or if it is partitioned without the UPPER then yes it works, but I'm just not seeing the restriction documented in the manuals.

In Reply to Horacio Villa:

Is it because of the "PARTITION BY RANGE" clause in the Create Index?


Raymond Bell

UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)
You do have a copy of the SQL Reference, right?

‘…An index is either a simple index or an extended index. An extended index is one of the following objects:

· An expression-based index

· A spatial index

· An XML index…’

So, going by your SQL and the fact you’re using UPPER (i.e. an expression), that makes the index extended – so you can’t partition by it.

Cheers,


Raymond

From: Art McEwen [mailto:[login to unmask email]
Sent: 22 November 2018 17:21
To: [login to unmask email]
Subject: [DB2-L] - RE: UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************


How does the UPPER function make it an extended or XML index?

In Reply to Raymond Bell:
Looks like you can't partition using an 'extended index'. It doesn't like your attempt to partition on an index defined using a function.

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

Kai Stroh

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)

Art,

if you can spare an extra 30 bytes in your table, you could add a separate column that will hold the uppercase value, and then use that column in the index. As far as I know, you cannot use expressions in the DEFAULT clause of a column, but you can use a trigger to get the same result:

CREATE TABLE HESKDB2.UPPER_TEST_PARTITIONED
( POSTAL_AREA_CD     CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME  CHAR ( 30 ) NOT NULL
, MUNICIPALITY_UPPER CHAR ( 30 ) NOT NULL
    WITH DEFAULT IMPLICITLY HIDDEN
)
PARTITION BY (POSTAL_AREA_CD)
( PARTITION 1 ENDING AT ( 'L00 000')
, PARTITION 2 ENDING AT ( 'ZZZZZZZZZZ')
IN PBJJDB01.S96;

--#SET TERMINATOR $

CREATE TRIGGER HESKDB2.UPPER_TEST_PARTITIONED_TRIGGER
NO CASCADE BEFORE INSERT ON HESKDB2.UPPER_TEST_PARTITIONED
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
  SET N.MUNICIPALITY_UPPER = UPPER(N.MUNICIPALITY_NAME);
END$

--#SET TERMINATOR ;

COMMIT WORK;


Now you can define an index on the column MUNICIPALITY_UPPER and Db2 will allow you to make it a partitioned index. Since the column MUNICIPALITY_UPPER has a default value, there is no need to specify it in an INSERT statement, and since it is implicitly hidden it will not show up when doing a SELECT * on the table.

Also, consider using ENDING AT (MAXVALUE) instead of ENDING AT ('ZZZZZZZZZZ'). Not sure if it's something that can happen, but in an EBCDIC tablespace you might get problems if the postal code starts with a digit as those have higher code points than letters.

 

Cheers

Kai



In Reply to Art McEwen:

Hi Listers,

Trying to migrate some Oracle code to DB2 and I'm stymied by the desire to maintain the UPPER function in an index (because the old app relies on it) while at the same time effectively partitioning the object. I've looked thru the SQL reference 'til I'm blind and I can't figure out what the mutually exclusive items are (it's not FOR BIT DATA). In this case this is a non-clustering index where the partitioning/clustering key is a char(10,0).

Given a common table definition except where one is partitioned and the other isn't I get:

CREATE TABLE HESKDB2.UPPER_TEST_NONPARTITIONED
( POSTAL_AREA_CD CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME CHAR ( 30 ) NOT NULL)
IN PBJJDB01.S95;
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+--

CREATE TABLE HESKDB2.UPPER_TEST_PARTITIONED
( POSTAL_AREA_CD CHAR ( 10 ) NOT NULL
, MUNICIPALITY_NAME CHAR ( 30 ) NOT NULL)
IN PBJJDB01.S96
PARTITION BY (POSTAL_AREA_CD)
(PARTITION 1
ENDING AT ( 'L00 000')
,PARTITION 2
ENDING AT ( 'ZZZZZZZZZZ'));
---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0



--Non-partitioned w/ UPPER

CREATE INDEX HESKDB2.S95T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_NONPARTITIONED
( UPPER(MUNICIPALITY_NAME,' '))
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


--Partitioned w/o UPPER

CREATE INDEX HESKDB2.S96T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_PARTITIONED
( MUNICIPALITY_NAME)
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
PARTITIONED
PARTITION BY RANGE
(PARTITION 1
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
,PARTITION 2
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
)
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

-- Partitioned w/ UPPER

CREATE INDEX HESKDB2.S96T01X1_PBJJDB01
ON HESKDB2.UPPER_TEST_PARTITIONED
( UPPER(MUNICIPALITY_NAME,' '))
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
PARTITIONED
PARTITION BY RANGE
(PARTITION 1
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
,PARTITION 2
USING STOGROUP HESKIDX4
PRIQTY 1320
SECQTY 20000
ERASE NO
FREEPAGE 0
PCTFREE 3
)
BUFFERPOOL BP1
CLOSE NO
;
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -628, ERROR: THE CLAUSES ARE MUTUALLY EXCLUSIVE
DSNT418I SQLSTATE = 42613 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5X SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 78 0 0 -1 225 2905 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000004E' X'00000000' X'00000000' X'FFFFFFFF'
X'000000E1' X'00000B59' SQL DIAGNOSTIC INFORMATION

Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

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

Office 613-548-6622
Cell 613-539-3903



--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Art McEwen

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Raymond Bell)

Yes Ray I do have the manuals and I have read them thanks very much.  The versions of the V11 SQL Reference (SC19-4066-07) nor the admin guide (SC19-4050-05) I had downloaded don't contain those lines you quote.   Apparently it's in SC19-4066-09 however so it must have been a late amendment.

 


In Reply to Raymond Bell:

You do have a copy of the SQL Reference, right?

‘…An index is either a simple index or an extended index. An extended index is one of the following objects:

· An expression-based index

· A spatial index

· An XML index…’

So, going by your SQL and the fact you’re using UPPER (i.e. an expression), that makes the index extended – so you can’t partition by it.

Cheers,


Raymond

From: Art McEwen [mailto:[login to unmask email]
Sent: 22 November 2018 17:21
To: [login to unmask email]
Subject: [DB2-L] - RE: UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************


How does the UPPER function make it an extended or XML index?

In Reply to Raymond Bell:
Looks like you can't partition using an 'extended index'. It doesn't like your attempt to partition on an index defined using a function.

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

Raymond Bell

UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)
Oi! Enough with the sarcasm; you started it! ;o)

TBH the SQL Ref’ version I had was pretty new – 08 or so I think – which I got because of the new REST SERVICE stuff. I must confess to having to look up what an ‘extended index’ was.

Anyway, that would appear to be the problem. Not sure what you’ll be able to do about it. :o(

Cheers,


Raymond


From: Art McEwen [mailto:[login to unmask email]
Sent: 23 November 2018 15:33
To: [login to unmask email]
Subject: [DB2-L] - RE: UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************


Yes Ray I do have the manuals and I have read them thanks very much. The versions of the V11 SQL Reference (SC19-4066-07) nor the admin guide (SC19-4050-05) I had downloaded don't contain those lines you quote. Apparently it's in SC19-4066-09 however so it must have been a late amendment.



In Reply to Raymond Bell:
You do have a copy of the SQL Reference, right?

‘…An index is either a simple index or an extended index. An extended index is one of the following objects:

· An expression-based index

· A spatial index

· An XML index…’

So, going by your SQL and the fact you’re using UPPER (i.e. an expression), that makes the index extended – so you can’t partition by it.

Cheers,


Raymond

From: Art McEwen [mailto:[login to unmask email]
Sent: 22 November 2018 17:21
To: [login to unmask email]
Subject: [DB2-L] - RE: UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************


How does the UPPER function make it an extended or XML index?

In Reply to Raymond Bell:
Looks like you can't partition using an 'extended index'. It doesn't like your attempt to partition on an index defined using a function.

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

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

Art McEwen

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Raymond Bell)

Yeah there's not much, change the app I guess (even Kai's suggestion would require that).   Luckily we found out before we went to prod.

 

Cheers,

In Reply to Raymond Bell:

Oi! Enough with the sarcasm; you started it! ;o)

TBH the SQL Ref’ version I had was pretty new – 08 or so I think – which I got because of the new REST SERVICE stuff. I must confess to having to look up what an ‘extended index’ was.

Anyway, that would appear to be the problem. Not sure what you’ll be able to do about it. :o(

Cheers,


Raymond


From: Art McEwen [mailto:[login to unmask email]
Sent: 23 November 2018 15:33
To: [login to unmask email]
Subject: [DB2-L] - RE: UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************


Yes Ray I do have the manuals and I have read them thanks very much. The versions of the V11 SQL Reference (SC19-4066-07) nor the admin guide (SC19-4050-05) I had downloaded don't contain those lines you quote. Apparently it's in SC19-4066-09 however so it must have been a late amendment.



In Reply to Raymond Bell:
You do have a copy of the SQL Reference, right?

‘…An index is either a simple index or an extended index. An extended index is one of the following objects:

· An expression-based index

· A spatial index

· An XML index…’

So, going by your SQL and the fact you’re using UPPER (i.e. an expression), that makes the index extended – so you can’t partition by it.

Cheers,


Raymond

From: Art McEwen [mailto:[login to unmask email]
Sent: 22 November 2018 17:21
To: [login to unmask email]
Subject: [DB2-L] - RE: UPPER function in partitioned indexes (V11 Z/os)


*********************************************
" 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"
*********************************************


How does the UPPER function make it an extended or XML index?

In Reply to Raymond Bell:
Looks like you can't partition using an 'extended index'. It doesn't like your attempt to partition on an index defined using a function.

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

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

Michael Hannan

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)

In Reply to Art McEwen:

Hi Listers,

Trying to migrate some Oracle code to DB2 and I'm stymied by the desire to maintain the UPPER function in an index (because the old app relies on it) while at the same time effectively partitioning the object. I've looked thru the SQL reference 'til I'm blind and I can't figure out what the mutually exclusive items are (it's not FOR BIT DATA). In this case this is a non-clustering index where the partitioning/clustering key is a char(10,0).

Art,

DB2 uses table controlled partitioning, and not index controlled partitioning. Index On Expression using UPPER for example is storing a calculated value in the index and not in the table, so clearly cannot be used for partitioning.

The App relies on UPPER but does not rely on partitioning.

No big deal. Create Table controlled partitioning if you please. Create your index on expression without partitions. 

A table does not need to have a "Partitioning Index" any more. It's optional. It happens when an index just happens to correspond to the TABLE partitioning scheme. Could be that partitioned by Growth is a better option, unless the partitioning scheme is related to clustering and will help the performance of queries.

It is not a logical application requirement to partition by something. It is a physical thing. You might well choose clustering in DB2 for performance that is not matched by Oracle.

Have I missed the point somehow?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Art McEwen

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Michael Hannan)

Hi Michael,

It was the non-clustering/partitioning indexes I was having the issue with.   Our habit is to use DPSIs for them (partly for parallelism, partly because 3390m3 disk).   The upper function was tripping me up because of an undocumented (at least in the initial GA manuals I had at the time) restriction.    I'll have to either lose the upper or lose the DPSI.

 

Art


In Reply to Michael Hannan:

In Reply to Art McEwen:

Hi Listers,

Trying to migrate some Oracle code to DB2 and I'm stymied by the desire to maintain the UPPER function in an index (because the old app relies on it) while at the same time effectively partitioning the object. I've looked thru the SQL reference 'til I'm blind and I can't figure out what the mutually exclusive items are (it's not FOR BIT DATA). In this case this is a non-clustering index where the partitioning/clustering key is a char(10,0).

Art,

DB2 uses table controlled partitioning, and not index controlled partitioning. Index On Expression using UPPER for example is storing a calculated value in the index and not in the table, so clearly cannot be used for partitioning.

The App relies on UPPER but does not rely on partitioning.

No big deal. Create Table controlled partitioning if you please. Create your index on expression without partitions. 

A table does not need to have a "Partitioning Index" any more. It's optional. It happens when an index just happens to correspond to the TABLE partitioning scheme. Could be that partitioned by Growth is a better option, unless the partitioning scheme is related to clustering and will help the performance of queries.

It is not a logical application requirement to partition by something. It is a physical thing. You might well choose clustering in DB2 for performance that is not matched by Oracle.

Have I missed the point somehow?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Michael Hannan

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)

Art,

I can see now you are having problems with a DPSI and not a Partitioning Index. I don't see anywhere in the manual where it says an Index on Expression cannot be a DPSI, but I think I have run into this problem before.

It is not essential to use a DSPI to get parallelism, however for an access path looking in only one partition, Parallelism might not be chosen, where looking in all partitions it might be chosen, because it then has many times more work to do. I don't normally agree with making a much worse access path just to try to force parallelism. The normal CPU cost even with zIIP offload might not be that much better, but would have to test it.Depends on the percentage off loaded.

So I would not be too concerned about forcing a DPSI, unless you expect to get runtime partition pruning based on a predicate on the partitioning key which was POSTAL_AREA_CODE in this case.

Do you have search queries using a range predicate on both the POSTAL_AREA_CODE and the UPPER(MUNICIPALITY)? That is the case where a DPSI would improve search performance, were it allowed. However if one of the two columns can be an equals predicate, then a normal secondary index will be O.K.

I am thinking that last time we had an index on expression, that we wanted to be partitioned (DPSI), we suggested to try to use PARTITIONED, but leave out the PARTITION BY clause. I think that idea may have worked but not certain now. It appears from past email trail that my colleague succeeded.

Note that manual states that an XML index cannot be a DPSI but says nothing about excluding an index including expression. Even the text for -628 Mutually Exclusive states Partitioned is not compatible with XML. I suspect the non compatible clauses are the Expression and the PARTITIONED BY.

Partitioned By is coded is order to specify the partition size. Maybe that could be done with a subsequent ALTER.

No guarantees, but I think it should be possible to make Expression and DPSI go together. I will recheck with my colleague if we succeeded or not, to be sure.

Michael Hannan

In Reply to Art McEwen:

Hi Michael,

It was the non-clustering/partitioning indexes I was having the issue with.   Our habit is to use DPSIs for them (partly for parallelism, partly because 3390m3 disk).   The upper function was tripping me up because of an undocumented (at least in the initial GA manuals I had at the time) restriction.    I'll have to either lose the upper or lose the DPSI.

Michael Hannan

RE: UPPER function in partitioned indexes (V11 Z/os)
(in response to Art McEwen)

Art,

This is now confirmed. While the partitioning columns must be in the table, you can make a partitioned index including a column with Expression. This could even be a "partitioning" index, with more columns than the partitioning key (at a guess).

Syntax:

CREATE   INDEX Auth.name ON Auth.Table
(COL1
,SUBSTR(COL2, 2, 8)
,COL3
,COL4
etc.
)
    USING STOGROUP SG000000 
    PRIQTY -1   
    SECQTY -1 
    ERASE NO   
    FREEPAGE 0 
    P CTFREE 10   
    NOT CLUSTER
    PARTITIONED
    BUFFERPOOL BP8K3     
    CLOSE YES 
    COPY NO   
   COMPRESS NO     ;                             

 Seems a little silly to me that IBM has prevented the PARTITION BY to used to specify space etc. in each partition.

Could be viewed as short-coming in the DB2 implementation, or a mistake, basically.

Test a subsequent ALTER though. 

Whether partitioning by your POSTAL_AREA_CODE is a good idea or not is another topic, but to me depends on the queries used. I would consider creating one index as DPSI and another as NPI and see which DB2 prefers for different SQLs. DPSI can have a performance benefit, despite others claiming it is not a performance measure.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Nov 27, 2018 - 08:50 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Nov 27, 2018 - 08:53 AM (Europe/Berlin)