Large Objects created on AS400 consuming huge dik space

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. Re: IDENTITY_VAL_LOCAL (Vern Hamberg)
2. Re: Large Objects created on AS400 consuming huge disk space
(Luis Rodriguez)
3. RE: Large Objects created on AS400 consuming huge disk space
(Dennis Lovelady)
4. RE: Large Objects created on AS400 consuming huge disk space
(Dennis Lovelady)
5. Re: Large Objects created on AS400 consuming huge disk space
(Luis Rodriguez)
6. AW: IDENTITY_VAL_LOCAL (Birgitta Hauser)
7. Copy source between systems with RDI 7.5 (?ke Olsson)


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

message: 1
date: Mon, 01 Feb 2010 18:57:11 -0600
from: Vern Hamberg <vhamberg@xxxxxxxxxxx>
subject: Re: IDENTITY_VAL_LOCAL

Yes, RRN(table-name) is available. The trouble is, that value can be
different for the same data set in a record due to at least a couple
things - most of all, RGZPFM with order based on a logical file. So it
isn't going to be guaranteed to be the same for any record between one
run and another.

Vern

Brian Johnson wrote:

No sure why someone thinks they need RRN to insert. However RRN is

available for SQL queries:

select rrn(mytable), mytable.* from mytable ...

No need to add additional fields, and works with physical files created

from DDS. The RRN result column could be used in subsequent update
statements, eliminating the need to know the primary key (perhaps one does
not even exist) for the table.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
fbocch2595@xxxxxxx
Sent: Monday, February 01, 2010 3:45 PM
To: midrange-l@xxxxxxxxxxxx
Subject: IDENTITY_VAL_LOCAL


Hi Folks, I'm out of my league on this one but I'd like to
know if it's possible to define a field in DDS that will hold
the RRN without having any programming to populate the field?

We are trying to update fields in an AS400 file from the web
server using SQL. Our web people are saying they need the
RRN in the file in order to insert the record using SQL.
They asked if we could create a field and use the function
Identity_Val_Local but we've got no experience with it.

Does anyone out there in AS400/iSeries land know how it's used?

As always, thanks ahead of time.

Frank


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

_______________


Confidentiality Notice: This e-mail, including attachments, may include

confidential and/or proprietary information, and may be used only by the
person or entity to

which it is addressed. If the reader of this e-mail is not the intended

recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.

________________

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

message: 2
date: Mon, 1 Feb 2010 20:31:47 -0430
from: Luis Rodriguez <luisro58@xxxxxxxxx>
subject: Re: Large Objects created on AS400 consuming huge disk space

Dennis,

I thought so, although I wasn't very sure if he meant it from the IFS
perspective...

That said, my question about the "QUALITY" lib was because I didn't know if
that was a "new" sys library that would have appeared after V5R3 (where I'm
stuck right now).

If "QUALITY" is a real user lib, I hope he remembers that it would not be
backed up with a SAVLIB *ALLUSR. Back in 2001, when I began to work at the
company I'm right now, they lost (read "deleted") some objects from a
library called "QUIRLIBD" and, Surprise!! they weren't available on the
last
(*allusr) backup. I had to explain to them that "Q" was preferably
dedicated
to system objects, and beginning a library with "Q" was not the best
idea...
End of story? They did not want to change their "Q" libs (3), as most of
their programs had the lib name written for each call :-( They ended up
writing a SAVLIB QUI* right after the *ALLUSR.

Regards


Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries


On Mon, Feb 1, 2010 at 7:36 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx

wrote:

Although I agree with your point of view about RTVDSKINF, I'm a little
mystified regarding your statement about DSPOBJD getting only QSYS.LIB.
Would you like to clarify? Also, I see in your list a library called
"QUALITY". Is that a system lib?

Since I believe that I understand what Rob is saying, I'll try to answer

in

his absence.

DSPOBJD has visibility only to the objects that are in libraries. From

an

Integrated File System perspective, that means the QSYS.LIB directory and
everything below it. Put another way, all the *other* objects in the
system
(all the stuff in those other directories), large or small, are invisible
to
DSPOBJD.

QUALITY would not be a "system lib" from the IBM or System i perspective.


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Genius is one per cent inspiration and ninety-nine per cent

perspiration."

-- Thomas Edison



--
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: 3
date: Mon, 01 Feb 2010 21:15:22 -0500
from: "Dennis Lovelady" <iseries@xxxxxxxxxxxx>
subject: RE: Large Objects created on AS400 consuming huge disk space

Of course the idea that there are any "large objects" to be
located is inherently flawed. Unless the objects responsible for
the growth are already known, there is no way to know if the
increase in storage was by many small objects or even possibly by
small increments to existing objects of any size.

If the storage increase is to "permanent storage" versus
"temporary storage", the quickest method to track percentage
differences is by user profile; even without knowing which old or
new objects might have grown, this possibly enables determination of
which user(s) to "chase". A [baseline] snapshot of the current
storage taken by each *USRPRF can be obtained using DSPUSRPRF
TYPE(*BASIC) OUTPUT(*OUTFILE), and then later [e.g. after a change
in permanent storage space is noted], obtain another Display User
Profile output file that is then used compare the difference in the
UPMXSU "max storage used" field from the baseline snapshot to the
current snapshot.

Of course, this method is likewise inherently flawed in that it assumes
some
individual user's objects are at the heart of the growth. There is no
one-size-fits-all solution; there is no best way; there is no "quickest
method" to determine the root of the problem. While one may work for you
this time, it may fall short next time.

For example, in more than one client implementation I've worked, all
objects
that are promoted (by Turnover) end up being owned by a single user on the
Production system. When storage utilization starts growing, it's a safe
bet
that that's the user who will be charged with the growth. But in fact
there
is no one to chase. No matter what user is actually adding to the data,
the
owner of the file - completely innocent in this case - is the culprit by
this analysis.

The comparison against baseline that's given (by User Profile) may work for
you this time. For another time, a comparison against baseline using
DSPOBJD may do the trick. An analysis of IFS directory growth may reveal a
problem sometimes and for others RTVDSKINF may be the savior. In fact, all
of these tools have value in the administrator's toolbox, and none of them
is better, except of course the one that solves the current problem.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"I would rather men should ask why no statue has been erected in my honor,
than why one has."
-- Marcus Cato (2nd century BC)
"It is better to deserve honors and not have them than to have them and not
deserve them."
-- Mark Twain




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

message: 4
date: Mon, 01 Feb 2010 21:37:06 -0500
from: "Dennis Lovelady" <iseries@xxxxxxxxxxxx>
subject: RE: Large Objects created on AS400 consuming huge disk space

If "QUALITY" is a real user lib, I hope he remembers that it would not
be
backed up with a SAVLIB *ALLUSR. Back in 2001, when I began to work at
the
company I'm right now, they lost (read "deleted") some objects from a
library called "QUIRLIBD" and, Surprise!! they weren't available on the
last
(*allusr) backup. I had to explain to them that "Q" was preferably
dedicated
to system objects, and beginning a library with "Q" was not the best
idea...
End of story? They did not want to change their "Q" libs (3), as most
of
their programs had the lib name written for each call :-( They ended
up
writing a SAVLIB QUI* right after the *ALLUSR.

It's a good point, but doesn't apply here. QUALITY is not a library; it is
a directory in the IFS of the example system. It would be saved with the
SAV command, which has no *NONIBM or *ALLUSR concept.

Regards,

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
I no longer need to punish, deceive or compromise myself. Unless, of
course,
I want to stay employed.




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

message: 5
date: Mon, 1 Feb 2010 22:39:48 -0430
from: Luis Rodriguez <luisro58@xxxxxxxxx>
subject: Re: Large Objects created on AS400 consuming huge disk space

You are right, of course. I think I just assumed (a grave error, I know) it
was another Q-lib. My brain got "stuck" in my initial (mistaken) premise of
seeing those objects as QSYS libs...

I need a rest.. Signing off :-)


Regards,

Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries


On Mon, Feb 1, 2010 at 10:07 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx

wrote:

If "QUALITY" is a real user lib, I hope he remembers that it would not
be
backed up with a SAVLIB *ALLUSR. Back in 2001, when I began to work at
the
company I'm right now, they lost (read "deleted") some objects from a
library called "QUIRLIBD" and, Surprise!! they weren't available on the
last
(*allusr) backup. I had to explain to them that "Q" was preferably
dedicated
to system objects, and beginning a library with "Q" was not the best
idea...
End of story? They did not want to change their "Q" libs (3), as most
of
their programs had the lib name written for each call :-( They ended
up
writing a SAVLIB QUI* right after the *ALLUSR.

It's a good point, but doesn't apply here. QUALITY is not a library; it

is

a directory in the IFS of the example system. It would be saved with the
SAV command, which has no *NONIBM or *ALLUSR concept.

Regards,

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
I no longer need to punish, deceive or compromise myself. Unless, of
course,
I want to stay employed.


--
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: 6
date: Tue, 2 Feb 2010 07:00:53 +0100
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: IDENTITY_VAL_LOCAL

Hi,

RRN is available, but there are several problems:
1. How to determine the last inserted relative record no within the current
level (that's what IDENTITY_VAL_LOCAL is for).
It is possible to retrieve the highest record no, but what if another row
or even several rows are inserted by another job
between your insert and the execution of the statement that retrieves the
highest record no?
Or if the file is created with the option REUSEDLT *YES, where deleted
records get replaced before adding new records at the end of the file?
(and the WEB people will not use native I/O!!!)

2. When using the relative record no to access rows with SQL a table scan
or
at least in a table probe will be performed.
Both methods will result in a bad performance for large files or tables.
Even in release 6.1 it is not possible to create an index over the
relative record no.

Identity columns can only be implemented in SQL tables.
If it is not possible to convert your DDS described tables into a SQL
Table,
you may consider the following solution:
- Add an additional (numeric!?) column to your DDS described table.
- Add a before insert trigger. Determine the next (running) number for this
column.
If you are on release V5R3 or higher, you can create an SQL Sequence
object and retrieve the next running number with the SQL command NEXT VALUE
FOR yourSequence.
If you are not yet on release V5R3 you may simulate a Sequence Object,
i.e. create a data area that always holds the highest value.
In your trigger program you read the data area add 1 to the highest number
save the data area and write the newly determined highest number into your
record/row.
- create a keyed logical file with unique key over this new column.

In this way you do not have to change your existing programs (except you
have to recompile them, because you added a column/field).
A single column unique key is added and SQL can use Index Access to
retrieve
the record/row, which is very fast.

We had a similar problems with our web application. A record can only be
identified by a single column/field. For compound keys we either need to
concatenate them (and resplit them later) or enhance our files with a
column
that holds an "artificial" unique key.

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

-----Urspr?ngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Brian Johnson
Gesendet: Tuesday, 02. February 2010 00:12
An: 'Midrange Systems Technical Discussion'
Betreff: RE: IDENTITY_VAL_LOCAL

No sure why someone thinks they need RRN to insert. However RRN is
available for SQL queries:

select rrn(mytable), mytable.* from mytable ...

No need to add additional fields, and works with physical files created
from
DDS. The RRN result column could be used in subsequent update statements,
eliminating the need to know the primary key (perhaps one does not even
exist) for the table.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
fbocch2595@xxxxxxx
Sent: Monday, February 01, 2010 3:45 PM
To: midrange-l@xxxxxxxxxxxx
Subject: IDENTITY_VAL_LOCAL


Hi Folks, I'm out of my league on this one but I'd like to
know if it's possible to define a field in DDS that will hold
the RRN without having any programming to populate the field?

We are trying to update fields in an AS400 file from the web
server using SQL. Our web people are saying they need the
RRN in the file in order to insert the record using SQL.
They asked if we could create a field and use the function
Identity_Val_Local but we've got no experience with it.

Does anyone out there in AS400/iSeries land know how it's used?

As always, thanks ahead of time.

Frank


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

_______________


Confidentiality Notice: This e-mail, including attachments, may include
confidential and/or proprietary information, and may be used only by the
person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by
replying
to this message and delete this e-mail immediately.

________________
--
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: Tue, 2 Feb 2010 08:15:35 +0100
from: ?ke Olsson <ake.olsson@xxxxxx>
subject: Copy source between systems with RDI 7.5

We have been using WDSC version 7 (copy - paste) to copy source between
systems/partitions.



After upgrading to RDI 7.5 we have noticed a problem in RDI. The text
associated with the source member does no longer go with the copy. This
works in WDSC but not in RDI.



When copying source within the same system the text is copied but not when
sending source from one machine/partition to another.



Has anyone else noticed this? Solutions?





Med v?nlig h?lsning / Best regards


?ke H Olsson



Box 433 SE 551 16 J?nk?ping Sweden visit: Brunnsgatan 11

phone: +46 (0)36 342976 mobile: +46 (0)705 482976 fax: +46 (0)36 34 29 29

ake.olsson@xxxxxx <mailto:ake.olsson@xxxxxx> www.pdb.se





This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended recipient,
please notify the sender immediately by return e-mail, delete this
e-mail and destroy any copies. Any dissemination or use of this
information by a person other than the intended recipient is
unauthorized and may be illegal.

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest 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.



End of MIDRANGE-L Digest, Vol 9, Issue 151
******************************************