limitkey column of systablepart (undocumented) change of behaviour in DB2 v11

Peter Van Paesschen

limitkey column of systablepart (undocumented) change of behaviour in DB2 v11

Although this column is marked 'for internal use only', we used it in many of our jobs (like automated rotate partition jobs) and they all failed after migrating from v10 to v11 because date and time values are stored having single quotes from v11 onwards (causing our sql statements to fail with SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID) 

I believe this should not be an undocumented feature . Are there others using this column or having issues with different undocumented changes in v11 ? 

Peter

Walter Janißen

AW: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11
(in response to Peter Van Paesschen)
Hi

We experienced the same problem, but we still have tablespaces, where the limitkey containing a date-value is not included in apostrophes. We are on V11 over a year now.

For example (tablespace is table-controlled):

31.10.2005,'9999'
30.11.2005,'0070'
30.11.2005,'0300'
30.11.2005,'0500'
30.11.2005,'9999'
31.12.2005,'0070'
31.12.2005,'0300'
31.12.2005,'0500'
31.12.2005,'9999'
31.01.2006,'0070'

I don’t know, when the quote is added. Reorg Rebalance?

BTW: Did you notice that TIMESTAMP-columns now have a scale greater than 0? Till V10 the scale was always 0. That’s also an undocumented change.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-6441
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Peter Van Paesschen [mailto:[login to unmask email]
Gesendet: Donnerstag, 9. April 2015 14:50
An: [login to unmask email]
Betreff: [DB2-L] - limitkey column of systablepart (undocumented) change of behaviour in DB2 v11


Although this column is marked 'for internal use only', we used it in many of our jobs (like automated rotate partition jobs) and they all failed after migrating from v10 to v11 because date and time values are stored having single quotes from v11 onwards (causing our sql statements to fail with SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID)

I believe this should not be an undocumented feature . Are there others using this column or having issues with different undocumented changes in v11 ?

Peter

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

Peter Van Paesschen

RE: AW: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11
(in response to Walter Janißen)

Hi Walter,

We see single quotes when doing for instance the rotate in v11 . So we have a mixture of rows with limitkey with and without single quotes which can last for long for certain of our tables .

limitkey for same table :

LIMITKEY                    
---+---------+---------+-----
'2015-04-13-23.59.59.999999'  -> v11
2015-04-02-23.59.59.999999   -> v10
2015-04-03-23.59.59.999999  
2015-04-08-23.59.59.999999  
2015-04-09-23.59.59.999999  
2015-04-10-23.59.59.999999  
2015-04-11-23.59.59.999999  
'2015-04-12-23.59.59.999999'
2015-04-04-23.59.59.999999  
2015-04-05-23.59.59.999999  
2015-04-06-23.59.59.999999  
2015-04-07-23.59.59.999999  

 

Peter

Paul Deepak Edward

RE: AW: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11
(in response to Peter Van Paesschen)

We have the single quotes on those values that existed prior to migration and post migration, we have double single quotes for the limitkey. Looks like a change in the SQL statement is required

LIMITKEY     
''2015-7-31''
'2013-07-31' 
'2013-08-31' 
'2013-09-30' 
'2013-10-31' 
'2013-11-30'

Any tips on overcoming this issue. We have few jobs failling repeatedly due to this issue.

Thanks & Regards

Paul

Walter Jani&#223;en

AW: AW: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11
(in response to Paul Deepak Edward)
Hi Paul

Are these date-values? I am wondering about the first one, because a zero is missing.
You can get rid of these apostrophes regardless, if there is one or two by REPLACE(LIMITKEY, '''', '')


Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Paul Deepak Edward [mailto:[login to unmask email]
Gesendet: Freitag, 3. Juli 2015 10:10
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11


We have the single quotes on those values that existed prior to migration and post migration, we have double single quotes for the limitkey. Looks like a change in the SQL statement is required

LIMITKEY
''2015-7-31''
'2013-07-31'
'2013-08-31'
'2013-09-30'
'2013-10-31'
'2013-11-30'

Any tips on overcoming this issue. We have few jobs failling repeatedly due to this issue.

Thanks & Regards

Paul

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

Roy Boxwell

AW: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11
(in response to Paul Deepak Edward)
I just coded our DDL extractor to handle this as a normal column. In the past this was a problem so these columns got flagged as "bad boys" which got them special handling. Now in DB2 11 you do not need the special handling anymore .

Roy

Sent from my iPad

On 03.07.2015, at 10:10, Paul Deepak Edward <[login to unmask email]<mailto:[login to unmask email]>> wrote:


We have the single quotes on those values that existed prior to migration and post migration, we have double single quotes for the limitkey. Looks like a change in the SQL statement is required

LIMITKEY
''2015-7-31''
'2013-07-31'
'2013-08-31'
'2013-09-30'
'2013-10-31'
'2013-11-30'

Any tips on overcoming this issue. We have few jobs failling repeatedly due to this issue.

Thanks & Regards

Paul

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

Ken Hynes

RE: limitkey column of systablepart (undocumented) change of behaviour in DB2 v11
(in response to Peter Van Paesschen)

"Internal use Only".....hmmmm. We had this problem and you just have to code around it. The resolutions proposed were to STRIP the quotation from the field or to substring the DIGITS. I don't recall the exact details, but I think the change was made to allow a dynamic change to the partition range with the online data repartitioning, but someone from IBM would need to confirm that.  .