Question & Answer
Why do I get error SQW03503E when I attempt to execute a dataflow? SQW03503E: "A default table space could not be found with a page size of at least '4096' that authorization ID 'DB2INST1' is allowed to use."
The SQL generated by DB2 Warehouse might create global temporary tables. Global temporary tables must be created in a user temporary table space. By default, DB2 does not create a user temporary table space, so you must create one manually.
There are typically two scenarios that can cause this error:
- The SQL Warehousing Tool (SQW) dataflow has generated SQL that uses a global temporary table. Either a user temporary table space was not defined, or the user does not have permission to use one of the existing user temporary table spaces. By default, when a database is created, a user temporary table space is not created.
- The SQW dataflow has generated SQL that uses a global temporary table or a permanent table with a page size that exceeds the page size of the table spaces available to this user. In this scenario, typically the page size in the error message is a value greater than 4096.
See the DB2 Information Center for information about table spaces. Also, the References section of this document contains additional information that may be useful when working with table spaces.
To resolve this problem, follow these steps :
- Identify the type of table that is created in the dataflow
- Determine the type of table space you need to use
- Determine the table spaces available in the database
- If an appropriate type and size of table space does not exist, create a new table space
- Determine if the appropriate permissions exist for the table space
- Grant permissions to the table space
Identify the type of table that is created in the dataflow
Before you can create the appropriate table space, you need to know the type of table that the SQW code was executing when the statement failed. The simplest way to determine this is to execute the dataflow with trace enabled. The CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE statement will be displayed before the error.
- To enable trace from the Design Studio, execute the control flow or data flow that triggered the error message. When the Flow Execution window is displayed, select the Diagnostics tab and choose Trace Level = both.
- To enable trace from the DWE Administration Console, navigate to the Manage Process page, then choose the process to be traced by clicking on the Process Name. From the Process Details page that is displayed, choose the Log and Trace tab and change the Trace Level to BOTH
Determine the type of table space you need to use
The following chart illustrates general table space usage :
Table space Type
Design Studio Property Name
Contents Description ( from list tablespaces command )
|CREATE TABLE||Regular or Large||Regular Table Space||Any Data|
|DECLARE GLOBAL TEMPORARY TABLE||User Temporary||User Temp Table Space||User Temporary data|
Determine the page size of the table space that you need to create. The error message you received will indicate the size you need. The example message listed above shows that a page size of 4096 is required. 4096 is the default page size.
Since it is possible that an existing table space can be used, you must determine which table spaces are available. There are two ways:
- In the Design Studio, you can use the Database Explorer to connect to the execution database and look at the table spaces currently created:
- Connect to the database and expand the connection until you see a folder labelled Table Spaces.
- Expand the Table Spaces to see a list of the table spaces that exist on the system
- By selecting the table space in the Database Explorer view, the Properties view will display the details for the selected table space. You can work through the table space list looking for the appropriate type of table space (see the Design Studio Property Name in the above table to see the type to look for).
For example, from the db2 command line issue the commands :
db2 connect to <database>
db2 list tablespaces
( where <database> is the execution database in the dataflow generating the error )
Create a new table space
If you have determined that a new table space is needed, then you can create the table space using the CREATE TABLESPACE command. For the full syntax of the CREATE TABLESPACE command see the CREATE TABLESPACE online reference.
For example, to create a user temporary table space with a page size of 4096 you can issue a command like:
CREATE USER TEMPORARY TABLESPACE usr_tbsp
MANAGED BY DATABASE
USING (FILE 'd:\tblspaces\user_tbsp' 5000 );
If you are using an existing table space, examine the permissions for the table space to determine who is permitted to use the table space. In DB2 9.1 or later, you can run the following query to get a list of the users and groups that are permitted to use the table space :
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTTYPE ='TABLESPACE'
Grant permissions to the table space
Depending on the user ID that is being used to execute the dataflow, you mighty need to grant permissions for the user to use the table space. For the full syntax of the GRANT TABLESPACE command see the on-line reference for GRANT TABLESPACE command.
For example, to grant permissions to an existing table space, issue the command :
GRANT USE OF TABLESPACE usr_tbsp TO USER db2inst1
- CREATE TABLESPACE command syntax
- DB2 Documentation for creating a user temporary table space
- GRANT TABLESPACE command syntax
- PRIVILEGES Administration View information for DB2 9.1
16 June 2018