db2 v11 for z/OS - help with char9

Chris Hoelscher

db2 v11 for z/OS - help with char9
I have a query:

SELECT
CAST ( yyy AS CHAR ( 6 ) ) AS yyy
FROM zzz
WITH UR FOR FETCH ONLY

Works great

When I change char to char9 I get

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: CCSID ) . FOR
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 40 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000'
X'FFFFFFFF' X'00000028' X'000001F6' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.

I plan to open an issue with IBM but wanted a second/third pair of eyes to see if I am missing something

Yes, I am at applcompat v11r1

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

Chris Hoelscher

db2 v11 for z/OS - help with char9
(in response to Chris Hoelscher)
It looks like in this query CHAR is used as a column size indicator, NOT a function

So I wonder why ifcid376 picked it up as possibly needing change …..

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Chris Hoelscher <[login to unmask email]>
Sent: Tuesday, August 28, 2018 11:37 AM
To: [login to unmask email] ([login to unmask email]) <[login to unmask email]>
Subject: [DB2-L] - db2 v11 for z/OS - help with char9

I have a query:

SELECT
CAST ( yyy AS CHAR ( 6 ) ) AS yyy
FROM zzz
WITH UR FOR FETCH ONLY

Works great

When I change char to char9 I get

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: CCSID ) . FOR
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 40 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000'
X'FFFFFFFF' X'00000028' X'000001F6' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.

I plan to open an issue with IBM but wanted a second/third pair of eyes to see if I am missing something

Yes, I am at applcompat v11r1

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266


The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.
-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Kai Stroh

RE: db2 v11 for z/OS - help with char9
(in response to Chris Hoelscher)

I hope I'm not totally misunderstanding the question, but did you do something like this:

SELECT
CAST(IBMREQD AS CHAR9(6)) AS IBMREQD
FROM SYSIBM.SYSDUMMY1               
WITH UR FOR FETCH ONLY

This will end in SQL error -104 because Db2 expects the name of a data type after the first "AS", and CHAR9 is not a data type. It's the name of a built-in function. Parsing ends after the token "CHAR9" and the error message points to the "(" symbol after that, which is arguable a little bit misleading.

What will work is either this:

SELECT
CAST(IBMREQD AS CHAR(9)) AS IBMREQD
FROM SYSIBM.SYSDUMMY1               
WITH UR FOR FETCH ONLY

or this:

SELECT
CHAR9(IBMREQD) AS IBMREQD
FROM SYSIBM.SYSDUMMY1
WITH UR FOR FETCH ONLY

The reason why your original statement was picked up by IFCID376 is probably because I think there is a change in the way DECIMAL values are cast to CHAR. If Db2 cannot guarantee that the "yyy" in your query is never a DECIMAL, then it will flag that query as potentially in need of review.




In Reply to Chris Hoelscher:

It looks like in this query CHAR is used as a column size indicator, NOT a function

So I wonder why ifcid376 picked it up as possibly needing change …..

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Chris Hoelscher <[login to unmask email]>
Sent: Tuesday, August 28, 2018 11:37 AM
To: [login to unmask email] ([login to unmask email]) <[login to unmask email]>
Subject: [DB2-L] - db2 v11 for z/OS - help with char9

I have a query:

SELECT
CAST ( yyy AS CHAR ( 6 ) ) AS yyy
FROM zzz
WITH UR FOR FETCH ONLY

Works great

When I change char to char9 I get

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: CCSID ) . FOR
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 40 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000'
X'FFFFFFFF' X'00000028' X'000001F6' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.

I plan to open an issue with IBM but wanted a second/third pair of eyes to see if I am missing something

Yes, I am at applcompat v11r1

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266


The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.
-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.



--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Michael Hannan

RE: db2 v11 for z/OS - help with char9
(in response to Chris Hoelscher)

In Reply to Chris Hoelscher:

It looks like in this query CHAR is used as a column size indicator, NOT a function

So I wonder why ifcid376 picked it up as possibly needing change …..

CHAR9(arg)   Function is supposed to act like DB2 V9 CHAR(arg) function where the argument is a DECIMAL data type, or something that would be implicitly converted to DECIMAL, e.g. using arithmetic. This was as a replacement, since DB2 V10 CHAR function was changed to produce same results as a CAST to CHAR, making CHAR a non upward compatible Function.

So the 9 means V9 format (right justified numeric with zero fill and leading sign), nothing to do with length. V10 CHAR function produces left justified result with no leading Zero fill and optional sign char.

CHAR9 function did appear in the SQL Ref eventually. I think in an update to DB2 11 Reference:

"CHAR9

The CHAR9 function returns a fixed-length character string representation of the argument. The CHAR9 function is intended for compatibility with previous releases of DB2 for z/OS that depend on the result format that is returned for decimal input values in Version 9 and earlier."

 

Strangely CHAR9 was not made available at DB2 V10 I believe (or just not in the manual), you had to use BIFCOMPAT initially.

IFCIDs were setup to identify use of non upward compatible stuff.

As Kai said, there is no CAST to CHAR9.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Sep 28, 2018 - 10:07 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Sep 28, 2018 - 10:12 AM (Europe/Berlin)