Topic
14 replies Latest Post - ‏2013-04-17T15:58:57Z by Arthur_Adams
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Process SQL file from command prompt

‏2013-04-02T10:07:54Z |
hi all,
im using IBM.Data.DB2.iSeries version 7.1 on windows 2008(64 bit) server.
what i want to do is, i have an sql file with some DDL and DML statements. i want to process the sql file using command prompt commands, here my doubts are
1. what command is used to execute the sql file?
2. how do i open the command prompt bcos, in my machine i dont see the command probmpt under i-Series Navigator.(for screenshot please open the attached file.
3. if possible to execute the sql file without commandprompt then please let me know that also.
Updated on 2013-04-05T06:44:14Z at 2013-04-05T06:44:14Z by SystemAdmin
  • B.Hauser
    B.Hauser
    244 Posts
    ACCEPTED ANSWER

    Re: Process SQL file from command prompt

    ‏2013-04-03T05:24:36Z  in response to SystemAdmin
    SQL Scripts located in either the IFS or in a Source Physical File Member can be executed by using the RUNSQLSTM CL Command.

    Birgitta
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: Process SQL file from command prompt

      ‏2013-04-03T05:26:39Z  in response to B.Hauser
      thanks for your response.
      here my main problem is, i dont know how to open CLP because, i dont feel that its not installed in my machine. please open the attached file.
      do we have to install anything specially for CLP? then please let me know.
      • B.Hauser
        B.Hauser
        244 Posts
        ACCEPTED ANSWER

        Re: Process SQL file from command prompt

        ‏2013-04-03T05:36:53Z  in response to SystemAdmin
        CL is available on each IBM i System, since it is the basic/control language.
        CL Commands can be executed from any interface that can connect the IBM i and allows to execute SQL commands by calling the QCMDEXC program/stored procedure.

        Birgitta
        • SystemAdmin
          SystemAdmin
          3129 Posts
          ACCEPTED ANSWER

          Re: Process SQL file from command prompt

          ‏2013-04-03T06:21:34Z  in response to B.Hauser
          hi, thanks for your responses.
          here with i have attached my start menu so, please tell me which one i have to select for CLP.
          • SystemAdmin
            SystemAdmin
            3129 Posts
            ACCEPTED ANSWER

            Re: Process SQL file from command prompt

            ‏2013-04-03T11:04:20Z  in response to SystemAdmin
            You need to go into Emulator and then select 'Start or Configure Session'. From there select a .ws file (if you've never done this before there will probably be only one, using the IBM i system name as its name), which you will need to configure. Make sure you set the size to 27x132. You should now be presented with a log on screen from which you can enter your user id and password. Don't worry about the other 3 fields until you know more about the IBM i.

            You should now see the IBM i Main Menu with a command line at the bottom from where you can enter commands or menu options. Any command you enter can be prompted by using F4 instead of the Enter key. All IBM commands have help text available - just press F1 on any of the parameters to see help for that parameter or move your cursor up to the top line and then press F1 to see the help for the whole of the command.

            To run some SQL, key in STRSQL (Start SQL) and then key in your SQL statements. If the SQL is contained in a source or stream file, key in RUNSQLSTM (Run SQL Statements) and press F4.

            Another way to run SQL is to start System i Navigator from the Start Menu. Navigate down to Databases and then your system name. The bottom right panel will now contain an option to 'Run an SQL script'. Double click this and a window will open that will already have set up a connection to your IBM i's database.

            Hope that helps.
            • SystemAdmin
              SystemAdmin
              3129 Posts
              ACCEPTED ANSWER

              Re: Process SQL file from command prompt

              ‏2013-04-03T11:59:53Z  in response to SystemAdmin
              thank you Adams,
              i opened it.
              now my next doubt is "How do i execute this command db2 -tvf myfile.sql because, i have an sql file with some insert/update scripts, i want to execute those in a single shot, but if i type this command then im getting error as Token TVF was not valid. Valid tokens: ( CL END GET SET CALL DROP FR
              db2 -tvf SQL1.SQL may i know how to execute this command"
              Thanks
              Winseelan
              • SystemAdmin
                SystemAdmin
                3129 Posts
                ACCEPTED ANSWER

                Re: Process SQL file from command prompt

                ‏2013-04-03T12:14:57Z  in response to SystemAdmin
                You don't say how you are attempting to run your SQL.

                If you are using RUNSQLSTM it would be something like:

                RUNSQLSTM SRCFILE(<library>/<source file>) SRCMBR(myfile)

                or

                RUNSQLSTM SRCSTMF('<path>/myfile.sql') (nb the single quote marks are important)
                If you are using 'Run an SQL Script', do File > Open and navigate to myfile.sql which will display the SQL source in the top pane. Click Run.

                However, before you try running myfile.sql I would suggest you try a simple 'Select * from .....' to prove that you are able to run some SQL succesfully.
                • SystemAdmin
                  SystemAdmin
                  3129 Posts
                  ACCEPTED ANSWER

                  Re: Process SQL file from command prompt

                  ‏2013-04-04T03:22:06Z  in response to SystemAdmin
                  Adams,
                  thanks for your help.

                  i ran this command RUNSQLSTM SRCSTMF('U:\1.sql') then im getting exception like this RUNSQLSTM command failed
                  next i tried this command RUNSQLSTM SRCSTMF(U:\1.sql) them im getting exception like this * String '\1.SQL) ' contains a character that is not valid.*

                  im totally new to this commands section so, can u help me please.

                  Thanks
                  Winseelan
                  • SystemAdmin
                    SystemAdmin
                    3129 Posts
                    ACCEPTED ANSWER

                    Re: Process SQL file from command prompt

                    ‏2013-04-04T07:29:58Z  in response to SystemAdmin
                    The first method is the more correct, the second fails because there are no single quotes.

                    Where exactly is the U drive? Don't forget, to the IBM i, U: probably means nothing.

                    (My name is Arthur, by the way)
                    • SystemAdmin
                      SystemAdmin
                      3129 Posts
                      ACCEPTED ANSWER

                      Re: Process SQL file from command prompt

                      ‏2013-04-04T08:29:08Z  in response to SystemAdmin
                      Another feature of IBM i is that every message comes with (usually) extensive additional help.

                      Move your cursor down to the message and press F1. Additional help text will then be displayed.
                      • ldubois
                        ldubois
                        122 Posts
                        ACCEPTED ANSWER

                        Re: Process SQL file from command prompt

                        ‏2013-04-04T14:05:45Z  in response to SystemAdmin
                        Mr/Ms winseelan: Have you tried the "Run SQL Scripts" feature of System i Navigator? To do this, bring up IBM i Access for Windows --> System i Navigator. Find your system in the list (or configure it), then expand the system, select Databases, then select your database, then "Run an SQL script." See this page for more information:
                        http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzatc/rzatcrunscript.htm

                        I've included a screen shot showing an example of how to navigate into the tool (in this example, the name of the database and system is xxxxxxx).

                        Once you have started the "Run an SQL script" tool, you can use File --> Open to open your .sql file.
                    • SystemAdmin
                      SystemAdmin
                      3129 Posts
                      ACCEPTED ANSWER

                      Re: Process SQL file from command prompt

                      ‏2013-04-05T03:11:39Z  in response to SystemAdmin
                      Arthur thanks for your response.
                      U: is the hard drive name, i put the sql file on U drive, file name as 1.sql so, i gave U:\1.sql
                      but, no luck. im new to this screen so, i dont know how to proceed.

                      Thanks
                      Winseelan
                      • SystemAdmin
                        SystemAdmin
                        3129 Posts
                        ACCEPTED ANSWER

                        Re: Process SQL file from command prompt

                        ‏2013-04-05T06:44:14Z  in response to SystemAdmin
                        I guessed that U: is a hard drive somewhere, but where exactly is it? Is it on your local machine or is it on a server somewhere? I know it's not on the IBM i because the concept of drive letters is completely alien to the IBM i. What I am really asking is, is U: accessible from the IBM i?

                        With IBM i, there is more than one file system. The IFS (installable file system) is the PC/server style system. WRKLNK is the command that will let you access the IFS, so, from a 5250 emulator screen, type WRKLNK and tell me what you see.
                        • Arthur_Adams
                          Arthur_Adams
                          8 Posts
                          ACCEPTED ANSWER

                          Re: Process SQL file from command prompt

                          ‏2013-04-17T15:58:57Z  in response to SystemAdmin

                          Winseelan

                          Did you manage to get your SQL working or did you give up?

                           

                          [PS Note to forum owner: Why are mine and winseelan's previous posts attributed to sbhupa?]

                          Updated on 2013-04-17T15:59:38Z at 2013-04-17T15:59:38Z by Arthur_Adams