SQL Scripts

Use the f option to execute SQL scripts (SQL commands stored in a file). Here are some things to keep in mind when creating SQL scripts:

If the file is a database file member in the QSYS.LIB file system, you must specify the member name in IFS format.
You may store more than one SQL statement in the file.
You can enter only one statement per record.
You must enter a complete statement in a single record unless you specify a delimiter using the d , t , or T options. If you specify a delimiter, a single statement may span multiple records.
Your SQL command cannot contain references to Qshell variables .
You may use the double-hyphen syntax to include comments in the file.

Several of these points are illustrated in Figure 20.21. The script file is CustListNbr01.sql, and the first two lines contain comments. The SQL command itself spans four lines. This is allowable because of the semicolon delimiter in the last line.


cat CustListNbr01.sql


-- to run: db2 -tvf CustListNbr01.sql <LIBRARY>


-- library name must be specified in -f option


select cstate, custname,company,custnbr


from customer


where company=3


order by 1,2;


/home/JSMITH $


db2 -tvf CustListNbr01.sql jsmiths


EXECUTING: select cstate, custname,company,custnbr from customer where


company=3 order by 1,2


DONE!


CSTATE  CUSTNAME              COMPANY   CUSTNBR


------- --------------------  --------  --------


MS       Ames Wholesale          3        1


OH       Army Surplus            3        3


OH       Jak's Liver Emporium    3       56


3 RECORD(S) SELECTED.


/home/JSMITH $

Figure 20.21: The db2 utility can execute SQL script files.

Figure 20.22 provides an example that uses Qshell I/O and the db2 utility. The file datain.csv contains comma-separated data received via some sort of EDI mechanism. The edi210.qsh Qshell script reads the CSV data and inserts new rows into the database.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}


cat datain.csv


2260,A192287,A-195,4


2260,A192287,C-267,12


2260,A192287,B-332,101


2260,A192287,N-271,5


2260,A192287,N-105,35


2260,A192287,D-328,17


/home/JSMITH $


cat edi210.qsh


IFS=','


while read custnbr ponbr itemnbr qty


do


db2 "insert into jsmiths.ediin values ($custnbr, '$ponbr', '$itemnbr',


$qty)"


done <datain.csv


/home/JSMITH $


edi210.qsh


DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.


DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.


DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.


DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.


DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.


DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.


/home/JSMITH $


db2 "select * from jsmiths.ediin"


CUST    PONBR     ITEM    QTY


------- --------  ------ ----


2260    A192287   A-195     4


2260    A192287   C-267    12


2260    A192287   B-332   101


2260    A192287   N-271     5


2260    A192287   N-105    35


2260    A192287   D-328    17


6 RECORD(S) SELECTED.


/home/JSMITH $

Figure 20.22: The db2 utility can be used to update a database table.

DB2 in Interactive Mode

The i option allows you to run db2 interactively. This differs from repeatedly running the db2 command in the following ways:

The db2 utility is loaded once and remains active between command executions.
Execution of SQL statements is similar to the way SQL scripts are executed, and is limited in the same ways. For example, interactive SQL statements cannot reference Qshell variables .
You can use transactions consisting of multiple SQL statements, all of which can be committed or rolled back in their entirety.
You can use db2 as if it were a filter, accepting a stream of commands and outputting the results as appropriate.

When you enter interactive mode, db2 responds with the DB2 > prompt. The continuation prompt, for incomplete SQL commands, is ?> .

When you run db2 in interactive mode, you are communicating directly with the db2 utility, not with Qshell. Therefore you must use db2 subcommands, rather than Qshell commands. The first db2 subcommand you should learn is help , which may be entered as the complete word "help," or as a single question mark. As Figure 20.23 shows, the db2 utility responds with a "crib sheet."


/home/JSMITH $


db2 -i

DB2>

help


Enter any of the following built-in commands:


help or ?        Show this brief help


-- anything      Comment line


exit or quit     End the DB2 session


echo             Echo the text listed


connect          Currently ignored


terminate        Currently ignored


!qshcommand      Run a QSH command or utility


@clcommand       Run a CL command


OTHER            Run the statement as SQL, reporting results


Current options specified are:


Connected with default user to *LOCAL database


Processing interactive statements


Statements delimited by newline


Verbose echo of statements is OFF


Misc


- If using delimited SQL (-t, -d or -T), end lines with \ to avoid


inserting a space (for example, in SQL literals)


- If using a user specified SQL delimiter character (-T or -d


option), be careful that the delimiter (if its the first character


on a line) doesn't match the special built-in command modifiers


(comment, CL or QSH command)


DB2>


exit


/home/JSMITH $

Figure 20.23: The db2 utility includes brief, online help text in interactive mode.

Figure 20.24 shows a more extensive db2 session. Here, the user queries data and inserts a row into a table. Notice that the db2 command includes the t switch. Without it, db2 would try to execute an SQL command every time the Enter key was pressed. Thanks to the t , the semicolon delimits the commands, allowing the operator to continue commands across multiple lines of input.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}


db2 -it


DB2>


select cstate,custname,company,custnbr


?>


from jsmiths.customer


?>


order by 1,2;


CSTATE  CUSTNAME             COMPANY  CUSTNBR


------- -------------------- -------- --------


Sal Monella            8          3


CA       Donaldson Electric     2          3


CA       Robert R. Roberts IV   8          2


CA       Sardine Paradise       7        222


FL       48% of Nothing         8          1


GA       Joe's Shoes            1         44


MS       Ames Wholesale         3          1


MS       Sue's Bridle Shop      1      10001


ND       Grayson Paul           4      40004


NJ       Gretta's Gifts         2        345


NY       Pretty Boy's Gym       7        777


OH       Army Surplus           3          3


OH       Jak's Liver Emporium   3         56


OH       Snowman Heat & Air     1      30003


OK       Xolomon Solutions      2          1


PA       Bank of Steele         1      20002


16 RECORD(S) SELECTED.


DB2>


insert into jsmiths.customer


?>


(cstate,custname,company,custnbr)


?>


values ('TX','Ben Dover',2,44);


DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY.


DB2>


select company,custnbr,custname,state


?>


from jsmiths.customer where company=2;


**** CLI ERROR *****


SQLSTATE: 42703


NATIVE ERROR CODE: -206


Column STATE not in specified tables.


DB2>


select company,custnbr,custname,cstate


?>


from jsmiths.customer where company=2;


COMPANY  CUSTNBR  CUSTNAME             CSTATE


-------- -------- -------------------- -------


2        1     Xolomon Solutions    OK


2        3     Donaldson Electric   CA


2       44     Ben Dover            TX


2      345     Gretta's Gifts       NJ


4 RECORD(S) SELECTED.


DB2>


quit


/home/JSMITH $