FWIW, on my recently-upgraded 6.3.4 server, I don't get the ANR9999D, but the
statement does fail:

  ANR0162W Supplemental database diagnostic information:  -1:22003:-413
([IBM][CLI
  Driver][DB2/AIX64] SQL0413N  Overflow occurred during numeric data type
  conversion.  SQLSTATE=22003
  ).
  ANR0516E SQL processing for statement select cast ( timestampdiff ( 2 ,
  end_time-start_time ) as decimal ( 4 , 0 ) ) from summary where activity =
  'EXPIRATION'  failed.
  ANS8001I Return code 3.

Changing the decimal() to 8,0 makes it run cleanly for me, too.  Maybe later
code levels identify the overflow condition rather than spew the ANR9999D,
but I don't know SQL well enough to know if the overflow itself is valid.
I'd guess the number of decimal digits needed for it to run OK will vary
depending on the values in the summary table.

(Oddly, a co-worker got a similar overflow yesterday on a select yesterday,
but not only did the statement fail, the server terminated the admin session...)

=Dave


On 12/05/2013 07:38 AM, Loon, EJ van - SPLXM wrote:

Hi Ronald!
Thanks for trying! I'm running 6.2.5.0. I just discovered that when I
change 4,0 to 8,0 it works. Looks like a bug to me...
Kind regards,
Eric van Loon
AF/KLM Storage Engineering

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Ronald Le Large
Sent: donderdag 5 december 2013 14:17
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: SQL statement generates an ANR9999D

Eric,

I can execute the sql without receiving the ANR9999

I run it on  V 6.3.3.0

Kind regards,

Ronald Le Large
Information technology officer Storage Management | Dir. 2.8.4.1
European Patent Office




-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Loon, EJ van - SPLXM
Sent: Thursday, December 05, 2013 2:07 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: [ADSM-L] SQL statement generates an ANR9999D

Hi TSM-ers!

I'm trying to build an SQL statement to measure expiration performance,
but I'm experiencing an ANR9999D error. I stripped it down to the
following statement:



select cast(timestampdiff(2,end_time-start_time) as decimal(4,0)) from
summary where activity='EXPIRATION'



As soon as I enter this command I receive the following error in the
activity log:



12/05/2013 13:22:45 ANR9999D Thread<239884> issued message 486 from:
(SESSION: 108135)

12/05/2013 13:22:45 ANR9999D Thread<239884>  0x000000010001ab84
StdPutMsg  (SESSION: 108135)

12/05/2013 13:22:45 ANR9999D Thread<239884>  0x000000010001c2c4 outMsgf
(SESSION: 108135)

12/05/2013 13:22:45 ANR9999D Thread<239884>  0x000000010028334c
smEndMessage  (SESSION: 108135)

12/05/2013 13:22:45 ANR9999D Thread<239884>  0x000000010027b96c
smExecuteSession  (SESSION: 108135)

12/05/2013 13:22:45 ANR9999D Thread<239884>  0x00000001000a824c
psSessionThread  (SESSION: 108135)

12/05/2013 13:22:45 ANR9999D Thread<239884>  0x000000010000c468
StartThread  (SESSION: 108135)



Could someone try this command on his server to see if it's a general
bug or if it's caused by something in my databases? I see the same
behavior on three different servers...

Thanks for your help in advance!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering

--
Hello World.                                David Bronder - Systems Architect
Segmentation Fault                                      ITS-EI, Univ. of Iowa
Core dumped, disk trashed, quota filled, soda warm.   david-bronder AT uiowa 
DOT edu