• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Changing the Library List for Run SQL Scripts

    June 3, 2009 Skip Marchesani

    The Run SQL Scripts function–a.k.a. the SQL Script Center or Script Center–is an easy-to-use option in System i Navigator that allows you to interactively create and execute one or more SQL statements and/or batch CL commands. One of the most frequently asked questions I get is: How do I access or find my tables and views using the Script Center?

    Accessing Tables and Views

    There are two options for finding or accessing your tables, views, and other database objects from the Script Center. One option is to qualify the object name with the library name in your SQL statement or CL command, and the other option is to use a library list to find or access the desired object. Exactly how the object name is qualified depends on the object naming convention that is being used by the Script Center.

    Object Naming Convention

    The Script center supports the two standard methods of System i object naming convention or object qualification: SQL and System. The default Script Center naming convention is SQL, which means that object names are qualified in the format “library.object,” and System object naming convention uses the more familiar format of “library/object.” Most likely you will want to change from SQL to System, which is relatively easy and takes just a couple of steps. If you’re comfortable using SQL naming, you don’t have to make this change.

    Figure 1.

    To change the object naming convention, first click on Connection in the toolbar at the top of the Run SQL Scripts window, then click on JDBC Setup as shown in Figure 1.

    Figure 2.

    Next click on the Format Tab on the right side of the resulting JDBC Setup window. Then select System (*SYS) from the Naming Convention drop-down at the top for the Format Tab window, click OK as shown in Figure 2, and you’re done.

    What Is the Default Library List?

    The default library list (and current library) for the Scripts Center is determined by the user profile that the Script Center is running under, which is determined by the System i Navigator sign-on option selected when the connection to the System i server was created. See your system administrator or System i security officer for more detailed information regarding the sign-on options.

    If you want to determine the default Script Center library list, you need to know the Script Center job ID. This can be found by clicking on View on the left side of the toolbar at the top of the Run SQL Scripts window, and then Job Log in the resulting drop down.

    The job ID can be found just underneath the toolbar on the top left side of the Job Log window. Once you have the job ID, use the WRKACTJOB command in the green screen interface or the Work Management function (if available to you) in System i Navigator to determine the default library list.

    Changing the Library List

    The Scripts Center allows you to change or customize its current library and/or library list. To change either, click on Connection in the toolbar at the top of the Run SQL Scripts window, then click on JDBC Setup as shown if Figure 1.

    Figure 3.

    Next click on the Server Tab (should be the default tab) on the left side of the resulting JDBC Setup window. At the top left you should see a text entry box for SQL Default Library, and immediately below that one for Library List as shown in Figure 3.

    Figure 4.

    The SQL Default Library is the library or schema name (schema is the SQL term for library) that will be used to locate unqualified object names referenced in SQL statements or CL commands. You can think of it as the equivalent of the current library in your library list.

    If the Script Center object naming convention is System (*SYS) and there is no entry for the SQL Default Library, the default library becomes *LIBL (the library list for the Script Center). If the object naming convention is SQL, the SQL Default Library is the library with the same name as the user profile that the SCRIPT Center is running under, which is determined by the System i Navigator sign-on option selected when the connection to the System i server was created. See your system administrator or System i security officer for more detailed information regarding the sign-on options.

    The SQL Default Library is changed by entering a single library name into the SQL Default Library text box as shown in Figure 4.

    The Library List text box is used to provide the names of one or more libraries that will be added to the server job’s library list, and the default is blank or no library name entries. Library names entered in the Library List text box are separated by commas or spaces as shown in Figure 4, and libraries can be added to your library list or replace the default list entirely.

    To replace the list, specify a list of library names. To add to the existing user library list, add *USRLIBL to the list of libraries. All libraries that are listed before *USRLIBL are added to the front of the user library list. All libraries that are listed after *USRLIBL are added to the end of the user library list.

    Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book DB2/400: The New AS/400 Database. He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Halcyon Software:  Webinar: How to Survive in IT with a reduced headcount, June 4
    Maximum Availability:  *noMAX - Subscription edition now available (US & UK)
    10ZiG Technology:  BOSaNOVA, Inc. is now 10ZiG Technology

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Are You an IT Action Hero? Aldon Wants to Know With No Power6 QCMs, IBM Waits for Power7

    Leave a Reply Cancel reply

Volume 9, Number 18 -- June 3, 2009
THIS ISSUE SPONSORED BY:

WorksRight Software
Help/Systems
Guild Companies

Table of Contents

  • RPG Beans from VB Apps
  • Changing the Library List for Run SQL Scripts
  • Admin Alert: Beyond Replication in an i5/OS High-Availability Environment

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle