Example of Fetch-Count With Server-Side Cursors - Aster Client

Teradata Aster® Client Guide

Product
Aster Client
Release Number
7.00
Published
May 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
hki1475000360386.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-2005
lifecycle
previous
Product Category
Software
The following steps are an example of how to use server-side cursors and fetch-count together:
  1. Enable server cursors by issuing the following command inside ACT (a setting of 1 turns cursors “on” and a setting of 0 means “off”):
    beehive=> \set use-server-cursors 1
  2. Set a fetch-count of 100:
    beehive=> \set fetch-count 100
  3. Issue the following SQL query:
SELECT A,B,COUNT(*) FROM lineitem GROUP BY a, b;

This will actually cause the following statements to be run:

BEGIN;
DECLARE x CURSOR FOR SELECT A,B,COUNT(*) FROM LINEITEM GROUP BY A,B;
FETCH 100 FROM x; // 100
FETCH 100 FROM x; // 200
...
...
FETCH 100 FROM x; // until all the rows have been fetched
CLOSE x;
END;

Aster Database reports all the fetched rows as the row count for this query.