Changing the Library List for Run SQL Scripts

Published: June 3, 2009

by 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