Informix Dynamic Server 11.50 Fundamentals exam 555 Certification Preparation, Part 5
This content is part # of # in the series: Informix Dynamic Server 11.50 Fundamentals exam 555 Certification Preparation, Part 5
This content is part of the series:Informix Dynamic Server 11.50 Fundamentals exam 555 Certification Preparation, Part 5
Stay tuned for additional content in this series.
Before you start
This tutorial gives an overview of the Informix Dynamic Server (IDS) implementation of Structured Query Language (SQL).
About this series
This complimentary series of nine tutorials has been developed to help you prepare for the IBM Informix Dynamic Server 11.50 Fundamentals certification exam (555). This certification exam will test your knowledge of entry-level administration of IDS 11.50, including basic SQL, how to install IDS 11.50, how to create databases and database objects, security, transaction isolation, backup and recovery procedures, and data replication technologies and purposes. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.
About this tutorial
This tutorial introduces you to SQL and helps to give you a good understanding of how IDS uses SQL to manipulate data in a relational database.
This tutorial is designed to help you become familiar with:
- How to perform a query on a table
- How to manipulate data using SQL
- The concept of a transaction
- The use of stored procedures and user-defined routines
This tutorial is written for IDS database professionals whose skills and experience are at a beginning to intermediate level. You should be familiar with basic database concepts.
To complete this tutorial, you do not need a copy of IDS. However, if you have one available to use, you will definitely get more out of the tutorial. If you don't have a copy already, you can download the free trial version (see Related topics).
SQL is a language that is used to define and manipulate database
objects. You use SQL to define a database table, insert data into the
table, change the data in the table, and retrieve data from the table.
The most important use of SQL is the
SELECT statement allows you
to retrieve data from a relational database, whereas
DELETE allow you to manipulate data in the
database. Like all languages, SQL has a defined syntax and a set of
language elements. In this tutorial, the focus is on data retrieval
and data manipulation using SQL.
All the SQL examples in this tutorial are based on the stores_demo
database, which comes with IDS product. The stores_demo database
contains a set of tables that describe an imaginary business. You can
easily create and populate the stores_demo database by running the
dbaccessdemo script with the
dbaccessdemo -log. You need to have the
Informix environment variables set when running the
dbaccessdemo script. The
dbaccessdemo script is located in the bin
directory of your Informix installation.
Data Manipulation Language (DML) - Retrieving data
Retrieve data from a table using a
SELECT statement is used to retrieve
data from a table or a view. This operation is also called a query.
You need the Connect privilege to access the relational database, as
well as the Select privilege on the table or view from which
SELECT retrieves data. In its simplest
SELECT statement can be used to
retrieve all the data from a table or view. The following statement is
an example of using the
SELECT statement to
retrieve all the data from the customer table:
SELECT * FROM customer;
SELECT statement can be as simple as the
one above, or more complex if you use multiple SQL clauses and
keywords. There are many different SQL clauses available in IDS. You
will see some of the most common SQL clauses in this tutorial.
You can use the
FIRST keyword to restrict
the number of rows in a query result. The following code is an example
showing how to retrieve the first 10 rows from the customer table:
SELECT FIRST 10 * FROM customer;
To retrieve only specific columns from a table, specify a list of
column names separated by commas after the
SELECT keyword. The following example
illustrates how to retrieve data for the columns named fname and lname
from the customer table:
SELECT fname, lname FROM customer;
DISTINCT keyword with a
SELECT statement to eliminate duplicate
rows from a query result. Here is an example of eliminating duplicate
customer data from the orders table:
SELECT DISTINCT customer_num FROM orders;
You can use the
AS clause with a
SELECT statement to assign a meaningful
name to an expression or column in the
SELECT list. The following example shows
AS clause to calculate the
processing time between the order date and the shipped date from the
SELECT ship_date-order_date AS process_time FROM orders;
There are many built-in SQL functions available that you can use with
SELECT statement. Table 1 provides a
list of built-in functions that are often used with
Table 1. Common SQL functions
|COUNT||Count of the numbers of rows in the query result|
|SUM||Add the values of a numeric column|
|AVG||Average value of a numeric column|
|MAX||Maximum value of a column|
|MIN||Minimum value of a column|
|DAY||Integer that represents the day|
|MONTH||Integer that represents the month|
|YEAR||Integer that represents the year|
|WEEKDAY||Integer that represents the day of the week (0 is Sunday)|
|DATE||Returns date values|
|CURRENT||Returns current date-time value|
|TODAY||Returns system date|
|TRUNC||Returns a truncated value of an expression|
|HEX||Returns a hexadecimal value|
|ROUND||Returns a rounded value|
|LENGTH||Returns the length of a character column|
Here is an example of the
COUNT (*) function that returns
the number of rows in the customer table:
SELECT COUNT(*) FROM customer;
Filter the data returned by a query using the
WHERE clause and predicates
You can use the
WHERE clause to select
specific rows from a table or view by specifying one or more selection
criteria or search conditions. A search condition consists of one or
more predicates. A predicate specifies something about a row that is
either true or false. Consider the following when building search
- Apply arithmetic operations only to numeric data types.
- Make comparisons only among compatible data types.
- Enclose character values within quotation marks.
- Specify character values exactly as they appear in the database.
Let's take a look at a few examples that illustrate how to use the
First, an example showing how to find the names of customers from the customer table whose customer numbers are greater than 120:
SELECT fname, lname, customer_num FROM customer WHERE customer_num > 120;
Here is an example of finding the names of customers from the customer table who live in Denver and whose customer numbers are greater than 120:
SELECT fname, lname, customer_num FROM customer WHERE customer_num > 120 AND city = 'Denver';
LIKE keyword supports wild card
characters in the quoted string. The percent sign (%) is a wild card
character that represents a string of zero or more characters.
The following example shows how to find all the customer names from the customer table that start with the letter L:
SELECT fname FROM customer WHERE fname LIKE 'L%';
There are many keywords and operators that you can use with a
WHERE clause to define your search
condition. Table 2 provides a list of the most common keywords and
Table 2. List of common keywords and operators to use in a WHERE clause
|Keywords / Operators||Description|
|BETWEEN||Range of values|
|IN||Subset of values|
|LIKE||Variable text search|
|MATCHES||Variable text search|
|IS NULL||Search for NULL strings|
|IS NOT NULL||Search for non-NULL strings|
|AND/OR/NOT||Logical operators used to connect two or more conditions|
|= or ==||Equal|
|!= or <>||Does not equal|
|>=||Greater than or equal to|
|<=||Less than or equal to|
You can use the preceding keywords and operators in a
WHERE clause to create comparison-condition
queries that perform the following actions:
- Include values
- Exclude values
- Find a range of values
- Exclude a range of rows
- Find a subset of values
- Identify NULL values
Use the equal sign (=) relational operator to include rows in a
WHERE clause, as the next example
SELECT customer_num, call_code, call_dtime, res_dtime FROM cust_calls WHERE user_id = 'maryj';
This query will return all rows from cust_calls table. Here is an example of a query using the 1=1 expression:
SELECT customer_num, call_code, call_dtime, res_dtime FROM cust_calls WHERE 1=1;
<> relational operators to
exclude rows in a
Listing 1. Examples of excluding rows with the
SELECT customer_num, company, city, state FROM odin.customer WHERE state != 'CA'; SELECT customer_num, company, city, state FROM odin.customer WHERE state <> 'CA';
Finding a range of rows
Listing 2 shows two ways to specify a range of rows in a
- Using the
- Using the
Listing 2. Example of specifying a range
SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog WHERE catalog_num BETWEEN 10005 AND 10008; SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog WHERE catalog_num >= 10005 AND catalog_num <= 10008;
Excluding a range of rows
Listing 3 uses the keywords
NOT BETWEEN to
exclude rows that have the character range 94000 through 94999 in the
Listing 3. Example of excluding a range
SELECT fname, lname, city, state FROM customer WHERE zipcode NOT BETWEEN '94000' AND '94999';
Finding a subset of values
Like excluding rows, the example shown in Listing 4 assumes the use of an ANSI-compliant database. The owner qualifier is in quotation marks to preserve the case sensitivity of the literal string.
Listing 4. Example of finding a subset of values
SELECT lname, city, state, phone FROM 'Aleta'.customer WHERE state = 'AZ' OR state = 'NJ' ORDER BY lname; SELECT lname, city, state, phone FROM 'Aleta'.customer WHERE state IN ('AZ', 'NJ') ORDER BY lname;
Identifying NULL values
IS NULL or
NOT NULL keywords to check for NULL
values. A NULL value represents either the absence of data or an
unknown value. A NULL value is not the same as a zero, a blank, or an
Listing 5. Example of returning all rows that have a null paid_date value
SELECT order_num, customer_num, po_num, ship_date FROM orders WHERE paid_date IS NULL ORDER BY customer_num;
Sorting the data returned by a query using the ORDER BY clause
ORDER BY clause to sort the query
result by one or more columns. The column names that are specified in
ORDER BY clause do not have to be
specified in the Projection list. The following example illustrates
ORDER BY clause, where data is
retrieved from the customer table and sorted based on the customer
SELECT fname, lname FROM customer ORDER BY customer_num;
By default, the
ORDER BY clause sorts data
in ascending order. You can change the query result to descending
order by using the
DESC keyword in the
ORDER BY clause.
Retrieve data from more than one table using joins
You can join two or more tables to combine a query result. It is very common that required data is distributed across two or more tables. For example, a full join of two three-column tables produces a query result with six columns. A join condition specifies a relationship between at least one column from each table to be joined. Because the columns in a join condition are being compared, they must have compatible data types. Listing 6 provides an example of joining two tables, customer and orders. The column name customer_num is the relationship between tables.
Listing 6. Example of a join
SELECT a.company, b.order_num, b.order_date FROM customer a, orders b WHERE a.customer_num = b.customer_num ;
IDS allows several types of joins:
CROSS JOIN: produces a Cartesian product by joining all possible pairs of rows
INNER JOIN: produces only the common rows from tables that meet the join condition
LEFT OUTER JOIN: produces some rows of one table, and all rows of another
RIGHT OUTER JOIN: resembles
LEFT, but the roles of the two tables are reversed
FULL OUTER JOIN: produces the union of all rows from an INNER join of the two tables and of all rows of each table that have no match in the other table
The example in Listing 6 is an example of an
INNER JOIN. The
JOIN returns only rows from tables
that meet the join condition. If a row exists in one table but not the
other, it is not included in the query result. The same query can be
rewritten by explicitly specifying the
INNER JOIN in the
FROM clause, as shown in Listing 7:
Listing 7. Example of an INNER JOIN
SELECT a.company, b.order_num, b.order_date FROM customer a INNER JOIN orders b ON a.customer_num = b.customer_num ;
Listing 8 shows the results of that inner join query.
Listing 8. Results from INNER JOIN query
COMPANY ORDER_NUM ORDER_DATE Play Ball 1001 05/20/1998 All Sports Supplies 1002 05/21/1998 Play Ball 1003 05/22/1998 Watson & Son 1004 05/22/1998
The Listing 9 is an example of a
LEFT OUTER JOIN. You are using the same
data set as the previous example and an almost identical SQL
statement. The only difference is the type of join. In this join
statement, the customer table is the dominant table that appears to
the left of the keyword that begins the
LEFT OUTER JOIN returns all of the rows for
which the join condition is true and, in addition, returns all other
rows from the dominant table, and displays the corresponding values
from the subservient table as NULL. The ORDERS table is the
subservient table here.
Listing 9. Example of a
SELECT a.company, b.order_num, b.order_date FROM customer a LEFT OUTER JOIN orders b ON a.customer_num = b.customer_num ;
Listing 10 shows the results of the left outer join:
Listing 10. Query results from
LEFT OUTER JOIN
COMPANY ORDER_NUM ORDER_DATE All Sports Supplies 1002 05/21/1998 Sports Spot Phil's Sports Play Ball 1001 05/20/1998 Play Ball 1003 05/22/1998 Play Ball 1011 06/18/1998 Play Ball 1013 06/22/1998 Los Altos Sports Watson & Son 1004 05/22/1998 Watson & Son 1014 06/25/1998
Combine two or more queries into a single query using set operators
You can combine two or more queries into a single query by using the
UNION clause, the
NOT IN clause, or the
clause. Set operators process the results of the queries, eliminate
duplicates, and return the final result set.
UNION clause generates a final query
result by combining two or more other query results.
NOT EXISTS or
NOT IN clause to introduce sub-queries that
show the difference between the two sets. These clauses generate a
query result by including all rows that are returned by the first
query, but not by the second or any subsequent queries.
IN clause to introduce sub-queries that
show the intersection of two sets. These clauses generate a query
result by including only rows that are returned by all the queries.
Listing 11 is an example of a query that makes
use of the
UNION set operator. In this
example, you select the state column from the customer table and the
corresponding code column from the state table.
Listing 11. Example of combining two queries
SELECT state FROM customer WHERE customer_num BETWEEN 100 AND 125 UNION SELECT code FROM state WHERE sname LIKE 'C%';
It is not necessary for combined columns or tables to be the same. Corresponding columns in the projection clauses for the combined queries must have compatible data types, but the columns do not need to use the same column names.
Summarize query result using the
GROUP BY clause
GROUP BY clause to organize rows in
a query result. Each unique set of groups is represented by a single
row in the query result. The
clause is also used to specify what columns are to be grouped together
to provide input to column functions such as
AVG. Listing 12 shows a list of all
customers and the city name where they live from the customer table:
Listing 12. Data from the customer table
FNAME CITY Chris Bartlesville Kim Blue Island James Brighton Bob Cherry Hill Eileen Denver Raymond Los Altos Margaret Los Altos Lana Menlo Park Alfred Menlo Park Jean Mountain View George Mountain View Dick Oakland Charles Palo Alto Philip Palo Alto Fred Phoenix Frank Phoenix Cathy Princeton Anthony Redwood City Roy Redwood City Donald Redwood City Arnold Redwood City Frank Redwood City
The following example shows the use of the
BY clause to count the number of
customers who live in each city:
SELECT city, COUNT(city) TOTAL FROM customer GROUP BY city;
COUNT function yields the following rows
in the query result. You specify city as the
GROUP BY column and label the expression
column as TOTAL. A
COUNT function returns
the number of rows grouped into each new row.
Listing 13. Results from
GROUP BY expression with
CITY TOTAL Los Altos 2 Mountain View 2 Brighton 1 Palo Alto 2 Denver 1 Oakland 1 Phoenix 2 Princeton 1 Redwood City 5 Blue Island 1 Menlo Park 2 Cherry Hill 1 Bartlesville 1
Add data to a table using the
INSERT statement is used to add new rows
to a table or a view. Inserting a row into a view also inserts the row
into the table on which the view is based. There are a few different
ways you can insert data into a table. Let's take a look at some
examples of the
Here is an example of inserting a row into customer table using the
INSERT INTO customer VALUES (202,'Cerny', 'Paul', 'IBM ', '2 Main St','', 'Lenexa', 'KS',66012,'');
Here is an example of finding records from the customer table whose customer numbers are greater than 120 and inserting the same record into a temporary table named temp_cust:
SELECT * FROM customer WHERE customer_num > 120 INSERT INTO TEMP temp_cust;
Here is an example of inserting records into the customer table from a
delimited text file named customer.txt using the
FROM clause. The text file can have
one or more records.
LOAD FROM customer.txt INSERT INTO customer;
Inserting values into serial columns
A table can have a column of the serial data types (SERIAL, SERIAL8, BIGSERIAL). The database server generates values for serial columns. When you insert values, specify the value zero for the serial column. The database server generates the next actual value in sequence. Serial columns do not allow NULL values.
You can insert consecutive numbers, explicit values, or explicit values that reset the value in a SERIAL, SERIAL8, or BIGSERIAL column.
- To insert a consecutive serial value, specify a zero (0) for the
serial column in the
INSERTstatement. In this case, the database server assigns the next highest value. The value the database server generates for you is one greater than the maximum value in the column.
- To insert an explicit value, specify a non-zero value for a serial column and the database server uses the value. If the serial column is uniquely indexed or has a unique constraint, and your value duplicates one already in the table, an error results. If the serial column does not have a unique constraint, duplicate values can be inserted in a serial column. If the value is greater than the current maximum value, you will create a gap in the series.
- To create a gap in the series (that is, to reset the serial value), specify a positive value that is greater than the current maximum value in the column.
NULL values are not valid in serial columns. If you use the interactive schema editor in DB-Access to define the table, a unique index is applied automatically to a SERIAL column.
Change data in a table or view using the
UPDATE statement is used to modify the
data in a table or a view. You can specify a condition by using the
WHERE clause with the
UPDATE statement. Each row that satisfies
the conditions specified by a
is modified in the table or view. If you do not specify a
WHERE clause, all rows are modified in the
table. The following example uses the
UPDATE statement to modify the shipping
charge to $15.00 for a particular order number:
UPDATE orders SET ship_charge = 15.00 WHERE order_num = 1001;
Purge data from a table using the
DELETE statement is used to delete
entire rows of data from a table. You can specify a condition by using
WHERE clause with a
DELETE statement. Each row that satisfies
the conditions specified by the
clause is deleted from the table. If you do not specify a
WHERE clause, all rows are deleted from the
table. The following example that uses the
DELETE statement to delete from the orders
table all records that do not have an order backlog:
DELETE FROM orders WHERE backlog = 'n';
A transaction is a set of one or more SQL statements that execute as a single operation. In the relational database world, it is also referred to as a unit of work. You can think of a transaction as a sequence of modifications that must be accomplished either completely or not at all. The transaction starts before the first SQL statement is executed from a sequence of SQL statements. The transaction is complete when either all the SQL statements execute successfully, or, in the case of an error, the changes made by the SQL statements are rolled back in sequence. A transaction provides protection against unforeseen failures and offers a programmable way to escape when the program detects a logical error.
You can think of transactions as activities in IDS that are tracked by logical log files. Any changes you made within a transaction are recorded in logical log files. Consider the following before using transactions:
- Logging must be turned on for the database.
- There must be a sufficient number of logical log files configured.
Please read the "Database Logging Mode" section in the third installment of this series for detailed information on how to turn on database logging.
In a database that was not created with the
MODE ANSI option, you initiate a
transaction with a
BEGIN WORK statement,
and issue a
statement to end the transaction. You can also use the
to cancel all modifications done since
BEGIN WORK statement starts a
transaction. This statement is valid only in a database that supports
transaction logging. Each row that an
INSERT statement affects during a
transaction is locked and remains locked throughout the
COMMIT WORK statement validates and
terminates a transaction. This statement commits all modifications
made to the database from the beginning of a transaction. The
COMMIT WORK statement releases all row and
table locks imposed during the transaction.
ROLLBACK WORK statement cancels and
terminates a transaction. This statement invalidates all changes since
the beginning of the transaction by restoring the database to the
state that existed before the transaction began. The
ROLLBACK WORK statement releases all row
and table locks imposed during the transaction.
WORK keyword is optional in the
COMMIT WORK, and
ROLLBACK WORK statements.
The following example shows a transaction bound by
BEGIN WORK and
COMMIT WORK statements:
BEGIN WORK; DELETE FROM orders WHERE backlog = 'n'; INSERT INTO STATE VALUES ('QB', 'Quebec'); COMMIT WORK;
In this example, first, you are deleting a row from the orders table where the value of the backlog column is n. Then you are inserting a new row in the state table. The transaction guarantees that both operations succeed or else neither succeeds.
If you fail to issue the
statement, IDS rolls back any modifications that the transaction made
to the database when the session ends. If you do not issue a
BEGIN WORK statement to start a
transaction, each statement executes within its own transaction and is
treated as a single-statement transaction. These single-statement
transactions do not require either a
BEGIN WORK statement or a
The type of database logging defines the way you manage transactions in
your SQL statements. If the database is using ANSI logging mode, you
do not start transactions with
Transactions are implicitly started in an ANSI-compliant database. You
do use the
ROLLBACK statement to end the transaction.
A user-defined routine (UDR) is a routine that you create and register in the system catalog tables and that you invoke within an SQL statement or another routine.
A UDR can be either a function or a procedure. A function is a routine that optionally accepts a set of arguments and returns a set of values. A function can be used in SQL expressions. A procedure is a routine that optionally accepts a set of arguments and does not return any values. A procedure cannot be used in SQL expressions because it does not return a value.
A user-defined routine can be written either using Informix Stored Procedure Language (SPL) or a supported external language, such as C or Java® technology.
- Informix Stored Procedure Language (SPL) routines
- Stored Procedure Language (SPL) is part of the database server.
SPL is simple to use and requires no support outside the database
server. SPL provides flow-control extensions to SQL. An SPL
routine is a UDR that is written in SPL and SQL. The body of an
SPL routine contains SQL statements and flow-control statements
for looping and branching. SPL routines are parsed, optimized as
far as possible, and then stored in the system catalog tables in
executable format. If you have an application that perform some
SQL tasks with little or no user interaction, the routine to
perform each task can be stored on the database server. There are
a couple of significant advantages to using SPL routines:
- Using SPL routines greatly reduces processing overhead and time. Every time you execute an SQL statement, it is sent from the client to the server, and goes through a parsing and optimizing process before actually performing its operation. With a SPL routine, parsing and optimizing are part of the process of creating the routine, so it executes much faster.
- Stored procedures reduce network traffic. A complex SQL statement can execute several times throughout the day, which can slow down the network. You can reduce the network traffic significantly by putting the same SQL statement into a stored procedure. In this case, only the statement that invokes the stored procedure goes over the network.
- You can call them from triggers to extend the ability of the trigger.
- They can be embedded within SQL statements or called with
- You can pass parameters as arguments to make them dynamic.
- External language routines
- An external language routine is a UDR that is written in an
external language. The body of an external language routine
contains statements for operations such as flow control and
looping. Therefore, you must use the appropriate compilation tool
to parse and compile an external language routine into an
executable format. The database server supports UDRs written in C
and in Java technology languages.
External routines are typically used to perform operations on user-defined data types. This tutorial does not discuss external language routines in detail. Please refer to IBM Informix Database Server documentation for details.
Creating SPL routines
SPL routines are the user-defined routines written using SQL and SPL.
Before you create a stored procedure, you must make a connection to
the database. The create procedure operation performs the parsing,
optimization, and conversion to ASCII before it is actually stored in
the sysprocedures system catalog table. You can use the
statement to create a user-defined routine. If you are creating a
procedure written in the C or Java technology languages, put the
source code in a separate file and use the
CREATE PROCEDURE FROM statement.
Listing 14 is a simple example of creating a stored procedure that inserts integers into a column of a table:
Listing 14. Example of creating an SPL procedure
CREATE PROCEDURE insert_rows( num INT ) DEFINE i INTEGER; LET i = 1; WHILE i < num INSERT INTO table1 (numbers) VALUES (i); LET i = i + 1; END WHILE; END PROCEDURE;
In IDS, although you can use
CREATE PROCEDURE to write and register an
SPL routine that returns one or more values (that is, an SPL
function), it is recommended that you use
FUNCTION instead. To register an
external function, you must use
To create an SPL function that returns one or more values, start with
CREATE FUNCTION keywords and end with
END FUNCTION keywords. Listing 15 shows
how to begin and end an SPL function:
Listing 15. Example of creating an SPL function
CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15), CHAR(2), CHAR(5); -- 6 return values items DEFINE p_lname,p_fname, p_city CHAR(15); --define each routine variable DEFINE p_add CHAR(20); DEFINE p_state CHAR(2); DEFINE p_zip CHAR(5); SELECT fname, address1, city, state, zipcode INTO p_fname, p_add, p_city, p_state, p_zip FROM customer WHERE lname = lastname; RETURN p_fname, lastname, p_add, p_city, p_state, p_zip; --6 items END FUNCTION;
Informix Dynamic Server allows you to create more than one SPL routine
with the same name but with different parameters. This feature is
known as routine overloading. For example, you can create the
following SPL routine in your database with the name
Listing 16. Example of routine overloading
CREATE FUNCTION read_address (customer_id INT ) -- one argument RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), CHAR(5); -- 6 items DEFINE p_lname,p_fname,p_lastname,p_city CHAR(15); --define each routine variable DEFINE p_add CHAR(20); DEFINE p_state CHAR(2); DEFINE p_zip CHAR(5); SELECT fname, lname, address1, city, state, zipcode INTO p_fname, p_lastname, p_add, p_city, p_state, p_zip FROM customer WHERE customer_num = customer_id; RETURN p_fname, p_lastname, p_add, p_city, p_state, p_zip; --6 items END FUNCTION;
Due to routine overloading, an SPL routine might not be uniquely
identified by its name alone within IDS. However, a routine can be
uniquely identified by a specific name. A specific name
is a unique identifier that you define in the
CREATE PROCEDURE or
CREATE FUNCTION statement, in addition to
the routine name. A specific name is defined with the
SPECIFIC keyword and is unique in the
database. Two routines in the same database cannot have the same
specific name, even if they have different owners.
Listing 17 shows how to define the specific
name in a
CREATE FUNCTION statement that
read_address function. Let's
read_address_bylastame as the
specific name of the function that looks up address by lastname, and
declare the specific name
read_address_bycustid for the function that
looks up address by customer number.
Listing 17. Example of a function with a specific name
CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), CHAR(5) -- 6 items SPECIFIC read_address_bylastame; ...... . ...... . END FUNCTION;
Listing 18. Example of the same function with a different specific name
CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), CHAR(5) -- 6 items SPECIFIC read_address_bycustid; ...... . ...... . END FUNCTION;
Executing SPL routines
You can use
EXECUTE PROCEDURE or
EXECUTE FUNCTION to execute an SPL routine
or external routine. In general, it is best to use
SPL procedures and
SPL functions. A stored procedure can be called from DB-Access, from
within an SPL routine, or from an external routine. SPL routines can
execute routines written in external languages, and external language
routines can execute SPL routines.
The following example shows the execution of an SPL procedure called
EXECUTE PROCEDURE insert_rows(10);
To execute a function, you need to consider the
INTO clause to store the return value.
Listing 19 is an example that shows the execution of an SPL function
update_customer(), which takes two
arguments as input and uses the
to specify the variable where the return value is stored:
Listing 19. Example of an SPL function execution
CREATE PROCEDURE get_customer_info(cust_num INT) DEFINE lname, fname, city CHAR(15); DEFINE add CHAR(20); DEFINE state CHAR(2); DEFINE zip CHAR(5); EXECUTE FUNCTION read_address(cust_num INT) INTO fname, lastname, add, city, state, zip; END PROCEDURE;
You use the
CALL statement to execute a UDR
from within an SPL routine. The
statement is identical in behavior to the
EXECUTE PROCEDURE and
EXECUTE FUNCTION statements, but you can
CALL from within an SPL routine.
Listing 20 is an example of calling the function
read_address from the SPL routine
Listing 20. Example of calling UDR
CREATE PROCEDURE get_customer_info(cust_num INT) DEFINE lname, fname, city CHAR(15); DEFINE add CHAR(20); DEFINE state CHAR(2); DEFINE zip CHAR(5); CALL read_address(cust_num) RETURNING fname, lastname, add, city, state, zip; END PROCEDURE;
Necessary privileges for stored procedures
There are three levels of privileges associated with stored procedures:
- Database-level privilege
- Language-level privilege
- Routine-level privilege
First, you must have the Resource privilege on a database to create any kind of SPL procedures or SPL functions within that database. By default, the Usage privilege on SPL is granted to PUBLIC. Here is an example of how you would grant the Resource privilege on the database to the role named programmers:
GRANT RESOURCE TO programmers;
Second, you need the Usage privilege on the SPL language to create an
SPL procedure. The Usage privilege on the SPL language is granted to
PUBLIC by default. Only user informix, the DBA, or a user who was
granted the Usage privilege
WITH GRANT OPTION can grant the Usage
privilege on SPL to another user. Here is an example of granting the
Usage privilege on the SPL language to the role named programmers:
GRANT USAGE ON LANGUAGE SPL TO programmers;
Last thing you need is routine execution permission. When you register
a UDR, you automatically receive the Execute privilege on that
routine. The Execute privilege allows you to invoke the UDR. Here is
an example of granting the Execute privilege on the
add_numbers SPL routine to the role named
GRANT EXECUTE ON add_numbers TO programmers;
Dropping a stored procedure
You can use the
DROP PROCEDURE statement to
drop a procedure from the database. However, the only users who can
drop a procedure are the owner of the procedure and a user with DBA
privilege. You can also use the
DROP ROUTINE statement to remove a UDR from
the database. Dropping a UDR removes the text and executable versions
of the UDR from the database. Here is an example of dropping a stored
DROP PROCEDURE insert_rows;
You can also include parameter data types after the name of the
procedure to identify the procedure, if there is more than one
procedure with same name. For example, the following statements can be
used to drop the overloaded function
drop procedure read_address(INT); drop procedure read_address(CHAR);
If you have declared a specific name when you created the procedure, you can later use that name when you alter, drop, grant privileges, revoke privileges, or update statistics on that procedure.
Here is an example of dropping an overloaded function using a specific name:
drop specific procedure read_address_bylastname; drop specific procedure read_address_bycustid;
Indexes created with a function
Using functional indexes
IDS also supports using functions to create an index. Creating and
using functional indexes can significantly improve the performance of
SELECT queries. An index stores column
values in a sorted order. A functional index transforms the data in a
column and stores the derived values in sorted order.
Suppose a table separately stores the first name and last name of customers. However, the requirement is to run a query that needs to search the customer's full name (first and last name). In this scenario, you need to concatenate first name and last name together to get the desired result. Here is an example of a query that searches customers with first name starting with L and last name starting with P:
SELECT * FROM customer WHERE TRIM(fname) || " " || TRIM(lname) LIKE 'L% P%';
Performance is negatively impacted when the table is large or when many sessions issue this type of query. A way to improve the performance of this query is to create a functional index on fullname.
Listing 21. Example of a function that concatenates two columns
CREATE FUNCTION fullname ( fname CHAR(15), lname CHAR(15) ) RETURNS VARCHAR(31) WITH (NOT VARIANT); RETURN TRIM(fname) || " " || TRIM(lname); END FUNCTION;
Here is how you would create a functional index:
CREATE INDEX fullnameIndex ON customer (fullname (fname,lname) );
Now when a query on fullname is executed, the database server
can use the functional index
fullnameIndex to determine which
rows satisfy the query. IDS ensures that the functional indexes are
always consistent with the data in the table by updating them during
INSERT, UPDATE, MERGE, and DELETE operations.
This tutorial has introduced you to Structured Query Language (SQL) and to some of the ways that IDS uses SQL to manipulate data in a relational database. It also covered the fundamentals of SQL, including SQL language elements, Data Manipulation Language (DML), SQL procedures, and user-defined functions.
- "IBM Informix Guide to SQL: Tutorial" (IBM, 2007): Get an introduction to the Informix implementation of SQL, including SQL statements, data types, and system catalog tables that provide information about database structures.
- developerWorks Informix zone: Get the resources you need to advance your skills in the Informix arena.
- "The IDS Detective Game" (developerWorks, April 2008): Learn or teach the basics of Informix Dynamic Server (IDS) and relational databases with an interactive game called "The IDS Detective Game".
- IDS roadmap for administrators, developers, and end users: Find resources for all aspects of IDS—planning, installing, configuring, administering, tuning, monitoring, and more.
- Informix library: Learn more details about IDS from the online manuals or the IDS Information Center.
- IBM Informix Dynamic Server 11.50 Information Center: Find information that you need to use the IDS family of products and features.
- Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.