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

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
    255 Posts

    Re: Process SQL file from command prompt

    ‏2013-04-03T05:24:36Z  
    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

    Re: Process SQL file from command prompt

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

    Birgitta
    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
    255 Posts

    Re: Process SQL file from command prompt

    ‏2013-04-03T05:36:53Z  
    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.
    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

    Re: Process SQL file from command prompt

    ‏2013-04-03T06:21:34Z  
    • B.Hauser
    • ‏2013-04-03T05:36:53Z
    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
    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

    Re: Process SQL file from command prompt

    ‏2013-04-03T11:04:20Z  
    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.
    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

    Re: Process SQL file from command prompt

    ‏2013-04-03T11:59:53Z  
    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.
    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

    Re: Process SQL file from command prompt

    ‏2013-04-03T12:14:57Z  
    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
    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

    Re: Process SQL file from command prompt

    ‏2013-04-04T03:22:06Z  
    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.
    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

    Re: Process SQL file from command prompt

    ‏2013-04-04T07:29:58Z  
    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
    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

    Re: Process SQL file from command prompt

    ‏2013-04-04T08:29:08Z  
    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)
    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

    Re: Process SQL file from command prompt

    ‏2013-04-04T14:05:45Z  
    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.
    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

    Re: Process SQL file from command prompt

    ‏2013-04-05T03:11:39Z  
    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)
    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

    Re: Process SQL file from command prompt

    ‏2013-04-05T06:44:14Z  
    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
    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
    10 Posts

    Re: Process SQL file from command prompt

    ‏2013-04-17T15:58:57Z  
    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.

    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