Ibm Db2

IBM DB2 A “schema” in DB2 refers to a qualifying name used to group objects together within a database.All tables have a fully qualified name consisting of its schema name and the table name. If the schema we choose is, say, “Finance” and the tables we create are called Employee and Department, then the fully qualified name for each table is “Finance.Employee” and “Finance.Department”. Schemas allow us to create 2 tables with the same name in the same database. Setting Up DB2 IBM DB2 Setup-Tools  Configuration Assistant 1. Select Communications Protocal  TCP/IP 2. Specifiy Communication parameters 167.6.207.174, Port 50000 3. Database Name/ Database Alias  Db2T Command Line DB2 DB2 has a command window (db2cmd) which is similar to an MS DOS window. To start the DB2 command window choose IBM DB2­>Command Window from the Start menu (there may be another level of menu before the Command Window). You should see the MS-DOS-like window appear. To see what databases are available for your use type: db2 list database directory

This should return a listing of databases that looks something like this (your list will be much longer): Note that all commands that you type in the DB2 Command window begin with the keyword "db2", thus sending the command to the db2 process for interpretation. If you wish, you can enter the command line interpretor by typing db2 . This provides you with a prompt at which you can type commands on the command line without prefacing them with "db2" Command Editor Use the Command Editor to generate, edit, execute, and manipulate SQL statements, IMS commands, and DB2 commands; to work with the resulting output; and to view a graphical representation of the access plan for explained SQL statements. To run commands and SQL statements:

1. To open an embedded Command Editor, expand the Control Center object tree until you find a DB2 database. Right-click the object and select Query from the pop-up menu. A Command Editor tile opens in the Control Center. 2. To open a stand-alone Command Editor from the Start menu, select Programs -> IBM DB2 --> [DB2 copy name] --> Command Line Tools --> Command Editor. 3. Select a target database to connect to. Targets that currently have connections or have had connections during the current session are listed in the Target drop-

down list. If your target is not listed, click Add and select a target from those available on the Specify Target window. You can also type the connect to database statement. 4. In the top pane, type the commands or SQL statements that you want to execute. 5. You can select commands and statements previously executed in this session from the History window. Click to open the History window. 6. You can execute commands and statements stored in a file. Click to locate the file using the file browser Open window. 7. You can specify the character to be used as the termination character for your statements and commands in the Statement termination character field. This termination character will override the character that you may have specified in the Tool Settings notebook. 8. For scripts containing CREATE PROCEDURE or CREATE TRIGGER statements, it is suggested that a termination character other than a semicolon be used, as the body of these statements might themselves contain one or more semicolons. 9. When stored procedures are using the same character as a delimiter, as this would cause execution problems. 10. Click to execute the commands or statements. 11. 12. View the execution results. Command results are displayed in the output area of the Commands page, while SQL results are returned on the Query Results page 13. Optional: The results from the most recently executed SQL statement are displayed on the Query Results page. To change the value in any cell, highlight the value and modify it. If the Automatically commit updates check box is selected, changes made to a cell are committed immediately when focus shifts off its row. If the check box is not selected, then click Commit to commit your changes, or click Rollback to rollback any uncommitted changes. 14. You can also add, modify, and delete rows in the table. 15. Optional: You can save your commands and statements in a file. Click specify a file using the file browser Save As window.

to

SQL 1. A comment in a SQL statement starts with two dashes and goes to the end of the line: 2. DB2 SQL does not come with a designated statement delimiter (terminator), though a semicolon is often used.

Table A table is an organized set of columns and rows. The number, type, and relative position, of the various columns in the table is recorded in the DB2 catalogue. View A view is another way to look at the data in one or more tables (or other views). For example,a user of the following view will only see those rows (and certain columns) in the EMPLOYEE table where the salary of a particular employee is greater than or equal to the average salary for their particular department. CREATE VIEW employee_view AS

SELECT a.empno, a.firstnme, a.salary, a.workdept FROM employee a WHERE a.salary >= (SELECT AVG(b.salary) FROM employee b WHERE a.workdept = b.workdept); DB2 Data Types DB2 comes with the following standard data types: • SMALLINT, INT, and BIGINT (i.e. integer numbers). • FLOAT, REAL, and DOUBLE (i.e. floating point numbers). • DECIMAL and NUMERIC (i.e. decimal numbers). • CHAR, VARCHAR, and LONG VARCHAR (i.e. character values). • GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC (i.e. graphical values). • BLOB, CLOB, and DBCLOB (i.e. binary and character long object values). • DATE, TIME, and TIMESTAMP (i.e. date/time values). • DATALINK (i.e. link to external object). • XML (i.e. contains well formed XML data). DB2 Special Registers

A special register is a DB2 variable that contains information about the state of the system. Eg:- CURRENT DATE, CURRENT TIME, CURRENT USER To see all schemas  http://testsams.navistar.com/db2info/db2info.asp

FETCH FIRST Clause

The fetch first clause limits the cursor to retrieving "n" rows. FETCH FIRST 3 ROWS ONLY;

CAST Expression

The CAST is expression is used to convert one data type to another CASE Expression

CASE expressions enable one to do if-then-else type processing inside of SQL statements. SELECT Lastname ANSWER ,sex AS sx ==================== ,CASE sex LASTNAME SX WHEN 'F' THEN 'FEMALE' WHEN 'M' THEN 'MALE' ELSE NULL END AS sexx FROM employee WHERE lastname LIKE 'J%' ORDER BY 1;