I am trying to suppress warning messages in SPUFI, while searching on net I found that --#SET TOLWARN YES, can do it. But when I code this line before my query, spufi still prints the warning messages as below.
I dont want these messages in my output. I guess spufi is rejecting line --#SET TOLWARN YES, because it treats line starting with two hyphens as comment. Is there a way around, or am I doing something wrong here.
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
If you look for the explanation of SQLSTATE=01506, it tells that an adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation. So how your SQL query looks like?
What level of DB2 are you at? And this article might just interest you.
Thanks for the link, I have already gone through it.
My query looks something like this.
select CURRENT DATE - 6 months from sysibm.sysdummy1;
On 31st March when it ran, March 31, 2013 - 6 MONTHS would be September 31, 2012 which does not exist (September has only 30 days) so the date adjustment, hence the warnings.
Our DBA says , "The messages are just telling you that DB2 corrected the invalid date to allow the SQL to run. The fact that it generates 8 lines for every record, this is what is filling up the JES2 spool and causing the system issue. "
To avoid using up system resources, we were looking at supressing the warning messages. Other option before us is to schedule job on 27 or 28 every month, so it never encounters the 30/31 days issue.
We know to put PARMS('SQL,1,TOLWARN(YES)') in batch job, can serve our purpose, but I was just testing the same parm in SPUFI, but not getting the desired result because of reason mentioned in my previous post.
In spufi I am testing for this query.
Code:
--#SET TOLWARN NO
SELECT MONTH(DATE('2013-03-31') - 6 MONTHS) from sysibm.sysdummy1;