Db2 on Z, XML SQLERR -16002

Marcus Davage

Db2 on Z, XML SQLERR -16002
In here (https://www.worldofdb2.com/profiles/blogs/pattern-matching-using-regular-expression-in-db2) and here (https://www.segus.com/2019-01-regular-expressions-in-db2-sql/ ), some really neat SQL is provided to perform regular expression matches in SQL on Db2 for Z. However, I keep getting the following message:

DSNT408I SQLCODE = -16002, ERROR: AN XQUERY EXPRESSION HAS AN UNEXPECTED TOKEN ( FOLLOWING newDoc[fn:matches. EXPECTED TOKENS MAY INCLUDE: . ERROR QNAME=err:XPST0003
DSNT418I SQLSTATE = 10505 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXQPRS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

From both this:
SELECT ID, COL1 from T1
WHERE XMLEXISTS('$newDoc[fn:matches(., "^(iP).*[0-9]$")]' PASSING
XMLQUERY('<doc>{$colcontent}</doc>' PASSING COL1 as "colcontent")
as "newDoc"
);
and this:
SELECT NAME, creator from SYSIBM.SYSTABLES
WHERE
XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING
XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
as "newXDoc")
order by 1
--fetch first 10 rows only
;

Any ideas, anyone?
Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 - SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image003.png (12.8k)

Roy Boxwell

Db2 on Z, XML SQLERR -16002
(in response to Marcus Davage)
Works fine here:



SELECT NAME, creator from SYSIBM.SYSTABLES
00001587

WHERE
00001793

XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING
00001893

XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
00001987

as "newXDoc")
00002087

order by 1
00002187

--fetch first 10 rows only
00002295

;
00002387

---------+---------+---------+---------+---------+---------+---------+------
---+---------+---------+---------+---------+---------+--

NAME
CR

---------+---------+---------+---------+---------+---------+---------+------
---+---------+---------+---------+---------+---------+--

IDENT10
SC

MIVPT22
SC

MVNXT01
MV

MVNXT02
MV

MVNXT03
MV

MVNXT04
MV

MVNXT05
MV

MVNXT06
MV



My firms Db2 does have , as decimal separator though...



I added a space after the . and it still works here



SELECT NAME, creator from SYSIBM.SYSTABLES

WHERE

XMLEXISTS('$newXDoc[fn:matches(. , "^[B-Z]{2,5}[0-9]{2}$")]' PASSING

XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")

as "newXDoc")

order by 1

--fetch first 10 rows only

;



Try that!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur
Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Davage, Marcus [mailto:[login to unmask email]
Sent: Monday, July 1, 2019 6:34 PM
To: [login to unmask email]
Subject: [DB2-L] - Db2 on Z, XML SQLERR -16002



In here
(https://www.worldofdb2.com/profiles/blogs/pattern-matching-using-regular-ex
pression-in-db2) and here
(https://www.segus.com/2019-01-regular-expressions-in-db2-sql/ ), some
really neat SQL is provided to perform regular expression matches in SQL on
Db2 for Z. However, I keep getting the following message:



DSNT408I SQLCODE = -16002, ERROR: AN XQUERY EXPRESSION HAS AN UNEXPECTED
TOKEN ( FOLLOWING newDoc[fn:matches. EXPECTED TOKENS MAY INCLUDE: . ERROR
QNAME=err:XPST0003

DSNT418I SQLSTATE = 10505 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXQPRS SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION



From both this:

SELECT ID, COL1 from T1

WHERE XMLEXISTS('$newDoc[fn:matches(., "^(iP).*[0-9]$")]' PASSING

XMLQUERY('<doc>{$colcontent}</doc>' PASSING COL1 as
"colcontent")

as "newDoc"

);

and this:

SELECT NAME, creator from SYSIBM.SYSTABLES

WHERE

XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING

XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")

as "newXDoc")

order by 1

--fetch first 10 rows only

;



Any ideas, anyone?

Regards,

Marcus Davage

Lead Product Developer

AMI-DevOps for Db2 – SQL Performance


Direct

+44 118 921 8517






Mobile

+44 7840 023 560




Email

<mailto:[login to unmask email]> [login to unmask email]







BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh,
Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No.
1927903 The content of this email is confidential. If you are not the
addressee, you may not distribute, copy or disclose any part of it. If you
receive this message in error, please delete this from your system and
notify the sender immediately.

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

Attachments

  • smime.p7s (5.1k)

Marcus Davage

Db2 on Z, XML SQLERR -16002
(in response to Marcus Davage)
OK. I guess it's a codepage problem, as it works perfectly in Data Studio...

Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 - SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





From: Davage, Marcus
Sent: 01 July 2019 17:34
To: '[login to unmask email]' <[login to unmask email]>
Subject: Db2 on Z, XML SQLERR -16002

In here (https://www.worldofdb2.com/profiles/blogs/pattern-matching-using-regular-expression-in-db2) and here (https://www.segus.com/2019-01-regular-expressions-in-db2-sql/ ), some really neat SQL is provided to perform regular expression matches in SQL on Db2 for Z. However, I keep getting the following message:

DSNT408I SQLCODE = -16002, ERROR: AN XQUERY EXPRESSION HAS AN UNEXPECTED TOKEN ( FOLLOWING newDoc[fn:matches. EXPECTED TOKENS MAY INCLUDE: . ERROR QNAME=err:XPST0003
DSNT418I SQLSTATE = 10505 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXQPRS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

From both this:
SELECT ID, COL1 from T1
WHERE XMLEXISTS('$newDoc[fn:matches(., "^(iP).*[0-9]$")]' PASSING
XMLQUERY('<doc>{$colcontent}</doc>' PASSING COL1 as "colcontent")
as "newDoc"
);
and this:
SELECT NAME, creator from SYSIBM.SYSTABLES
WHERE
XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING
XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
as "newXDoc")
order by 1
--fetch first 10 rows only
;

Any ideas, anyone?
Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 - SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image003.png (12.9k)
  • image004.png (13.1k)