Retrieve SQL (RTVSQL) source statement

Search400.com member Jean-Paul Lamontre discusses the SQL Visual Explain tool, which comes with iSeries Navigator. Jean-Paul finds this tool very handy to improve the performance of SQL statements. In this tip he shares why he feels it would be beneficial for you to also have Data Description Language (DDL) in the index, and he explains the best way to achieve that.

RtvSql-Retrieve SQL source statement

SQL Visual Explain tool, which comes with iSeries Navigator, is a very handy tool that helps you to improve the performance of your SQL statements. If you haven't had the opportunity to try it, I highly recommend it. You can find the Visual Explain tool here. If you have used it, I'm confident that you found it a very convenient tool that is able to graphically explain an SQL sentence, to highlight overload process (i.e. when a subquery works thousands of records to return a dozen), to suggest missing index, and to create that index that misses to decrease your request answer time.

Up until that point, all goes well. But as soon as it is necessary to report the correction on several production machines, I believe it would be more convenient to have the Data Description Language (DDL) of that index.

The RTVSQL tool allows you to have DB2 export the source code DDL of an SQL object.

DB2 has the ability to rebuild the DDL of the alias, constraint, function, index, procedure, schema, table, trigger, type and view.

Here's a sample of DB2 requesting a DDL of object QSYS2/SYSTABLES *FILE:

And the corresponding DDL

Here's the last version: Right click and save as an IFS file.

  • RTVSQL-CMD.TXT
  • RTVSQLFM-DSPF.TXT
  • RTVSQLHP-PNLGRP.TXT
  • RTVSQLR-RPGLE.TXT

    And then…

  • CPYFRMSTMF FROMSTMF('/IfsDir/Sample.txt') TOMBR('/qsys.lib/mylib.lib/qxxxsrc.file/sample.mbr') MBROPT(*REPLACE) STMFCODPAG(*PCASCII) CVTDTA(*AUTO) DBFCCSID(*FILE) TABEXPN(*NO)

  • CHGPFM FILE(mylib/QxxxSRC) MBR(sample) SRCTYPE(xxx) TEXT('description')
  • Note: The above source code is maintained at Jean-Paul Lamontre's site. If you have questions regarding this tip, visit Lamontre's site for contact information.


    This was first published in January 2006

    Dig deeper on DB2 UDB (universal databases)

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchEnterpriseLinux

    SearchDataCenter

    Close