Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE

Tony Andrews

Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE
So I tried to create a global variable in V12 with an array datatype as follows:

CREATE TYPE PHONE_NUMS AS CHAR(10) ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS; ==> Received a -060 Invalid CCSID specification.

When looking into this, found the following: 'When defining a variable as a character array datatype i An array type with elements
that are defined as character strings must be defined with CCSID UNICODE'.

So I executed the following and it was created, loaded, and tested:

CREATE TYPE PHONE_NUMS AS CHAR(10) CCSID UNICODE ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS;

Can anyone shed some information on why the UNICODE restriction for a character global array variable?

Thanks in advance,

Tony Andrews

Nadir Doctor

Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE
(in response to Tony Andrews)
Hi Tony,

One of the main advantages of unicode is to simplify application
development and not have custom code for handling special character sets -
when using utf-8, the first 127 characters are the traditional ascii values
which also simplifies adapting current ascii applications to unicode.


Best Regards,
Nadir



On Wed, Mar 7, 2018 at 9:41 AM, Tony Andrews <[login to unmask email]> wrote:

> So I tried to create a global variable in V12 with an array datatype as
> follows:
>
> CREATE TYPE PHONE_NUMS AS CHAR(10) ARRAY[5];
>
> CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS; ==> Received a -060 Invalid
> CCSID specification.
>
> When looking into this, found the following: 'When defining a variable as
> a character array datatype i An array type with elements
> that are defined as character strings must be defined with CCSID
> UNICODE'.
>
> So I executed the following and it was created, loaded, and tested:
>
> CREATE TYPE PHONE_NUMS AS CHAR(10) CCSID UNICODE ARRAY[5];
>
> CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS;
>
> Can anyone shed some information on why the UNICODE restriction for a
> character global array variable?
>
> Thanks in advance,
>
> Tony Andrews
>
> -----End Original Message-----
>
>

Michael Hannan

RE: Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE
(in response to Tony Andrews)

Tony,

I am equally perplexed that Declared Variables don't have to be UNICODE but Global Arrays do have to be. Did the developers run out of time to get it working? or think for Global use, we would all be better off with Unicode.

Also a Global Variable non-array type does not have to be UNICODE?

String manipulation in SQL is sometimes made more complex by having UNICODE used at a basically EBCDIC site for reports and screen codepages. Even the 3rd party software vendors struggle with DSN@* appearing in the software names, due to codepage issues, meaning @ has no consistent representation at all sites.

UNICODE maybe attempt to solve having a common representation for all usage of the Global Array and limiting to meaningful character strings, but may introduce more CASTing problems than it solves.

I found processing SYSPACKSTMT which has some BIT DATA string columns, actually containing possibly UNICODE encoding or possible EBCDIC under the covers, quite tricky to process. I have been forced into tricks using UNPACK function.

So the array cannot be BIT DATA. The alternative might be BINARY, but it's almost impossible to Cast BINARY or VARBINARY to any other data type, so rather limiting.

I will be interested to hear how efficient Global Array Variable lookups can be compared to SELECT SQLs, UDFs, expressions in a SET statement, etc. to see how the Global Arrays are best used. e.g. Would I want to store the User Scratch Pad area in an item of a Global Array, or is it no use, due to no affect from COMMIT and ROLLBACK? When might Global Arrays be suitable for communication data between processes concurrent or not?

I guess we can compare to the builtin Global Variables. These seem to require SET statements to use Unicode encoding for Char strings or may use implicit conversion to Unicode, when nothing is coded.

Any experiences would be valuable.

My observation for Global Variables and Arrays, is that there is no logging. So clearly not to be used for any data that has any recovery requirements, should a process storing values fail to commit successfully. 
 
In Reply to Tony Andrews:

So I tried to create a global variable in V12 with an array datatype as follows:

CREATE TYPE PHONE_NUMS AS CHAR(10) ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS; ==> Received a -060 Invalid CCSID specification.

When looking into this, found the following: 'When defining a variable as a character array datatype i An array type with elements
that are defined as character strings must be defined with CCSID UNICODE'.

So I executed the following and it was created, loaded, and tested:

CREATE TYPE PHONE_NUMS AS CHAR(10) CCSID UNICODE ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS;

Can anyone shed some information on why the UNICODE restriction for a character global array variable?

Thanks in advance,

Tony Andrews

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 08, 2018 - 08:20 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 08, 2018 - 11:51 AM (Europe/Berlin)

Daniel Luksetich

Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE
(in response to Michael Hannan)
I performed a test using the global array data type during Db2 12 ESP for IDUG. Functionally they help simplify programming, especially in situations where you have a routine with variable input. Previously, folks would pack an array into a VARCHAR data type and then unpack it in the routine. Now, they can pack it into a global array, and do it within a SELECT of SET statement. They can code a loop in the routine to process the array using array functions. So, there is definitely value. Now, I did not performance test use of global arrays. However, from previous experience with inline SQL user-define functions the typical casting overhead is an additional 10% CPU over not casting. So, that’s a safe value to estimate, but it’s also compounded by the variableness of the array.



Here's the short of it. You could potentially gain a significant reduction in development time by employing global arrays, also avoiding potential redundancy in routines. Assume you pay an additional 10% overhead.



I’m a big fan of this.



Maybe Pat can comment on why Unicode.



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, March 8, 2018 1:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE



Tony,

I am equally perplexed that Declared Variables don't have to be UNICODE but Global Arrays do have to be. Did the developers run out of time to get it working? or think for Global use, we would all be better off with Unicode.

Also a Global Variable non-array type does not have to be UNICODE?

String manipulation in SQL is always made more complex by having UNICODE used at a basically EBCDIC site for reports and screen codepages. Even the 3rd party software vendors struggle with DSN@* appearing in the software names, due to codepage issues, meaning @ has no consistent representation at all sites.

UNICODE maybe attempt to solve having a common representation for all usage of the Global Array and limiting to meaningful character strings, but may introduce more CASTing problems than it solves.

I found processing SYSPACKSTMT which has some BIT DATA string columns, actually containing possibly UNICODE encoding or possible EBCDIC under the covers, quite tricky to process. I have been forced into tricks using UNPACK function.

So the array cannot be BIT DATA. The alternative might be BINARY, but it's almost impossible to Cast BINARY or VARBINARY to any other data type, so rather limiting.

I will be interested to hear how efficient Global Array Variable lookups can be compared to SELECT SQLs, UDFs, expressions in a SET statement, etc. to see how the Global Arrays are best used. e.g. Would I want to store the User Scratch Pad area in an item of a Global Array, or is it no use, due to no affect from COMMIT and ROLLBACK? When is What types of communication data between processes concurrent or not are suitable?

I guess compare to the builtin Global Variables. These seem to require SET statements to use Unicode encoding for Char strings or may use implicit conversion to Unicode, when nothing is coded.

Any experiences would be valuable.

In Reply to Tony Andrews:

So I tried to create a global variable in V12 with an array datatype as follows:

CREATE TYPE PHONE_NUMS AS CHAR(10) ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS; ==> Received a -060 Invalid CCSID specification.

When looking into this, found the following: 'When defining a variable as a character array datatype i An array type with elements
that are defined as character strings must be defined with CCSID UNICODE'.

So I executed the following and it was created, loaded, and tested:

CREATE TYPE PHONE_NUMS AS CHAR(10) CCSID UNICODE ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS;

Can anyone shed some information on why the UNICODE restriction for a character global array variable?

Thanks in advance,

Tony Andrews



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Tony Andrews

Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE
(in response to Daniel Luksetich)
Thanks Dan (and Michael). Yes the use of array datatypes (especially with SP parameters) is great, as stated. And the
functions that come with array processing makes it easy to load and unload. I have tested in our little Themis system,
with no performance issues.

But with the global variables being ‘Global’, maybe that had something to do with having to define them with UNICODE for the
string datatypes as opposed to a declared variable within code.

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Thursday, March 08, 2018 9:14 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE

I performed a test using the global array data type during Db2 12 ESP for IDUG. Functionally they help simplify programming, especially in situations where you have a routine with variable input. Previously, folks would pack an array into a VARCHAR data type and then unpack it in the routine. Now, they can pack it into a global array, and do it within a SELECT of SET statement. They can code a loop in the routine to process the array using array functions. So, there is definitely value. Now, I did not performance test use of global arrays. However, from previous experience with inline SQL user-define functions the typical casting overhead is an additional 10% CPU over not casting. So, that’s a safe value to estimate, but it’s also compounded by the variableness of the array.

Here's the short of it. You could potentially gain a significant reduction in development time by employing global arrays, also avoiding potential redundancy in routines. Assume you pay an additional 10% overhead.

I’m a big fan of this.

Maybe Pat can comment on why Unicode.

Cheers,
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, March 8, 2018 1:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 12 on z/OS - Global Variable Arrays Must be Defined as CCSID UNICODE


Tony,

I am equally perplexed that Declared Variables don't have to be UNICODE but Global Arrays do have to be. Did the developers run out of time to get it working? or think for Global use, we would all be better off with Unicode.

Also a Global Variable non-array type does not have to be UNICODE?

String manipulation in SQL is always made more complex by having UNICODE used at a basically EBCDIC site for reports and screen codepages. Even the 3rd party software vendors struggle with DSN@* appearing in the software names, due to codepage issues, meaning @ has no consistent representation at all sites.

UNICODE maybe attempt to solve having a common representation for all usage of the Global Array and limiting to meaningful character strings, but may introduce more CASTing problems than it solves.

I found processing SYSPACKSTMT which has some BIT DATA string columns, actually containing possibly UNICODE encoding or possible EBCDIC under the covers, quite tricky to process. I have been forced into tricks using UNPACK function.

So the array cannot be BIT DATA. The alternative might be BINARY, but it's almost impossible to Cast BINARY or VARBINARY to any other data type, so rather limiting.

I will be interested to hear how efficient Global Array Variable lookups can be compared to SELECT SQLs, UDFs, expressions in a SET statement, etc. to see how the Global Arrays are best used. e.g. Would I want to store the User Scratch Pad area in an item of a Global Array, or is it no use, due to no affect from COMMIT and ROLLBACK? When is What types of communication data between processes concurrent or not are suitable?

I guess compare to the builtin Global Variables. These seem to require SET statements to use Unicode encoding for Char strings or may use implicit conversion to Unicode, when nothing is coded.

Any experiences would be valuable.

In Reply to Tony Andrews:
So I tried to create a global variable in V12 with an array datatype as follows:

CREATE TYPE PHONE_NUMS AS CHAR(10) ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS; ==> Received a -060 Invalid CCSID specification.

When looking into this, found the following: 'When defining a variable as a character array datatype i An array type with elements
that are defined as character strings must be defined with CCSID UNICODE'.

So I executed the following and it was created, loaded, and tested:

CREATE TYPE PHONE_NUMS AS CHAR(10) CCSID UNICODE ARRAY[5];

CREATE VARIABLE GV_PHONE_NUMS PHONE_NUMS;

Can anyone shed some information on why the UNICODE restriction for a character global array variable?

Thanks in advance,

Tony Andrews



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

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

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