MDBitz Development Resources

Back from an extended vacation and I find myself in the middle of a project needing to export data from a DB2 database. Fortunately I have decent knowledge of SQL and the basic syntax matches up but then I found myself wanting to limit the results returned. Now in T-SQL and MySQL I often would use the, LIMIT, TOP or SET ROWCOUNT commands but they aren’t available in DB2. Instead you use the FETCH FIRST command.

To put things simply to limit the number of rows returned the command is:

FETCH FIRST 10 ROWS ONLY

What this means is that your basic SELECT statement would be entered as:

SELECT * FROM mySchema.myTable WHERE TRAN_TS >= '05/01/2010' FETCH FIRST 10 ROWS ONLY

Database maintenance can be a full -time job when working within a large corporation. Cleanup or deletion of records can especially be time-consuming and error prone when dealing with large data tables with millions of records. Often developers will try to run a basic delete statement without knowing about batching or truncating the activity log. Below is a simple method for performing a batch delete.

Define you DELETE statement

The first step is to define your DELETE statement with the appropriate WHERE clause. If for example you want to delete all records before a set date then your query would look similar to:

DELETE FROM my_table WHERE action_ts < '06/01/2009'

Implementing the basic batch loop

To implement a basic loop we need to define some variables to keep track of a few variables. First we define @err to obtain the error id if an error occours, and @nCount to hold the total number of rows deleted.

DECLARE @err INT, @nCount INT
SELECT @nCount=0

Next we implement or while statement to execute or delete statement while more records are available to be deleted. In this example we will test if any rows exist matching or deletion statement.

WHILE EXISTS (SELECT * FROM my_table WHERE action_ts < '06/01/2009')
BEGIN
    ... 
END

In our while loop we will delete our records in batches by use of the TOP command that limits how many records are effected. We will be using a batch size of 10000.

DELETE TOP 10000 FROM my_table WHERE action_ts < '06/01/2009'

After our delete query we want to check if any errors occurred and if so break out of our batch loop. This is done by obtaining the current error status and if an error occurred then breaking out of the loop by the break command.

SELECT @err=@@ERROR, @nCount=@nCount+@@ROWCOUNT
IF @err=0
BEGIN
    ...
END
ELSE
BEGIN
    RAISERROR 20001 'Failed to delete date from table my_table'
    BREAK
END

Dumping the activity log and output statements

To be notified on how many rows are being deleted we can output the @nCount variable that is udpated after every deletion with the number of rows affected by the last delete statement. We can use the print statement to output the number.

PRINT '%l! rows deleted', @nCount

Also when dealing with a large amount of rows it is possible for the activity log to get full for the database. To clean up the log use the dump tran command with the truncate_only option.

DUMP TRAN my_db WITH TRUNCATE_ONLY

The Full Batch Delete Script

Putting the entire script together we end up with:

DECLARE @err INT, @nCount INT
SELECT @nCount=0
 
WHILE EXISTS (SELECT * FROM my_table WHERE action_ts < '06/01/2009')
BEGIN
 
    DELETE TOP 10000 FROM my_table WHERE action_ts < '06/01/2009'
 
    SELECT @err=@@ERROR, @nCount=@nCount+@@ROWCOUNT
    IF @err=0
        BEGIN
            DUMP TRAN my_db WITH TRUNCATE_ONLY
            PRINT '%l! rows deleted', @nCount
        END
    ELSE
        BEGIN
            RAISERROR 20001 'Failed to delete date from table my_table'
            BREAK 
        END
END

For those that worry about the extra time needed for doing the exists select clause in the while loop you could optionally use a separate variable to hold the latest row count and test for if the row count is greater than 0.

What is Pagination

Pagination is the process of displaying a subset of the data on a web page at one time. The simplest example is a google search result in that after a search is performed you are displayed with the first x results as page 1. Then utilizing the page handlers you are able to view page 2, 3, etc until there are no additional results.

Pagination and Server Side Code

When developing an application the back end is responsible for obtaining and storing the requested data to be presented to the user. There are two obtions available to developers when dealing with pagination. The first is to obtain all the data and save it in the session for which only subsets of it will be displayed depending on the page the user is viewing. While the second method is to obtain only the data that is for the current page and when a new page is requested then that data is obtained.

This article deals with the second method as the smaller data set will allow for the results to be returned to the user faster as well as having a smaller strain on the resources of the server.

Limitations of Sybase

When utilizing a Sybase database as the data warehouse for your application there are difficulties in creating this dynamic pagination as there is no LIMIT or OFFSET commands in the Transact-SQL (T-SQL) language.  In other SQL languages these commands allow you to specify how many results you want (LIMIT) and where to start returning your results from (OFFSET). To overcome this limitation we will have to make use of a Stored Procedure to manipulate data into and out of a temporary database.

Implementation of Pagination

For the purposes of this tutorial lets assume we are working with a User table created by the following statement:

CREATE TABLE T_USER (
  ID INT IDENTITY,
  USER_NAME VARCHAR(50),
  LAST_NAME VARCHAR(50),
  FIRST_NAME VARCHAR(50),
  UNIQUE CLUSTERED (ID)
)

From this table we are going to want to obtain x users starting with the y^th one. So to begin lets create a stored procedure that takes 2 parameters pageSize and offset.

CREATE PROCEDURE USER_PAGINATION
@offset INT,
@pageSize INT,
AS
BEGIN
 //--content
END

Now to perform the pagination we are going to have to perform 2 queries. The first query is going to select every user in the Users table ordered by their USER_NAME and insert them into a temp table with a new identity column that equals their order in the temp table.

SELECT ID_KEY = IDENTITY(5)
  USER_NAME,
  FIRST_NAME,
  LAST_NAME
INTO #TEMPUTBL
FROM T_USER
ORDER BY USER_NAME

The second query will then select the rows from the temp table beginning with the offset upto the offset + pageSize

SELECT *
  FROM #TEMPUTBL
  WHERE
    ID_KEY > @offset and
    ID_KEY <= (@offset + @pageSize)

Putting the whole thing together we end up with:

CREATE PROCEDURE USER_PAGINATION
@offset INT,
@pageSize INT,
AS
BEGIN
  SELECT ID_KEY = IDENTITY(5)
    USER_NAME,
    FIRST_NAME,
    LAST_NAME
  INTO #TEMPUTBL
  FROM T_USER
  ORDER BY USER_NAME
 
  SELECT *
    FROM #TEMPUTBL
    WHERE
      ID_KEY > @offset and
      ID_KEY <= (@offset + @pageSize)
END

Last step is simply to run the stored procedure passing the pageSize and the offset.

execute USER_PAGINATION 50, 25

Resources

• Transact-SQL User’s Guide