Re: Converting SQL scripts from iNavigator to Source files

[Darryl Freinkel <dhfreinkel@xxxxxxxxx>]

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.