1

I tried running the query in SPUFI as : 

select * from (select * from emptable 
order by empno asc fetch first 10 rows only) as A 
order by empno desc fetch first 1 rows only;

The error returned is :

sqlcode = -4700, error: attempt to use new function before new function mode.

I am running DB2 for z/OS version 9.

piet.t
  • 11,718
  • 21
  • 43
  • 52
sleeper
  • 31
  • 4
  • Hmm, according to the manual -4700 is "-4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE". But I can't find anything special about your query that should require NFM on a current DB2-installation. What version of DB2 are you running (in CM)? – piet.t Aug 19 '16 at 09:15
  • Hi Piet , I just ran the query - select getvariable('SYSIBM.VERSION`) from SYSIBM.SYSDUMMY1 and the result was DSN09010 .. so i think its version 9 – sleeper Aug 19 '16 at 09:28

1 Answers1

5

The problem is that you are using order by and fetch first in a subselect - which is a feature that has not existed before version 9 of DB2 for z/OS:

In prior versions of DB2 for z/OS, the ORDER BY and FETCH FIRST n ROWS ONLY clauses were supported only at the statement level as part of select-statement or a SELECT INTO statement. Version 9 delivers additional flexibility by allowing both FETCH FIRST n ROWS ONLY and ORDER BY clauses when they are specified as part of a subselect or a fullselect statement.

(DB2 Version 9.1 for z/OS - What's New? GC18-9856-12)

Obviously your DB2-installation is still running in compatibility mode and has not been switched to new-function mode, so this feature is not yet available.

Please note that your installation has a more severe problem than CM <-> NFM since

Support for DB2 for z/OS Version 9 was discontinued on June 27, 2014.

piet.t
  • 11,718
  • 21
  • 43
  • 52