Try to work hard !

Many times users are only interested in a subset of the results returned from an SQL query. DB2 provides a mechanism for limiting the records returned from an SQL query with the FETCH FIRST command. Using the FETCH FIRST syntax improves the performance of queries when not all results are required. Listed below are some examples explaining how to use FETCH FIRST.

Example 1: Returning the first 100 rows from a table called employee: 

select * from employee fetch first 100 rows only 

Example 2: Using the fetch first syntax with an order by clause 

select * from employee order by salary desc fetch first 10 rows only

Comment +0

 DB2 Version 9.5 for Linux, UNIX, and Windows

After executing a statement that returns one or more result sets, use one of the functions available in the ibm_db API to iterate through the returned rows of each result set. If your result set includes columns with extremely large data (such as a column defined with a BLOB or CLOB data type), you might want to retrieve the data on a column-by-column basis to avoid using too much memory in your Python process.

Before you begin

You must have a connection resource returned by either the ibm_db.exec_immediate() or ibm_db.execute() function that has one or more associated result sets.

About this task

This procedure fetches rows or columns from result sets.

Procedure

  1. Call one of the following functions to fetch a row from the result set:
    Table 1. ibm_db fetch functions
    FunctionDescription
    ibm_db.fetch_tuple()Returns a tuple, indexed by column position, representing a row in a result set. The columns are 0-indexed.
    ibm_db.fetch_assoc()Returns a dictionary, indexed by column name, representing a row in a result set.
    ibm_db.fetch_both()Returns a dictionary, indexed by both column name and position, representing a row in a result set.
    ibm_db.fetch_row()Sets the result set pointer to the next row or requested row. Use this function to iterate through a result set.
    These functions accept the following arguments:
    stmt
    A valid statement resource.
    row_number
    The number of the row that you want to retrieve from the result set. Specify a value for this optional parameter if you requested a scrollable cursor when you called the ibm_db.exec_immediate() or ibm_db.prepare() function. With the default forward-only cursor, each call to a fetch method returns the next row in the result set.
  2. Optional: If you called the ibm_db.fetch_row() function, for each iteration over the result set, call the ibm_db.result() function to retrieve the value from the specified column. You can specify the column by either passing an integer that represents the position of the column in the row (starting with 0), or a string that represents the name of column.
  3. Continue fetching rows until the fetch method returns False, which indicates that you have reached the end of the result set.

    For more information about the ibm_db API, see http://code.google.com/p/ibm-db/wiki/APIs.

Example

Example 1: Fetch rows from a result set by calling the ibm_db.fetch_both() function.

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
    print "The ID is : ",  dictionary["EMPNO"]
    print "The Name is : ", dictionary[1]
    dictionary = ibm_db.fetch_both(stmt)Copy

Example 2: Fetch rows from a result set by calling the ibm_db.fetch_tuple() function.

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
    print "The ID is : ", tuple[0]
    print "The name is : ", tuple[1]
    tuple = ibm_db.fetch_tuple(stmt)Copy

Example 3: Fetch rows from a result set by calling the ibm_db.fetch_assoc() function.

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_assoc(stmt)
while dictionary != False:
    print "The ID is : ", dictionary["EMPNO"]
    print "The name is : ", dictionary["FIRSTNME"]
    dictionary = ibm_db.fetch_assoc(stmt)Copy

Example 4: Fetch columns from a result set

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE
stmt = ibm_db.exec_immediate(conn, sql)
while ibm_db.fetch_row(stmt) != False:
    print "The Employee number is : ",  ibm_db.result(stmt, 0)
    print "The Name is : ", ibm_db.result(stmt, "NAME") Copy

What to do next

When you are ready to close the connection to the database, call the ibm_db.close() function. If you attempt to close a persistent connection created with ibm_db.pconnect(), the close request returns TRUE, and the persistent IBM® Data Server client connection remains available for the next caller.


Comment +0