Re: SQL Question - Select * versus selecting all fields individually -- MIDRANGE-L

For my two cents, always, always bring in only the fields you need.
Bringing in the entire table or multiple tables defeats the whole
purpose of SQL.

Also, as other have indicated, IBM does not optimize field moves. It
moves field by field. Really no reason for it to do that but that is way
it does it. The problem is obviously important enough for IBM to provide
us with data structures on output of file I/O. SQL needs to do the same
thing. If the out or input is a data structure, just do a single move.

This, also, opens up an idea for another utility (as if I don't have
enough to do already). Paste an SQL statement into a screen or maybe
read from a source member and generate a data structure for that SQL
Statement using the ODBC api's. From or to line numbers in a source
member or markers in the source to say where to start or just spin
through the source member and generate a structure for each SQL.

Does this have potential? Anybody seen a utility to do this already?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
midrange-l-request@xxxxxxxxxxxx
Sent: Friday, April 06, 2007 2:44 AM
To: midrange-l@xxxxxxxxxxxx
Subject: MIDRANGE-L Digest, Vol 6, Issue 731

Send MIDRANGE-L mailing list submissions to
midrange-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/midrange-l
or, via email, send a message with subject or body 'help' to
midrange-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
midrange-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of MIDRANGE-L digest..."

*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is meaningful.

Today's Topics:

1. SQL Question - Select * versus selecting all fields
individually (Glenn Gundermann)
2. Re: SQL Question - Select * versus selecting all fields
individually (Scott Klement)
3. RE: SQL Question - Select * versus selecting all fields
individually (Elvis Budimlic)
4. Re: SQL Question - Select * versus selecting all fields
individually (Peter Dow (ML))
5. Re: V5R2 --> V5R3 (PaulMmn)
6. Re: V5R2 --> V5R3 (Al Barsa)
7. Re: V5R2 --> V5R3 (Bruce Vining)
8. Proxy and .pac (Richard ECUYER)
9. Re: FTP commands to Japanese system (Simon Coulter)

----------------------------------------------------------------------

message: 1
date: Thu, 5 Apr 2007 19:39:19 -0400 (EDT)
from: "Glenn Gundermann" <ggundermann@xxxxxx>
subject: SQL Question - Select * versus selecting all fields
individually

Hi Everyone,

Someone here with lots of SQL experience but none with System i says:
"My 2 cents on your code is not to use Select *. Always list the columns
solely for performance reasons."

My thinking is to select all columns into an externally described DS
based
on the table so I have everything I might need.

I could:
(a) keep my code as is, using select *, or
(b) select every field individually
(c) only select the fields I need, or

What is everyone doing when using embedded SQL?

Tks,

Glenn Gundermann
ggundermann@xxxxxx
(647) 272-3295

------------------------------

message: 2
date: Thu, 05 Apr 2007 17:49:49 -0500
from: Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>
subject: Re: SQL Question - Select * versus selecting all fields
individually

Hi Glenn,

Someone here with lots of SQL experience but none with System i says:
"My 2 cents on your code is not to use Select *. Always list the
columns solely for performance reasons."

I never put * in a program (though I use it often for ad-hoc interactive

SQL statements) in a program, I always select only the fields I need.
Two reasons:

a) The fewer fields it has to extract, the faster it runs.

b) Maintenance is easier. If I change fields in the file (make them
bigger, smaller, etc.) then I only have to change the programs that
refer to those particular fields. The fewer programs that refer to
each given field, the easier they are to change!

------------------------------

message: 3
date: Thu, 5 Apr 2007 17:53:47 -0500
from: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
subject: RE: SQL Question - Select * versus selecting all fields
individually

For performance reasons, I always go with option c in your list.

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: SQL Question - Select * versus selecting all fields
individually

Hi Everyone,

Someone here with lots of SQL experience but none with System i says:
"My 2 cents on your code is not to use Select *. Always list the columns
solely for performance reasons."

My thinking is to select all columns into an externally described DS
based
on the table so I have everything I might need.

I could:
(a) keep my code as is, using select *, or
(b) select every field individually
(c) only select the fields I need, or

What is everyone doing when using embedded SQL?

Tks,

Glenn Gundermann

------------------------------

message: 4
date: Thu, 05 Apr 2007 18:13:48 -0700
from: "Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>
subject: Re: SQL Question - Select * versus selecting all fields
individually

Hi Glenn,

Option c is good for performance and to avoid level-check problems -- if

new fields are added, or fields you aren't using are changed, your
program will still run correctly.

I've used option a with the externally-defined DS when I know the file
isn't going to change a lot but the program might (i.e. might use more
fields), and performance is acceptable -- usually interactive
inquiry-type programs.

I haven't tested it, but I think there are a lot of other factors that
affect performance more directly than * vs a list of fields, e.g. having

the correct indices.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Glenn Gundermann wrote:

Hi Everyone,

Someone here with lots of SQL experience but none with System i says:
"My 2 cents on your code is not to use Select *. Always list the

columns

solely for performance reasons."

My thinking is to select all columns into an externally described DS

based

on the table so I have everything I might need.

I could:
(a) keep my code as is, using select *, or
(b) select every field individually
(c) only select the fields I need, or

What is everyone doing when using embedded SQL?

Tks,

Glenn Gundermann
ggundermann@xxxxxx
(647) 272-3295

------------------------------

message: 5
date: Thu, 5 Apr 2007 21:51:49 -0400
from: PaulMmn <PaulMmn@xxxxxxxxxxxxx>
subject: Re: V5R2 --> V5R3

So -that's- how they do it! (: I have noticed that from release to
release some jobs that use outfiles post messages in the job log that
the file used for output doesn't match the file provided by IBM.

--Paul E Musselman
PaulMmn@xxxxxxxxxxxxxxxxxxxx

With all due respect to Rob (who really knows his stuff), IBM has a
tendency to add new fields to the end of output files which are all (?)
generated as LVLCHK(*NO), so this should not break applications on a
release boundary change.

Al

Al Barsa, Jr.
Barsa Consulting Group, LLC

------------------------------

message: 6
date: Thu, 5 Apr 2007 22:11:37 -0400
from: Al Barsa <barsa@xxxxxxxxxxxxxxxxxxx>
subject: Re: V5R2 --> V5R3

I'd love to know the message ID, and what kind of message it is.

Al

Al Barsa, Jr.
Barsa Consulting Group, LLC

400>390

"i" comes before "p", "x" and "z"
e gads

Our system's had more names than Elizabeth Taylor!

914-251-1234
914-251-9406 fax

http://www.barsaconsulting.com
http://www.taatool.com
http://www.as400connection.com

PaulMmn

<PaulMmn@xxxxxxxx

m.com>
To
Sent by: midrange-l@xxxxxxxxxxxx

midrange-l-bounce
cc
s@xxxxxxxxxxxx

Subject
Re: V5R2 --> V5R3

04/05/2007 10:04

PM

Please respond to

Midrange Systems

Technical

Discussion

<midrange-l@midra

nge.com>

So -that's- how they do it! (: I have noticed that from release to
release some jobs that use outfiles post messages in the job log that
the file used for output doesn't match the file provided by IBM.

--Paul E Musselman
PaulMmn@xxxxxxxxxxxxxxxxxxxx

With all due respect to Rob (who really knows his stuff), IBM has a
tendency to add new fields to the end of output files which are all (?)
generated as LVLCHK(*NO), so this should not break applications on a
release boundary change.

Al

Al Barsa, Jr.
Barsa Consulting Group, LLC

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

------------------------------

message: 7
date: Fri, 6 Apr 2007 04:47:55 -0500
from: Bruce Vining <bvining@xxxxxxxxxx>
subject: Re: V5R2 --> V5R3

Just guessing, but the system (rather than the using application) will
often post CPD706A, with an appropriate reason code, when a mismatch is
detected.
Bruce Vining

Al Barsa <barsa@xxxxxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/05/2007 09:11 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>

To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: V5R2 --> V5R3

I'd love to know the message ID, and what kind of message it is.

Al

Al Barsa, Jr.
Barsa Consulting Group, LLC

400>390

"i" comes before "p", "x" and "z"
e gads

Our system's had more names than Elizabeth Taylor!

914-251-1234
914-251-9406 fax

http://www.barsaconsulting.com
http://www.taatool.com
http://www.as400connection.com

PaulMmn
<PaulMmn@xxxxxxxx
m.com>
To

Sent by: midrange-l@xxxxxxxxxxxx
midrange-l-bounce
cc

s@xxxxxxxxxxxx

Subject

Re: V5R2 --> V5R3
04/05/2007 10:04
PM

Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>

So -that's- how they do it! (: I have noticed that from release to
release some jobs that use outfiles post messages in the job log that
the file used for output doesn't match the file provided by IBM.

--Paul E Musselman
PaulMmn@xxxxxxxxxxxxxxxxxxxx

With all due respect to Rob (who really knows his stuff), IBM has a
tendency to add new fields to the end of output files which are all (?)
generated as LVLCHK(*NO), so this should not break applications on a
release boundary change.

Al

Al Barsa, Jr.
Barsa Consulting Group, LLC