ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

ACS Run Sql Scripts

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ACS Run Sql Scripts

    Might seem like a dumb question.. how do i establish a library list in here?

    Also, I have quite a few "saved" in .SQL files on my PC. When I double-click to open them directly, I always have to choose a server. Is there any way to set a default server?

  • #2
    Far easier to do a short (very) non-professional video than try to describe it - so here it is: https://screencast.com/t/FBgQvp6w2b

    Comment


    • #3
      Thanks Jon.. After following your video (what's not professional??) that doesn't totally work for me. Whatever library I have in the DEFAULT SCHEMA is recognized (ASTHHOBJ). However, it will not locate any tables in the SCHEMA LIST.

      If I look at the "Global Variables and Special Registers" tab, I can see ALL of the libraries I added to the schema list in my CURRENT PATH:
      CURRENT PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","ASTHHOBJ","A STDTA","VARSHP35F","WQ_TBF","HHPLIB","REPORTS"
      I have to be doing something else wrong?

      Comment


      • #4
        And you reconnected using the modified connection?

        Can't think of anything else I'm afraid - I rarely work across different libraries.

        Comment


        • #5
          Yes. And the SQL error clearly states that it's looking for the table in the "default" schema

          Comment


          • #6
            Sorry - no idea then. If you have no further luck here maybe try midrange.com - lots of ACS expertise over there.

            Comment


            • #7
              I'm not sure if it would make a difference, but see if your SQL naming convention is set to *SYS on the Format tab of the JDBC configuration.

              Comment


              • #8
                That should be the magic pixie dust Brian.

                Edited to note that with a little assistance from Paul Tuohy I was able to establish that despite the connection I had told it to use it was using a different system. Didn't notice because one of the libraries in my list occurs on both systems.

                SO the answer is:

                Under the "Format" tab set naming to *SYS

                Under the "System" tab set your default schema in the first box then list any other libraries you want in the LIBL in the "Library list" box with each name separated from the next by a comma.

                Save the result then under "Connection" in the Run SQL Scripts window select "Apply JDBC Connection" and pick your modified connection.

                That's it.
                Last edited by JonBoy; April 26, 2019, 04:28 PM.

                Comment


                • #9
                  Just a comment:
                  It makes a big difference whether SQL Naming conventions or System Naming conventions are used!
                  At least if the database objects are not qualified and for creating new database objects!

                  1. When using SQL Naming conventions ...
                  you can only access tables and views located in the current or default schema unqualified. Tables and views in other libraries/schemas have to be qualified.
                  If you specify a Default Schema in the Configuration, this default schema becomes your CURRENT SCHEMA.
                  The default/current schema can be set or modified by executing the SET CURRENT SCHEMA statement.

                  Unqualified specified SQL routines (stored procedures, user defined functions and user defined table functions) are searched within the SQL Path.
                  The SQL path can include multiple schemas and also allow the special value *LIBL.
                  The SQL path can be set or modified by executing the SET CURRENT PATH statement.
                  Tables and views are NOT searched within the SQL path, so even if the SQL path includes the table/view schema and the default schema defers from the table/view schema, the unqalified specified table/view is not found.

                  The library list is completely ignored. It might be used, if the special value *LIBL is specified in the SET CURRENT PATH statement.

                  2. When using System Naming conventions ....
                  The library list is searched for finding unqualified specified Tables/View AND unqualified specified SQL routines.
                  The library list can be predfined in the connection by populating the library list.
                  The library list can be set or modified by executing the CHGLIBL or ADDLIBLE or RMVLIBLE commands. In the ACS script CL commands to be executed must be preceeded by an CL:.

                  Attention: If a CURRENT SCHEMA is set, unqualified specified tables/views are searched in this CURRENT SCHEMA, even though System Naming is used.

                  Birgitta

                  Comment


                  • #10
                    An alternative method I recently discovered.

                    With Run SQL Scripts you can run CL commands, if you prefix them with "cl:". E.g.
                    Code:
                    cl:call mylib/mypgm;
                    This means you can create a CL program that adds a bunch of libraries to the library list, then call it from Run SQL Scripts using this method. Because the CL command is run by the remote iSeries job that is handling the SQL requests for this specific connection, it is the library list of that job that is updated. So subsequent SQL requests (until you disconnect) use that library list

                    Comment

                    Working...
                    X