RE: Why does USER require VARCHAR and not work with CHAR? -- MIDRANGE-L

Hi Glen,

the only recommendation about varying length fields is, you should add the
ALLOCATE key word and allocate as much space in your column, that up to 80%
of all (varying length) data can be stored directly in the column and only
the outlier are stored within the overflow area.
The information for varying length fields with less than a maximum of 30
Characters are completely stored within the column whether allocate is
specified or not.
If allocate is not specified for longer varying length fields only 16 Byte
are reserved in the column to hold the address of the overflow area, where
all varying length field and large object information is saved. When reading
from those fields and additional step, accessing the overflow area, is
performed which in total can slow down the performance.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn
Gundermann
Sent: Donnerstag, 12. Oktober 2017 23:44
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Why does USER require VARCHAR and not work with CHAR?

Hi Matt,

Good comments.
Is it documented as a best practice to use VARCHAR on all character columns?
I.E.
Is there a minimum length for the column to worry about CHAR vs. VARCHAR?
Is there any thinking about the column usage when deciding on CHAR vs.
VARCHAR?

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144

On 12 October 2017 at 17:26, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

An additional question is why folks are defining fixed length strings
in any new database files.

I know in most modern programming languages doing string trims on all
those CHAR fields is a major pain in the....

How often have you had to do a TRIM(FirstName) + TRIM(LastName) so
that it shows up as "John Smith" instead of "John
Smith "

It is for that reason I loath CHAR and NCHAR.

I sometimes wonder how many CPU cycles have been burned over time
removing all those blank spaces at the end of CHAR and NCHAR strings.

-----Original Message-----
From: Paul Therrien [mailto:paultherrien@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, October 12, 2017 4:18 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: Why does USER require VARCHAR and not work with CHAR?

Yeah, I realized this after I responded to you. My only thinking on
this is that the 'generate' option somehow forces you to model your
database field after the attributes of the special register.

What if you changed the change_user field to not be a generate-always
field, and allowed it to default like the create_user field?
There would be a chance that someone could overwrite the change_user
field on an insert, but is this something you can live with?

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Glenn Gundermann
Sent: Thursday, October 12, 2017 4:36 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Why does USER require VARCHAR and not work with CHAR?

Hi Paul,

My question is really asking why the first column is okay using CHAR
and the second column not, when both are using the same special register.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144

On 12 October 2017 at 15:08, Paul Therrien <paultherrien@andecosoftware.
com>
wrote:

The USER special register is defined as Varchar(18) so I would guess
anything that uses the User would also need to be varchar(18).

From SQL Reference...
USER
The USER special register specifies the run-time authorization ID at
the current server. The data type of the special register is
VARCHAR(18).

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf
Of Glenn Gundermann
Sent: Thursday, October 12, 2017 2:48 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Why does USER require VARCHAR and not work with CHAR?

Hi there,

IBM i 7.3, TR 2, cum level 17061, DB2 PTF group level 6

I have a CREATE TABLE statement where the create user column is
using
CHAR(18) and the change user column won't allow CHAR(18) but works
if VARCHAR(18).
Why can't they both be CHAR(18)?

This won't work:

CREATE OR REPLACE TABLE glenn_test_table FOR SYSTEM NAME test_table (
create_user FOR COLUMN recrtuser CHAR(18) CCSID 37 NOT NULL
DEFAULT USER ,
change_user FOR COLUMN rechguser CHAR(18) CCSID 37 NOT NULL
GENERATED ALWAYS AS (USER) )
RCDFMT RPCMMNT

SQL0574 Column, sequence, or variable attribute is not valid.

This does work:

CREATE OR REPLACE TABLE glenn_test_table FOR SYSTEM NAME test_table (
create_user FOR COLUMN recrtuser CHAR(18) CCSID 37 NOT NULL
DEFAULT USER ,
change_user FOR COLUMN rechguser VARCHAR(18) CCSID 37 NOT NULL
GENERATED ALWAYS AS (USER) )
RCDFMT RPCMMNT

My question, why?

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at

https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at

https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at

https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD