MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2014

RE: testing an SQL procedure



fixed

Rob,
Yea, had I been thinking I may have caught the command name style
but have now used the command - output requires knowing "PTF Group"
by number which leaves me out of school.
Ops mgr says he'll get the required group this week on test so the 8 ball
may be removed from "play".

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, September 02, 2014 12:55 PM
To: Midrange Systems Technical Discussion
Subject: RE: testing an SQL procedure

Gary,

I had to read between the lines on your last email. Let me try some translating from English.
By "prod and test parts" I'll assume you meant production and test partitions.
If you got a file not found message on group_ptf_info then you probably are behind the 8 ball on ptf groups and should upgrade. In that case you will have to fall back to Buck's suggestion.
However, Buck was suggesting that you get out of sql and get to an IBM i
5250 command line. From that command line enter WRKPTFGRP He was not suggesting that you try: select * from wrkptfgrp If the IBM i 5250 command line is totally foreign to you, or, you are forbidden from accessing the command line except for the most rudimentary commands, as in 'limited capabilities, yes', then you could try IBM Navigator for System i, either the fat client or the 2001 port browser client. There's a way to look up PTF information there.


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: Gary Thompson <gthompson@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/02/2014 02:45 PM
Subject: RE: testing an SQL procedure
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Thanks Rob, Thanks Buck,
I ran that select on prod and test parts and both return *FILE not found
tried FROM group_ptf_info and FROM wrkptfgrp ?
I'll suggest those two links to ops mgr
Also, my user prof has all except *SECADM

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
rob@xxxxxxxxx
Sent: Tuesday, September 02, 2014 12:11 PM
To: Midrange Systems Technical Discussion
Subject: RE: testing an SQL procedure

Now you do:

select *
from group_ptf_info

http://www.ibm.com/developerworks/ibmi/library/i-db2_catalog/index.html?ca=dat


I think this should be part of the "system catalog" as defined in:
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatalog.htm?lang=en


But IBM thinks this view should be referenced by:
IBM i 7.2>Database>Performance and query optimization>IBM i Services>PTF
Services>GROUP_PTF_INFO
Yeah, if I was looking up a sql way to see my PTF group for TCP/IP I would
definitely think that Database Performance and query optimization would be
my go to reference. NOT!

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: Gary Thompson <gthompson@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/02/2014 01:52 PM
Subject: RE: testing an SQL procedure
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Chuck,
Thanks for your insight.
I failed to think of including that link but will be more thorough in
future posts.
Also, as posted to Rob, I now know the partition I am testing on is not
yet to
the ptf level spec'd in Michael's article, and our production part not be
for a
month or more . . . and, as you see, I don't know how to verify ptf levels

. . .

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Tuesday, September 02, 2014 11:20 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: testing an SQL procedure

On 02-Sep-2014 12:10 -0500, rob@xxxxxxxxx wrote:
On 02-Sep-2014 11:56 -0500, Gary Thompson wrote:
Still trying to test my SQL procedure I created last week.
From Four Hundred Guru article
Dynamic Compound Statements in DB2 for i
by Michael Sansoterra:

I'm trying to run the following from Run Sql Scripts:

BEGIN
DECLARE doc_num CHARACTER(11);
call swiretest/fsp975dn ('82 ',1140627,'4200',doc_num); END ;
; This results in:
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token DOC_NUM was not valid.

This was run against our test partition which is V7R1M0, with
"recent" PTF's I'm doing everything I can to avoid embedded SQL,
wanting to learn the easier/faster way to create and test SQL
procedures.

Any chance you left out some stuff in the beginning? Like

Create Procedure
MyProcedureName (list of parameters)
Language SQL

and then your Begin and stuff...


Probably best to read the article [why a link was not included in the
OP.?.?] referenced in the OP, for why the CREATE PROCEDURE might be
unnecessary, for the scripted SQL:
<http://www.itjungle.com/fhg/fhg011514-story02.html>

The OP might want to try the same test script in a RUNSQLSTM to test if

there is any difference; perhaps also, to define and verify "recent", esp.

with regard to the stated minimum [from the article] of DB2 group PTF
level 26.

--
Regards, Chuck
--
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.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact