Re: Is there a DLTSQLINF like there is a PRTSQLINF?

The sql packages are a separate and distinct object.
The SQL access plans are imbedded into the program itself.


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: Evan Harris <auctionitis@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 12/14/2016 02:31 PM
Subject: Re: Is there a DLTSQLINF like there is a PRTSQLINF?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I seem to remember being able to delete SQLPKG objects (apart from the
model ones) without any side effects and the next run would re-create
them.
Maybe try deleting the *SQLPKG objects after an appropriate save...

On Thu, Dec 15, 2016 at 6:43 AM, Carel <coteijgeler@xxxxxxxxx> wrote:

As far as I know, there is no way to do that, such a command does not
exist, wish it did.

What I recall is when running the SQL statement (stored in the SQLPKG)
again, the metadata of the run (access path, how to run the SQL

statement)

is updated after each execution.

Otherwise you have to delete the old SQLPKG object and recreate it again
with a compile.

Fear not much of a heklp here.


Regards,

Carel Teijgeler






Op 14-12-2016 om 16:33 schreef Rob Berendt:

Let's say you wanted to refresh the sql information stored with a

program.

The stuff you see with PRTSQLINF. Is there such a thing like

DLTSQLINF?

I'd like to stop short of recompiling the program.
If no command, then an API maybe?

Something which doesn't require something like:
insert into -yourlib-.qaqqini
values('REBUILD_ACCESS_PLAN','*YES',null)

Some info on a job whose fix from IBM was the above QAQQINI:

MCH3203
Message . . . . : Function error X'1720' in machine instruction.
Internal
dump identifier (ID)
.
Cause . . . . . : The machine instruction that was running failed.
Time
stamp is 12/12/16 02:30:00, error code is X'1720', error class is 0,
device
number is X'0000'. The error class indicates how the damage was
detected:

From program . . . . . . . . . : DbopReferenceDataTable

Instruction . . . . . . . . : 003740
To program . . . . . . . . . . : QQQOOODBOP
To library . . . . . . . . . : QSYS
To module . . . . . . . . . : QQQOOOINV
To procedure . . . . . . . . : CALLDBMAINTFOROPENOROPTIMIZE
To statement . . . . . . . . : 5108


If you do the PRTSQLINF you will see 'access plan' messages about that
program.

I ran the program once with the modified qaqqini and no longer got the
message. Even after running it again with the qaqqini file set back.

I suspect some PTF's I put on this weekend are getting a bit picky

about

this here access plan info. I have PMR open. Twas a sev 1 until the
qaqqini work around. Played absolute havoc with SQL updates.

Rob Berendt

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

--

Regards
Evan Harris
--
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


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