Overriding the rowset limit for a session

About this task

For commands that perform INSERT TO ... SELECT FROM or CREATE TABLE AS ... SELECT operations, the rowset limit can affect the results by limiting the number of rows that are inserted to the resulting table. If you are using these commands to create user tables, you can override the rowset limit within your user session to ensure that those queries complete with all the matching rows. This override does not change the limit for other SELECT queries, or for INSERT TO ... SELECT FROM or CTAS queries that write to external table destinations.

To override the rowset limit for INSERTS and CTAS operations in a session, complete the following table:

Procedure

  1. Open a session with the database and log in by using your database user account.
  2. Use the following command to set the session variable. 
    MY_DB.MYSCHEMA(NZUSER)=> SET ROWSETLIMIT_LEVEL=0;
SET VARIABLE
  3. Use the following command to show the status of the rowset limit for the session: 
    MY_DB.MYSCHEMA(NZUSER)=> SHOW ROWSETLIMIT_LEVEL;
NOTICE:  ROWSETLIMIT_LEVEL is off

Results

When the rowset override is enabled (rowsetlimit_level=0), keep in mind the following behaviors for your INSERT and CTAS queries:
  • A CTAS operation to a user table destination is not subject to the rowset limit.
  • A CTAS operation to an external table is subject to the rowset override.
  • An INSERT INTO <table> SELECT FROM operation, where <table> is a user table, is not subject to the rowset limit override.
  • An INSERT INTO <table> SELECT FROM operation, where <table> is an external table, is subject to the rowset limit override.

To disable the override and restore the limit to all queries, set the value of the rowsetlimit_level session variable to 1 (on).