The shell scripts make sure the db connection is set up and the sql script does the actual work. However in most cases 1 is ok and sometimes I am fine with 2 as well.Īnother pattern that I started to like is to have shell and sql scripts in conjunction. Everything other than 0 might be suspicious. I am looking at the error level that gets returned by db2. I went with logfile parsing in the past but this is too cumbersome for simple check whether my script was successful or not. Just my personal preference is to check for errors. If you’ll be executing a script against multiple databases, would you prefer to have to edit the file to include the proper connection information for each one, or would you prefer to just connect first? Having the database name in the file can be useful for the output to verify later that you connected to the right database when executing the script. The choice between the two depends on your personal preferences and environment. The other choice is to incorporate the connect into the script like this:Īnd simply execute the full script: $ db2 -tvmf filename.sql |tee There are things that may either be a part of the SQL script you’re executing, or done outside of the SQL script prior to executing the script.įor example you could have this SQL script:Īnd first connect to a database or issue SET CURRENT SCHEMA or other commands, followed by executing the script: There are plenty of other interesting command line options available in the DB2 Knowledge Center: Things that may Happen Inside or Outside of a Script f – file – indicates that db2 should execute statements from a file, with the filename specified one space after the f.m – prints the number of lines affected by DML.This is extremely useful when reviewing output or troubleshooting failed statements v – verbose – the statement will be echoed in output prior to the result of the statement.d – delimiter – the default delimiter is being overriden, and db2 uses the character immediately following d as the delimiter.t – terminated – the statements are terminated with a delimiter.Here is the breakdown of these options I use most frequently: ![]() There are several command line options, I used above. In the case of an alternate delimiter, the file might look like this:Ĭonnect to table index into file would be executed using:ĭb2 -vmf filename.sql |tee This is required when working with certain triggers and stored procedures. Since the semicolon is the default delimiter, you could execute the above file using:ĭb2 -tvmf filename.sql |tee įinally, you might have a file that uses an alternate delimiter. The semicolon is the delimiter in this example. Note also that each line terminates in a semicolon( ). Note the lack of the “db2” at the beginning of each line. Shell scripts can be executed at the UNIX or Linux command line simply like this:Īlways send the output somewhere so you can review it later if needed.Ī file of pure SQL should look more like: ksh to indicate they are shell scripts specifically. The quotes around the statements may be optional in some scenarios. Notice the “db2” before each statement, and the quotes around the statements. If they are shell scripts, they will look something like this: Scripts of SQL commands may show up as shell scripts, actually. Some of those extensions are not specifically related to executing a script of SQL, but it is good information to have. ![]() You may need to stick to or a avoid certain file extensions for Windows.Ī list of file extensions frequently used with DB2 databases: Extensionĭata Definition Language – such as CREATE TABLE, ALTER TABLE, and CREATE INDEXĭata Manipulation language – such as INSERT, UPDATE, and DELETEĭelimited data, often CSV, but may use other delimitersĭB2’s Integrated Exchange Format – includes both data and information on table structure Windows is more problematic in my experience, as are MS applications like Outlook. dml acceptable for data definition language and data manipulation language when a file is specific to only one of those. File Namingįor Linux and UNIX systems the file name does not matter a bit. Sometimes we just have a large number of commands that need to be done as a whole. ![]() Sometimes developers provide such a script to be executed. There are quite a few scenarios in which DBAs need to execute a script of SQL.
0 Comments
Leave a Reply. |