× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. October 2017

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

Sounds like a reasonable default. Certainly better than the IBM default of 0.

Wonder how many cases someone has had to optimize that. Definitely gives you greater control.

I would hope the RDBMS would be smart enough to handle these things behind the scenes for the user as to avoid the complexity in setting that value. You would need to somehow have a crystal ball to foretell the future of the average size of data in a column ahead of time. I would think the database optimization process would analyze this for you and adjust accordingly through regularly scheduled DB management processes.

-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Friday, October 13, 2017 2:34 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Why does USER require VARCHAR and not work with CHAR?

Or as in the case of MS SQL Server...

It keeps varchar data in the row (up to a max row size of 8K)

It also assumes that the varchars are 50% filled when allocating memory for sorting ect...

I'd seem to me that DB2's option allows for bit more efficiency ...

Charles


On Fri, Oct 13, 2017 at 12:49 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

Or could it be that other databases are silly stupid and always ship
you out to extents?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Matt Olson <Matt.Olson@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/13/2017 02:27 PM
Subject: RE: Why does USER require VARCHAR and not work with CHAR?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I do not use that keyword. I'll have to look into it though as it
seems like a silly default.

I wonder why the RDBMS on iSeries has this parameter when other DB
manufacturers don't require it? Is it a case of other RDMBS are
smarter about the storage of varchar data and dynamically adjust the
allocations in a table based on algorithms?

-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxx]
Sent: Friday, October 13, 2017 10:41 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Why does USER require VARCHAR and not work with CHAR?

Just curious, Matt - do you use the ALLOCATE attribute for VARCHAR
columns on the i? It is highly recommended, as Birgitta mentioned in
her post here.

I believe this is not part of other RDBMS. And tools generally do not
set it when generating tables for use on IBM i. The Default is 0,
which results in 2 IOs for each access to the data for each column.

Regards
Vern

On 10/13/2017 9:17 AM, Matt Olson wrote:
I always store everything as varchar so I don't have to constantly
be
trimming trailing blank spaces in a thousand places in various
applications. The CHAR trailing blank space problem leads to bloated
software. It forces developers to wrap all their column names in
TRIM() command somewhere (either in SQL query or in RPG, .NET, java, etc).

The only case I can think of is CHAR(1). But that might be for
putting
something like "Y" or "N", which I also loath as that is not
automatically translated in higher level languages as a Boolean flag.

Instead of Y/N, its better to use a BIT field (1/0). However DB2
doesn't
have BIT database types, so you are left with a less than ideal option
of using smallint.

-----Original Message-----
From: Glenn Gundermann [mailto:glenn.gundermann@xxxxxxxxx]
Sent: Thursday, October 12, 2017 4:44 PM
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


--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.