Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how to execute the sql script on the DB2 command line. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

How to execute sql scripts on the DB2 command line

Db2 = > connect to dbName user xxx using password

Db2 = > sql statement

To execute a sql script file:

Db2 = > quit

C:\ > db2-tvf sql file name

Db2-td@-f filename

@ is the statement Terminator.

E:\ > db2? Options

Db2 [option...] [db2-command | sql-statement |

[? [phrase | message | sqlstate | class-code]

Option:-a,-c,-e {c | s},-finfile,-lhistfile,-n,-o,-p,-rreport,-s,-t,

-td;,-v,-w,-x and-zoutputfile.

Option describes the default settings

-a display SQLCA OFF

-c automatically implement ON

-e display SQLCODE/SQLSTATE OFF

-f read the input file OFF

-l record commands in a history file OFF

-n remove the newline character OFF

-o display output ON

-p displays the db2 interactive prompt ON

-r Save the output report to the file OFF

-s stops executing OFF when a command goes wrong

-t sets the statement termination character OFF

-v send back the current command OFF

-w displays FETCH/SELECT warning message ON

-x do not print column headings OFF

-z Save all output to the output file OFF

Note:

Use the DB2OPTIONS environment variable to customize the default value of the option.

The minus sign (-) immediately after the option letter turns the option off.

Use UPDATE COMMAND OPTIONS to change option settings (to interact or

File input method).

You can only provide examples of scripting in nt for your reference:

Sample script:

Db2 connect to yourdb user yourname using yourpassword

Db2 insert into newuser (username,password,email) values ('Amy','1234','Amy@ss.com')

Db2 insert into newuser (username,password,email) values ('Judy','1234','Judy@ss.com')

Db2 commit

Db2 disconnect yourdb

Run script: run db2cmd X:\ XXX.bat

The following FAQ excerpts from this forum can be referenced:

"

Run the DB2 script in the command window, using the file name of the db2-svtf script.

For example, the script file is named sample.sql, and run: db2-svtf sample.sql

Parameters:

S means to stop running the script when you encounter an error

V stands for outputting the result to the screen

T refers to the delimiter of each line with the; sign

F means to be followed by the script file name "- the copyright of this excerpt belongs to Banzhu, not to me."

It's a pity to see how AS400 writes scripts.

Db2-x select SERIALNO from tabname where clause

C: > db2 attach to db2164 user ccp

Enter the current password for ccp:

Instance connection information

Instance server = DB2/NT 8.2.0

Authorization ID = CCP

Local instance alias = DB2164

C: > db2 connect to dw164 user ccp

Enter the current password for ccp:

Database connection information

Database server = DB2/NT 8.2.0

SQL authorization ID = CCP

Local database alias = DW164

C: > db2 select * from CCP_STS1 fetch first 2 rows only with ur

CUST_ID NOW_PRED_S LOAD_TIME

3094736. ZFS 2008-05-07-10.02.00.453000

3145886. ZFS 2008-05-07-10.02.00.453000

2 records have been selected.

C: > db2 list command options

Command line processor option settin

Backend process wait time (seconds) (DB2BQTIME) = 1

Number of retries connected to the backend (DB2BQTRY) = 60

Request queue wait time (seconds) (DB2RQTIME) = 5

Enter queue wait time (seconds) (DB2IQTIME) = 5

Command options (DB2OPTIONS) = + m

Option describes the current setting

-a display SQLCA OFF

-c automatically implement ON

-d retrieves and displays the XML declaration OFF

-e display SQLCODE/SQLSTATE OFF

-f read the input file OFF

-I display XML data with indented OFF

-l record commands to a history file OFF

-m displays the number of affected rows OFF

-n remove the newline character OFF

-o display output ON

-p displays the interactive input prompt ON

-Q preserves spaces and newline character OFF

-r Save the output to the report file OFF

-s stops executing OFF when a command goes wrong

-t sets the statement termination character OFF

-v return the current command OFF

-w displays FETCH/SELECT warning message ON

-x do not print column headings OFF

-z Save all output to the output file OFF

C: > db2set DB2OPTIONS=-x

C: > db2 select * from CCP_STS1 fetch first 2 rows only with ur

4654908. ZFS 2008-05-07-10.02.00.453000

3716687. ZFS 2008-05-07-10.02.00.453000

This is how to execute the sql script in the DB2 command line shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.