The informix guide will continuously be updated throughout the quarter. Relevant information that you require for different parts for your PDA assignments will be put into it periodically. A complete set of manuals from which we have derived information in the Informix Introductory Guide can be found in http://www.ics.uci.edu/~informix. Please be aware that the manuals are huge and describe all the different parts of informix, many of which we will not use. As you make progress, you may discover that the introductory guide we are creating contain all the information you need for a specific part of your project. You are advised to refer to the original manuals to get the information. We will also appreciate if you let us know the missing information and we will strive to help you find the required information and include it in the guide. This will help future ICS184 classes.
You can login Informix from any workstation (solaris) in ICS lab, using your UNIX account and password.
username@rodan% module load informix
username@rodan% dbaccess
Then the Informix DBACCESS screen will pop up.
DBACCESS: Query-language Connection Database Table Session Exit
Use SQL query language.
------------------------------------------------ Press CTRL-W for Help --------
DBAccess has multiple levels of menus.
The toppest menu (main menu) is shown above.
You can either use the cursor to move around the menu and select a command,
or use the capital to make the selection.
A brief explanation will show up below the manu. In addition,
you can also use the Ctrl-W for more detailed information about the selection.
SELECT DATABASE >>
Select a database with the Arrow Keys, or enter a name, then press Return.
----------------------- @rodan_ius_net --------- Press CTRL-W for Help --------
ics184_group1@rodan_ius_net sysmaster@rodan_ius_net
ics184_group2@rodan_ius_net sysutils@rodan_ius_net
ics184_group3@rodan_ius_net
After logging in Informix/DBAccess, you can start your work on database. Note that you can always use CTRL-C to interrupt the current operation and return to the upper level menu.
Now you need to choose an area to work. You can either create a database for yourself or select a previously existing database.
CONNECTION: Connect Disconnect Exit
Connect to a database environment.
----------- sysmaster@rodan_ius_net ------------ Press CTRL-W for Help --------
Now you are in database sysmaster.
Note that sysmaster is like the root directory,
please do not create anything in there,
just use it temporarily to create your own database.
DATABASE: Select Create Info Drop cLose Exit
Create a new database.
----------- sysmaster@rodan_ius_net ------------ Press CTRL-W for Help --------
CREATE DATABASE >>ICS184_groupX
Enter the name you want to assign to the new database, then press Return.
----------------------- @rodan_ius_net --------- Press CTRL-W for Help --------
Enter the name of your database as shown above and press Return.
Please replace "X" by your own project group number.
Every time after you login Informix/DBAccess, from the above screen you got after connecting to "rodan_ius_net", simply select your own database ICS184_groupX, you will be in your own database. Choose "Exit" option to return the the main menu and continue your work.
Databases can be considered similar to subdirectories in that
they are initially private to the user who created them.
To share a database among your group members, the creator of the database
should run the following SQL command for each member in the group other than the creator.
Follow the introduction to Using SQL Query to run the "grant" command.
grant dba to <username>;
Other group members then can access the database with their own username
and password. For this class, each group uses one database.
Please also use the command "grant connect to projects;" to add an additional user,
so that the professor and TA can access your database.
In your database, you can proceed to create and populate tables.
From the main menu, choose "Table" option.
You will see the following screen:
TABLE: Create Alter Info Drop Exit
Create a new table.
--------- ICS184_groupX@rodan_ius_net ---------- Press CTRL-W for Help --------
This is the place you do all kinds of operation on tables,
such as creating a new table, altering the schema of a table, droping a table,
and checking the information of a table.
From the main menu, choose "Query-language" option.
You will see the following screen:
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Enter new SQL statements using SQL editor.
-------- ICS184_groupX@rodan_ius_net ----------- Press CTRL-W for Help --------
This is the place you execute SQL query.
You can construct a new SQL query, make modification on the query,
and run the query.
To use SQL query,
SQL is not case sensitive(for key words). A complete set of informix SQL commands can be found in Informix Guide to SQL: Syntax and Informix Guide to SQL: Tutorial.
In the following introduction, "<>" means that you should replace what is contained in this <> including <> with your own value. For example, <table_name> should be replaced by the actual table name you want to use.
To create a simplest table, it is very easy:
Syntax: CREATE TABLE <tableName> (
< list of column definitions including column name and data type >
);
Example: CREATE TABLE test (
i integer,
s varchar(10)
);
For attributes with other data type, please refer to the
date type section of the Informix manuals.
For each column, you can also add any of the following property or column-level constraint:
Example: CREATE TABLE accounts (
acc_num integer PRIMARY KEY,
acc_code integer UNIQUE,
open_date date DEFAULT TODAY
);
You can also specify any of the following table-level constraints:
Example: CREATE TABLE customer (
ssn char(9),
name varchar(20) NOT NULL,
acct integer,
age integer,
PRIMARY KEY(ssn),
FOREIGN KEY(acct) REFERENCES accounts(acc_num),
CHECK ( age>=18 )
);
You can also use CHECK (<conditions>) to check constraints
to designate conditions that must be met before
data can be assigned to a column during an INSERT or UPDATE statement.
Example: CREATE TABLE people (
id serial PRIMARY KEY,
name varchar(20),
age int CHECK (age>0),
sex char CHECK (sex='M' OR sex='F'),
birthday date,
working boolean,
salary money,
descript text,
CHECK ( (working AND (salary IS NOT NULL)) OR
(NOT working AND salary IS NULL) )
);
To remove a table from your database, execute
Syntax: DROP TABLE <tableName>;
We suggest you execute
Example: DROP TABLE test;
Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command.
Syntax: INSERT INTO < tableName > VALUES( <list of values for all columns, in order> );
INSERT INTO < tableName > ( <list of column names> )
VALUES( <list of values for columns in the previous list, in order> );
For instance, we can insert the tuple (10, 'foobar')
into relation test by either command below:
Example: INSERT INTO test VALUES( 10, 'foobar' );
INSERT INTO test(i,s) VALUES( 10, 'foobar' );
To insert a new tuple into the table, you must make sure
not to generate duplicate values for the columns that form a key.
Load statement inserts data from a file into an existing table.
It does not overwrite existing data. You cannot add a row that has the
same key as an existing row.
The format of the data in the file should also follows the required format by Informix.
The syntax is following:
Syntax: LOAD FROM '<fileName>' DELIMITER '<delimiter>' INSERT INTO <tablename> (<column name list>);
If you don't use DELIMITER to specify your own delimiter,
LOAD statement uses "|" as the default delimiter.
The following example load tuple values stored in file test.dat
into table test:
Example: LOAD FROM 'people.dat' INSERT INTO people(name, age, sex, birthday, working, salary, descript);
Content of test.dat is:
Tom|22|M|02/12/1978|T|$56,000.00|a good guy|
Mike|40|M|11/30/1960|T|$75,000.00||
Mary|18|F|04/23/1982|F||smart student|
Description:
We can see the tuples in a relation with the command:
Syntax: SELECT * FROM <tableName> WHERE <conditions>;
For instance, after the above create and insert statements, either of the following command
Example: SELECT * FROM test where i=10;
SELECT i,s FROM test where i=10;
produces the result
I S
10 foobar
The details of Load command can be found here.
You can use CREATE VIEW statement to create a new view based on existing tables and views in the database.
Syntax: CREATE VIEW <viewName> (
<list of column names and their data types>
) AS <SELECT list of column names> FROM <tableName> ;
An example of creating view is:
Example: CREATE VIEW student ( id, name )
AS SELECT * FROM test;
An important technique for improving the performance of queries is
to create indexes.
An index on an attribute of a relation allows the database to
quickly find the tuples in the relation with a given value for the indexed attribute.
CREATE INDEX <indexName> ON <tableName> (<a list of key attributes);
For instance, we can create an index on student id in the previous student view.
CREATE INDEX sid_index ON student(id);
It may be useful if the queries involved in the student id such as the following query:
SELECT *
FROM student
WHERE id = 10;
A trigger is a database object that automatically sets off a specified set of
SQL statements when a specified event occurs.
You can use CREATE TRIGGER statement to create a trigger.
It associates SQL statements with a precipitating action on a table.
When the precipitating action occurs, the associated SQL statements are triggered.
The precipitating action and the associated action can both
be an INSERT, DELETE, or UPDATE statement.
For the associated statements, they can be performed before or after
the triggering statement executes, or for each row affected by
the triggering statement.
CREATE TRIGGER <triggerName>
[ INSERT|DELETE|UPDATE OF <> ON <tableName>
[ BEFORE|AFTER|FOR EACH ROW ] [ WHEN (<condition>) ] ( <statement> );
For instance, we have two tables: account_table and trans_table.
Whenever a new transaction occurs, i.e. a new tuple inserted in trans_table,
the corresponding balance in the account_table should be updated.
The following trigger can make sure the values in the tables integrated.
NEW is reserved for the new table after the triggering statement executed.
CREATE TABLE account_table(
account_no int,
balance float
);
create table trans_table(
account_no int,
trans_no int,
trans_amount float
);
CREATE TRIGGER tran_trigger
INSERT ON trans_table
REFERENCING NEW as new
FOR EACH ROW (
UPDATE account_table SET balance=balance+new.trans_amount
WHERE account_no = new.account_no
);
insert into account_table values (123, 0);
When a new tuple inserted into trans_table, account_table is updated too:
insert into trans_table values (123, 1, 20);
select * from account_table;
produce the following results:
account_no balance
1 20.00000000000
Dynamic SQL allows a program to form an SQL statement during execution, so that the statement can be determined by user input. The action is performed in two steps.
It uses PREPARE statement to have the database server examine the statement text
and prepare it for execution.
EXEC SQL prepare <statement id> from '<statement>';
It uses EXECUTE statement to execute the prepared statement.
EXEC SQL execute <statement id> from '<statement>';
For instance, if you want to inquire the information of some students,
you can use the following dynamic SQL for query:
EXEC SQL BEGIN DECLARE SECTION;
int sid;
char sname[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL prepare query_stud from 'select id,name from student where name=?';
EXEC SQL execute query_stud into :sid, sname using 'Mike';
printf("Student: (%d, %s)\n", sid, sname);
EXEC SQL execute query_stud into :sid, sname using 'David';
printf("Student: (%d, %s)\n", sid, sname);
The above produces the following results:
Student: (2, Mike)
Student: (9, David)
SQL statements can be embedded in the C and COBOL program. In C, all the statement is preceded by "EXEC SQL". There is a detail example in the next section.
A cursor is a special data object that represents the current state of a query. It is used for retrieving multiple resulting rows of query. It is used in 5 steps:
EXEC SQL DECLARE <cursorName> CURSOR FOR <statement>;
EXEC SQL OPEN <cursorName>;
EXEC SQL FETCH <cursorName>;
EXEC SQL CLOSE <cursorName>;
EXEC SQL FREE <cursorName>;
For instance, the following program lists the information of all students.
EXEC SQL BEGIN DECLARE SECTION;
int sid;
char sname[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE cursor_stud CURSOR FOR select id,name from student;
EXEC SQL OPEN cursor_stud;
while ( SQLCODE == 0 ) {
EXEC SQL OPEN cursor_stud INTO :sid, :sname;
if ( SQLCODE == 0 )
printf("Student (%d, %s)\n", sid, sname);
}
EXEC SQL CLOSE cursor_stud;
EXEC SQL FREE cursor_stud;
SQLCODE is set to 0 by the database if the select statement is valid,
otherwise set to 100. It is used to detect the end of data.
You can use C program with embedded SQL to do the work yourself. To use ESQL/C, follow the following steps:
The following are some example programs.
More examples can be found in /home/informix/ius/demo/esql. Some slides on using informix from other interfaces is shown here.
In your program, you should include it.h. Informix uses Sun's C++ compiler to compile the program. You can modify the Makefile by changing the program name demo to your own program name, and use make command to compile the program.
The following are some example programs.
By using JDBC to connect to the database, you can write Java program to do your project. You need set environment variable CLASSPATH like:
setenv CLASSPATH $INFORMIXDIR/lib/ifxjdbc-g.jar:$CLASSPATH
The following are some example programs.