Wednesday, November 4, 2020

Getting "Run SQL scripts" to use the library list

change run sql scripts to use library list

Perhaps I am the only person who experienced this issue with my "Run SQL scripts", but I am going to share the solution I found.

I have to say "Run SQL scripts" is probably my favorite IBM i tool. I open it when I start my work day, and I use it all day to help me develop and test various SQL statements before I copy them into programs, procedures, or source members that I then create the objects from using the RUNSQLSTM command.

When the latest release of Access Client Solutions, ACS 1.1.8.5, was released I download the new install files, uninstalled the version of ACS I already had, and ran the install program to install it onto my PC. When it completed and I started to use "Run SQL scripts" again I found that I had to qualify the object, file, view, etc. name with the library. If I did not I always received the message telling me that the object was not found.

If you are someone else who is experiencing this too I have the solution!

In "Run SQL scripts"...

1.  Select "Connection" on the menu bar.

2.  Select "Edit JDBC Configuration – Default" in the drop down list

3.  Select the "Format" tab at the top of the "JDBC Configuration – Default" window

4.  The default in the "Naming convention" box is "SQL"

5.  Using the drop down select "System"

6.  Click on the "Save" button at the bottom of the window.

7.  You should see a message in the message section of the main "Run SQL scripts" screen that tells you that it is "applying the JDBC configuration". It disconnects from the IBM i and reconnects to apply I made to the JDBC connection. When that has finished the message below is displayed.


Click on image to open larger version

I can now any execute SQL statement over an object in my library list without having to qualify with the library name.

What is interesting is that my work computer is still using the previous version ACS, 1.1.8.4, with "SQL" in the "Naming convention" box and I do not have to qualify the object name if it is in my library. Strange.

14 comments:

  1. You can also run a CL command. Just put CL: before command and don't forget about ; on the end.

    e.g.
    cl: call system/setlibl;

    ReplyDelete
    Replies
    1. I have written about that before.

      You can also prompt the command with the F4 key.

      Delete
    2. You can use cl: or you can call a DB2 supplied Stored Procedure QCMDEXC:

      CALL QCMDEXC('put CL command here');

      Example: CALL QCMDEXC('CALL CLPGMX (parm1 parm2)';
      CALL QCMDEXC('AddLibLE myLib');

      Delete
  2. RSS tool also allows you to make many different JDBC session setups. This allows you to apply different *LIBL to the session. Then connecting to different library set ups is made easier.

    Go into the JDBC Configurations... menu option and you get another properties window. Create as many as you need.

    -Matt

    ReplyDelete
    Replies
    1. With a library list applied at the JDBC configuration level, the library list is set each time you connect using that JDBC configuration.

      Switching libraries is as simple as switching JDBC configuration.

      I often use CTRL+F11 to reset my connection for various reasons. Having a JDBC configuration set up that matches my testing environment I can have that library list used right away.

      Creating several new JDBC settings at one time can also be rather quick because the JDBC settings are just a text file stored in your "iAccessClient\RunSQLScripts\JDBC" folder.

      Delete
  3. What default system schema or library list do you have set up in the JDBC connection in Run SQL Scripts? We don't have to qualify it and it works for us.

    Default SQL schema: Specifies the default SQL schema. SQL statements can be created without specifying the schema that an object is located in. The objects in the SQL statement are said to be unqualified.
    Example: In the following SQL statement, QCUSTCDT is an unqualified object. The schema that is used to locate the table name QCUSTCDT is called the implicit qualifier.
    SELECT * FROM QCUSTCDT

    Notes:
    If a default SQL schema is specified, it will be used as the implicit qualifier. The default SQL schema will also be the first item in the schema list.
    If a default SQL schema is not explicitly specified, one of the following applies:
    For SQL naming, the implicit qualifier is the run-time authorization. This is a schema with the same name as the user profile specified on the JDBC connection.
    For system naming, the implicit qualifier is the job schema list.
    Library list: The library list is used for resolving unqualified names. It specifies one or more libraries that you want to add to or replace the library list of the server job. The library names can be separated by spaces or commas. Note that library names cannot be longer than 10 characters in length. You must use the SET PATH statement (SQL) if you have libraries with names longer than 10 characters.

    Naming convention: Specifies the naming convention used when referring to objects. The possible values are:

    SQL - A period (.) is used as a qualifier between the schema and object name.
    SYS - A forward slash (/) is used as a qualifier between the schema and object name.

    ReplyDelete
    Replies
    1. I don't like using the default schema list because the default schema may not always be the library list you want to work with.

      I think its far better to use cl: or call the IBM supplied stored procedure CALL QCMD('put CL command here');

      Delete
  4. If anyone has TAATOOLS installed you can also use CHGLBLJOBD to rebuild the session *LIBL. Ex, cl:CHGLBLJOBD MYJOBD;

    -Matt

    ReplyDelete
  5. Is there a way to RETRIEVE the Job# of the JDBC connected job (maybe using RTVJOBA, for example as it is done within a CL-program) or dynamically using SQL table function or some other means?

    Thanks in advance.

    ReplyDelete
    Replies
    1. You can use the JOB_NAME and then you would need to parse the job number from that.

      This post will give you an idea of how to do that.

      Delete
    2. Thank you Simon. Appreciate it very much. I was trying the "Current_Job" just as in Current_Date/Time/Server, and was not getting anywhere.

      Delete
    3. JOB_NAME is a Global Variable. See here to learn more about what else you can use.

      Delete
  6. Hi Simon, Long time follower, I always seem to find myself on your informative site. Question, does your QUSER profile have a JOBD assigned with production library list?

    ReplyDelete
    Replies
    1. I leave the IBM user profiles as they are intended, i.e. default job description, default IBM library list.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.