<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > ALTER statements >


Previous pageReturn to chapter overviewNext page

5_2_indicator Version 5.2 Feature

The ALTER STREAM lets you control data flowing through native streams, allowing you to pause streams temporarily or reset their stream clock. You can pause streams individually, or pause all streams in a schema, using any expression that refers to one or more streams.

You cannot use ALTER STREAM to control foreign streams. As of  version 5.2, ALTER STREAM commands result in "no action" if the stream is a "foreign" stream.

s-Server supports two actions on streams through the ALTER STREAM statement:




<alterstream> ::= ALTER STREAM <qualified-stream-name> | <streams-list> <alterAction>

<streams-list> ::= [stream-expr[, stream-expr ] * ]

<alterAction> ::= RESET | PAUSE | RESUME


Pausing or Resuming a Stream

You can pause native streams even if there are running SQL statements accessing the stream. Queries running on "paused" streams stop receiving any rows or punctuations. A paused stream stops "inserting" rows from running INSERT statements and exerts back-pressure on all upstream as well as downstream pipelines/applications. A paused stream remains paused until its resumed using ALTER STREAM … RESUME.

ALTER STREAM … RESUME resumes pushing data through the nexus corresponding to the stream being resumed.

ALTER STREAM … PAUSE has no effect if the stream is already paused.

Why Pause or Resume Streams ?

Often, you will want to recreate or redefine views and pumps that use native streams as sources or sinks. By pausing source and sink streams, you can make these changes and then resume pipeline streams.


The following are examples of pausing or resetting streams:

--pause stream "mystream" in schema "myschema"

ALTER STREAM "myschema"."mystream" PAUSE;

-- resume all streams in “myschema”


-- reset a given list of streams


Resetting a Stream

You can reset streams by using a statement along the following lines:

ALTER STREAM "myschema"."mystream" RESET;


You can think of native streams as analogous to a topic in message queue middleware systems such as ActiveMQ. In this sense, clients "subscribe" to a native streams by running a SQL query (a SELECT statement) on that stream. In the same way, clients publish to a native stream (which can be the same stream) using INSERT statements.

Once you create a native stream using the CREATE STREAM statement, the stream definition is saved to the catalog. The first time you run a SELECT or INSERT on the stream, this action instantiates the stream in s-Server (runtime). The runtime instance (also called a nexus) corresponding to that stream, persists in memory even if all clients cancel their INSERT or SELECT statements. s-Server maintains these streams in order to maintain key state information, in particular its "clock", or the last ROWTIME or rowtime bound emitted by the stream. s-Server does so in order to guarantee that this clock is monotonically increasing.

By using ALTER STREAM … RESET, you can "reset" the stream's clock. (Resetting removes the nexus corresponding to the native stream from s-Server's runtime.) Once you reset the stream, INSERT statements can start inserting records with rowtimes >= ROWTIME_MIN (rowtime epoch).

Why Reset Streams?

Resetting streams can help you accomplish a number of goals. Because native streams are fed by external data sources, resetting them lets you replay data from the past in order to troubleshoot or validate analytics pipelines. You might also want to do so in order to identify data quality issues.

This would mean resetting the stream and then replaying data from an external source, such as a log file or Kafka topic. (If you did not reset the stream, rows from replayed data would be rejected because they would arrive after the stream's high water mark, or clock time.)

Restrictions on Resetting Streams

A stream cannot be reset if there are SQL statements running on it. If there are INSERT or SELECT statements running on a stream, ALTER STREAM … RESET fails with the following error:

ALTER STREAM RESET failed as at least one stream, <stream-name>  is in use.



ALTER STREAM stream1, stream2, stream21, stream11 RESET;

-- or

ALTER STREAM newschema.stream2, oldschema.* RESET;

-- reset all streams in myschema


--reset stream "mystream" in current schema