SQW03503E - A default table space could not be found when executing a dataflow

Answer

There are typically two scenarios that can cause this error:

1. 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.
2. 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.

Solution summary

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 :

Statement Type	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.

Determine the table spaces available in the database

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:
1. Connect to the database and expand the connection until you see a folder labelled Table Spaces.
2. Expand the Table Spaces to see a list of the table spaces that exist on the system
3. 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).
• Alternatively, you can run the following command on the DB2 Command Line to list the table spaces in the database. See the DB2 Documentation for LIST TABLESPACES for more details on the syntax of the command.


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 );

Determine if the appropriate permissions exist for the table space

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

References

• 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