× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I'll try the QSH option.

I ran visual explain and found some interesting info but not the direct issue.

Searching further I found a reference to UPPER that slows the system and that was my major culprit. I removed upper and the job ran in 20 minutes.


Thanks

Sent from my iPad

On Mar 14, 2015, at 12:59 PM, Arco Simonse <arco400@xxxxxxxxx> wrote:

Darryl,

I missed the fact (from your other post) that you are on V 5.4, I think the
SRCSTMF parameter will not be there.
You can also use qshell to execute an IFS source.

QSH CMD('db2 -t -f ''/mgyrc/sqlsrc/modsrc/m00000116i.sql''
''MYDTALIB''')


But that will not give you the summary of RUNSQLSTM.

Dit you try Visual Explain in iNavigator?

Best regards,
Arci Simonse

2015-03-14 17:55 GMT+01:00 CRPence <CRPbottle@xxxxxxxxx>:

On 14-Mar-2015 08:59 -0500, Darryl Freinkel wrote:

Does anyone know what the maximum width a source for SQL can be?

~32K bytes (minus 12), same as for any Source Physical File (SRC-PF)
Record Format. Apparently however, the unstated release of interest is
v5r4 [as inferred from the other recent post]. On v5r4 the effective limit
is 80 bytes, because by then there is not yet a parameter on the Run SQL
Statement (RUNSQLSTM) command for the SQL to process any data beyond the
/margin/ of 80 bytes. Since V6R1 there is the Source Margins (MARGINS)
parameter:

<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/cl/runsqlstm.htm>
_Run SQL Statements_ (RUNSQLSTM)
"...
_Source margins_ (MARGINS)

Specifies the part of the source input record that contains source text.
The left margin is always position 1 of the input record. The right margin
defaults to 80.

If the Source stream file (SRCSTMF) parameter is specified, margins are
ignored. ...
..."


I need to isolate which statements are causing slow run times and
which part of a script is failing. Running the script in iNavigator
does not provide enough feedback even when including debug
facilities.

Using the RUNSQLSTMT command provides a nice audit trail which I want
to use.
As I recall, the same class of debug messages that would appear in a
batch or interactive joblog that issued the system-supplied RUNSQLSTM
command, should appear in the joblog of the server job that processes the
statement(s) from the Run SQL database feature of iNav.


I ask the question because my scripts go out to 200 characters wide
and I do not want to have to reformat a ton of code. If I could just
paste the code into RDI and then run it, we would all win.
Deferring to an alternate script processor [than RUNSQLSTM] or
reformatting the statements, are likely the only options :-( on v5r4.
Again, I am not sure why the debug feedback mechanism via the Run SQL is
insufficient; at least when deferring to the joblog of the server.

The following QSHell invocation should be able to take as input a stream
file named longSQL [in the current directory], and place the wrapped [at
80-bytes] stream data records into the member named ONLY of the source file
named SRCLEN80; the database source physical file would have been
previously created with CRTSRCPF RCDLEN(92) MBR(ONLY) into a permanent
library in *LIBL:

qsh cmd('cat ./longSQL | rFile -wlQ "srclen80(only)"')

For reference:
IBM i V5R4-> i5/OS Information Center, Version 5 Release 4-> Programming->
Development tools-> Qshell-> Utilities-> Working with files and
directories-> Rfile
<http://www.ibm.com/support/knowledgecenter/ssw_i5_54/rzahz/rfile.htm>
Rfile - Read or write record files

After that wrapping, copying the wrapped records into fixed-length
database records, the statements in the source should be capable of being
run instead, using RUNSQLSTM; e.g., after the above QSH request
successfully copies the wrapped data to the SRC-PF:

RUNSQLSTM SRCFILE(*LIBL/SRCLEN80) SRCMBR(ONLY) /* COMMIT(as_rqd)
NAMING(as_rqd) ERRLVL(as_rqd) etc. */

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.