ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Defining/Creating SQL View using iSeries Navigator or something

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

  • Defining/Creating SQL View using iSeries Navigator or something

    Is there a way to define/create (and probably modify) an (SQL) VIEW using iSeries Navigator ? I thought there was but I can't seem to track it down. I created a view and I can see the object in the SCHEMA panel but nothing seems to say "create a view" with this option. Or do I have to create and run an SQL script ?

  • #2
    ACS and Client Access provide Wizards for generating a view. Position on VIEW, Right click and NEW.

    BTW you should replace the Client Access (which includes the iSeries Navigator) with ACS (Access Client Solutions).
    Client Access is outdated and officially not supported by any IBM i Release after 7.1 and WINDOWS 10 or higher.
    Until the last TR Client Access was still updated, but NO longer!

    Birgitta

    Comment


    • #3
      Thanks for the response;

      Originally posted by B.Hauser View Post
      ACS and Client Access provide Wizards for generating a view. Position on VIEW, Right click and NEW.
      There must be a browser problem as the "right click" pops up options such as "open link in new tab".

      But in any event, in the "Views" pain (< see what I did there) there is a drop down "Actions" button which provides the "New" view creation function.

      How do I go about amending the view. For example, how do I add or remove a column to my view. There doesn't seem to be a way to edit the SQL statements in the view. Surely there is an edit function ?

      Also, is there any advantage or point to saving the definition to a source physical file ?

      Originally posted by B.Hauser View Post
      BTW you should replace the Client Access (which includes the iSeries Navigator) with ACS (Access Client Solutions).
      Client Access is outdated and officially not supported by any IBM i Release after 7.1 and WINDOWS 10 or higher.
      Until the last TR Client Access was still updated, but NO longer!Birgitta
      I am aware of this but I still have both applications on my PC and I will sometimes use Ops Nav because it is faster. But for this View experiment I am using ACS.

      Comment


      • #4
        "here must be a browser problem as the "right click" pops up options such as "open link in new tab"."
        I don't think you're in the right app. That sounds like a browser.

        i Access Client Solutions (iACS) is the new client to replace the obsolete (and unsupported) i Access for Windows (fka Client Access). In iACS, you open Database: Schemas. For DB work, most of the functions match the obsolete iNav app.

        Comment


        • #5
          Originally posted by jtaylor___ View Post
          "here must be a browser problem as the "right click" pops up options such as "open link in new tab"."
          I don't think you're in the right app. That sounds like a browser.
          It is a browser because when I click on "Navigator for i" within iACS it opens a browser, Firefox which is my default browser. URL states "IP address of my iSeries:2004/ibm/console/login" and I see all the familiar tasks/functions including database.

          But in any event, is there a way to edit the VIEW after I created it ? If I want to add field for example, how would I do that ?



          i Access Client Solutions (iACS) is the new client to replace the obsolete (and unsupported) i Access for Windows (fka Client Access).
          Yup, I've got that


          Comment


          • #6
            From the IBM i Access Client Solutions app, click Schemas. In the Schemas window, expand your system, then Schemas and your schema. Right-click on Views->New->View.

            To edit views, I use the Generate SQL with the "OR REPLACE clause" option.

            Comment


            • #7
              Originally posted by JimKerr View Post
              Yup, I've got that
              Awesome. Navigator for i will get you close but...it's better to use ACS. Navigator for i does not include an SQL client. You will be able to generate the SQL statement, but won't be able to make changes to it or run the SQL statement. ACS has an SQL client (which is very nice!!) and will be able to do what you need to do.

              I'm on v7r3 tr6, ACS 1.1.8.2

              Changing a View
              - Launch ACS
              - Select system from the drop down
              - Select Schemas from within the Database pane
              - In Schemas, expand the explorer tree, selecting the schema (library) where the view resides
              - Click Views in the explorer pane and the list of views within that schema should be displayed in the right pane
              - Right click the view you'd like to change, then click Generate SQL...
              - Verify that Open in Run SQL Scripts radio button is selected
              - Click the Options tab and check OR REPLACE CLAUSE box
              - Click Generate button and Run SQL Scripts is launched with the view's SQL statements
              - Modify as required
              - Position cursor on SQL statement to run, and press [Ctrl] + r

              Creating a View
              - Launch ACS
              - Select system from the drop down
              - Select Run SQL Scripts from within the Database pane
              - Select EDIT menu, then Insert from Examples...
              - Select Create or Replace View
              - Click Insert button
              - Modify as required
              - Position cursor on SQL statement to run, and press [Ctrl] + r


              Integration note: if you have a 5250 session running, instead of launching the ACS main app, use the Action menu within the 5250 session. Any module launched from the Action menu will automatically connect to the same system that the active 5250 session is connected to. If you're in a Run SQL Scripts session, the corresponding menu is called the Tools menu.

              Comment


              • #8
                Originally posted by Herb... View Post

                Awesome. Navigator for i will get you close but...it's better to use ACS. Navigator for i does not include an SQL client. You will be able to generate the SQL statement, but won't be able to make changes to it or run the SQL statement. ACS has an SQL client (which is very nice!!) and will be able to do what you need to do.

                I'm on v7r3 tr6, ACS 1.1.8.2

                Changing a View
                - Launch ACS
                - Select system from the drop down
                - Select Schemas from within the Database pane
                - In Schemas, expand the explorer tree, selecting the schema (library) where the view resides
                - Click Views in the explorer pane and the list of views within that schema should be displayed in the right pane
                - Right click the view you'd like to change, then click Generate SQL...
                - Verify that Open in Run SQL Scripts radio button is selected
                - Click the Options tab and check OR REPLACE CLAUSE box
                - Click Generate button and Run SQL Scripts is launched with the view's SQL statements
                - Modify as required
                - Position cursor on SQL statement to run, and press [Ctrl] + r

                Creating a View
                - Launch ACS
                - Select system from the drop down
                - Select Run SQL Scripts from within the Database pane
                - Select EDIT menu, then Insert from Examples...
                - Select Create or Replace View
                - Click Insert button
                - Modify as required
                - Position cursor on SQL statement to run, and press [Ctrl] + r


                Integration note: if you have a 5250 session running, instead of launching the ACS main app, use the Action menu within the 5250 session. Any module launched from the Action menu will automatically connect to the same system that the active 5250 session is connected to. If you're in a Run SQL Scripts session, the corresponding menu is called the Tools menu.
                Thanks Herb, I am having some success with this now. There is a bit of a learning curve as I get to grips with this.

                Comment


                • #9
                  Another day another battle;

                  I am trying to amend a view using the instructions Herb provided. When I perform the "Generate SQL" I am not getting the "Run SQL scripts" window that allows me to edit the SQL statements. I just get a greyed out window of the SQL statements for the view definition.

                  I suspect it is because I don't have the "- Verify that Open in Run SQL Scripts radio button is selected" but I can't see the "radio button" anywhere.

                  Is this they way it is now, battling the software for hours just to add a column/field to a view ?!

                  (In Ops Navigator, the radio button "open in Run SQL Scripts" is visible)
                  Last edited by JimKerr; November 14, 2019, 12:56 PM.

                  Comment


                  • #10
                    So, after much hair pulling, gnashing of teeth and tears, I stumbled on a weird "thing". In ACS, I cannot find a radio button to check so that the Generate SQL function opens the view definition in an SQL scripts window.

                    However, when I initiate "Navigator for i" from ACS, an application starts called "Navigator Requests". This application doesn't show much other than "Connection Information" which shows the system it is connected to and the status "Listening for requests". I had no idea what this application was doing but I had closed it earlier. But it seems this application must be running/open in order for the Generate SQL function to open the SQL Scripts window.

                    Comment


                    • Herb...
                      Herb... commented
                      Editing a comment
                      Ouch!! Sounds frustrating!! What version of ACS are you using (I'm using 1.1.8.2)? IBM has focused on enhancing the SQL/Schema functionality in several of the latest releases.

                  • #11
                    Yes, the saga continues. I was running V1.1.6 and upgraded to v1.1.8.2 and now, when I perform the "Generate SQL" (with option REPLACE) function the SQL scripts window does start, every time. Plus that little "connection" window is no longer invoked. But there is still no sign of the "open in Run SQL Scripts" radio button.

                    Comment


                    • #12
                      It sounds like you're making good progress. Upgrading to the newest release makes several VERY HELPFUL features available to you.

                      The "Open in Run SQL Scripts" radio button is on the 1st Tab of the Generate SQL dialog box. Before you click the Options Tab for the "OR REPLACE CLAUSE" check box is, look at the options on the Output Tab (it's the first/default Tab, please see the attached image).

                      Comment


                      • #13
                        Originally posted by Herb... View Post
                        It sounds like you're making good progress. Upgrading to the newest release makes several VERY HELPFUL features available to you.

                        The "Open in Run SQL Scripts" radio button is on the 1st Tab of the Generate SQL dialog box. Before you click the Options Tab for "OR REPLACE CLAUSE" check box is, look at the options on the Output Tab (it's the first/default Tab, please see the attached image).
                        Thanks Herb.

                        I have figured out what the "problem" is.

                        When I was accessing the schemas I was not using the ACS application. My config/install of ACS has three sections; "General", "Database" and "Management". The "General" section lists available tasks such as "Data Transfer", "5250 Emulator", "Integrated File System", "Navigator for i". I was selecting the "Navigator for i" task which runs in a browser and from there I can drill down to the Database/schema/views and work on the views there through my browser.

                        However, what I should have been using was the ACS "Database" section which runs as its own application, i.e. not in a browser. I guess I wasn't paying attention earlier or became confused when "Navigator for i" was brought up.

                        But thanks again.

                        Comment

                        Working...
                        X