Tip

Retrieve SQL (RTVSQL) source statement

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

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    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:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.