0

I'm trying to do pagination in DB2. I wouldn't like to do it with subquery, but OFFSET is not working with TIMESTAMP_FORMAT.

Use of function TIMESTAMP_FORMAT in QSYS2 not valid. Data mapping error on member

I've found this question, but seems here the problem is with content of column and it's not my case, as values are alright and TIMESTAMP_FORMAT works without OFFSET. I didn't look for some other way to not use TIMESTAMP_FORMAT, as I need to create pagination on queries written not by me, but by client.

The query looks like this.

SELECT DATE(TIMESTAMP_FORMAT(CHAR("tablename"."date"),'YYMMDD')) 
FROM tableName
OFFSET 10 ROWS

I get

"[SQL0583] Use of function TIMESTAMP_FORMAT in QSYS2 not valid."

I'm not sure how OFFSET can relate to TIMESTAMP_FORMAT, but when I replace the select with select * it works fine.

I wonder why there is a conflict between OFFSET and TIMESTAMP_FORMAT and is there a way to bypass this without subquery.

TH1995
  • 35
  • 7

2 Answers2

0

I suspect your problem is bad data...

The default for the IBM interactive tools, STRSQL and ACS Run SQL Scripts, is OPTIMIZE(*FIRSTIO) meaning get the first few rows back as quickly as possible...

With the OFFSET 10 clause you're probably accessing rows initially that you didn't before.

Try the following

create table mytest as (
  SELECT DATE(TIMESTAMP_FORMAT(CHAR("tablename"."date"),'YYMMDD')) as mydate
FROM tableName
) with data

If that doesn't error, then yes you've found a bug, open a PMR.

Otherwise, you can see how far along the DB got by looking at the rows in the new table and track down the record with bad data.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • Thanks for reply Charles, I've tried to order by that field to be sure that data is used and there is no bad data problem, now I tried your suggestion too and it doesn't error. Do you think subquery is the only alternative to the bypass this somehow? – TH1995 Apr 12 '19 at 15:13
  • @TH1995 a subquery might have the same problem. Db2 for i does rewrite queries...so it might actually run the same thing behind the scenes – Charles Apr 12 '19 at 17:52
0

From Listing of SQL Messages:

SQL0583
Function &1 in &2 cannot be invoked where specified because it is defined to be not deterministic or contains an external action.
Functions that are not deterministic cannot be specified in a GROUP BY clause or in a JOIN clause, or in the default clause for a global variable.
Functions that are not deterministic or contain an external action cannot be specified in a PARTITION BY clause or an ORDER BY clause for an OLAP function and cannot be specified in the select list of a query that contains an OFFSET clause.
The RAISE_ERROR function cannot be specified in a GROUP BY or HAVING clause.

I don't know how to check these properties for the QSYS2.TIMESTAMP_FORMAT function (there is no its definition in the QSYS2.SYSROUTINES table), but it looks like improper definition of this function - there is no reason to create it as not deterministic or external action.
You can "deceive" DB2 like this: 

CREATE FUNCTION MYSCHEMA.TIMESTAMP_FORMAT(str VARCHAR(4000), fmt VARCHAR(128))
RETURNS TIMESTAMP
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN QSYS2.TIMESTAMP_FORMAT(str, fmt);

SELECT 
DATE(MYSCHEMA.TIMESTAMP_FORMAT(CHAR(tablename.date), 'YYMMDD')) 
--DATE(QSYS2.TIMESTAMP_FORMAT(CHAR(tablename.date), 'YYMMDD')) 
FROM table(values '190412') tableName(date)
OFFSET 10 ROWS;

And use this function instead. It works on my 7.3 at least.
It's a harmless deception, and you may ask IBM support to clarify such a "feature" of QSYS2.TIMESTAMP_FORMAT...

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • Yeah, that's the problem, thanks Mark. Documentation explains that though it's a deterministic function but there are cases when it uses CURRENT_TIMESTAMP (e.g. when format-string is not explicitly specified, or when locale-name is not explicitly specified). So it's non-deterministic function actually. – TH1995 Apr 15 '19 at 05:43
  • @TH1995 There is no an ability not to specify the `format-string` parameter of the [TIMESTAMP_FORMAT](https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzscatsformat.htm) function. `Deterministic` means (briefly) - the same result on the same parameters *for the same statement invocation*, and not across different invocations or different sessions. It doesn't depend on what is the locale setting in the current session. So, once again, from my point of view it's wrong declaration of this system function. – Mark Barinstein Apr 15 '19 at 07:57