First question:
Is it a good standard practice to create the table with no keys and use
the Create Index?
SQL Tables do not have a key like DDS files can have. It is only possible to
define additional primary and unique key constraints.
We always have an "artificial" unique key (identity column) that we define
as primary key. This column value is used in depended tables (and not any
compound unique key).
Additionally we define unique key constraints for the "real" unique key.
For everything else SQL indexes are used (btw. SQL indexes can be accessed
with native I/O like any keyed logical file)
In the new table it will have a 2400 varchar field. I would guess that
90%+ of the records will not ever be displayed again.
For the Allocate clause should I still use the average length of the real
comment, say 150 or leave it at 0?
Using a VarChar field instead of multiple single fields is a good idea.
If you define a Column with ALLOCATE(0) the complete content or the Varying
Character field is stored in an so called Overflow area. Within the column
itself only the address of the overflow area is stored.
When reading from the column, an additional step must be performed.
If you specify a length with ALLOCATE only texts that are longer as the
specified length are stored within the Overflow area. Shorter texts are
stored directly within the column.
The best performance can be reached if the allocated length can hold up to
80% or all texts.
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
(WalzCraft) Jerry Forss
Sent: Freitag, 3. Februar 2017 14:46
To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx)
<midrange-l@xxxxxxxxxxxx>
Subject: SQL Table Create
Hi All,
I am making my first, of many, attempt to create a DDL Table to eventually
replace a DDS file.
First question:
Is it a good standard practice to create the table with no keys and use the
Create Index?
Second question:
The table I want to replace is a comment file that has the comments broken
into 50 char chunks.
The user enters 50 char at a time, hits enter to add the record, tries to
remember where they left off and continues to add.
What a mess. It was created in 1989.
I created a new pgm that splices all the 50 char records together, displays
a single 2400 char field that they can add to and insert into the middle of.
I then re-parse the field into 50 char chunks again.
There are about 25 million records with the 50 char comment.
In the new table it will have a 2400 varchar field. I would guess that 90%+
of the records will not ever be displayed again.
For the Allocate clause should I still use the average length of the real
comment, say 150 or leave it at 0?
Thanks!!
Jerry Forss
Information Technology Team
WalzCraft
608-781-6355 EXT 2530
jforss@xxxxxxxxxxxxx
Subject to Change Notice:
WalzCraft reserves the right to improve designs, and to change
specifications without notice.
Confidentiality Notice:
This message and any attachments may contain confidential and privileged
information that is protected by law. The information contained herein is
transmitted for the sole use of the intended recipient(s) and should "only"
pertain to "WalzCraft" company matters. If you are not the intended
recipient or designated agent of the recipient of such information, you are
hereby notified that any use, dissemination, copying or retention of this
email or the information contained herein is strictly prohibited and may
subject you to penalties under federal and/or state law. If you received
this email in error, please notify the sender immediately and permanently
delete this email. Thank You;
WalzCraft PO Box 1748 La Crosse, WI. 54602-1748
www.walzcraft.com<
http://www.walzcraft.com/> Phone... 800-237-1326
--
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:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://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
midrange.com
